MySQL Tutorial/Privilege/Grant

Add localhost to mysql table for accessing sample_db

   <source lang="sql">

mysql> mysql> INSERT INTO mysql VALUES("localhost","sample_db","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y"); mysql></source>

A user doesn"t need a password if connecting from a certain machine

   <source lang="sql">

GRANT ALL PRIVILEGES ON myDatabase.* TO newuser@192.168.0.2</source>

GRANT ALL

   <source lang="sql">

mysql> mysql> GRANT ALL ON databaseName.* TO "your_mysql_name"@"your_client_host";</source>

Granting Privileges on the new database

Syntax:

   <source lang="sql">

GRANT <privileges> ON <database> TO <user> [IDENTIFIED BY <password>] [WITH GRANT OPTION]</source>

The items in square brackets are optional.

GRANT OPTION MAX_QUERIES_PER_HOUR 50 MAX_UPDATES_PER_HOUR 50

   <source lang="sql">

GRANT SELECT, UPDATE ON test.* TO "user1"@"domain1.ru" IDENTIFIED BY "pw1" WITH GRANT OPTION MAX_QUERIES_PER_HOUR 50 MAX_UPDATES_PER_HOUR 50;</source>

Grant Select and Update privilege to a user

   <source lang="sql">

GRANT SELECT, UPDATE (Title, Start_date) ON test.Employee TO "user1"@"domain1.ru" IDENTIFIED BY "pw1" REQUIRE SSL;</source>

GRANT SELECT, UPDATE ON databaseName.*

   <source lang="sql">

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

mysql></source>

Grant select, update privilege for a remote user

   <source lang="sql">

GRANT SELECT, UPDATE (Title, start_date) ON test.Employee TO "user1"@"domain1.ru" IDENTIFIED BY "pw1" REQUIRE SUBJECT "test client cert."

 AND ISSUER "Test C.A.";</source>
   
  

WITH GRANT OPTION condition allows the user to give others privileges to that database

   <source lang="sql">

GRANT ALL PRIVILEGES ON myDatabase.* TO newuser@localhost IDENTIFIED BY "newpassword" WITH GRANT OPTION;</source>

This would allow the user "newuser" to give others privileges to SELECT,INSERT,UPDATE or DELETE.

No user can GRANT more privileges that they themselves possess.