index act articles resource 26 ext iframe

mySQL Grant Command

This internet article tells you about how to setup privileges by granting permissions to that user and database.

mySQL Grant Command
2690 Views
You can use the mySQL GRANT command to give priveledges to users to a specific database.




The syntax is as follows and follows the standard mySQL keywords and syntax you would perhaps use in a PHP script.




Code: [hide]
  1. GRANT privileges
     
  2. ON databasename.tablename
     
  3. TO username@host
     
  4. IDENTIFIED BY “password“; 

The priveledges you can grant to a user are vast and for added security, you should only grant permissions to a users on a database that are requried by your application:

  • ALL (all privileges)

  • ALTER

  • CREATE

  • DELETE

  • DROP

  • FILE

  • INDEX

  • INSERT

  • PROCESS

  • REFERENCES (not used currently)

  • RELOAD

  • SELECT

  • SHUTDOWN

  • UPDATE

  • USAGE (can connect but no privileges)

Example use of the above syntax and privileges:

Code: [hide]
  1. GRANT SELECT, INSERT
     
  2. ON mydatabase.*
     
  3. TO user@domain.com
     
  4. IDENTIFIED BY “somepassword” 

The above wildcard (*) will replace the entire list of tables in the database. You can specify just specify one table name if required. You can also specify an IP Address for the domain part of the “TO” command such as ‘doobdee@63.124.45.2’.




You can revoke privileges using the following syntax:

Code: [hide]
  1. REVOKE privileges
     
  2. ON databasename.tablename
     
  3. FROM username@hostname 

Usually, only server administrators should worry about this kind of permission configuration. If you are a normal webmaster using a typical control panel such as Cpanel – all this is done behind the scenes and you will not need to worry about setting up such code.
mySQL Grant Command