How To Grant User Privileges at the Database Level

Q

How To Grant User Privileges at the Database Level? - MySQL FAQs - Managing User Accounts and Access Privileges

✍: FYIcenter.com

A

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'

2007-05-10, 5528👍, 0💬