Granting and Revoking Privileges Mysql Server

Privilege Management in MySQL  Server

Identify what privileges do you want to grant and on what database and follow below steps to grant

Here I am granting privileges for database anand to test

grant create on anand.* to test;

Query OK, 0 rows affected (0.00 sec)

create table help (id int);

Query OK, 0 rows affected (0.07 sec)

select * from help;

ERROR 1142 (42000): SELECT command denied to user 'test'@'localhost' for table 'help'

grant select on anand.help to test;

Query OK, 0 rows affected (0.00 sec)

select * from help;

Empty set (0.00 sec)

insert into help values (10);

ERROR 1142 (42000): INSERT command denied to user 'test'@'localhost' for table 'help'

grant insert on anand.help to test;

Query OK, 0 rows affected (0.00 sec)

insert into help values (10);

Query OK, 1 row affected (0.00 sec)

select * from help;
+------+
| id   |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

If you want to revoke the privilege from an user you can do so as mentioned below

REVOKE select on anand.help from test;

Query OK, 0 rows affected (0.02 sec)

select * from help;

ERROR 1142 (42000): SELECT command denied to user 'test'@'localhost' for table 'help'

Althoug you should avoid any such grant, however you can grant the privleges with grant option which let the grantee authorized to futher delegate the granted privileges


grant all on anand.* to test with grant Option;

Query OK, 0 rows affected (0.00 sec)