Learn MySQL Access Privileges and Account Management

Mysql Access Privileges

creating user in mysql  

>CREATE USER 'mcmc'@'localhost' IDENTIFIED BY 'mcdocute';

Accessing Mysql using  the newly created user

                 >mysql  -mcmc -p

 Syntax how to change password in MySQL

-->SET PASSWORD FOR 'juan'@'%.loc.gov' = PASSWORD('newpass');

GRANT ALL ON db1.* TO 'mcmc'@'localhost';= allowing mcmc to access batabase name db1.

GRANT SELECT ON db2.invoice TO 'mcdo'@'localhost';=allowing mcmc to only performed select to database name db2.

GRANT USAGE ON *.* TO 'mcmc'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;= allowing user mcmc to database with only limited of 90 queries in an our

REVOKE INSERT ON *.* FROM 'mcmc'@'localhost';= revoke means user mcmc is not allowed to insert in the database.




Account Management

Permission  Description

Select_priv  =Permission to run SELECT queries

Insert_priv = Permission to run INSERT statements

Update_priv = Permission to run UPDATE statements

Delete_priv = Permission to run DELETE statements

Create_priv = Permission to CREATE tables and databases

Drop_priv = Permission to DROP tables and databases

Reload_priv = Permission to RELOAD the database (a FLUSH statement for example)

Shutdown_priv = Permission to SHUTDOWN the database server

Process_priv = Permission to view or kill PROCESSes.

File_priv = Permission to read and write FILEs (for example LOAD DATA INFILE)

Grant_priv = Permission to GRANT available permissions to other users

References_priv = Permissions to create, modify or drop INDEXes

  Alter_priv= Permission to ALTER table structures.

Examples: 

Before you can make a query in a database make sure that you use or you access the database  by using this code:

USE mysql;


    

DESCRIBE user;  this code is use to describe the user

DESC host; this code is use to describe host

 DESC tables_priv;

DESC columns_priv;
 

DESC db;
this code show the database user and the privileges.
 

 

 SELECT host,db,select_priv,insert_priv FROM host;

SELECT host,user,select_priv,insert_priv FROM user;
 

SELECT host,db,user,select_priv,insert_priv FROM db;
 this code shows the  

SELECT host, user FROM user;
this code show the different host and users