SQL/MySQL/User Permission/SQL Privilege

Adding Users to Database

GRANT SELECT, UPDATE (FieldName1, FieldName2, FieldName3)
ON databaseName.tableName
TO "myuserName"@"localhost" IDENTIFIED BY "myPassword"
WITH MAX_CONNECTIONS_PER_HOUR 25;

Check out the privilege for a user

/* oost is the usr name */
SELECT host, db, user, table_name, table_priv, column_priv 
   FROM tables_priv WHERE user="oost";

Check the privilege

/* oost is the usr name */
SELECT host, user, select_priv, update_priv FROM user WHERE user="oost";

Grant select and update on two column in a table

  
/* BookTitle and Copyright are field names */
GRANT SELECT, UPDATE (BookTitle, Copyright)
ON databaseName.tableName
TO "usrName"@"domain1.ru" IDENTIFIED BY "password"
REQUIRE SSL;

Grant select and update to all tables

  
/*  "*" means all tables */
GRANT SELECT, UPDATE
ON test.*
TO "userName"@"domain1.ru" IDENTIFIED BY "password"
WITH GRANT OPTION MAX_QUERIES_PER_HOUR 50 MAX_UPDATES_PER_HOUR 50;

Grant select and update with time limitation

  
GRANT SELECT, UPDATE
ON databaseName.*
TO "userName"@"domain1.ru" IDENTIFIED BY "pw1"
WITH GRANT OPTION MAX_QUERIES_PER_HOUR 50 MAX_UPDATES_PER_HOUR 50;

Grant with user name and password

  
GRANT SELECT, UPDATE (columnName1, columnName2)
ON databaseName.tableName
TO "userName"@"domain1.ru" IDENTIFIED BY "password"
REQUIRE SUBJECT "test client cert."
  AND ISSUER "Test C.A.";

List the host, DB, table name and column name for a user

  
/* oost is the usr name */
SELECT host, db, user, table_name, column_name, column_priv 
   FROM columns_priv WHERE user="oost";

Read column privilege

  
SELECT host, db, user, table_name, column_name, column_priv 
   FROM columns_priv WHERE user="myuserName";

Read host, db, user, table name and table privilege from privilege table

  
SELECT host, db, user, table_name, table_priv, column_priv 
   FROM tables_priv WHERE user="myuserName";

Read the user table

SELECT host, user, select_priv, update_priv FROM user WHERE user="myuser1";