Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2012
    Posts
    3

    Unanswered: Grant permission

    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.

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Jan 2012
    Posts
    3
    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.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2004
    Posts
    480
    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.

  6. #6
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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';
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •