User Creation and Authorization

MySQL 5.7 and Earlier Versions

In versions prior to MySQL 8.0, users could be created implicitly via the GRANT statement or explicitly using CREATE USER. The recommended approach is to create the user first and then assign privileges.

Creating Users:

-- Create a user with a password
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'StrongPassword123';

-- Create an administrative user with full privileges
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

-- Create a user with specific global privileges
GRANT RELOAD, PROCESS ON *.* TO 'monitor'@'localhost' IDENTIFIED BY 'SecretPass';

-- Grant column-specific privileges
GRANT SELECT(employee_id) ON hr.employees TO 'report_user'@'localhost' IDENTIFIED BY 'ReportPass';

Granting Privileges:

Privileges can be assigned using either plaintext passwords or hashed authentication strings. To copy a user's password hash from an existing account, query the mysql.user table:

SELECT user, host, authentication_string FROM mysql.user WHERE user = 'source_user';

Use the retrieved hash to create a new user:

GRANT SELECT, INSERT, UPDATE ON sales.* TO 'clone_user'@'192.168.1.%' 
IDENTIFIED BY PASSWORD '* hashed_value_here';

MySQL 8.0 and Later Versions

MySQL 8.0 enforces stricter security policies. The GRANT statement can no longer create users implicitly. You must use CREATE USER first. Additionally, the default authentication plugin has changed to caching_sha2_password, though mysql_native_password is still available for compatibility.

-- Create user with a specific authentication plugin
CREATE USER 'new_app'@'10.0.0.%' 
IDENTIFIED WITH mysql_native_password BY 'MyPassword123' 
PASSWORD EXPIRE INTERVAL 90 DAY;

-- Assign privileges
GRANT SELECT, INSERT, UPDATE ON inventory.* TO 'new_app'@'10.0.0.%';

-- Apply changes
FLUSH PRIVILEGES;

Stored Routine Permissions

Permissions for stored procedures and functions must be granted explicitly for each object rather than at the database level.

-- Grant execution rights on a specific procedure
GRANT EXECUTE ON PROCEDURE my_db.calculate_total TO 'executor'@'localhost';

-- Grant rights to alter a routine
GRANT ALTER ROUTINE, EXECUTE ON PROCEDURE my_db.cleanup_data TO 'developer'@'%';

User Maintenance Operations

Modifying Users

Changing Passwords:

-- Using ALTER USER (Recommended)
ALTER USER 'web_user'@'localhost' IDENTIFIED BY 'NewPassword456';

-- Using SET PASSWORD
SET PASSWORD FOR 'web_user'@'localhost' = PASSWORD('NewPassword456');

Renaming Accounts:

RENAME USER 'old_name'@'localhost' TO 'new_name'@'localhost';

Deleting Users

Avoid using DELETE statements directly on the mysql.user table. Always use the DROP USER command to ensure the account is removed clean.

DROP USER 'obsolete_user'@'192.168.1.50';

Revoking Privileges

To remove specific access rights, use the REVOKE statement:

REVOKE INSERT, UPDATE ON inventory.* FROM 'read_only_user'@'%';

The WITH GRANT OPTION Clause

The WITH GRANT OPTION clause allows a user to grant their own privileges to other users. This is powerful and should be used cautiously. If a root user loses this capability (often indicated by the Grant_priv column being 'N'), it can be restored by updating the system tables:

UPDATE mysql.user SET Grant_priv = 'Y' WHERE user = 'root' AND host = 'localhost';
FLUSH PRIVILEGES;

MySQL Access Control System

Verification Process

MySQL uses a hierarchical system of grant tables (user, db, tables_priv, columns_priv, procs_priv) located in the mysql system database. The verification flow is as follows:

1. Connection Verification: The system checks the user table for a match on User, Host, and Password.
2. Global Privileges: If the user table has 'Y' for a privilege, access is granted globally, and further checks are skipped.
3. Database Privileges: If global privileges are 'N', the system checks the db table for database-specific access.
4. Table Privileges: If database privileges are insufficient, the tables_priv table is checked.
5. Column Privileges: Finally, the columns_priv table is checked for column-specific access.

Grant vs. Direct Table Modification

Standard DDL commands like GRANT and REVOKE update both the system tables and the in-memory cache immediately. Direct UPDATE or INSERT statements on the grant tables modify the disk data but do not update the memory cache. To apply changes made via direct modification, you must run:

FLUSH PRIVILEGES;

Permission Levels

• Global Level: Applies to all databases. Syntax: ON *.*
• Database Level: Applies to a specific database. Syntax: ON db_name.*
• Table Level: Applies to a specific table. Syntax: ON db_name.table_name
• Column Level: Applies to specific columns within a table.
• Routine Level: Applies to stored procedures or functions.

Common Privileges Explained

• ALL [PRIVILEGES]: Grants all basic privileges except GRANT OPTION and PROXY.
• ALTER: Allows modifying table structures (requires CREATE and INSERT for renaming).
• FILE: Allows reading and writing files on the server host using SELECT ... INTO OUTFILE and LOAD DATA INFILE. This is a global privilege.
• PROCESS: Allows viewing all processes with SHOW PROCESSLIST.
• SUPER: Allows administration operations like KILL threads, setting global variables, and binary log control.
• USAGE: Represents "no privileges." It is used to create a user or modify resource limits without granting specific database access.

Account Restrictions and Security

User Composition

A MySQL user account is defined by a username and a host component ('user_name'@'host_name'). The host can be a hostname, IP address, or a pattern using wildcards (% for any characters, _ for a single character).

Effect of Privilege Changes

• Changes made via GRANT, REVOKE, or ALTER USER take effect immediately for new sessions. Existing sessions may need to reconnect for global changes to apply.
• Database-level changes take effect after the next USE db_name command.
• Table and column changes take effect on the next query.

Resource Limits

You can restrict how much a user can consume the server resources:

CREATE USER 'limited_user'@'localhost' IDENTIFIED BY 'password'
WITH MAX_QUERIES_PER_HOUR 500
     MAX_UPDATES_PER_HOUR 100
     MAX_CONNECTIONS_PER_HOUR 50
     MAX_USER_CONNECTIONS 5;

Password Expiration Policies

Password expiration can be defined globally using the default_password_lifetime system variable or set individually per user.

-- Set password to expire in 90 days
ALTER USER 'employee'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

-- Disable expiration
ALTER USER 'service_account'@'localhost' PASSWORD EXPIRE NEVER;

Account Locking

Accounts can be locked to prevent login without deleting them.

-- Lock an account
ALTER USER 'suspended_user'@'localhost' ACCOUNT LOCK;

-- Unlock an account
ALTER USER 'suspended_user'@'localhost' ACCOUNT UNLOCK;

File Import and Export Permissions

Configuring Secure File Privileges

The FILE privilege is required to perform file imports and exports (e.g., SELECT INTO OUTFILE). The server variable secure_file_priv controls the directories accessible for these operations:

• NULL (empty or not set): No restrictions (not recommended for production).
• /path/to/dir: Imports/Exports are restricted to the specified directory.
• NULL (value): Completely disables import/export functionality.

To modify this read-only variable, add it to the configuration file (my.cnf or my.ini):

[mysqld]
secure-file-priv = "/var/lib/mysql-files"

Granting Export Permissions

To allow a user to export data:

GRANT FILE ON *.* TO 'data_exporter'@'localhost';