MySQL User and Privileges Management

Introduction

In this lab, we will explore user management and privileges in MariaDB, essential skills for securing your database system. You'll learn how to create and manage user accounts, assign appropriate privileges, and view permissions. Through hands-on exercises, you'll gain practical experience with these crucial database security operations and understand how to implement basic security measures in MariaDB. This knowledge is fundamental for any database administrator to ensure proper access control and data protection.

Skills Graph

Creating and Managing User Accounts

First, connect to MySQL as root:

sudo mysql -u root

Creating Users

Let's create users with different access patterns:

-- Create a user that can connect only from localhost
CREATE USER 'hr_user'@'localhost' IDENTIFIED BY 'hr123';

-- Create a user that can connect from any host
CREATE USER 'reports_user'@'%' IDENTIFIED BY 'reports123';

-- Create a user without a password (not recommended for production)
CREATE USER 'intern'@'localhost';

The @'localhost' restricts connections to local machine only, while @'%' allows connections from any host. Creating users without passwords is not recommended for production environments.

To see the users we've created:

SELECT User, Host FROM mysql.user;

Expected output shows all users in the system:

+--------------+-----------+
| User         | Host      |
+--------------+-----------+
| reports_user | %         |
| hr_user      | localhost |
| intern       | localhost |
| mariadb.sys  | localhost |
| mysql        | localhost |
| root         | localhost |
+--------------+-----------+

Managing Existing Users

To modify existing users:

-- Change a user's password
SET PASSWORD FOR 'intern'@'localhost' = PASSWORD('intern123');

-- Require user to change password at next login
ALTER USER 'hr_user'@'localhost' PASSWORD EXPIRE;

The PASSWORD EXPIRE option forces users to change their password at next login - useful for temporary passwords.

To remove a user:

-- Remove a user
DROP USER 'reports_user'@'%';

After dropping the user, verify the user list has updated:

+-------------+-----------+
| User        | Host      |
+-------------+-----------+
| hr_user     | localhost |
| intern      | localhost |
| mariadb.sys | localhost |
| mysql       | localhost |
| root        | localhost |
+-------------+-----------+

Assigning Privileges

Now we'll learn to assign different types of privileges to control what users can do.

Basic Privilege Management

Let's grant some basic privileges to our HR user:

-- Grant SELECT and INSERT privileges on company_db.employees table
GRANT SELECT, INSERT ON company_db.employees TO 'hr_user'@'localhost';

-- Verify the privileges
SHOW GRANTS FOR 'hr_user'@'localhost';

The GRANT command gives specific permissions (SELECT, INSERT) on specific database objects to users. The SHOW GRANTS command displays current privileges.

Expected output:

+----------------------------------------------------------------------------------------------------------------+
| Grants for hr_user@localhost                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `hr_user`@`localhost` IDENTIFIED BY PASSWORD '*27DB7FC6D35D1F5E6A4C37473D9018DD46DC2944' |
| GRANT SELECT, INSERT ON `company_db`.`employees` TO `hr_user`@`localhost`                                      |
+----------------------------------------------------------------------------------------------------------------+

Different Privilege Levels

MariaDB supports privileges at different levels of granularity:

-- Global privileges (all databases)
GRANT CREATE USER ON *.* TO 'admin'@'localhost';

-- Database level privileges
GRANT ALL PRIVILEGES ON company_db.* TO 'hr_user'@'localhost';

-- Table level privileges
GRANT SELECT, UPDATE ON company_db.employees TO 'intern'@'localhost';

-- Column level privileges
GRANT SELECT (name, department), UPDATE (department)
ON company_db.employees TO 'intern'@'localhost';

The *.* syntax refers to all databases and tables, while database.* refers to all tables in a specific database. Column-level privileges allow fine-grained access control.

Revoking Privileges

To remove privileges:

-- Revoke specific privileges
REVOKE INSERT ON company_db.employees FROM 'hr_user'@'localhost';

-- Revoke ALL privileges
REVOKE ALL PRIVILEGES ON company_db.employees FROM 'intern'@'localhost';

Always verify privilege changes after revoking them using SHOW GRANTS.

MariaDB [(none)]> SHOW GRANTS FOR 'hr_user'@'localhost';
+----------------------------------------------------------------------------------------------------------------+
| Grants for hr_user@localhost                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `hr_user`@`localhost` IDENTIFIED BY PASSWORD '*27DB7FC6D35D1F5E6A4C37473D9018DD46DC2944' |
| GRANT ALL PRIVILEGES ON `company_db`.* TO `hr_user`@`localhost`                                                |
| GRANT SELECT ON `company_db`.`employees` TO `hr_user`@`localhost`                                              |
+----------------------------------------------------------------------------------------------------------------+

MariaDB [(none)]> SHOW GRANTS FOR 'intern'@'localhost';
+---------------------------------------------------------------------------------------------------------------+
| Grants for intern@localhost                                                                                   |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `intern`@`localhost` IDENTIFIED BY PASSWORD '*A381F474A9DDCAA5BC97DAB83A69431B1C8D8902' |
+---------------------------------------------------------------------------------------------------------------+

Summary

In this lab, we've covered the essential aspects of MariaDB user and privilege management:

1. Creating and managing user accounts with different connection restrictions
2. Assigning and revoking privileges at various levels (global, database, table, column)

These skills are fundamental for securing your MariaDB installation and implementing proper access control. Understanding user management and privileges is crucial for maintaining database security and ensuring users have appropriate access to database resources.