Users and Permissions

Updated on 2024-12-06 GMT+08:00

View PDF

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.

**Table 1** Syntax differences between an M-compatible database and GaussDB
No.	Syntax	Description	Difference
1	CREATE ROLE	Creates a role.	In an M-compatible database: Options involving the following keywords cannot be specified: ENCRYPTED, UNENCRYPTED, RESOURCE POOL, PERM SPACE, TEMP SPACE, and SPILL SPACE.
2	CREATE USER	Creates a user.
3	CREATE GROUP	Creates a user group. CREATE GROUP is the alias of CREATE ROLE and is not recommended.
4	ALTER ROLE	Modifies role attributes.
5	ALTER UER	Modifies user attributes.
6	ALTER GROUP	Modifies the attributes of a user group.	-
7	DROP ROLE	Deletes a role.	-
8	DROP USER	Deletes a user.	-
9	DROP GROUP	Deletes a user group.	-
10	DROP OWNED	Deletes the database objects owned by a database role.	-
11	REASSIGN OWNED	Changes the owner of a database object.	This syntax is not supported in an M-compatible database.
12	GRANT	Grants permissions to roles and users.	In an M-compatible database, permissions on objects such as functions, stored procedures, tablespaces, and database links cannot be granted or revoked.
13	REVOKE	Revokes permissions from one or more roles.
14	ALTER DEFAULT PRIVILEGES	Sets the permissions that will be granted to objects created in the future. (It does not affect permissions granted to existing objects.)	This syntax is not supported in an M-compatible database.

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	Enables DELETE. Level: global, database, and table.
DROP	Allows you to delete databases, tables, or views. Level: global, database, and table.
EVENT	Enables 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	Enables LOCK TABLES 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.

Parent topic: SQL

Previous topic: Other Statements

Next topic: System Catalogs and System Views

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot