GRANT command in SQL is used grant one or more privileges to a database user. The basic syntax for GRANT statement in a SQL is given below.
GRANT privilege_name
ON object_name
TO {user_name |PUBLIC |role_name}
[WITH GRANT OPTION];
- privilege_name: is the privilege provided to the user. The access right for a user includes ALL, SELECT and EXECUTE.
- Object_name: is the name of database objects like SEQUENCE, PROC, STORED, TABLE and VIEW.
- user_name is the name of the user for whom the permission is granted.
- role_name is the name of role played by the user to whom the privilege is granted.
- PUBLIC is used to provide access to all the users present in the database.
- ROLES are defined as set of privileges which are grouped together.
- WITH GRANT OPTION will permit the user to transfer their privilege to other users.
Read: SQL create User quesry
Example for MySQL Grant
Consider a an account is created using GRANT command to provide privileges to other users. The following MySQL codes are used to create account and grant permission to other users.
CREATE USER ‘Trinity@host’ IDENTIFIED BY ‘Pass1’;
GRANT ALL ON *.* TO ‘Trinity@host’ WITH GRANT OPTION;
The clause ON *.* will provide access to all tables and objects present in the database. Only “host” database server will be allowed to connect the user “Trinity”, this process will secure MySQL server.
The following SQL query is used to create a user who can access a sample database and connect with any host.
CREATE USER ‘Smith’@’%’ IDENTIFIED BY ‘Pass2’;
GRANT ALL model.* TO ‘Smith’@’%’ WITH GRANT OPTION;
GRANT command is used to provide several privileges to the user. The following are the privileges used in the MySQL database.
| Privileges | Description |
| ALL | All level Privileges are provided except the GRANT option |
| ALTER | Privileges for using the ALTER TABLE is given |
| ALTER ROUTINE | This allows the user to change or can drop the saved routine |
| CREATE | This privilege for creating the database and a table is provided |
| CREATE ROUTINE | Using this permission to create a saved routine is allowed |
| CREATE TABLESPACE | This allows the user to create, drop or alter tablespace and log file groups |
| CREATE TEMPORARY TABLES | Privilege granted to the user to create a temporary table. |
| CREATE USER | Access to statements like DROP USER, REVOKE ALL PRIVILEGES, RENAME USER and CREATE USER are provided. |
| CREATE VIEW | Access to creating or modifying a view is given. |
| DELETE | Privilege to DELETE statement is given. |
| DROP | Permission to DROP view, table and database is provided. |
| EVENT | Permission to schedule events in event scheduler is provided. |
| EXECUTE | Privilege to execute stored routines is provided to the user. |
| FILE | The user is provided with privilege to read any file in the database directory. |
| GRANT OPTION | Permits the users to grant or revoke privileges of other user accounts. |
| INDEX | Access to create or remove index is given. |
| INSERT | The user is provided with privilege to use INSERT Statement. |
| LOCK TABLES | Permission to lock tables for which the user have SELECT privilege |
| PROCESS | Privilege to see all the process is given to the user. |
| PROXY | Proxy user will be enabled |
| REFERENCES | Not Implemented |
| RELOAD | FLUSH operation privilege is provided to the user. |
| REPLICATION CLIENT | Permits the user to query about the position of master and slave servers |
| REPLICATION SLAVE | The user is given privilege to replicate slaves which can read binary log events from the master. |
| SELECT | Permits the user to utilize the SELECT statement. |
| SHOW DATABASES | All the databases are shown to the user. |
| SHOW VIEW | Privilege to utilize SHOW CREATE VIEW statement is provided |
| SHUTDOWN | Permission to use mysqladmin command is provided to the user. |
| SUPER | Permits the user to use administrative operations like PURGE BINARY LOGS, CHANGE MASTER, SET GLOBAL, TO and KILL are provided. |
| SHOW VIEW | Privilege to utilize SHOW CREATE VIEW statement is provided |
| SHUTDOWN | Permission to use mysqladmin command is provided to the user. |
| SUPER | Permits the user to use administrative operations like PURGE BINARY LOGS, CHANGE MASTER, SET GLOBAL, TO and KILL are provided. |
| TRIGGER | The use of TRIGGER operations is allowed |
| UPDATE | Privilege to use UPDATE statement is provided to the user. |
| USAGE | No privileges are provided. |