You can't login as testuser and try to give yourself privileges, it just doesn't work.
GRANT SELECT ON DATABASE.CHECK_SBIN TO testuser IDENTIFIED BY 'password' Put an entry into mysql.user, with the host set to '%' [Any host, are you sure that's what you want?] and all privileges set to 'N'. It also put an entry into mysql.tables_priv (testuser@'%') giving testuser Select privileges on the table DATABASE.CHECK_SBIN [Note, this gives testuser select privileges to the whole table, so it doesn't even check column level Select privileges] GRANT SELECT (id, jobid) ON DATABASE.CHECK_SBIN TO testuser Added the colum,n Select privilege to mysql.tables_priv.Column_priv and add an entry into mysql.columns_priv for testuser@'%' giving access to the specified fields. Since the testuser has Select privileges on the whole table, you get all the columns. So all your troubles came from running the first query, giving testuser Full select privileges on the table. running this query as root should fix your problem: REVOKE SELECT ON DATABASE.CHECK_SBIN FROM testuser; if that doesn't work you might try unsetting the Select privilege FROM mysql.tables_priv.Table_priv WHERE User='testuser' AND Host='%' Chris -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 6:56 PM To: [EMAIL PROTECTED] Subject: RE: GRANT TO SELECTED COLUMNS Hi Chris/Lists, In fact, I was able to login using both ways, here's what I did. 1. mysql -u testuser -ptestuser -hlocalhost DATABASE >> OK 2. In the mysql prompt, I issue the query 'SELECT * FROM CHECK_SBIN' >> Access denied ** This is were I'm getting the error Access denied for user 'testuser' Thanks, Richard "Chris" <[EMAIL PROTECTED]> 13/11/2003 10:34 AM To: "Mysql List" <[EMAIL PROTECTED]> cc: (bcc: BORNAY Richard/Engr/STATS/ST Group) Subject: RE: GRANT TO SELECTED COLUMNS That second query, byt itself, should have done it, were you logged in as testuser when you tried to run it? Chris -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 5:26 PM To: [EMAIL PROTECTED] Subject: GRANT TO SELECTED COLUMNS Hello List, I have a table named CHECK_SBIN with fields id,jobid,filename,sbin,count, then I would like to create an account lets say testuser with SELECT privileges only to a specific columns. In this case I would like to open only id and jobid to 'testuser'. I have already created an account to testuser in two ways using the commands below. 1. Provide SELECT privileges first on the CHECK_SBIN table - GRANT SELECT ON DATABASE.CHECK_SBIN TO testuser IDENTIFIED BY 'password' >> OK Then issue another GRANT statement for the specific columns - GRANT SELECT (id, jobid) ON DATABASE.CHECK_SBIN TO testuser >> OK Comments: I was able to access mysql using the account and seeing only CHECK_SBIN but when I do 'SELECT * FROM CHECK_SBIN' it shows me all the columns in the table. 2. Use only the second SQL - GRANT SELECT (id, jobid) ON DATABASE.CHECK_SBIN TO testuser >> OK Comments: An error occured 'Access denied for testuser'. Note: The grant tables are all checked correctly. System: MySQL 4.0.13 Solaris 8 I checked the manual and mailing list archive and was not successful in finding any related matter to my problem. Any help would be appreciated. Thanks, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
