Hi,
I have given all the permission to a user say 'apoorv' on one of the DB say 'db1'.
I need to restrict the user to access some tables on db1.
I am using MySql Version - 5.1.50
Please suggest.

If you have all permissions to this user on db1, now you need to revoke access on particular tables. Do you want to revoke all permissions on a table for example, insert, update or delete but allow select's?

Have a look at REVOKE. Here is an example:

REVOKE INSERT,UPDATE,DELETE ON db1.<tablename> FROM <user>;

Where tablename and user should be replaced with your username and tablename.

You will need to do this for each table/permission you want to revoke.

Hi Ronan, thanks for reply.

we have 1000 tables in db1database, and want to restrict only 20-30 tables for user.

I want to revoke all the permission on some tables ( select permission also).

It is very difficult to give rights on all tables (1000 tables) one by one and revoke it.

plz help.

so write a script
and then modify that script and run it against MYSQL db.
you could set up that script using a front end language, and then run it agsin as many users as you require.

one thing I find lacking in the MySQL security model is the concept of user / work groups. ie permissions are assigned by groups and users are assigned to groups and can be a member of more than one group. however that may have changed with more recent MySQL implementations

Note that you don't have to give permissions one table at a time. you can give permission to that user for ALL tables in that database and then just use REVOKE to remove their permissions from the tables you don't want them to have access to.

just so it is clear you aren't adding them one by one to 1000 tables.

If you use the information_schema database you get all the information about each of the various databases and tables. So you could generate the SQL statements with this:

SELECT CONCAT('GRANT ALL ON ', TABLE_SCHEMA, '.', TABLE_NAME, ' TO user;') grants FROM TABLES WHERE TABLE_SCHEMA = 'db';