Security
 
Creating New DataBase User & Granting Permission
 

GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON [object_type] {tbl_name | * | *.* | db_name.*}
TO user [IDENTIFIED BY [PASSWORD] 'password']
[, user [IDENTIFIED BY [PASSWORD] 'password']] ...
[REQUIRE
NONE |
[{SSL| X509}]
[CIPHER 'cipher' [AND]]
[ISSUER 'issuer' [AND]]
[SUBJECT 'subject']]
[WITH with_option [with_option] ...]
object_type =
TABLE
| FUNCTION
| PROCEDURE
with_option =
GRANT OPTION
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
 
If you wish to change the access privileges for all the tables of a database, the correct form to use is ON database.*. If you wish to alter global privileges, then specify ON *.*.Itis not allowed to use wild cards in database names.
 
Example:
 
Grant ALL on *.* to ravish identified by 'ebiz';
 
Registering New Users
 
All users with the computer name *.myorganization.com are permitted to link to MySQL if they know the password xxx. The privilege USAGE means that all global privileges have been set to N. The users thereby at first have no privileges whatsoever (to the extent that so far no individual databases, tables, or columns have been made accessible to all users who can log into MySQL):
 
GRANT USAGE ON *.* TO ''@'%.myorganization.com' IDENTIFIED BY 'xxx'
 
The following command gives the user admin on the local computer unrestricted privileges. All privileges (including Grant)are set:
 
GRANT ALL ON *.* TO admin@localhost IDENTIFIED BY 'xxx'
 
WITH GRANT OPTION
 
Enabling Access to a Database
 
The following command gives the user peter on the local computer the right to read and alter data in all tables of the database mylibrary. If peter@localhost is unknown to the user table of the mysql database, then this name is added without a password. (If there is already a peter@localhost, then the password is not changed.)
 
GRANT SELECT, INSERT, UPDATE, DELETE
ON library.* TO peter@localhost
 
If you wish to add to peter's privileges the right to lock tables and create temporary tables (which is useful in many applications), the command looks like this:
 
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, LOCK TABLES ON library.* TO peter@localhost
 
Prohibiting Changes in a Database
 
The next command takes away from peter the right to make changes to mylibrary, but peter retains the right to read the database using SELECT (assuming that the command of the previous example was just executed).
 
REVOKE INSERT, UPDATE, DELETE
ON mylibrary.* FROM peter@localhost
 
Enabling Access to Tables
 
With the following command the user kahlila on the local computer is given the right to read data from the table authors in the database mylibrary (but not to alter it):
 
GRANT SELECT ON mylibrary.authors TO kahlila@localhost
 
Enabling Access to Individual Columns
 
The access privileges for katherine are more restrictive than those for kahlila: She is permitted only to read the columns title and subtitle of the table books in the database mylibrary.
 
GRANT SELECT(title, subtitle) ON mylibrary.books TO katherine@localhost
 
Granting Database Access to All Local Users
 
All users on the local computer can read and edit data in the mp3 database:
 
GRANT SELECT, INSERT, DELETE, UPDATE ON mp3.* TO ''@localhost
 
Viewing Access Privileges with SHOW GRANTS
 
If you have lost track of which privileges a particular user has, the command SHOW GRANTS is just what you need:
 
SHOW GRANTS FOR peter@localhost;
Grants for peter@localhost:
GRANT SELECT ON mylibrary.* TO 'peter'@'localhost'
1 row in set (0.00 sec)
SHOW GRANTS FOR testuser@localhost
GRANT USAGE ON *.* TO 'testuser'@'localhost'
IDENTIFIED BY PASSWORD '663c5dd53dae4ed0'
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, LOCK TABLES
ON 'myforum'.* TO 'ptestuser'@'localhost'
 
Changing a Password with mysqladmin
 
The program mysqladmin carries out various administrative tasks. Although this program does not offer any immediate assistance in managing access privileges, it does offer two applications that seem to fit into this chapter.
 
Changing a Password
 
You can use GRANT to change the password of a previously registered user. However, GRANT can be used only when at the same time access privileges are to be changed. If all you want to do is to change a password, then mysqladmin is a simpler alternative:
 
> mysqladmin -u peter -p password newPW
Enter password: oldPW
 
The above command changes the password for the user peter on the computer localhost. Please note that the new password is passed as a parameter, while the old password is entered on request. (This order, first the new and then the old, is rather unusual.)