Mysql GRANT

MySQL GRANT Function

This function grants privileges to MySQL user account.

Syntax GRANT

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 ...]

object_type:
    TABLE
  | FUNCTION
  | PROCEDURE

priv_level:
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name

user_specification:
    user [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
            

database and table privilege

• databasename.* : all table within databasename
• databasename.tabl1: only tabl1 within databasename

priv_type

• ALL : Grant all privileges at specified access level except GRANT OPTION
• ALTER : Enable use of ALTER TABLE
• ALTER ROUTINE : Enable stored routines to be altered or dropped
• CREATE : Enable database and table creation
• CREATE ROUTINE : Enable stored routine creation
• CREATE TEMPORARY TABLES: Enable use of CREATE TEMPORARY TABLE
• CREATE USER: Enable use of [[CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES
• CREATE VIEW: Enable views to be created or altered
• DELETE: Enable use of DELETE
• DROP: Enable databases, tables, and views to be dropped
• EVENT: Enable use of events for the Event Scheduler
• EXECUTE: Enable the user to execute stored routines
• FILE: Enable the user to cause the server to read or write files
• GRANT OPTION: Enable privileges to be granted to or removed from other accounts
• INDEX: Enable indexes to be created or dropped
• INSERT: Enable use of INSERT
• LOCK TABLES: Enable use of LOCK TABLES on tables for which you have the SELECT privilege
• PROCESS: Enable the user to see all processes with SHOW PROCESSLIST
• REFERENCES: Not implemented
• RELOAD: Enable use of FLUSH operations
• REPLICATION CLIENT: Enable the user to ask where master or slave servers are
• REPLICATION SLAVE: Enable replication slaves to read binary log events from the master
• SELECT: Enable use of SELECT
• SHOW DATABASES: Enable SHOW DATABASES to show all databases
• SHOW VIEW: Enable use of SHOW CREATE VIEW
• SHUTDOWN: Enable use of mysqladmin shutdown
• SUPER: Enable use of other administrative operations such as CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL, and mysqladmin debug command
• TRIGGER: Enable trigger operations
• UPDATE: Enable use of UPDATE
• USAGE: Synonym for “no privileges”

Example 1: Grant ALL Privileges

mysql> CREATE USER 'hob'@'localhost' IDENTIFIED BY 'me@34';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE DATABASE hobdb;
Query OK, 1 row affected (0.06 sec)

mysql> GRANT ALL ON hobdb.* TO 'hobdb'@'localhost';
Query OK, 0 rows affected (0.34 sec)

Example 2:Grant No Privileges

mysql> GRANT USAGE ON hobdb.* TO 'sophal'@'localhost' WITH MAX_QUERIES_PER_HOUR 50;
Query OK, 0 rows affected (0.41 sec)

Example 3: GRANT INSERT, SELECT, and DELETE

mysql> GRANT SELECT, INSERT, DELETE ON hobdb.*
    -> TO 'sophal'@'wlith' ;
Query OK, 0 rows affected (0.00 sec)

Example 4:

mysql> GRANT ALL ON hobdb.* TO 'christ'@'localhost'
    ->     IDENTIFIED BY '123schrist'
    ->     WITH MAX_QUERIES_PER_HOUR 40
    ->          MAX_UPDATES_PER_HOUR 16
    ->          MAX_CONNECTIONS_PER_HOUR 5;

Related Links

• MySQL Account Management Statements
• MysQL Tutorial
• Tutorial Home

---

---CREATE USER--- DROP USER--- GRANT User Privilege--- RENAME USER--- REVOKE--- SET PASSWORD---

---