Managing Users & Access Rights in MySQL

In information security, the principle of least privilege recommends that a user, system, or application should only have the minimum level of access needed to perform their tasks.

In MySQL, the root account should be reserved for administrative tasks only, and everyday operations should be carried out using accounts with limited, role-specific privileges.

Generally, using an account with full administrative privileges is not recommended as it often leads to severe breaches and damages on both the database structure and its data.

In short, creating users with limited privileges helps to enhance security, reduce the risk of errors, ensure compliance, and maintain better control over your MySQL environment.

Managing user accounts

To manage user accounts and privileges, you will need to initially connect to the database instance using an account with full administrative privileges, such as the root account:

$ mysql -u root -p

Note: The password of the root account is by default set to root.

Creating a new user

To create a new user account, you can use the CREATE USER keywords:

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

Where:

• 'username' is the name of the new user account.
• 'host' is the name of the host the user will connect from.
• 'password' is the password of the new user account.

Note that:

• 'username'@'129.56.3.14' means that the user can only connect to the database instance from the IP address 129.56.3.14.
• 'username'@'localhost' means that the user can only connect to the database instance from the same host.
• 'username'@'%' means that the user can connect to the database instance from any host.

Example

This SQL statement will create a new user named admin, whose password is 'iXdfo2_(#sdfmx', that can only connect to the MySQL instance from the local host:

mysql> CREATE USER 'admin'@'localhost' IDENTIFIED BY 'iXdfo2_(#sdfmx';

Changing the password of a user

To change the password of a user account, you can use the ALTER USER keywords with the same syntax as the CREATE USER keywords:

ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

Example

This SQL statement will update the password of the admin user:

mysql> ALTER USER 'admin'@'localhost' IDENTIFIED BY 'newSecurePassword456';

Dropping users

To delete a user account including all its privileges, you can use the DROP USER keywords:

DROP USER 'username'@'host';

Example

This SQL statement will remove the user named admin from the database:

mysql> DROP USER 'admin'@'localhost';

Managing privileges

In MySQL, privileges are permissions (or access rights) given to users that define what actions they are allowed to perform on databases and their related objects.

Granting privileges

To grant one or more privilege types to a user, you can use the GRANT keyword:

GRANT privilege_type, ... ON database_name.table_name TO 'username'@'host';

Where:

• privilege_type, ... is a list of privileges to grant.
• database_name is the name of the database the privileges will be granted on.
• table_name is the name of the table within the database the privileges will be granted on.
• username@host is the name of the user account the privileges will be granted to.

Alternatively, to grant a set of privileges for all the tables of a database, you can replace the table_name with a wildcard character:

GRANT privilege_type, ... ON database_name.* TO 'username'@'host';

Finally, to grant a set of privileges to all tables of all databases, you can also replace the database_name with a wildcard character:

GRANT privilege_type, ... ON *.* TO 'username'@'host';

Once the GRANT statement executed, you can use the FLUSH keyword to effectively apply the changes:

FLUSH PRIVILEGES;

Granting privileges on databases

To allow a user to create or delete a database, you can use the CREATE and DROP privileges combined with the *.* expression:

GRANT CREATE|,DROP ON *.* TO 'username'@'host';

Example

This SQL statement will grant the right to both create and delete databases to the admin user:

mysql> GRANT CREATE, DROP ON *.* TO 'admin'@'localhost';

Granting privileges on tables

To allow a user to create, modify, or delete tables, you can use the CREATE, ALTER, and DROP privileges:

GRANT CREATE|,ALTER|,DROP ON database_name.table_name TO 'username'@'host';

Where:

• ALTER allows you to modify the structure of tables.
• CREATE allows you to create new tables.
• DROP allows you to delete tables.

To allow a user to retrieve, insert, update, or delete table records, you can use the SELECT, INSERT, UPDATE, and DELETE privileges:

GRANT SELECT|,INSERT|,UPDATE|,DELETE ON database_name.table_name TO 'username'@'host';

Where:

• SELECT allows you to retrieve records.
• INSERT allows you to add records.
• UPDATE allows you to modify records.
• DELETE allows you to remove records.

Example

This SQL statement will allow the statistics_service user to retrieve records from all the tables of all the databases:

mysql> GRANT SELECT ON *.* TO 'statistics_service'@'localhost';

This SQL statement will allow the admin user to create and modify any table in the learnbackend database:

mysql> GRANT CREATE, ALTER ON learnbackend.* TO 'admin'@'localhost';

This SQL statement will allow the authentication_service user to retrieve, insert, and update records from and into the users table of the authentication database:

mysql> GRANT SELECT, INSERT, UPDATE ON authentication.users TO 'authentication_service'@'localhost';

Granting privileges on columns

To allow a user to retrieve, insert, and update table columns, you can use the SELECT, INSERT, and UPDATE privileges:

