Users and Permissions

Overview

For details about user and permission management of GaussDB, see "Database Security Management > Managing Users and Their Permissions" in Developer Guide.

For details about the user and permission syntax, see "SQL Reference > SQL Syntax > SQL Statements" in M Compatibility Developer Guide.

Differences

Syntax format differences

For details about the authorization syntax of GaussDB, see "SQL Reference > SQL Syntax > SQL Statements > G > GRANT" in M Compatibility Developer Guide.

The authorization 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 1** 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".

GaussDB supports the following permissions based on objects.

**Table 2** Types of permissions that can be granted in GaussDB
Object	Permissions That Can Be Granted
Database	CREATE, CONNECT, TEMPORARY, TEMP, ALTER, DROP, and COMMENT
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

In MySQL, '*.*' represents a global-level authorization object; in GaussDB, '{DATABASE} db_name' represents a database-level authorization object. The database level of GaussDB corresponds to the global level of MySQL.
In MySQL, 'schema_name.*' represents a database/schema-level authorization object; in GaussDB, '{SCHEMA} schema_name' represents a schema-level authorization object. The schema level of GaussDB corresponds to the database/schema level of MySQL.
In MySQL, a username consists of two parts: username@hostname, but a username is only itself in GaussDB.
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 GaussDB, 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, while both MySQL 8.0 and GaussDB 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.
GaussDB has 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 GaussDB, 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 GaussDB, 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 GaussDB, 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, system administrators with the SYSADMIN attribute have the highest permission in the system. After separation of duties is enabled, a system administrator does not have the CREATEROLE or AUDITADMIN attribute. 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 GaussDB, 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 GaussDB, 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.
After a database, table, or column is deleted, the related permission granting information is still retained in system catalogs in MySQL. If an object with the same name is created again, the user still has the original permissions. However, related permission granting information will be deleted from GaussDB. If an object with the same name is created again, permissions need to be granted again.
When database-level permissions are granted, MySQL supports fuzzy match of database names using underscores (_) and percent signs (%). However, GaussDB does 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 GaussDB, permissions cannot be granted to users who are not created.