Hi, The thing is, we cannot dynamically pass columnnames to GRANT or REVOKE statements through procedures from mysql.
Hence invoke it through script. like the one: create procedure gg(IN c varchar(20)) BEGIN declare cnt int; declare i int default 1; declare col_name varchar(30); select count(*) into cnt from information_schema.columns where table_schema="the_base" and table_name='t100'; select cnt; while (i<=cnt) do select column_name into col_name from information_schema.columns where table_schema='the_base' and table_name='t100' and ordinal_position=i; select col_name; grant select(col_name) on forum.catagory to 'xx'@localhost identified by 'mysql'; If col_name = c then revoke select(col_name) on forum.catagory from 'xx'@localhost identified by 'mysql'; end if; set i=i+1; end while; end; mysql> call g(hide_this_col); Thanks ViSolve DB Team. ----- Original Message ----- From: "Gilles MISSONNIER" <[EMAIL PROTECTED]> To: <mysql@lists.mysql.com> Sent: Wednesday, February 07, 2007 9:26 PM Subject: Re: revoke SELECT on a column [ MySQL 4.1 ] + column privileges > hello, > first thanks to ViSolve DB Team, > and since then, my question turns out to be : > in the base that contains a table of 100 columns, I want to disable SELECT > on only 1 column "hide_this", > how to apply column privileges using a loop in mysql, that could do : > ____ > for each column in the_base.t100 where column_name is NOT hide_this > do > GRANT SELECT(column_name_n) ON the_base.t100 to 'a_user'@'localhost' > identified by 'a_passwd'; > done > ---- > > Or should I build a script to create sql commands for that ? > > thanks, > > > >> >> You have applied TABLE level GRANT PRIVILEGES and tried to REVOKE that with >> COLUMN PRIVILEGES. Hence the error. >> >> To Fix it, apply column privileges --- >> >> mysql> GRANT SELECT(hide_this) ON the_base.t100 to 'a_user'@'localhost' >> identified by 'a_passwd'; >> >> mysql> select * from information_schema.column_privileges; >> >> mysql> REVOKE SELECT(hide_this) ON the_base.t100 from 'a_user'@'localhost' >> identified by 'a_passwd'; >> >> Note: Always TABLE PRIVILEGES override COLUMN PRIVILEGES >> >> >> Thanks >> ViSolve DB Team >> >> ----- Original Message ----- From: "Gilles MISSONNIER" <[EMAIL PROTECTED]> >> To: <mysql@lists.mysql.com> >> Sent: Tuesday, February 06, 2007 11:05 PM >> Subject: revoke SELECT on a column [ MySQL 4.1 ] >> >> >> Hello, >> In a table [say t100], having 100 columns, >> I want to allow the select on all columns but 1. >> >> I tried to do this by granting all columns in the table t100, of the base, >> then revoke SELECT on the column "hide_this", >> but this doesn't work. >> >> >> mysql> GRANT SELECT ON the_base.t100 to 'a_user'@'localhost' >> identified by 'a_passwd'; >> >> mysql> revoke SELECT (hide_this) on the_base.t100 from >> 'a_user'@'localhost'; >> ERROR 1147 (42000): There is no such grant defined for user 'a_user' on >> host 'localhost' on table 'current' >> >> >> Is there a turn around, or should grant the select on the 99 other columns > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
