HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

How to grant access for multiple tables to a user in MySQL DB?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
tablesusergrantmysqlformultiplehowaccess

Problem

I have mysql db named "greats" with four tables as below

+---------------------+
| 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

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.