What is User Management and Access Control in MYSQL?

User Management and Access Control in MYSQL

MySQL database security and integrity depend on controlling who can access and modify data. SQL’s Data Control Language (DCL) prioritizes User Management and Access Control for this control. Specifically, DCL commands are used to change database object access rights and permissions.

CREATE USER, GRANT, and REVOKE are the main DCL commands you will use.

Creating New User Accounts

Users must be created before access control can be implemented. Define the user’s login credentials and connection location with the CREATE USER statement.

Enter a username, host, and password to create a user.

Create ‘username’@’host’ with ‘password’;
‘username’: Your user account name.
‘@’host’: This vital element indicates the MySQL server’s allowed host(s).
- ‘localhost’: Only computers running the MySQL server can connect. This is a typical and safe limitation for users connecting from the server itself, like local apps or background activities.
- ‘%’: This wildcard typically indicates that any host can be used to connect. Although practical, this should be used with caution as it is less secure than designating a specific host or range of IPs.
- Specific IP address (‘192.168.1.10’) or hostname (‘appserver.mydomain.com’): only permits connections coming from that particular data.
- IP range with wildcard (‘192.168.1.%’): permits any host inside that IP range to connect.
- These host constraints are crucial for security, according to data.
IDENTIFIED BY ‘password’: This line establishes the user account’s password. Naturally, the password needs to be strong. IDENTIFIED BY PASSWORD with a hashed password value is another method that some data demonstrate.

Examples of creating users with various host limitations include the following:

-- Make the user 'app_user' capable of connecting just from the server computer.
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'secure_password_123';
-- Make the user 'report_user' capable of connecting from any computer (use with caution).
CREATE USER 'report_user'@'%' IDENTIFIED BY 'another_secure_password';
-- Allow 'admin_john' to login from a specified IP address (192.168.1.50).
-- The original code for this user was already correct.
CREATE USER 'admin_john'@'192.168.1.50' IDENTIFIED BY 'admin_password';

Granting Permissions (GRANT)

Once a user has been formed, their rights are usually relatively limited, usually only allowing them to connect (occasionally called USAGE). The GRANT statement must be used to specifically grant them the required privileges.

You can grant a user particular kinds of privileges on particular database objects using the GRANT statement.

The basic syntax for the GRANT statement is as follows:

GRANT privileges ON object TO 'user'@'host' [optional_clauses];

privilege_type: This outlines what the user is permitted to do. Typical benefits consist of:
- SELECT: Retrieve information from tables.
- INSERT: Update tables with new rows.
- UPDATE: Make changes to the tables’ current data.
- DELETE: Get rid of table rows.
- CREATE: Make tables, indexes, or databases.
- DROP: Get rid of tables, databases, or indexes.
- ALTER: Adjust the table structure.
- ALL PRIVILEGES: Provides every privilege that is accessible. Usually reserved for administrative accounts, use this very carefully.
object_type: The level at which the privileges are applicable is specified here:
- *.*: Applies to all databases and all tables within them (global privileges).
- database_name.*: This is applicable to every table in a given database.
- database_name.table_name: Refers to a particular database table.
- Although it is less usual, you can also grant rights on particular columns within a table.
TO ‘username’@’host’: Indicates which user account (username plus host) has been given the privileges. There must already be a user account.
[WITH GRANT OPTION]: If present, this enables the recipient user to give other users the same rights they were granted. Given its strength, this choice need to be given sparingly, mostly to other administrators.

-- Grant SELECT and INSERT privileges on all tables in 'my_app_db' to 'app_user'@'localhost'
GRANT SELECT, INSERT ON my_app_db.* TO 'app_user'@'localhost';
-- Grant only SELECT privilege on the 'reports' table in 'analysis_db' to 'report_user'@'%'
GRANT SELECT ON analysis_db.reports TO 'report_user'@'%';
-- Grant ALL PRIVILEGES on the 'system_config' database to 'admin_john'@'192.168.1.50'
GRANT ALL PRIVILEGES ON system_config.* TO 'admin_john'@'192.168.1.50';
-- Grant ALL PRIVILEGES globally to an admin user (use with caution, grant option allows them to create users)
-- Corrected object specification from '.' to '*.*' for global privileges
GRANT ALL PRIVILEGES ON *.* TO 'super_admin'@'localhost' WITH GRANT OPTION;

Revoking Permissions (REVOKE)

The REVOKE statement should be used to revoke permissions when a user no longer needs them. This is GRANT’s opposite and is necessary to uphold the least privilege principle and keep a secure system.

The syntax for REVOKE mirrors that of GRANT:

REVOKE privilege_type ON object_type FROM ‘username’@’host’;

privilege_type, object_type, and ‘username’@’host’: These are equivalent to the GRANT statement. You designate which users, which objects, and which rights you are removing.

Removing the INSERT privilege from ‘app_user’@’localhost’ on all tables in’my_app_db’ is an example of rescinding rights.

Examples of revoking permissions:

-- Revoke INSERT privilege on all tables in 'my_app_db' from 'app_user'@'localhost'
REVOKE INSERT ON my_app_db.* FROM 'app_user'@'localhost';
-- Revoke ALL PRIVILEGES on the 'system_config' database from 'admin_john'@'192.168.1.50'
REVOKE ALL PRIVILEGES ON system_config.* FROM 'admin_john'@'192.168.1.50';
-- Revoke the ability to grant privileges from 'super_admin'@'localhost'
REVOKE GRANT OPTION ON *.* FROM 'super_admin'@'localhost';

The Root Account vs. General User Accounts

The default superuser account in MySQL is the root account. It has almost unrestricted administrative access to the whole MySQL server. The root account is only meant to be used for database management duties like:

Installing and configuring MySQL.
Creating and dropping databases.
Creating, modifying, and dropping user accounts.
Granting and revoking privileges to other users.
Performing server maintenance and troubleshooting.

It is strictly forbidden to do general tasks using the root account. This implies:

Applications shouldn’t use the root account to access the database.
The root account should not be used by regular users who choose, enter, update, or delete data on a daily basis.

There is a serious security risk when using the root account for routine tasks or application connections. An attacker may take total control of your MySQL server if the login credentials for such an account were compromised, which could result in data loss, destruction, or service interruption.

Creating Non-Superuser Accounts

For apps and ordinary users, you should always create non-superuser accounts rather than employing root. This complies with the least privilege security principle, which states that each user or application should only be given the minimal amount of access necessary to carry out its designated job.

You do the following actions to create a non-superuser account:

Make a user account: Make use of the CREATE USER statement, limiting the host from which the user can access (e.g., ‘localhost’ for a local application, a specific IP for a remote server), and providing a unique username and strong password.
Give only the privileges that are required: Use the GRANT statement to grant the newly formed user only the permissions required for the particular database or table. Connect as the root user or another user with adequate privileges (e.g., GRANT OPTION).
For a user of an application who only accesses and modifies data in one database.
For someone who needs to run reports on particular tables only.
General operating accounts shouldn’t be given broad privileges like CREATE, DROP, or ALL PRIVILEGES. Administrative positions should be the only ones with these strong permissions.

You may greatly lessen the possible impact of a hacked account and guarantee a more secure and reliable database environment by defining particular users with restricted access and host constraints. Although the root account is still an essential tool, it should only be used for administrative tasks.

Page Content

Tutorials