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.
mysqlGRANT SELECT N the_base.t100 to 'a_user'@'localhost'
identified by 'a_passwd';
mysqlrevoke SELECT (hide_this) on the_base.t100 from
'a_user'@'localhost';
ERRR 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
?
regards,
_--_
Gilles Missonnier
IAP - gimi (AT) iap (DOT) fr
01 44 32 81 36

Hi,

No

You have applied TABLE level GRANT PRIVILEGES and tried to REVKE that with
CLUMN PRIVILEGES. Hence the error.

To Fix it, apply column privileges

mysqlGRANT SELECT(hide_this) N the_base.t100 to 'a_user'@'localhost'
identified by 'a_passwd';

mysqlselect * from ;

mysqlREVKE SELECT(hide_this) N the_base.t100 from 'a_user'@'localhost'
identified by 'a_passwd';

Note: Always TABLE PRIVILEGES override CLUMN PRIVILEGES

Thanks
ViSolve DB Team

Message
From: "Gilles MISSNNIER" <gimi (AT) iap (DOT) fr>
To: <mysql (AT) lists (DOT) 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.

mysqlGRANT SELECT N the_base.t100 to 'a_user'@'localhost'
identified by 'a_passwd';

mysqlrevoke SELECT (hide_this) on the_base.t100 from
'a_user'@'localhost';
ERRR 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
?

regards,

_--_
Gilles Missonnier
IAP - gimi (AT) iap (DOT) fr
01 44 32 81 36

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 NT hide_this
do
GRANT SELECT(column_name_n) N the_base.t100 to 'a_user'@'localhost'
identified by 'a_passwd';
done

should I build a script to create sql commands for that ?

thanks,

You have applied TABLE level GRANT PRIVILEGES and tried to REVKE that with
CLUMN PRIVILEGES. Hence the error.

To Fix it, apply column privileges

mysqlGRANT SELECT(hide_this) N the_base.t100 to 'a_user'@'localhost'
identified by 'a_passwd';

mysqlselect * from ;

mysqlREVKE SELECT(hide_this) N the_base.t100 from 'a_user'@'localhost'
identified by 'a_passwd';

Note: Always TABLE PRIVILEGES override CLUMN PRIVILEGES
--
Thanks
ViSolve DB Team

Message From: "Gilles MISSNNIER" <gimi (AT) iap (DOT) fr>
To: <mysql (AT) lists (DOT) 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.
--
mysqlGRANT SELECT N the_base.t100 to 'a_user'@'localhost'
identified by 'a_passwd';

mysqlrevoke SELECT (hide_this) on the_base.t100 from
'a_user'@'localhost';
ERRR 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

Hi,

The thing is,
we cannot dynamically pass columnnames to GRANT or REVKE 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;
mysqlcall g(hide_this_col);

Thanks
ViSolve DB Team.
Message
From: "Gilles MISSNNIER" <gimi (AT) iap (DOT) fr>
To: <mysql (AT) lists (DOT) 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 NT hide_this
do
GRANT SELECT(column_name_n) N the_base.t100 to 'a_user'@'localhost'
identified by 'a_passwd';
done

should I build a script to create sql commands for that ?

thanks,


>>
>You have applied TABLE level GRANT PRIVILEGES and tried to REVKE that with
>CLUMN PRIVILEGES. Hence the error.
>>
>To Fix it, apply column privileges
>>
>mysqlGRANT SELECT(hide_this) N the_base.t100 to 'a_user'@'localhost'
>identified by 'a_passwd';
>>
>mysqlselect * from ;
>>
>mysqlREVKE SELECT(hide_this) N the_base.t100 from 'a_user'@'localhost'
>identified by 'a_passwd';
>>
>Note: Always TABLE PRIVILEGES override CLUMN PRIVILEGES
>>
>>
>Thanks
>ViSolve DB Team
>>
>Message From: "Gilles MISSNNIER" <gimi (AT) iap (DOT) fr>
>To: <mysql (AT) lists (DOT) 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.
>>
>>
>mysqlGRANT SELECT N the_base.t100 to 'a_user'@'localhost'
>identified by 'a_passwd';
>>
>mysqlrevoke SELECT (hide_this) on the_base.t100 from
>'a_user'@'localhost';
>ERRR 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

