snippetsqlMinor
How to grant access for multiple tables to a user in MySQL DB?
Viewed 0 times
tablesusergrantmysqlformultiplehowaccess
Problem
I have mysql db named "greats" with four tables as below
Here i have decided to create an mysql user "test" and i want to give the access only for specific table of the db "greatstat".
So, May i know how to grant specific table perm for an user in MYSQL ?
+---------------------+
| Tables_in_greatstat |
+---------------------+
| log |
| sitedet1 |
| siteindex |
| user |
| whois1 |
+---------------------+Here i have decided to create an mysql user "test" and i want to give the access only for specific table of the db "greatstat".
So, May i know how to grant specific table perm for an user in MYSQL ?
Solution
First create the user
To grant only SELECT privilege to the siteindex table in the greatstat database
To grant only SELECT privilege to the whois1 table in the greatstat database
To grant all privileges to every table in the greatstat database
Please read the MySQL Documentation on the GRANT command.
GRANT USAGE ON *.* TO test@'localhost' IDENTIFIED BY 'whateverpassword';To grant only SELECT privilege to the siteindex table in the greatstat database
GRANT SELECT ON greatstat.siteindex TO test@'localhost';To grant only SELECT privilege to the whois1 table in the greatstat database
GRANT SELECT ON greatstat.whois1 TO test@'localhost';To grant all privileges to every table in the greatstat database
GRANT ALL PRIVILEGES ON greatstat.* TO test@'localhost';Please read the MySQL Documentation on the GRANT command.
Code Snippets
GRANT USAGE ON *.* TO test@'localhost' IDENTIFIED BY 'whateverpassword';GRANT SELECT ON greatstat.siteindex TO test@'localhost';GRANT SELECT ON greatstat.whois1 TO test@'localhost';GRANT ALL PRIVILEGES ON greatstat.* TO test@'localhost';Context
StackExchange Database Administrators Q#15893, answer score: 9
Revisions (0)
No revisions yet.