Users and Permissions

Description

In M-compatible mode, the behaviors and syntaxes related to user and permission control inherit the GaussDB mechanism but are not synchronized with those in MySQL.

User and permission behaviors are the same as those in GaussDB. For details, see "Database Security Management > Managing Users and Their Permissions" in Developer Guide.

Some syntaxes for users and permissions are tailored in GaussDB. For details about the syntaxes, see "SQL Reference > SQL Syntax > SQL Statements" in M-Compatibility Developer Guide. For details about the syntax differences between an M-compatible database and GaussDB, see Table 1.

When a user is created, a schema with the same name as the user is automatically created in an M-compatible database, but it is not created in MySQL.

Differences

• Syntax format differences

  For details about the M-compatible permission granting syntaxes, see "SQL Reference > SQL Syntax > G > GRANT" in M-Compatibility Developer Guide. The permission granting syntax in MySQL is as follows:

  -- Global, database-level, table-level, and stored procedure–level permission granting syntax
  GRANT
       priv_type [(column_list)]
         [, priv_type [(column_list)]] ...
       ON [object_type] priv_level
       TO user [auth_option] [, user [auth_option]] ...
       [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
       [WITH {GRANT OPTION | resource_option} ...]
   
  -- Syntax for granting permissions to a user proxy
  GRANT PROXY ON user
       TO user [, user] ...
       [WITH GRANT OPTION]
   
  object_type: {
       TABLE
    | FUNCTION
    | PROCEDURE
  }
   
  priv_level: {
       *
    | *.*
    | db_name.*
    | db_name.tbl_name
    | tbl_name
    | db_name.routine_name
  }
   
  user:
       'user_name'@'host_name'
   
  auth_option: {
       IDENTIFIED BY 'auth_string'
    | IDENTIFIED WITH auth_plugin
    | IDENTIFIED WITH auth_plugin BY 'auth_string'
    | IDENTIFIED WITH auth_plugin AS 'auth_string'
    | IDENTIFIED BY PASSWORD 'auth_string'
  }
   
  tls_option: {
       SSL
    | X509
    | CIPHER 'cipher'
    | ISSUER 'issuer'
    | SUBJECT 'subject'
  }
   
  resource_option: {
    | MAX_QUERIES_PER_HOUR count
    | MAX_UPDATES_PER_HOUR count
    | MAX_CONNECTIONS_PER_HOUR count
    | MAX_USER_CONNECTIONS count
  }

• Differences in types of permissions granted
  In MySQL, the following types of permissions can be granted.

  Table 2 Types of permissions that can be granted in MySQL

  Permission Type	Definition and Permission Level
  ALL [PRIVILEGES]	Grants all permissions of a specified access level, except GRANT OPTION and PROXY.
  ALTER	Enables ALTER TABLE. Level: global, database, and table.
  ALTER ROUTINE	Allows you to modify or delete stored procedures. Level: global, database, and routine.
  CREATE	Enables database and table creation. Level: global, database, and table.
  CREATE ROUTINE	Enables stored procedure creation. Level: global and database.
  CREATE TABLESPACE	Allows you to create, modify, or delete tablespaces or log file groups. Level: global.
  CREATE TEMPORARY TABLES	Enables CREATE TEMPORARY TABLE. Level: global and database.
  CREATE USER	Enable CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES. Level: global.
  CREATE VIEW	Allows you to create or modify views. Level: global, database, and table.
  DELETE	Enable DELETE. Level: global, database, and table.
  DROP	Allows you to delete databases, tables, or views. Level: global, database, and table.
  EVENT	Enable scheduled tasks. Level: global and database.
  EXECUTE	Allows you to execute stored procedures. Level: global, database, and stored procedure.
  FILE	Allows you to enable the server to read or write files. Level: global.
  GRANT OPTION	Allows you to grant permissions to or remove permissions from other accounts. Level: global, database, table, stored procedure, and proxy.
  INDEX	Allows you to create or delete indexes. Level: global, database, and table.
  INSERT	Enables INSERT. Level: global, database, table, and column.
  LOCK TABLES	LOCK TABLES is enabled on tables with the SELECT permission. Level: global and database.
  PROCESS	Allows you to view all running threads through SHOW PROCESSLIST. Level: global.
  PROXY	Enables a user proxy. Level: from user to user.
  REFERENCES	Enables foreign key creation. Level: global, database, table, and column.
  RELOAD	Enables FLUSH. Level: global.
  REPLICATION CLIENT	Allows you to query the location of the source server or replica server. Level: global.
  REPLICATION SLAVE	Allows replicas to read binary logs from the source. Level: global.
  SELECT	Enables SELECT. Level: global, database, table, and column.
  SHOW DATABASES	Enables SHOW DATABASES to display all databases. Level: global.
  SHOW VIEW	Enables SHOW CREATE VIEW. Level: global, database, and table.
  SHUTDOWN	Enables mysqladmin shutdown. Level: global.
  SUPER	Enables other management operations, such as the CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL, and mysqladmin debug commands. Level: global.
  TRIGGER	Enables TRIGGER. Level: global, database, and table.
  UPDATE	Enables UPDATE. Level: global, database, table, and column.
  USAGE	Equivalent to "no privilege".

  M-compatible databases support the following permissions by level:

  Table 3 Types of permissions that can be granted in M-compatible databases

  Object	Permissions That Can Be Granted
  Schema	CREATE, USAGE, ALTER, DROP, and COMMENT
  Table and view	SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, ALTER, DROP, COMMENT, INDEX, and VACUUM
  Column	SELECT, INSERT, UPDATE, REFERENCES, and COMMENT
  Sequence	SELECT, USAGE, UPDATE, ALTER, DROP, and COMMENT

• The schema-level objects to which permissions are granted are represented by 'dbname.*' in MySQL, but '{DATABASE | SCHEMA} dbname' in M-compatible databases.
• In MySQL, a username consists of two parts: username@hostname, but a username is only itself in M-compatible databases.
• MySQL allows you to modify user authentication, secure connection, and resource parameter attributes (including auth_option, tls_option, and resource option) with the GRANT syntax. In M-compatible databases, permission granting syntax does not support this function, and you need to use CREATE USER and ALTER USER to set user attributes.
• MySQL supports permission granting with a user proxy. GRANT PROXY ON is used to manage permissions of users in a unified manner. MySQL 5.7 does not provide the role mechanism, but MySQL 8.0 and M-compatible databases provide the role mechanism. If a role can manage and control the permissions of users in a unified manner, it can replace GRANT PROXY ON.
• M-compatible databases have a concept called public. All users have public permissions and they can query some system catalogs and system views. Users can grant or revoke public permissions. In MySQL, newly created users have only the global usage permission, which is almost low to none. They have only the permission to connect to the database and query the information_schema database.
• In M-compatible databases, the owner of an object has all permissions on the object by default. For security purposes, the owner can discard some permissions. However, ALTER, DROP, COMMENT, INDEX, VACUUM, and re-grantable permissions on the object are implicitly inherent permissions of the owner: MySQL does not have a concept called owner. Even if a user creates a table, the user cannot perform operations such as IUD on the table without being granted the corresponding permissions.
• In MySQL, All users have the USAGE permission, which indicates no permission. When REVOKE or GRANT USAGE is executed, no modification is performed. In M-compatible databases, the USAGE permission has the following meanings:
  • For schemas, USAGE allows access to objects contained in the schema. Without this permission, it is still possible to see the object names.
  • For sequences, USAGE allows use of the nextval function.
• In M-compatible databases, administrator roles can be set for users, including system administrator (SYSADMIN), security administrator (CREATEROLE), audit administrator (AUDITADMIN), monitor administrator (MONADMIN), O&M administrator (OPRADMIN), and security policy administrator (POLADMIN). By default, the system administrator with the SYSADMIN attribute has the highest permission in the system. After separation of duties is enabled, the system administrator does not have the CREATEROLE attribute (security administrator) or the AUDITADMIN attribute (audit administrator). That is, the system administrator can neither create roles or users, nor view or maintain database audit logs. In MySQL, administrator roles cannot be set for users, and there is no design for separation of duties.
• In M-compatible databases, the ANY permission can be granted to a user, indicating that the user can have the corresponding permission in non-system mode, including CREATE ANY TABLE, SELECT ANY TABLE, and CREATE ANY INDEX. In MySQL, ANY permission cannot be granted.
• MySQL provides SHOW GRANTS to query user permissions. In M-compatible databases, you can run a gsql client meta-command '\l+', '\dn+', or '\dp' to query permission information, or query related columns in system catalogs such as pg_namespace, pg_class, and pg_attribute for permission information.
• When a database, table, or column is deleted from MySQL, the related permission granting information is still retained in the system catalog. If an object with the same name is created again, the user still has the original permissions. In M-compatible databases, when a database, table, or column is deleted, related permission granting information is deleted. If an object with the same name is created again, permissions need to be granted again.
• When granting database-level permissions, MySQL supports fuzzy match of database names using underscores (_) and percent signs (%). However, M-compatible databases do not support fuzzy match of object names using special characters such as underscores (_) or percent signs (%), which are identified as common characters.
• In MySQL, if a user specified in the GRANT statement does not exist, a user account is created by default (this feature has been removed from MySQL 8.0). In M-compatible databases, permissions cannot be granted to users who are not created.