Skip to content
logo
Percona Server for MySQL
Utility user
Initializing search
    percona/psmysql-docs
    percona/psmysql-docs
    • Home
      • The Percona XtraDB storage engine
      • List of features available in Percona Server for MySQL releases
      • Percona Server for MySQL feature comparison
      • Changed in Percona Server 8.0
      • Understand version numbers
      • Install Percona Server for MySQL
        • Install using APT repository
        • Files in DEB package
        • Build APT packages
        • Install from downloaded DEB packages
        • Apt pinning
        • Run Percona Server for MySQL
        • Uninstall
        • Install using a RPM repository
        • Files in RPM package
        • Install from downloaded RPM packages
        • Run Percona Server for MySQL
        • Uninstall
        • Install with binary tarballs
        • Binary tarballs available
        • Install Percona Server for MySQL from a source tarball
        • Compile Percona Server for MySQL 8.0 from source
      • Post-installation
      • Percona Server for MySQL in-place upgrading guide: from 5.7 to 8.0
      • Upgrade using the Percona repositories
      • Upgrade from systems that use the MyRocks or TokuDB storage engine and partitioned tables
      • Upgrade using Standalone Packages
      • Downgrade Percona Server for MySQL
      • Running Percona Server for MySQL in a Docker Container
      • Docker environment variables
      • Improved InnoDB I/O scalability
      • Adaptive network buffers
      • Multiple page asynchronous I/O requests
      • Thread pool
      • XtraDB performance improvements for I/O-bound highly-concurrent workloads
      • Prefix index queries optimization
      • Limit the estimation of records in a Query
      • Jemalloc memory allocation profiling
      • The ProcFS plugin
      • Binary logs and replication improvements
      • Compressed columns with dictionaries
      • Extended SELECT INTO OUTFILE/DUMPFILE
      • Extended SET VAR optimizer hint
      • Improved MEMORY storage engine
      • Suppress warning messages
      • Limiting the disk space used by binary log files
      • Support for PROXY protocol
      • SEQUENCE_TABLE(n) function
      • Slow query log rotation and expiration
      • Trigger updates
      • Extended mysqlbinlog
      • Extended mysqldump
      • InnoDB full-text search improvements
      • Too many connections warning
      • Handle corrupted tables
      • Percona Toolkit UDFs
      • Kill idle transactions
      • XtraDB changed page tracking
      • Enforcing storage engine
      • Expanded fast index creation
      • Backup locks
      • Audit log plugin
      • Start transaction with consistent snapshot
      • Extended SHOW GRANTS
      • Utility user
        • Version specific information
        • System variables
          • utility_user
          • utility_user_password
          • utility_user_schema_access
          • utility_user_privileges
          • utility_user_dynamic_privileges
      • Working with SELinux
      • Working with AppArmor
      • PAM authentication plugin
      • Server variables
      • SSL improvements
      • Data masking
        • Using LDAP authentication plugins
        • LDAP authentication plugin system variables
        • Data at Rest Encryption
        • Use the keyring component or keyring plugin
          • Using the Key Management Interoperability Protocol (KMIP)
          • Use the Amazon Key Management Service (AWS KMS)
          • FIDO authentication plugin
        • Encryption functions
        • Encrypt File-Per-Table Tablespace
        • Encrypt schema or general tablespace
        • Encrypt system tablespace
        • Encrypt temporary files
        • Encrypt Binary Log Files and Relay Log Files
        • Encrypting the Redo Log data
        • Encrypt the undo tablespace
        • Rotate the master key
        • Advanced encryption key rotation
        • Encrypt doublewrite buffers
        • Verify the encryption for tables, tablespaces, and schemas
      • User statistics
      • Slow query log
      • Extended show engine InnoDB status
      • Show storage engines
      • Process list
      • Misc. INFORMATION_SCHEMA tables
      • Thread based profiling
      • InnoDB page fragmentation counters
      • Stacktrace
      • Libcoredumper
      • Manage group replication flow control
      • Group replication system variables
      • Percona MyRocks introduction
      • Percona MyRocks installation guide
      • Updated supported features
      • MyRocks limitations
      • Differences between Percona MyRocks and Facebook MyRocks
      • MyRocks Information Schema tables
      • MyRocks server variables
      • MyRocks status variables
      • Gap locks detection
      • Data loading
      • Installing and configuring Percona Server for MySQL with ZenFS support
      • TokuDB introduction
      • TokuDB installation
      • Use TokuDB
      • Fast updates with TokuDB
      • TokuDB files and file types
      • TokuDB file management
      • TokuDB background ANALYZE TABLE
      • TokuDB variables
      • TokuDB status variables
      • TokuDB fractal tree indexing
      • TokuDB troubleshooting
      • TokuDB Performance Schema integration
      • Frequently asked questions
      • Migrate and removing the TokuDB storage engine
      • Percona TokuBackup
      • Release notes index
      • Percona Server for MySQL 8.0.32-24 (2023-03-20)
      • Percona Server for MySQL 8.0.31-23 (2023-02-09)
      • Percona Server for MySQL 8.0.30-22 Update (2022-11-21)
      • Percona Server for MySQL 8.0.30-22 (2022-11-21)
      • Percona Server for MySQL 8.0.29-21 (2022-08-08)
      • Percona Server for MySQL 8.0.28-20 (2022-06-20)
      • Percona Server for MySQL 8.0.28-19 (2022-05-12)
      • Percona Server for MySQL 8.0.27-18 (2022-03-02)
      • Percona Server for MySQL 8.0.26-17 (2022-01-26)
      • Percona Server for MySQL 8.0.26-16 (2021-10-20)
      • Percona Server for MySQL 8.0.25-15 (2021-07-13)
      • Percona Server for MySQL 8.0.23-14 (2021-05-12)
      • Percona Server for MySQL 8.0.22-13 (2020-12-14)
      • Percona Server for MySQL 8.0.21-12 (2020-10-13)
      • Percona Server for MySQL 8.0.20-11 (2020-07-21)
      • Percona Server for MySQL 8.0.19-10 (2020-03-23)
      • Percona Server for MySQL 8.0.18-9
      • Percona Server for MySQL 8.0.17-8
      • Percona Server for MySQL 8.0.16-7
      • Percona Server for MySQL 8.0.15-6
      • Percona Server for MySQL 8.0.15-5
      • Percona Server for MySQL 8.0.14
      • Percona Server for MySQL 8.0.13-4
      • Percona Server for MySQL 8.0.13-3
      • Percona Server for MySQL 8.0.12-2rc1
      • List of variables introduced in Percona Server for MySQL 8.0
      • Development of Percona Server for MySQL
      • Trademark policy
      • Index of INFORMATION_SCHEMA tables
      • Frequently asked questions
      • Copyright and licensing information
      • Glossary

    • Version specific information
    • System variables
      • utility_user
      • utility_user_password
      • utility_user_schema_access
      • utility_user_privileges
      • utility_user_dynamic_privileges

    Utility user¶

    Percona Server for MySQL has implemented ability to have a MySQL user who has system access to do administrative tasks but limited access to user schema. This feature is especially useful to those operating MySQL As A Service.

    This user has a mixed and special scope of abilities and protection:

    • Utility user does not appear in the mysql.user table and can not be modified by any other user, including root.

    • Utility user does not appear in INFORMATION_SCHEMA.USER_STATISTICS, INFORMATION_SCHEMA.CLIENT_STATISTICS or THREAD_STATISTICS tables or in any performance_schema tables.

    • Utility user’s queries may appear in the general and slow logs.

    • Utility user does not have the ability create, modify, delete or see any schemas or data not specified, except for information_schema.

    • Utility user may modify all visible, non-read-only system variables (see expanded_option_modifiers functionality).

    • Utility user may see, create, modify and delete other system users only if given access to the mysql schema.

    • Regular users may be granted proxy rights to the utility user but attempts to impersonate the utility user fail. The utility user may not be granted proxy rights on any regular user.

    For example, GRANT PROXY ON utility_user TO regular_user; does not fail, but any actual attempt to impersonate as the utility user fails.

    GRANT PROXY ON regular_user TO utility_user; fails when utility_user is an exact match or is more specific than than the utility user specified.

    At server start, the server notes in the log output that the utility user exists and the schemas that the utility user can access.

    Version specific information¶

    • The utility_user_dynamic_privileges variable was implemented in Percona Server for MySQL 8.0.20-11.

    • Percona Server for MySQL 8.0.17-8: The feature was ported from Percona Server for MySQL 5.7.

    System variables¶

    In order to have the ability for a special type of MySQL user, which will have a very limited and special amount of control over the system and can not be see or modified by any other user including the root user, three new options have been added.

    utility_user¶

    Option Description
    Command Line: Yes
    Config file utility_user=<user@host>
    Scope: Global
    Dynamic: No
    Data type String
    Default NULL

    Specifies a MySQL user that will be added to the internal list of users and recognized as the utility user.

    Option utility_user specifies the user which the system creates and recognizes as the utility user. The host in the utility user specification follows conventions described in the MySQL manual. For example, the conventions allow wildcards and IP masks. Anonymous user names are not permitted to be used for the utility user name.

    This user must not be an exact match to any other user that exists in the mysql.user table. If the server detects that the user specified with this option exactly matches any user within the mysql.user table on start up, the server reports an error and exits gracefully.

    If host name wildcards are used and a more specific user specification is identified on start up, the server reports a warning and continues.

    Error message
    utility_user=frank@% and [frank@localhost](mailto:frank@localhost) exists within the mysql.user table.
    

    If a client attempts to create a MySQL user that matches this user specification exactly or if host name wildcards are used for the utility user and the user being created has the same name and a more specific host, the creation attempt fails with an error.

    Error message
    utility_user=frank@% and CREATE USER [‘frank@localhost](mailto:'frank@localhost)’;
    

    As a result of these requirements, it is strongly recommended that a very unique user name and reasonably specific host be used.

    Verify the script or tools test they are running within the correct user by executing SELECT CURRENT_USER() and comparing the result against the known utility user.

    utility_user_password¶

    Option Description
    Command Line: Yes
    Config file utility_user_password=password
    Scope: Global
    Dynamic: No
    Data type String
    Default NULL

    Specifies the password required for the utility user.

    Option utility_user_password specifies the password for the utility user and must be specified or the server exits with an error.

    Utility user password
    utility_user_password=Passw0rD
    

    utility_user_schema_access¶

    Option Description
    Command Line: Yes
    Config file utility_user_schema_access=schema,schema,schema
    Scope: Global
    Dynamic: No
    Data type String
    Default NULL

    Specifies the schemas that the utility user has access to in a comma delimited list.

    Option utility_user_schema_access specifies the name(s) of the schema(s) that the utility user will have access to read write and modify. If a particular schema named here does not exist on start up it will be ignored. If a schema by the name of any of those listed in this option is created after the server is started, the utility user will have full access to it.

    Utility user schema access
    utility_user_schema_access=schema1,schema2,schema3
    

    utility_user_privileges¶

    Option Description
    Command Line: Yes
    Config file utility_user_privileges=privilege1,privilege2,privilege3
    Scope: Global
    Dynamic: No
    Data type String
    Default NULL

    This variable can be used to specify a comma-separated list of extra access privileges to grant to the utility user. Supported values for the privileges list are: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, GRANT, 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

    Option utility-user-privileges allows a comma-separated list of extra access privileges to grant to the utility user.

    Utility user privileges
    utility-user-privileges =”CREATE,DROP,LOCK TABLES”;
    

    utility_user_dynamic_privileges¶

    Option Description
    Command Line: Yes
    Config file utility_user_dynamic_privileges=privilege1,privilege2,privilege3
    Scope: Global
    Dynamic: No
    Data type String
    Default NULL

    This variable was implemented in Percona Server for MySQL 8.0.20-11.

    This variable allows a comma-separated list of extra access dynamic privileges to grant to the utility user. The supported values for the dynamic privileges are:

    • APPLICATION_PASSWORD_ADMIN

    • AUDIT_ADMIN

    • BACKUP_ADMIN

    • BINLOG_ADMIN

    • BINLOG_ENCRYPTION_ADMIN

    • CLONE_ADMIN

    • CONNECTION_ADMIN

    • ENCRYPTION_KEY_ADMIN

    • FIREWALL_ADMIN

    • FIREWALL_USER

    • GROUP_REPLICATION_ADMIN

    • INNODB_REDO_LOG_ARCHIVE

    • NDB_STORED_USER

    • PERSIST_RO_VARIABLES_ADMIN

    • REPLICATION_APPLIER

    • REPLICATION_SLAVE_ADMIN

    • RESOURCE_GROUP_ADMIN

    • RESOURCE_GROUP_USER

    • ROLE_ADMIN

    • SESSION_VARIABLES_ADMIN

    • SET_USER_ID

    • SHOW_ROUTINE

    • SYSTEM_USER

    • SYSTEM_VARIABLES_ADMIN

    • TABLE_ENCRYPTION_ADMIN

    • VERSION_TOKEN_ADMIN

    • XA_RECOVER_ADMIN

    Other dynamic privileges may be defined by plugins.

    Option utility_user_dynamic_privileges allows a comma-separated list of extra-access dynamic privileges to grant to the utility user.

    Utility user dynamic privileges
    utility_user_dynamic_privileges =”SYSTEM_USER,AUDIT_ADMIN”;
    

    Contact us

    For free technical help, visit the Percona Community Forum.

    To report bugs or submit feature requests, open a JIRA ticket.

    For paid support and managed or consulting services , contact Percona Sales.


    Last update: 2023-01-12
    Percona LLC and/or its affiliates, © 2023
    Made with Material for MkDocs

    Cookie consent

    We use cookies to recognize your repeated visits and preferences, as well as to measure the effectiveness of our documentation and whether users find what they're searching for. With your consent, you're helping us to make our documentation better.