Sohail Hasan wrote:
Egor Egorov wrote:
Sohail Hasan <[EMAIL PROTECTED]> wrote:I tried to GRANT only SELECT rights by using the below mentioned syntax but it is giving error:
I have created a database and created a user with select rights to that database, there is a table name xyz in that database that I don't want that user to view, that's why i want to revoke that select rights from only that table, can anyone tell me what syntax of revoke would be used here.
You can't revoke SELECT privilege from the table if you grant privileges on the database.
You can grant SELECT privilege on each table that you want to be visible to user instead.
GRANT SELECT ON product cmstemp.* TO autobot1 IDENTIFIED BY 'autobot1';
Where product is the tablename and cmstemp is the DB name.
shasan
No doubty a syntax error, as you have it wrong. You need "cmstemp.product" where you have "product cmstemp.*". "cmstemp.*" means all tables in db cmstemp. Take another look at the manual, <http://www.mysql.com/doc/en/GRANT.html>. You can "GRANT SELECT ON *.*" to grant select permission to all tables in all dbs, "GRANT SELECT ON cmstemp.*" to grant select permission on all tables in the cmstemp db, or you can "GRANT SELECT ON cmstemp.product" to grant select permission on just the product table in the cmstemp db.
As I understand it, you want autobot1 to have select rights to all tables in cmstemp except table xyz. You could revoke autobot1's select access to cmstemp.*, then grant select on cmstemp.table_name for each table in cmstemp except xyz, as Egor said.
Alternatively, you could move table xyz to a separate db, then give autobot1 select privileges in cmstemp.* but not the separate db. Of course, while that makes the autobot1 GRANT setup trivial, it may complicate other matters, as statements which involve tables in cmstemp and table xyz would then have to explicitly name the separate db (new_db.xyz). You'll have to judge for yourself whether that's worth it. Personally, I probably wouldn't do this unless it made logical sense for reasons other than just simplifying my GRANT setup.
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
