Home >> FAQs/Tutorials >> MySQL Tutorials >> Index

MySQL FAQs - Managing User Accounts and Access Privileges

By: FYIcenter.com

Part:   1  2  3  4  5   6 

(Continued from previous part...)

How To Grant User Privileges at the Database Level?

If you want to grant a user privilege at the database level, you can use the "GRANT privilegeName ON databaseName.* TO userName" command. The argument "databasename.*" in the command stands for all tables in the specified database. The following tutorial exercise shows you how to create a new database and grant access privilege to a user only for this new database:

>cd \mysql\bin
>mysql -u root -pretneciyf

mysql> CREATE DATABASE faq;
Query OK, 1 row affected (0.04 sec)

mysql> CREATE USER qa IDENTIFIED BY 'iyf';
Query OK, 0 rows affected (0.24 sec)

mysql> GRANT CREATE ON faq.* TO qa;
Query OK, 0 rows affected (0.00 sec)

mysql> QUIT;

>mysql -u qa -piyf

mysql> USE faq;
Database changed

mysql> USE fyi;
ERROR 1044 (42000): Access denied for user 'qa'@'%' 
 to database 'fyi'

How To View User Privileges?

If a regular user wants to see his/her own granted privileges, he/she can use the "SHOW GRANTS" command. If the "root" user wants to see other user's granted privileges, he/she can use the "SHOW GRANTS FOR userName" command. The following tutorial exercise shows you how to view user's granted privileges:

>cd \mysql\bin
>mysql -u root -pretneciyf

mysql> SHOW GRANTS;
+------------------------------------------------------
| Grants for root@localhost                            
+------------------------------------------------------
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost'...
+------------------------------------------------------
1 row in set (0.00 sec)

mysql> SHOW GRANTS for dev;
+------------------------------------------------------
| Grants for dev@%                      |
+------------------------------------------------------
| GRANT CREATE ON *.* TO 'dev'@'%' ... 
+------------------------------------------------------
1 row in set (0.00 sec)

mysql> SHOW GRANTS for qa;
+------------------------------------------------------
| Grants for qa@%                    |
+------------------------------------------------------
| GRANT USAGE ON *.* TO 'qa'@'%' ...
| GRANT CREATE ON `faq`.* TO 'qa'@'%'                  
+------------------------------------------------------
2 rows in set (0.00 sec)

The output says:

• "root" has all privileges on all databases.
• "dev" has only "CREATE" privilege on all databases.
• "qa" has no privileges on any databases, except for "CREATE" privilege on "faq" database.

How To Revoke User Privileges?

If your want remove some granted user privileges, you can use the "REVOKE privilegeName ..." command. You can only revoke privileges in the same way as they were granted. For example, you can not revoke a privilege on a specific database, if that privilege was granted to all databases. The following tutorial exercise shows you how to view user's granted privileges:

>cd \mysql\bin
>mysql -u root -pretneciyf

mysql> GRANT CREATE ON *.* TO DEV;
Query OK, 0 rows affected (0.00 sec)

mysql> REVOKE CREATE ON QA.* FROM DEV;
ERROR 1141 (42000): There is no such grant defined for
 user 'DEV' on host '%'

mysql> REVOKE CREATE ON *.* FROM DEV;
Query OK, 0 rows affected (0.00 sec)

(Continued on next part...)

Part:   1  2  3  4  5   6