Mysql Privileges

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.  
3. Privileges for database object(s) such as table(s), indexe(s), stored routine(s), and view(s). It can be granted for specific objects within a database OR for all objects of a given type within a database.

user, db, host, tables_priv, columns_priv, and procs_priv tables used to stored account privileges.

Following are  permissible Privileges for GRANT and REVOKE




Privilege	Column	Context
CREATE	Create_priv	databases, tables, or indexes
DROP	Drop_priv	databases, tables, or views
GRANT OPTION	Grant_priv	databases, tables, or stored routines
LOCK TABLES	Lock_tables_priv	databases
REFERENCES	References_priv	databases or tables
EVENT	Event_priv	databases
ALTER	Alter_priv	tables
DELETE	Delete_priv	tables
INDEX	Index_priv	tables
INSERT	Insert_priv	tables or columns
SELECT	Select_priv	tables or columns
UPDATE	Update_priv	tables or columns
CREATE TEMPORARY TABLES	Create_tmp_table_priv	tables
TRIGGER	Trigger_priv	tables
CREATE VIEW	Create_view_priv	views
SHOW VIEW	Show_view_priv	views
ALTER ROUTINE	Alter_routine_priv	stored routines
CREATE ROUTINE	Create_routine_priv	stored routines
EXECUTE	Execute_priv	stored routines
FILE	File_priv	file access on server host
CREATE USER	Create_user_priv	server administration
PROCESS	Process_priv	server administration
RELOAD	Reload_priv	server administration
REPLICATION CLIENT	Repl_client_priv	server administration
REPLICATION SLAVE	Repl_slave_priv	server administration
SHOW DATABASES	Show_db_priv	server administration
SHUTDOWN	Shutdown_priv	server administration
SUPER	Super_priv	server 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*/