Home >> FAQs/Tutorials >> MySQL Tutorials

MySQL FAQs - Managing User Accounts and Access Privileges

By: FYIcenter.com

Part:   1   2  3  4  5  6 

A collection of 17 FAQs on MySQL user accounts and access privileges. Clear answers are provided with tutorial exercises on creating and deleting user accounts; setting and changing passwords; giving and removing user privileges; showing granted privileges and levels. Topics included in this collection are:

1. What Is a User Account?
2. What Are the Predefined User Accounts?
3. How To Add a New User Account?
4. How To Test a New User Account and Password?
5. How To Change the Password for Your Own User Account?
6. How To Change the Password of Another User Account?
7. How To Delete a User Account?
8. How To List All Existing User Accounts?
9. How To Rename an Existing User Account Name?
10. What Are User Privileges?
11. How Many Scope Levels Can User Privileges Apply?
12. How To Grant User Privileges at the Global Level?
13. How To Grant User Privileges at the Database Level?
14. How To View User Privileges?
15. How To Revoke User Privileges?
16. How To Give a User Read-Only Access to a Database?
17. Where Are User Privileges Stored on the Server?

Please note that all answers and tutorials are based on MySQL 5.0. Sometimes you may need to run previous tutorials in order to continue a later tutorial.

What Is a User Account?

A user account is identified by a user name and defines the user's attributes, including the following:

• Password for connection authentication.
• User privileges, for examples: Shutdown_priv, Create_priv, Drop_priv, Insert_priv, Update_priv, Delete_priv, etc..
• Various limits, for example: max_questions, max_updates, max_connections, etc..

What Are the Predefined User Accounts?

There is only one predefined user account called "root":

• "root" was created during the installation process.
• "root" has no password initially. You need to assign a new password as soon as you finishes the installation.
• "root" has all access privileges granted.

Here is a tutorial exercise to check the "root" user account:

>cd \mysql\bin
>mysql -u root

mysql> USE mysql;
Database changed

mysql> SELECT User, Password, Shutdown_priv, Create_priv 
    -> FROM user WHERE User = 'root';
+------+----------+---------------+-------------+
| User | Password | Shutdown_priv | Create_priv |
+------+----------+---------------+-------------+
| root |          | Y             | Y           |
+------+----------+---------------+-------------+
1 row in set (0.00 sec)

How To Add a New User Account?

If you want to add a new user account, you can connect to the server as "root" and use the "CREATE USER ..." command. The command syntax for create a new user account with a specified user name and password is:

CREATE USER userName IDENTIFIED BY 'passwordString';

This command will be executed by the MySQL server to create the user account and store the password in an encrypted format. Here is good tutorial exercise:

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

mysql> CREATE USER dev IDENTIFIED BY 'retneciyf';
Query OK, 0 rows affected (0.42 sec)

mysql> SELECT User, Password, Shutdown_priv FROM user;
+------+-------------------------------------------+-----+
| User | Password                                  | ... |
+------+-------------------------------------------+-----+
| root |                                           | Y   |
| dev  | *3735F1D8464342BA852E10101E55E422EBAAAF35 | N   |
+------+-------------------------------------------+-----+
2 rows in set (0.00 sec)

(Continued on next part...)

Part:   1   2  3  4  5  6