User management

User Management

Previously, to connect to the database, we used two users: root and the user we created for remote connections. They had all possible privileges in the system, from executing simple SELECT queries to deleting databases. In the real world, you should be more careful about separating privileges and only allow users to do what they need to do.

We will work with users directly from the virtual machine where our database server is running, using the built-in client:

ssh username@host
mysql -u mysqluser -pP@ssw0rd optionalDatabaseName

Here: - testuser is the username; - after @ are the IP addresses from which the user can connect; - P@ssw0rd is the user's password. - optionalDatabaseName is the database to connect to

Restricting the IP addresses from which a user can connect is an additional layer of security in the system, but this mechanism is difficult to manage at the database server level when the infrastructure is large and dynamic and when the client's IP address can change frequently. That is why this mechanism is almost never used; instead, connections from all IP addresses are simply allowed by specifying %.

Setting Up Privileges

The query for setting user privileges looks like this:

GRANT <PRIVILEGE> ON database.table TO 'username'@'host';

Here PRIVILEGE is the role that needs to be assigned to the user, that is, the list of actions the user can perform. MySQL has the following roles: CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, and RELOAD. When setting up privileges, these roles need to be specified separated by commas. For example, if you need to allow a user only INSERT, UPDATE, DELETE, SELECT operations, the GRANT command would look like this:

GRANT INSERT, UPDATE, DELETE, SELECT ON database.table TO 'username'@'host';

If a user needs to be granted all these roles, they can be replaced with a single keyword ALL:

GRANT ALL ON database.table TO 'username'@'host';

As an example, let's set up the privileges for our newly created user so that they can read and write data in the company database that we used in the previous examples. To do this, we will relog as root and execute the following command:

exit
sudo mysql -u root
GRANT INSERT, UPDATE, DELETE, SELECT ON company.* TO 'testuser'@'%';

By the way, this exact privilege configuration is the most commonly used for database users that are used by web applications, since typically a web application only needs to read and write data, but certainly not create tables or delete databases.

After executing this command, we can connect to the server under the testuser user from MySQL Workbench, specifying the IP address, username, password, and the database name for which our user has privileges configured (in the default schema field).

Now let's execute SELECT and INSERT queries:

use company;
SELECT * from Employees;
INSERT INTO Employees (FirstName, LastName, Position, Department, HireDate) Values ('Jane', 'Dough', 'Developer', 'IT', '2023-01-10');

Updating Privileges

Now let's return to our database server. Imagine that our testuser actually only needs to read the database, but not update data in it. Then we need to update their privileges, which can be done using the following command:

REVOKE INSERT, UPDATE, DELETE ON company.* FROM 'testuser'@'%';

And here is the command to update a user's password:

ALTER USER 'testuser'@'%' IDENTIFIED BY 'newP@ssw0rd';

Deleting a User

If a user is no longer needed, they can be deleted like this:

DROP USER  'testuser'@'%' ;

Managing users and setting up their privileges is an important aspect of database administration. The Least Privilege Principle, which means granting users in the system only the privileges they need to perform their tasks, is one of the fundamental computer system security techniques. Although certain details may differ, user management in most database management systems works almost the same way. Therefore, once you learn to work with MySQL, you will easily figure out how to set up privileges for any database.