GRANT

功能描述

GRANT用于授予一个或多个角色的权限。

注意事项

本章节只包含dolphin新增的语法，原openGauss的语法未做删除和修改。 增加ALTER ROUTINE、CRAETE ROUTINE、CREATE TEMPORARY TABLES、CREATE USER、CREATE TABLESPACE、INDEX权限。增加USAGE语法。 增加GRANT role_name TO role_name语法。

语法格式

• 新增ALTER ROUTINE权限

与function和procedure的alter权限基本一致

修改后的语法说明为：

GRANT { { EXECUTE | ALTER ROUTINE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
ON {FUNCTION {function_name ( [ {[ argmode ] [ arg_name ] arg_type} [, ...] ] )} | PROCEDURE {proc_name ( [ {[ argmode ] [ arg_name ] arg_type} [, ...] ] )} [, ...] | ALL FUNCTIONS IN SCHEMA schema_name [, ...] | ALL PROCEDURE IN SCHEMA schema_name [, ...] | schema_name.*}
TO { [ GROUP ] role_name | PUBLIC } [, ...]
[ WITH GRANT OPTION ];

• 新增CREATE ROUTINE权限

与CREATE ANY FUNCTION权限基本一致

修改后的语法说明为：

GRANT { CREATE ANY TABLE | ALTER ANY TABLE | DROP ANY TABLE | SELECT ANY TABLE | INSERT ANY TABLE | UPDATE ANY TABLE |
  DELETE ANY TABLE | CREATE ANY SEQUENCE | CREATE ANY INDEX | CREATE ANY FUNCTION | CREATE ROUTINE | EXECUTE ANY FUNCTION |
  CREATE ANY PACKAGE | EXECUTE ANY PACKAGE | CREATE ANY TYPE } [, ...]
  [ON *.*]
  TO [ GROUP ] role_name [, ...]
  [ WITH ADMIN OPTION ];

• 新增CREATE TEMPORARY TABLES权限

与TEMPORARY权限基本一致

修改后的语法说明为：

GRANT { { CREATE | CONNECT | CREATE TEMPORARY TABLES | TEMPORARY | TEMP | ALTER | DROP | COMMENT } [, ...]
    | ALL [ PRIVILEGES ] }
    ON { DATABASE database_name [, ...] | database_name.* }
    TO { [ GROUP ] role_name | PUBLIC } [, ...]
    [ WITH GRANT OPTION ];

• 新增CREATE USER权限

控制用户创建新用户的权限，与用户的CREATEROLE和 NOCREATEROLE权限基本一致

新增的语法说明为：

GRANT CREATE USER ON *.* TO ROLE_NAME;

• 新增CREATE TABLESPACE权限

控制用户创建新表空间的权限

新增的语法说明为：

GRANT CREATE TABLESPACE ON *.* TO ROLE_NAME;

• 新增INDEX权限

与CREATE ANY INDEX权限基本一致

修改后的语法说明为：

GRANT INDEX 
  ON *.* 
  TO [ GROUP ] role_name [, ...]
  [ WITH ADMIN OPTION ];

• 新增USAGE语法

当用户不存在时，GRANT USAGE会创建用户；当用户存在时，GRANT USAGE会修改用户的密码。创建/修改用户密码的权限要求和直接使用CREATE USER/ALTER USER时一致。

修改后的语法说明为：

GRANT USAGE 
  ON *.* TO role_name
  IDENTIFIED BY [PASSWORD] password_string;

• 新增GRANT role_name TO role_name语法

授予一个角色的权限给另一个角色

修改后的语法说明为：

GRANT role_name TO role_name [ WITH GRANT OPTION ];

参数说明

N/A

示例

GRANT ALTER ROUTINE ON FUNCTION TEST TO USER_TESTER;

GRANT CREATE ANY FUNCTION TO USER_TESTER;

GRANT CREATE TEMPORARY TABLES ON DATABASE DATABASE_TEST TO USER_TESTER; 

GRANT CREATE USER ON *.* TO USER_TESTER;

GRANT CREATE TABLESPACE ON *.* TO USER_TESTER;

GRANT INDEX TO TEST_USER;

openGauss=# GRANT USAGE ON *.* TO new_user IDENTIFIED BY 'test-1234';
WARNING:  Using GRANT for creating new user is deprecatedand will be removed in future release. Create new user with CREATE USER statement.
ALTER ROLE
openGauss=# GRANT USAGE ON *.* TO new_user IDENTIFIED BY 'new_password-1234';
WARNING:  Using GRANT statement to modify existing user's password is deprecated and will be removed in future release. Use ALTER USER statement for this operation.
ALTER ROLE

openGauss=# CREATE USER user1 IDENTIFIED BY 'test-1234';
NOTICE:  The iteration value of password is not recommended.Setting the iteration value too small reduces the security of the password, and setting it too large results in performance degradation.
CREATE ROLE
openGauss=# CREATE USER user2 IDENTIFIED BY 'test-1234';
NOTICE:  The iteration value of password is not recommended.Setting the iteration value too small reduces the security of the password, and setting it too large results in performance degradation.
CREATE ROLE
openGauss=# GRANT user1 TO user2;
GRANT ROLE
openGauss=# GRANT 'user1' TO user2;
NOTICE:  role "user2" is already a member of role "user1"
GRANT ROLE
openGauss=# GRANT user1 TO 'user2';
NOTICE:  role "user2" is already a member of role "user1"
GRANT ROLE

相关链接

GRANT