CODEWITHSUNDEEP
mysql
Alvin SIU
Alvin SIU

Reputation: 1042

Grant select to all tables except Table1

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

Upvotes: 1

Views: 2626

Answers (1)

Rahul Tripathi
Rahul Tripathi

Reputation: 172628

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";

Upvotes: 1

Related Questions