MySQL User Profile Creation Guide

1-- Connect using a MySQL admin or root account
2mysql -u root -p                  # or mickael_admin or mysql --login-path=client
3
4-- Check both settings (should return values, not empty)
5SHOW VARIABLES LIKE 'partial_revokes';        -- Should be: ON
6SHOW VARIABLES LIKE 'validate_password.%';    -- Should return 8 rows

1-- If partial_revokes = OFF:
2SET GLOBAL partial_revokes = ON;
3
4-- If password validation empty:
5INSTALL COMPONENT 'file://component_validate_password';

1[mysqld]
2# Added on [DATE]
3# Enable partial revokes for user profile management (basic, standard, maintenance, admin)
4# Matches Pre-Prod config - allows REVOKE on specific databases after global GRANT
5partial_revokes=ON

 1-- Create user with password security settings (5 failed attempts = 1 day lockout)
 2CREATE USER 'username_basic'@'localhost' IDENTIFIED BY 'secure_password' FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;
 3
 4-- Grant read-only access to all databases
 5GRANT SELECT ON *.* TO 'username_basic'@'localhost';
 6
 7-- Remove access to MySQL system database
 8REVOKE SELECT ON mysql.* FROM 'username_basic'@'localhost';
 9
10-- Apply all privilege changes
11FLUSH PRIVILEGES;

 1-- Create user with password security settings (5 failed attempts = 1 day lockout)
 2CREATE USER 'username_stand'@'localhost' IDENTIFIED BY 'secure_password' FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;
 3
 4-- Grant data manipulation access to all databases
 5GRANT SELECT, UPDATE, INSERT, DELETE ON *.* TO 'username_stand'@'localhost';
 6
 7-- Remove access to MySQL system database
 8REVOKE SELECT, UPDATE, INSERT, DELETE ON mysql.* FROM 'username_stand'@'localhost';
 9
10-- Apply all privilege changes
11FLUSH PRIVILEGES;

 1-- Create user with password security settings (5 failed attempts = 1 day lockout)
 2CREATE USER 'username_maint'@'localhost' IDENTIFIED BY 'secure_password' FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;
 3
 4-- Grant database administration privileges with ability to grant privileges to others
 5GRANT SELECT, UPDATE, INSERT, DELETE, CREATE, ALTER, DROP, REFERENCES, LOCK TABLES, CREATE TABLESPACE, CREATE TEMPORARY TABLES, CREATE VIEW, EVENT, EXECUTE, INDEX, SHOW VIEW, TRIGGER ON *.* TO 'username_maint'@'localhost' WITH GRANT OPTION;
 6
 7-- Remove all administrative access to MySQL system database
 8REVOKE SELECT, UPDATE, INSERT, DELETE, CREATE, ALTER, DROP, REFERENCES, LOCK TABLES, CREATE TABLESPACE, CREATE TEMPORARY TABLES, CREATE VIEW, EVENT, EXECUTE, INDEX, SHOW VIEW, TRIGGER, GRANT OPTION ON mysql.* FROM 'username_maint'@'localhost';
 9
10-- Apply all privilege changes
11FLUSH PRIVILEGES;

 1-- Create user with password security settings (5 failed attempts = 1 day lockout)
 2CREATE USER 'username_admin'@'localhost' IDENTIFIED BY 'secure_password' FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;
 3
 4-- Grant all traditional database and server administration privileges
 5GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO 'username_admin'@'localhost' WITH GRANT OPTION;
 6
 7-- Grant all dynamic privileges and administrative roles
 8GRANT APPLICATION_PASSWORD_ADMIN, AUDIT_ABORT_EXEMPT, AUDIT_ADMIN, AUTHENTICATION_POLICY_ADMIN, BACKUP_ADMIN, BINLOG_ADMIN, BINLOG_ENCRYPTION_ADMIN, CLONE_ADMIN, CONNECTION_ADMIN, ENCRYPTION_KEY_ADMIN, FIREWALL_EXEMPT, FLUSH_OPTIMIZER_COSTS, FLUSH_STATUS, FLUSH_TABLES, FLUSH_USER_RESOURCES, GROUP_REPLICATION_ADMIN, GROUP_REPLICATION_STREAM, INNODB_REDO_LOG_ARCHIVE, INNODB_REDO_LOG_ENABLE, PASSWORDLESS_USER_ADMIN, PERSIST_RO_VARIABLES_ADMIN, REPLICATION_APPLIER, REPLICATION_SLAVE_ADMIN, RESOURCE_GROUP_ADMIN, RESOURCE_GROUP_USER, ROLE_ADMIN, SENSITIVE_VARIABLES_OBSERVER, SERVICE_CONNECTION_ADMIN, SESSION_VARIABLES_ADMIN, SET_USER_ID, SHOW_ROUTINE, SYSTEM_USER, SYSTEM_VARIABLES_ADMIN, TABLE_ENCRYPTION_ADMIN, TELEMETRY_LOG_ADMIN, XA_RECOVER_ADMIN ON *.* TO 'username_admin'@'localhost' WITH GRANT OPTION;
 9
10FLUSH PRIVILEGES;

1-- All users
2SELECT User, Host FROM mysql.user ORDER BY User;
3
4-- By profile
5SELECT User FROM mysql.user WHERE User LIKE '%_basic';   -- Basic
6SELECT User FROM mysql.user WHERE User LIKE '%_stand';   -- Standard  
7SELECT User FROM mysql.user WHERE User LIKE '%_maint';   -- Maintenance
8SELECT User FROM mysql.user WHERE User LIKE '%_admin';   -- Admin

1SHOW GRANTS FOR 'username_profile'@'localhost';

1-- Change password
2ALTER USER 'username_profile'@'localhost' IDENTIFIED BY 'new_password';
3
4-- Delete user
5DROP USER 'username_profile'@'localhost';
6
7-- Always flush after changes
8FLUSH PRIVILEGES;

1-- Exit admin session
2EXIT;
3
4-- Login as new user
5mysql -u username_profile -p
6
7-- Test access
8SHOW DATABASES;

1-- Verify settings
2SHOW VARIABLES LIKE 'partial_revokes';
3SHOW VARIABLES LIKE 'validate_password.policy';
4SELECT VERSION();-- Should be 8.0.16+