Grant user access to limited number of tables in MySQL

Question

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 joe@localhost IDENTIFIED BY 'pass';

The error says I have an error in my syntax.

score 78 · Accepted Answer · edited May 23 '17 at 10:31

78

Run them as two individual GRANT statements:

GRANT SELECT ON testdb.fruits TO joe@localhost IDENTIFIED BY 'pass';
GRANT SELECT ON testdb.sports TO joe@localhost 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 joe@localhost IDENTIFIED BY 'pass';
REVOKE ALL PRIVILEGES ON testdb.tblname FROM joe@localhost;

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

edited May 23 '17 at 10:31

Community

1
1

answered Dec 03 '11 at 16:58

Michael Berkowski

267,341
46
444
390

Interesting. Can MySQL also remove access to specific tables? That way I can assign `testdb.*` then just remove accesss to the tables I don't want them accessing. – enchance Dec 03 '11 at 17:01
@enchance Yes, see addition above. – Michael Berkowski Dec 03 '11 at 17:03
GRANT SELECT ON testdb.* TO joe@localhost IDENTIFIED BY 'pass'; REVOKE ALL PRIVILEGES ON testdb.tblname FROM joe@localhost; The revoke statement will not work. It will give the following error "There is no such grant defined for user 'joe' on host 'localhost' on table 'tblname'" – Rahul Nov 27 '14 at 14:09
1

@Rahul Indeed, I cannot make it work either with MariaDB 5.5. I wonder if this worked correctly back in 2011, when I would probably have tested it on 5.0, and no one has commented before that it doesn't work. I'm going to add a note above, and link to [this other question](http://stackoverflow.com/questions/8131849/how-to-subtract-privileges-in-mysql) – Michael Berkowski Nov 27 '14 at 14:18

score 6 · Answer 2 · answered Aug 16 '17 at 20:26

You can use the mysql.tables_priv table directly:

INSERT INTO mysql.tables_priv (`Host`, `Db`, `User`, `Table_name`, `Grantor`, `Table_priv`)
VALUES
('%', DATABASE(), 'someuser', 'mytable1', CURRENT_USER, 'Select,Insert,Update,Delete'),
('%', DATABASE(), 'someuser', 'mytable2', CURRENT_USER, 'Select,Insert,Update,Delete')

After a manual update to these tables, you will need to explicitly run FLUSH PRIVILEGES query to tell MySQL to update its permissions cache (not required when using GRANT)

Grant user access to limited number of tables in MySQL

2 Answers2