Monday 24 November 2014

Mysql Privileges - Types of privileages in MySql - How do I grant privileges in MySQL

Mysql Privileges - Types of privileages in MySql - How do I grant privileges in MySQL

There are three types of privileges in MySql

  1. Administrative privilege(s) enable users to manage operation of MySQL server. These are global because they are not specific to a particular mysql database. 
  2. Database privileges apply to a database and to all objects within it, means apply to specific database. 
  3. Privileges for database object(s) such as table(s), indexe(s), stored routine(s), and view(s). These privileges apply table, index, Stored Procedure, views etc. of a single database.  It can be granted for specific objects within a database OR for all objects of a given type within a database.


Following are  permissible Privileges for GRANT and REVOKE
PrivilegeColumnContext
CREATECreate_privdatabases, tables, or indexes
DROPDrop_privdatabases, tables, or views
GRANT OPTIONGrant_privdatabases, tables, or stored routines
LOCK TABLESLock_tables_privdatabases
REFERENCESReferences_privdatabases or tables
EVENTEvent_privdatabases
ALTERAlter_privtables
DELETEDelete_privtables
INDEXIndex_privtables
INSERTInsert_privtables or columns
SELECTSelect_privtables or columns
UPDATEUpdate_privtables or columns
CREATE TEMPORARY TABLESCreate_tmp_table_privtables
TRIGGERTrigger_privtables
CREATE VIEWCreate_view_privviews
SHOW VIEWShow_view_privviews
ALTER ROUTINEAlter_routine_privstored routines
CREATE ROUTINECreate_routine_privstored routines
EXECUTEExecute_privstored routines
FILEFile_privfile access on server host
CREATE USERCreate_user_privserver administration
PROCESSProcess_privserver administration
RELOADReload_privserver administration
REPLICATION CLIENTRepl_client_privserver administration
REPLICATION SLAVERepl_slave_privserver administration
SHOW DATABASESShow_db_privserver administration
SHUTDOWNShutdown_privserver administration
SUPERSuper_privserver administration






Following are mysql grant permission example:

GRANT SELECT ON databasename.* TO user@'localhost';
/* Give the select permission to user i.e user for database databasename*/

GRANT SELECT ON databasename.* TO user@'localhost' IDENTIFIED BY 'password';
/* Give the select permission to user i.e user for database databasename and create the user*/

GRANT SELECT, INSERT, DELETE ON databasename TO username@'localhost' IDENTIFIED BY 'password';
/* Give the SELECT, INSERT, DELETE permission to user i.e user for database databasename*


select * from mysql.user where User='username';
/*To see a list of the privileges that have been granted to a specific user:*/


GRANT all privileges ON databasename.* TO user@'localhost';
/* Give all permissions to user i.e user for database databasename*/