GRANT SELECT|INSERT|UPDATE (column_name, ...) ON database_name.table_name TO 'username'@'host';

Where:

• SELECT (column_name, ...) allows you to read data from specific columns.
• INSERT (column_name, ...) allows you to insert data into specific columns.
• UPDATE (column_name, ...) allows you to update specific columns.

Example

These SQL statements will allow the authentication_service user to retrieve the name, email, and password columns, and update the is_active column in the users table of the authentication database:

mysql> GRANT SELECT (name, email, password_hash) ON authentication.users TO 'authentication_service'@'localhost';
mysql> GRANT UPDATE (is_active) ON authentication.users TO 'authentication_service'@'localhost';

Listing privileges

To view the privileges assigned to a user, you can use the SHOW GRANTS keywords:

SHOW GRANTS FOR 'username'@'host';

Example

This SQL statement will show all the privileges of the admin user:

mysql> SHOW GRANTS FOR 'admin'@'localhost';
+-------------------------------------------------------------------+
| Grants for admin@localhost                                        |
+-------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `admin`@`localhost`                         |
| GRANT ALL PRIVILEGES ON `authentication`.* TO `admin`@`localhost` |
| GRANT ALL PRIVILEGES ON `learnbackend`.* TO `admin`@`localhost`   |
+-------------------------------------------------------------------+
3 rows in set (0.00 sec)

Revoking privileges

To remove specific privileges from a user account, you can use the REVOKE keyword:

REVOKE privilege_type, ... ON database_name.table_name FROM 'username'@'host';

And to revoke all privileges at once, you can use the REVOKE ALL PRIVILEGES keywords:

REVOKE ALL PRIVILEGES ON my_database.* FROM 'username'@'host';

Example

This SQL statement will revoke the DELETE privilege of the payment_service user on the transactions table of the banking database:

mysql> REVOKE DELETE ON banking.transactions FROM 'payment_service'@'localhost';

Managing roles

In MySQL, a role is a named collection of privileges that allow administrators to grant or revoke identical privileges to and from multiple users at once, rather than managing individual privileges for each user.

Creating roles

To create a new role, you can use the CREATE ROLE keywords:

CREATE ROLE 'role_name';

Example

This SQL statement will create a new role named read_only:

mysql> CREATE ROLE 'read_only';

Granting privileges to a role

To grant privileges to a role, you can use the GRANT keywords:

GRANT privilege_type ON database_name.table_name TO 'role_name';

Example

This SQL statement will allows the read_only role to retrieve records from all the tables of the learnbackend database:

mysql> GRANT SELECT ON learnbackend.* TO 'read_only';

Granting a role to a user

To assign a user to a role, you can use the GRANT keyword:

GRANT 'role_name' TO 'username'@'host';

Example

This SQL statement will assign the read_only role to the stats_service user:

mysql> GRANT 'read_only' TO 'stats_service'@'localhost';

Listing roles

To list existing roles, you can use the SELECT keyword to query to role_edges table of the mysql database:

SELECT * FROM mysql.role_edges [WHERE TO_USER = 'username'];

Example

This SQL statement will show all the roles assigned to the stats_service user:

mysql> SELECT * FROM mysql.role_edges WHERE TO_USER = 'stats_service';
+-----------+-----------+---------+---------------+-------------------+
| FROM_HOST | FROM_USER | TO_HOST | TO_USER       | WITH_ADMIN_OPTION |
+-----------+-----------+---------+---------------+-------------------+
| %         | read_only | %       | stats_service | N                 |
+-----------+-----------+---------+---------------+-------------------+
1 row in set (0.00 sec)

Listing role privileges

To list the privileges of a role, you can use the SHOW GRANTS keywords:

SHOW GRANTS FOR 'role_name';

Example

This SQL statement will show all the privileges of the read_only role:

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

Removing a role

To remove a role from a user, you can use the REVOKE keyword:

REVOKE 'role_name' FROM 'username'@'host';

To remove a role from the database, you can use the DROP ROLE keywords:

DROP ROLE 'role_name';

Example

This SQL statement will unassign the read_only role from the stats_service user:

mysql> REVOKE 'read_only' FROM 'stats_service'@'host';

This SQL statement will completely remove the read_only role from the database:

mysql> DROP ROLE 'read_only';

Summary

Here's a summary of what you've learned in this lesson:

• The CREATE USER keywords are used to create a new user account.
• The ALTER USER keywords are used to modify an existing user account.
• The DROP USER keywords are used to remove an existing user account.
• The GRANT keyword is used to assign privileges to a user or a role.
• The SHOW GRANTS FOR keywords are used to list the privileges assigned to a user or a role.
• The REVOKE keyword is used to remove privileges from a user or a role.
• The CREATE ROLE keywords are used to create a new role.
• The DROP ROLE keywords are used to remove an existing role.