In this article we wre going to learn, how to grant privileges to users.
MYSQL SYNTAX :
Example 2 :
Permission_types :
ORACLE SYNTAX:
Create a role:
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.
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.
Permission_types :
| Permission_type | Description |
|---|---|
| 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) |
ORACLE SYNTAX:
Create a role:
CREATE ROLE MY_ROLE;
Assign privileges to role :
GRANT CREATE TABLE,
ALTER TABLE,
DROP 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.