In this article we wre going to learn, how to grant privileges to users.
MYSQL SYNTAX :
GRANT [permission_type] ON [database_name].[table_name] TO [username]@’localhost’;
Example 1 :
GRANT CREATE ON MY_DATABASE.* TO ‘sree’@’localhost’;
Above query allows the user to create tables or databsaes in ‘MY_DATABASE’ .
Example 2 :
GRANT ALL PRIVILEGES ON * . * TO ‘sree’@’localhost’;
Above query assigns all privileges (creating table , inserting data , update data, delete data …..) to user ‘sree’ on all the tables of all databases. ‘*’ (asterisks) symbol means all.
|CREATE|| allows the user to create tables or databases|
|DROP|| allows the user to delete tables or databases|
|DELETE|| allows the user to delete rows from tables|
|INSERT|| allows the user to insert rows into tables|
|SELECT|| allows the user to read/view the data from tables |
|UPDATE|| allows the user to update table rows|
|GRANT OPTION|| allows the user to grant or remove other users’ privileges|
|ALL PRIVILEGES|| allows the user all access to a specified database (if no database is specified, across the system)|
Create a role:
CREATE ROLE MY_ROLE;
Assign privileges to role :
GRANT CREATE TABLE,
TO MY_ROLE ;
Assign role to user
GRANT MY_ROLE TO sree ;
CREATE TABLE – Enables a user to create a table owned by that user.
DROP TABLE – Enables a user to drop a table owned by that user.
ALTER TABLE – Enables a user to alter a table owned by that user.