Hello,
thanks again to ViSolve DB Team.

so, it turns out that for MySQL4.1, il will have to built a sql file,
built with script (shell or perl).
[ note that so far I use MySQL 4.1 on production
server, and I only test features on MySQL 5 on an other machine linux
Debian].

at last, my LAST QUESTIN concerns column "Comment"

In MySQL 5, I can do :
mysqlselect column_name,CLUMN_CMMENT from information_schema.columns
where table_schema="a_base" and table_name='a_tab';

in MySQL 4.1 , the "equivalent" would be :
mysqlselect distinct Column_name from columns_priv
where Db='a_base' and Table_name='a_tab';

AND THERE IS N CMMENT.

but, from the page :

it seems that one could use "Comment"

mysqlSHW FULL CLUMNS FRM a_tab;
displays :
Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |

I cannot find how to set these "Comment" ?

An idea ?

thanks.
-Gilles-

Thu, 8 Feb 2007, ViSolve DB Team wrote:

Hi,

The thing is,
we cannot dynamically pass columnnames to GRANT or REVKE statements
through procedures from mysql.

Hi,

The thing is you want to add "comments" to column, which is presently null,
as per SHW FULL CLUMNS FRM <table>;

Then add " CMMENT 'your comment for the column name' ", for the columns
you want comments.

For Instance,

mysqlcreate table test
(topic varchar(10)default null CMMENT 'topic for the forum',
title varchar(10) default null CMMENT 'title for the forum
topic');

mysqlshow full columns from test\G
1. row
Field: topic
Type: varchar(10)
Collation: latin1_swedish_ci
Null: YES
Key:
Default: NULL
Extra:
Privileges: select,insert,update,references
Comment: topic for the forum
2. row
Field: title
Type: varchar(10)
Collation: latin1_swedish_ci
Null: YES
Key:
Default: NULL
Extra:
Privileges: select,insert,update,references
Comment: title for the forum topic
2 rows in set (0.01 sec)

Hope Useful ! and Welcome for your interest.

Thanks
ViSolve DB Team.
Message
From: "Gilles MISSNNIER" <gimi (AT) iap (DOT) fr>
To: "ViSolve DB Team" <mysqlsupport (AT) visolve (DOT) com>
Cc: <mysql (AT) lists (DOT) mysql.com>
Sent: Friday, February 09, 2007 6:54 AM
Subject: Re: revoke SELECT on a column [ MySQL 4.1 ] + column "Comment"

Hello,
thanks again to ViSolve DB Team.

so, it turns out that for MySQL4.1, il will have to built a sql file,
built with script (shell or perl).
[ note that so far I use MySQL 4.1 on production server, and I only test
features on MySQL 5 on an other machine linux Debian].
--
at last, my LAST QUESTIN concerns column "Comment"

In MySQL 5, I can do :
mysqlselect column_name,CLUMN_CMMENT from information_schema.columns
where table_schema="a_base" and table_name='a_tab';
--
in MySQL 4.1 , the "equivalent" would be :
mysqlselect distinct Column_name from columns_priv
where Db='a_base' and Table_name='a_tab';

AND THERE IS N CMMENT.

but, from the page :

it seems that one could use "Comment"

mysqlSHW FULL CLUMNS FRM a_tab;
displays :
Field | Type | Collation | Null | Key | Default | Extra | Privileges |
Comment |
--
I cannot find how to set these "Comment" ?

An idea ?

thanks.

-Gilles-
--
Thu, 8 Feb 2007, ViSolve DB Team wrote:
>
>Hi,
>>
>The thing is,
>we cannot dynamically pass columnnames to GRANT or REVKE statements
>through procedures from mysql.
>>