Privileges to access database content in MySQL can be given in different level and context to the user; or in other word in seperate process then setting up a user's account. The list below represents different level scopes of setting privileges in MySQL:

  • Administrative privileges - This is a global privilege which can be applied to all databases. It enables users to manage operation of MYSQL server.
  • Database privileges - These privileges are applied at the database level and the objects involved in it. The privileges can be specific to a database or global.
  • Object privilege - These privileges are applied to the database objects such as tables, indexes, views and stored procedures. It can be for specific objects or all the objects of a database.

These privileges from above are stored in the GRANT tables called user, db, tables_priv, columns_priv, and procs_priv. They are read and stored in memory when the server starts. Whenever there is a change in the privileges, the tables are reloaded. Below is a table of few privileges, the associated column name in the grant table and their context of usage. They are used in GRANT and INVOKE statements.

Prvileges and associated column names
Privilege Column Context Usage
CREATE create_priv databases, table or indexes Creates new databases, tables
DROP drop_priv databases, tables, or views This privilege drops the existing database, tables and views
ALTER alter_priv tables This privilege enables use of ALTER table to change the structure of tables
DELETE delete_priv tables This privilege enables rows to be deleted from tables
INDEX index_priv tables This privilege enables to create and drop indexes
INSERT insert_priv tables or columns This privilege enables rows to be inserted into database
SELECT select_priv tables or columns This privilege enables rows to be selected from tables in database
CREATE VIEW create_view_priv views This privilege enables use of CREATE VIEW
SHOW VIEW show_view_priv views This privilege enables use of SHOW CREATE VIEW
ALTER ROUTINE alter_routine_priv stored routines This privilege enables use of ALTER TABLE to change structure of tables
CREATE ROUTINE create_routine_priv stored routines This privilege is needed to create stored routines
CREATE TABLESPACE create_tablespace_priv server administration This privilege is needed to create, alter and drop tablespaces and log file groups
CREATE USER create_user_priv server administration This privilege is enables use of CREATE USER, DROP USER, RENAME USER and REVOKE ALL PRIVILEGES
SHOW DATABASES show_db_priv server administration This privilege is enables the account to see database names by issuing SHOW DATABASE statement
SHUTDOWN shutdown_priv server administration This privilege is enables use of mysqladmin shutdown command
SUPER super_priv server administration This privilege is enables an account to use of CHANGE MASTER TO, KILL threads belonging to other accounts

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

GRANT statement

The MYSQL GRANT statement syntax, which allows granting access privileges to database accounts, is presented in example below:

GRANT privileges (columnList)

ON (objectType) privilegeLevel

TO account (IDENTIFIED BY 'password')

[REQUIRE encryption]

WITH options;

Where,

  • privileges - The privileges given above which is assigned to the account. Multiple privileges can be granted using single GRANT statement.
  • columnList - This specifies the column to which privilege is applied. This is an optional choice.
  • privilegeLevel - The level of privilege such as global privilege, database-specific privilege, table specific privilege, column specific privilege ?etc.
  • account - Specifies which account to which privilege is granted.
  • password - It specifies the password to assign to the account.
  • REQUIRE encryption - Specifies if the account has to be connected to the server over secured SSL connection.
  • WITH options - We can add options such as allocating MYSQL database server resources, grant one accounts privilege to other accounts etc...

A couple examples more of GRANT statements:

GRANT ALL ON *.* TO 'super'@'localhost' WITH GRANT OPTION;

GRANT SELECT, UPDATE, DELETE ON patientsDB.* TO 'doctor'@'%';

REVOKE statement

The REVOKE statements are used to revoke the privileges from an account. Its syntax goes like this:

REVOKE privilegeType (columnList) [, priv_type(COlumnList)]

ON objectType privilegeLevel

FROM user [, user] ...;

To revoke all privileges of a user/s, this may be done:

REVOKE ALL PRIVILEGES, GRANT OPTION 

FROM user [, user] ...;

Where,

  • The list of privileges to be revoked are given after REVOKE keyword.
  • The privilege level at that privilege are to be revoked are given after ON clause.
  • The accounts to which the privileges are revoked are given after FROM keyword.

To check the privileges of a user before revoking them, the following syntax is used:

SHOW GRANTS FROM user;

Example:

REVOKE UPDATE, DELETE ON patientsDB.* FROM 'doctor'@'%';

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'doctor'@'localhost';