SQL/MySQL/User Permission/SQL Privilege

Adding Users to Database

   <source lang="sql">

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

      </source>
   
  

Check out the privilege for a user

   <source lang="sql">

/* oost is the usr name */ SELECT host, db, user, table_name, table_priv, column_priv

  FROM tables_priv WHERE user="oost";
 
          
      </source>
   
  

Check the privilege

   <source lang="sql">

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

      </source>
   
  

Grant select and update on two column in a table

   <source lang="sql">
 

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

      </source>
   
  

Grant select and update to all tables

   <source lang="sql">
 

/* "*" 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;

      </source>
   
  

Grant select and update with time limitation

   <source lang="sql">
 

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;

      </source>
   
  

Grant with user name and password

   <source lang="sql">
 

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.";

      </source>
   
  

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

   <source lang="sql">
 

/* oost is the usr name */ SELECT host, db, user, table_name, column_name, column_priv

  FROM columns_priv WHERE user="oost";

      </source>
   
  

Read column privilege

   <source lang="sql">
 

SELECT host, db, user, table_name, column_name, column_priv

  FROM columns_priv WHERE user="myuserName";
          
      </source>
   
  

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

   <source lang="sql">
 

SELECT host, db, user, table_name, table_priv, column_priv

  FROM tables_priv WHERE user="myuserName";
          
      </source>
   
  

Read the user table

   <source lang="sql">

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

      </source>