Practicing Grant and Revoke command Syntax
GRANT
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO user_specification [, user_specification] ...
[REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
[WITH with_option ...]
GRANT PROXY ON user_specification TO user_specification [, user_specification] ...
[WITH GRANT OPTION]
object_type:
TABLE
| FUNCTION
| PROCEDURE priv_level:
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name user_specification:
user [
| IDENTIFIED WITH auth_plugin [AS 'auth_string']
IDENTIFIED BY [PASSWORD] 'password' ]
ssl_option:
SSL
| X509
| CIPHER 'cipher'
| ISSUER 'issuer'
| SUBJECT 'subject' with_option:
GRANT OPTION
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
privileges indicates the privileges that you assign to the account. For example, the CREATE privilege allows an account to create databases and create tables. You can grant multiple privileges using single GRANT statement; the privileges are separated by commas.
column_list specifies the columns to which a privilege applies. The columns are separated by commas and listed within parentheses. The column_list is optional element.
privilege_level specifies the level at which the privileges apply. You can use global privileges, database-specific privileges, table-specific privileges, column-specific privileges, etc.
account specifies which account is being granted the privileges.
password specifies the password to assign to the account. If the account exists, the GRANT statement replaces the old password by the new one. Like the CREATE USER statement, you use plain text password followed by the IDENTIFIED BY clause. The IDENTIFIED BY clause is optional.
After the REQUIRE clause, you specifies whether the account has to connect to the database server over secure connection using SSL.
If you want the account to have the privilege that can grant its own privileges to other accounts, you need to use the WITH clause with GRANT OPTION clause. In addition, you can use the WITH clause to allocate MySQL database server’s resource e.g., to set how many connections or statements that an account can use per hour.
This is very helpful in shared environments such as MySQL shared hosting.
If the account that you specify in the GRANT statement after the TO clause exists, the GRANT statement modifies its privileges,
otherwise, the GRANT statement creates a new account with the specified privileges.
Available privileges to use with MySQL GRANT
Privilege Description
ALL [PRIVILEGES] Grant all privileges at specified access level except GRANT OPTION ALTER Allow to use of ALTER TABLE statement
ALTER ROUTINE Allow user to alter or drop stored routine CREATE Allow user to create database and table CREATE ROUTINE Allow user to create stored routine CREATE
TABLESPACE
Allow user to create, alter or drop tablespaces and log file groups CREATE
TEMPORARY TABLES
Allow user to create temporary table by using CREATE TEMPORARY TABLE
CREATE USER Allow user to use the CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES statements.
CREATE VIEW Allow user to create or modify view
DELETE Allow user to use DELETE
DROP Allow user to drop database, table and view EVENT Allow user to schedule events in Event Scheduler EXECUTE Allow user to execute stored routines
FILE Allow user to read any file in the database directory.
GRANT OPTION Allow user to have privileges to grant or revoke privileges from other accounts
INDEX Allow user to create or remove indexes.
INSERT Allow user to use INSERT statement
LOCK TABLES Allow user to use LOCK TABLES on tables for which you have the SELECT privilege
PROCESS Allow user to see all processes with SHOW PROCESSLIST statement.
PROXY Enable user proxying
REFERENCES Not implemented
RELOAD Allow user to use FLUSH operations REPLICATION
CLIENT
Allow user to query to see where master or slave servers are REPLICATION
SLAVE
Allow user to use replicate slaves to read binary log events from the master.
SELECT Allow user to use SELECT statement SHOW DATABASES Allow user to show all databases
SHOW VIEW Allow user to use SHOW CREATE VIEW statement SHUTDOWN Allow user to use mysqladmin shutdown command
SUPER Allow user to use other administrative operations such as CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL, and mysqladmin command
TRIGGER Allow user to use TRIGGER operations.
UPDATE Allow user to use UPDATE statement USAGE Equivalent to “no privileges
Example of Grant Command
Database administrator first uses CREATE USER to create an account, then GRANT to define its privileges and characteristics. For example:
create user 'student'@'localhost' identified by 'stu123';
SELECT User FROM mysql.user;
Granting Privileges to user student from user root GRANT ALL ON test.* TO 'student'@'localhost';
All the rights are given to user student on database test
REVOKE
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level FROM user [, user] ...
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...
REVOKE PROXY ON user FROM user [, user] ...
The REVOKE statement enables system administrators to revoke privileges from MySQL accounts.
Description:
You specify a list of privileges that you want to revoke from an account right after the REVOKE keyword. You need to separate privileges by comma.
ON clause specifies the privilege level at that privileges are to be revoked.
After FROM keyword, you specify the account that you want to revoke the privileges. You can specify multiple accounts in the FROM clause. You separate the accounts by comma.
Taking all the privileges from user student Example
REVOKE ALL ON test.* FROM 'student'@'localhost';
