华为GaussDB T CREATE ROLE

墨天轮 2019-10-12

437

CREATE ROLE

功能描述

创建一个数据库角色。

角色是一组权限（系统权限和对象权限）的集合。为了方便用户使用，创建数据库成功后，默认创建了如下系统角色：

DBA：具有所有系统权限，该角色不可删除。

RESOURCE：具有创建存储过程、函数、触发器、表、序列的权限。

CONNECT：具有连接数据库的权限。

STATISTICS：具有创建、查看WSR快照、生成WSR报告的权限，但是没有修改设置WSR参数的权限。此角色具有的权限如下：

OBJECT OWNER	OBJECT NAME	OBJECT_TYPE	PRIVILEGES	GRANTABLE
SYS	ADM_HIST_LONGSQL	VIEW	SELECT	NO
SYS	ADM_HIST_PARAMETER	VIEW	SELECT	NO
SYS	ADM_HIST_SNAPSHOT	VIEW	SELECT	NO
SYS	ADM_HIST_SQLAREA	VIEW	SELECT	NO
SYS	ADM_HIST_SYSSTAT	VIEW	SELECT	NO
SYS	ADM_HIST_SYSTEM	VIEW	SELECT	NO
SYS	ADM_HIST_SYSTEM_EVENT	VIEW	SELECT	NO
SYS	ADM_HIST_WR_CONTROL	VIEW	SELECT	NO
SYS	ADM_SEGMENTS	VIEW	SELECT	NO
SYS	ADM_TABLESPACES	VIEW	SELECT	NO
SYS	DBMS_JOB	PROCEDURE	EXECUTE	NO
SYS	DV_BUFFER_POOL_STATS	VIEW	SELECT	NO
SYS	DV_DATABASE	VIEW	SELECT	NO
SYS	DV_INSTANCE	VIEW	SELECT	NO
SYS	DV_LATCHS	VIEW	SELECT	NO
SYS	DV_LIBRARY_CACHE	VIEW	SELECT	NO
SYS	DV_LOCKED_OBJECTS	VIEW	SELECT	NO
SYS	DV_LOCKS	VIEW	SELECT	NO
SYS	DV_LONG_SQL	VIEW	SELECT	NO
SYS	DV_OPEN_CURSORS	VIEW	SELECT	NO
SYS	DV_PARAMETERS	VIEW	SELECT	NO
SYS	DV_SEGMENT_STATS	VIEW	SELECT	NO
SYS	DV_SESSIONS	VIEW	SELECT	NO
SYS	DV_SESSION_EVENTS	VIEW	SELECT	NO
SYS	DV_SQLS	VIEW	SELECT	NO
SYS	DV_SYSTEM	VIEW	SELECT	NO
SYS	DV_SYS_EVENTS	VIEW	SELECT	NO
SYS	DV_SYS_STATS	VIEW	SELECT	NO
SYS	DV_TEMP_POOLS	VIEW	SELECT	NO
SYS	DV_TRANSACTIONS	VIEW	SELECT	NO
SYS	DV_UNDO_SEGMENTS	VIEW	SELECT	NO
SYS	DV_WAIT_STATS	VIEW	SELECT	NO
SYS	SNAP_ID$	SEQUENCE	SELECT	NO
SYS	WSR$CREATE_SESSION_SNAPSHOT	PROCEDURE	EXECUTE	NO
SYS	WSR$CREATE_SNAPSHOT	PROCEDURE	EXECUTE	NO
SYS	WSR$DROP_CTIME_PARTITION	PROCEDURE	EXECUTE	NO
SYS	WSR$DROP_SNAPSHOT_PARTITION	PROCEDURE	EXECUTE	NO
SYS	WSR$DROP_SNAPSHOT_RANGE	PROCEDURE	EXECUTE	NO
SYS	WSR$DROP_SNAPSHOT_TIME	PROCEDURE	EXECUTE	NO
SYS	WSR$INSERT_ERRORLOG	PROCEDURE	EXECUTE	NO
SYS	WSR$INSERT_SQL_LIST	PROCEDURE	EXECUTE	NO
SYS	WSR$INSTANCE_SNAP	PROCEDURE	EXECUTE	NO
SYS	WSR$QUERY_TOPSESSION_SQL	PROCEDURE	EXECUTE	NO
SYS	WSR$QUERY_TRANSACTION	PROCEDURE	EXECUTE	NO
SYS	WSR$TOPSESSION	PROCEDURE	EXECUTE	NO
SYS	WSR$TOPSESSION_TOPEVENT	PROCEDURE	EXECUTE	NO
SYS	WSR$TOPSQL_CPU_TIME	PROCEDURE	EXECUTE	NO
SYS	WSR$TOPSQL_ELAPSED_TIME	PROCEDURE	EXECUTE	NO
SYS	WSR$TOPSQL_EXECUTIONS	PROCEDURE	EXECUTE	NO
SYS	WSR$TOPSQL_GETS	PROCEDURE	EXECUTE	NO
SYS	WSR$TOPSQL_IO_WAIT	PROCEDURE	EXECUTE	NO
SYS	WSR$TOPSQL_LONGSQL_TIME	PROCEDURE	EXECUTE	NO
SYS	WSR$TOPSQL_LONGSQL_TIME_PREFIX	PROCEDURE	EXECUTE	NO
SYS	WSR$TOPSQL_PARSES	PROCEDURE	EXECUTE	NO
SYS	WSR$TOPSQL_READS	PROCEDURE	EXECUTE	NO
SYS	WSR$WRITE_INSTANCE_SNAP	PROCEDURE	EXECUTE	NO
SYS	WSR_CONTROL	TABLE	INSERT,SELECT,UPDATE	NO
SYS	WSR_DBA_SEGMENTS	TABLE	INSERT,SELECT	NO
SYS	WSR_EXCEPTION_LOG	TABLE	INSERT,SELECT	NO
SYS	WSR_INSTANCE_SNAP	TABLE	INSERT,SELECT	NO
SYS	WSR_LATCH	TABLE	DELETE,INSERT,SELECT	NO
SYS	WSR_LIBRARYCACHE	TABLE	DELETE,INSERT,SELECT	NO
SYS	WSR_LONGSQL	TABLE	INSERT,SELECT	NO
SYS	WSR_PARAMETER	TABLE	DELETE,INSERT,SELECT	NO
SYS	WSR_SEGMENT	TABLE	INSERT,SELECT	NO
SYS	WSR_SESSION_EVENTS	TABLE	INSERT,SELECT	NO
SYS	WSR_SESSION_SQL	TABLE	INSERT,SELECT	NO
SYS	WSR_SNAPSHOT	TABLE	DELETE,INSERT,SELECT	NO
SYS	WSR_SQLAREA	TABLE	INSERT,SELECT	NO
SYS	WSR_SQLPLAN	TABLE	INSERT,SELECT	NO
SYS	WSR_SQLTEXT	TABLE	INSERT,SELECT	NO
SYS	WSR_SQL_LIST	TABLE	INSERT,SELECT	NO
SYS	WSR_SQL_LIST_PLAN	TABLE	INSERT,SELECT	NO
SYS	WSR_SYSTEM	TABLE	DELETE,INSERT,SELECT	NO
SYS	WSR_SYSTEM_EVENT	TABLE	DELETE,INSERT,SELECT	NO
SYS	WSR_SYS_STAT	TABLE	DELETE,INSERT,SELECT	NO
SYS	WSR_TRANSACTION	TABLE	INSERT,SELECT	NO
SYS	WSR_WAITSTAT	TABLE	DELETE,INSERT,SELECT	NO

注意事项

执行该语句的用户需要有CREATE ROLE系统权限。
角色名不能与数据库中已有的用户名和角色名重名，否则会提示错误信息。

语法格式

CREATE ROLE role_name [ IDENTIFIED BY password  [ ENCRYPTED ]]


参数说明
role_name角色名。
若角色名包含_#$以外的特殊字符或者空格，则角色名必须用双引号（""）或者反引号（``）括起来。
IDENTIFIED BY创建带密码的角色。IDENTIFIED BY后面是密码。
password预留属性，目前暂时未使用。
ENCRYPTED用于标识指定的密码是加密的密文，当指定为密文时不校验密码规范。
使用ENCRYPTED方式创建的角色需要用明文密码登录，不推荐采用此方式创建角色。


示例
创建一个名为developers的角色。
--删除角色developers。
DROP ROLE developers;

--创建角色developers。
CREATE ROLE developers;

  gaussdb t
  「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
 关注作者
 
  
 【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
   评论

 领墨值

 
  有奖问卷
  意见反馈 
 客服小墨