module 4

Module Content

• MySQL Users

• Selecting Users

• User Privileges

• Changing User Passwords

MYSQL Users

• The MySQL user is a record in the USER table of the MySQL server that contains the login information, account privileges, and the host information for MySQL account. It is essential to create a user in MySQL for accessing and managing the databases.

• Create User statement allows us to create a new user account in the database server. It provides authentication, SSL/TLS, resource-limit, role, and password management properties for the new accounts. It also enables us to control the accounts that should be initially locked or unlocked.

Here is an example video for creating a user:

MYSQL Users

Create user

When the MySQL server installation completes, it has a ROOT user account only to access and manage the databases. But, sometimes, you want to give the database access to others without granting them full control. In that case, you will create a non-root user and grant them specific privileges to access and modify the database.

Syntax:

• • The following syntax is used to create a user in the database server.

  • CREATE USER [IF NOT EXISTS] account_name IDENTIFIED BY 'password';  

MYSQL Users

Select user

Code Example:

• Step 1: Open the MySQL server by using the mysql client tool.

• Step 2: Enter the password for the account and press Enter.

• Enter Password: ****

• Step 3: Execute the following command to show all users in the current MySQL server.

• MySQL> select user from MySQL. User;  

User Privileges

• • MySQL server provides multiple types of privileges to a new user account.

Some of the most commonly used privileges are given below:

• ALL PRIVILEGES: It permits all privileges to a new user account.

• CREATE: It enables the user account to create databases and tables.

• DROP: It enables the user account to drop databases and tables.

• DELETE: It enables the user account to delete rows from a specific table.

• INSERT: It enables the user account to insert rows into a specific table.

• SELECT: It enables the user account to read a database.

• UPDATE: It enables the user account to update table rows.

Drop User Statement

• • The Drop User statement allows us to remove one or more user accounts and their privileges from the database server. If the account does not exist in the database server, it gives an error.

  • If you want to use the Drop User statement, it is required to have a global privilege of Create User statement or the DELETE privilege for the MySQL system schema.

Syntax

• The following syntax is used to delete the user accounts from the database server completely.

• DROP USER 'account_name';  

Drop User Statement (code example)

Code example:

• The following are the steps required to delete an existing user from the MySQL server database.

• Step 1: Open the MySQL server by using the mysql client tool.

• Step 2: Enter the password for the account and press Enter.

• Enter Password: ********  

• Step 3: Execute the following command to show all users in the current MySQL server.

• mysql> select user from MySQL.User;   

Select User Statement

• If we need to see the list of all user's accounts in a database, we can use the select user command. Most times, we assume that there is a SHOW USERS command similar to SHOW DATABASES, SHOW TABLES, etc. for displaying the list of all users available in the database server.

• Unfortunately, MySQL database does not have a SHOW USERS command to display the list of all users in the MySQL server.

We can use the following query to see the list of all user in the database server:

-Select user from mysql.user;

Select User Statement (code example)

Code Example:

We can get information of the current user by using the user() or current_user() function

Get all users:

• • SELECT user FROM user; 

or this:

• SELECT *

• FROM user;

Changing User Passwords

To change the password of any user account, note the following:

• The details of the user account that you want to change.

• An application used by the user whose password you want to change. If you reset the user account password without changing an application connection string, then the application cannot connect with the database server.

MySQL allows us to change the user account password in three different ways, which are given below:

• UPDATE Statement

• SET PASSWORD Statement

• ALTER USER statement

Changing User Passwords (code example)

Code example:

• 1. USE mysql;  

• 2. UPDATE user SET password = PASSWORD('jtp12345') WHERE user = 'peter' AND host = 'localhost';  

• 3. FLUSH PRIVILEGES;

On new versions of MySQL, use the following command:

• ALTER USER 'user-name'@'localhost' IDENTIFIED BY 'NEW_USER_PASSWORD'