enchance enchance - 1 year ago 71
MySQL Question

Grant user access to limited number of tables in MySQL

I'm running some tests and I'm trying to see if I can create a user with access to only 1 or 2 tables in my db. Does anyone know how this is done? My code below fails:

GRANT SELECT ON testdb.fruits, testdb.sports TO [email protected] IDENTIFIED BY 'pass';


The error says I have an error in my syntax.

Answer Source

Run them as two individual GRANT statements:

GRANT SELECT ON testdb.fruits TO [email protected] IDENTIFIED BY 'pass';
GRANT SELECT ON testdb.sports TO [email protected] IDENTIFIED BY 'pass';

The MySQL GRANT syntax only permits one object in the priv_level position:, though it may use a * as a wildcard:

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_specification [, user_specification] ...
    [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
    [WITH with_option ...]

object_type:
    TABLE
  | FUNCTION
  | PROCEDURE

priv_level:
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name

The part below does not appear to work on MySQL 5.5. How to "subtract" privileges in MySQL addresses why.

To grant SELECT on all tables then selectively revoke, you could do:

GRANT SELECT ON testdb.* TO [email protected] IDENTIFIED BY 'pass';
REVOKE ALL PRIVILEGES ON testdb.tblname FROM [email protected];

This seems to be an odd method though, and I think I would individually GRANT rather than individually REVOKE.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download