Gestión de Usuarios y Permisos en SQL

Managing users and permissions in MySQL is a fundamental aspect of maintaining the security and integrity of our databases. Many security and data loss problems can be avoided simply by implementing a proper granular permissions strategy, rather than sharing administrator credentials. Let's see how we can effectively and securely create and manage users in MySQL.

How to create users in MySQL and why is it important?

One of the biggest problems in development and production environments is the sharing of administrative credentials. This practice, although common, represents a serious security risk. When an administrator shares their username and password, they lose the ability to track who made what changes to the database.

MySQL provides a robust system for creating users with specific permissions. This allows:

• Granular control of actions: determining exactly what each user can and cannot do.
• Restriction of connections: limiting from which locations a user can connect
• Traceability: identify who performed each operation

To create a new user in MySQL, we use the following syntax:

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

For example:

CREATE USER 'becoplatzi'@'%' IDENTIFIED BY 'beco123';

In this command:

• becoplatzi is the user's name
• % is a wildcard that allows connection from any host
• beco123 is the password

How to restrict access by location?

We can limit where a user can connect from:

• To allow connections only from the local server: 'username'@'localhost'.
• To allow connections from anywhere: 'username'@'%'.
• To allow connections from a specific IP address: 'username'@'192.168.0.101'.

This ability to restrict by location adds an additional layer of security to our databases.

What permissions can we assign to users in MySQL?

Once the user has been created, we must assign specific permissions to the user. The most common permissions are:

1. CREATE: allows you to create new tables or databases.
2. ALTER: allows you to modify the structure of existing tables.
3. DROP: allows you to delete tables or databases
4. INSERT: allows you to insert data into tables
5. UPDATE: allows you to modify existing data
6. DELETE: allows you to delete records
7. SELECT: allows you to query data
8. REFERENCES: allows you to create relationships between tables
9. INDEX: allows you to create and delete indexes

To assign these permissions, we use the GRANT command:

GRANT permission1, permission2, ... ON database_name.table_name TO 'username'@'host' [WITH GRANT OPTION];

For example:

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, INDEX ON platzisql.* TO 'becoplatzi'@'%' WITH GRANT OPTION;

The WITH GRANT OPTION option allows the user to grant his own permissions to other users.

How to assign permissions according to the user's role?

For a DBA (Database Administrator):

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, INDEX ON database.* TO 'dba_user'@'host';

For an application:

GRANT INSERT, UPDATE, SELECT ON database.* TO 'app_user'@'host';

For a data analyst:

GRANT SELECT, INDEX ON database.* TO 'analyst_user'@'host';

For a developer:

GRANT CREATE, ALTER, INSERT, SELECT, REFERENCES, INDEX ON database.* TO 'dev_user'@'host';

How to check and use the created users?

To see the existing users in MySQL:

SELECT host, user FROM mysql.user;

To connect to a specific user:

mysql -u username -p

Once logged in, we can check which databases we have access to:

SHOW DATABASES;

And to use a specific database:

USE database_name;SHOW TABLES;

Important note: MySQL uses its own tables to manage users and permissions. These tables are located in the mysql database, which makes the system transparent and robust.

Proper management of users and permissions is critical to maintaining the security and integrity of our databases. Implementing a clear strategy for assigning permissions according to specific roles will help us prevent problems and maintain effective control over who can do what in our system. What user management strategies do you use in your projects? Share your experience in the comments.