1

I am using MySQL 5.5.42 in Linux.

Could I grant SELECT to User1 on all tables in Database1 except Table1 ?

This is because Table1 contain sensitive data and User1 is not allow to read.

For other users, I can easily grant select on Database1.*

For User1, I have to grant select on Dataabse1.Table2, Table3, Table4, Table5, ...

This is quite a trouble, especially when adding new tables to Database1.

Is there any other quicker method ?

Many thanks.

Alvin SIU

Alvin SIU
  • 1,022
  • 10
  • 28
  • possible duplicate of [MySQL grant all privileges to database except one table](http://stackoverflow.com/questions/6288554/mysql-grant-all-privileges-to-database-except-one-table) – Excel Aug 31 '15 at 07:51

1 Answers1

1

You can try like this:

SELECT CONCAT("GRANT SELECT ON db.", yourtable, " TO user@localhost;")
FROM information_schema.TABLES
WHERE table_schema = "databasename" AND yourtable <> "excepttable";
Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331