Trafodion 权限相关命令

当前的Trafodion数据库支持通过LDAP的方式来管理用户及权限。Trafodion有很多与权限控制相关的SQL命令，本文列举一些常用的SQL语法供读者参考，这些SQL命令大部分可以通过Trafodion官网的SQL手册查看到，可能有少部分目前尚未维护在官网中。

1 查看系统有哪些用户(get users)

SQL>get users;

Users
=====

DB__ADMIN
DB__ROOT
GTM

2 查看系统有哪些角色(get roles)

SQL>get roles;

Roles
=====

DB__ADMINROLE
DB__HBASEROLE
DB__HIVEROLE
DB__LIBMGRROLE
DB__ROOTROLE
DB__SERVICESROLE
PUBLIC

3 查看某个用户属于哪个角色(get roles for user user_name)

SQL>get roles for user gtm;

PUBLIC

4 查看某个角色有哪些用户(get users for role role_name)

SQL>get users for role DB__ADMINROLE;

DB__ADMIN

5 查看某个用户有哪些权限(get privileges for user user_name)

SQL>get privileges for user gtm;

SIDU-R-    TRAFODION."GTM"."SB_HISTOGRAMS"
SIDU-R-    TRAFODION."GTM"."SB_HISTOGRAM_INTERVALS"
SIDU-R-    TRAFODION."GTM"."SB_PERSISTENT_SAMPLES"
SIDU-R-    TRAFODION.GTM.T001
SIDU-R-    TRAFODION.GTM2.SB_HISTOGRAMS
SIDU-R-    TRAFODION.GTM2.SB_HISTOGRAM_INTERVALS
SIDU-R-    TRAFODION.GTM2.SB_PERSISTENT_SAMPLES
S--U---    TRAFODION.SEABASE.T4 <Column> ID

6 查看某个角色有哪些权限(get privileges for role role_name)

SQL>get privileges for role DB__ROOTROLE;

--- SQL operation complete.

7 查看系统有哪些全局权限(get component privileges on sql_operations)

SQL>get component privileges on sql_operations;

Operation information on Component SQL_OPERATIONS
=================================================

ALTER
ALTER_LIBRARY
ALTER_ROUTINE
ALTER_ROUTINE_ACTION
ALTER_SCHEMA
ALTER_SEQUENCE
ALTER_SYNONYM
ALTER_TABLE
ALTER_TRIGGER
ALTER_VIEW
CREATE
CREATE_CATALOG
CREATE_INDEX
CREATE_LIBRARY
CREATE_PROCEDURE
CREATE_ROUTINE
CREATE_ROUTINE_ACTION
CREATE_SCHEMA
CREATE_SEQUENCE
CREATE_SYNONYM
CREATE_TABLE
CREATE_TRIGGER
CREATE_VIEW
DML_DELETE
DML_EXECUTE
DML_INSERT
DML_REFERENCES
DML_SELECT
DML_SELECT_METADATA
DML_UPDATE
DML_USAGE
DROP
DROP_CATALOG
DROP_INDEX
DROP_LIBRARY
DROP_PROCEDURE
DROP_ROUTINE
DROP_ROUTINE_ACTION
DROP_SCHEMA
DROP_SEQUENCE
DROP_SYNONYM
DROP_TABLE
DROP_TRIGGER
DROP_VIEW
MANAGE
MANAGE_COMPONENTS
MANAGE_LIBRARY
MANAGE_LOAD
MANAGE_PRIVILEGES
MANAGE_ROLES
MANAGE_STATISTICS
MANAGE_TENANTS
MANAGE_USERS
QUERY_ACTIVATE
QUERY_CANCEL
QUERY_SUSPEND
REGISTER_HIVE_OBJECT
REMAP_USER
SHOW
UNREGISTER_HIVE_OBJECT
USE_ALTERNATE_SCHEMA

8 查看某个用户有哪些全局权限(get component privileges on sql_operations for user_name)

SQL>get component privileges on sql_operations for gtm;

Privilege information on Component SQL_OPERATIONS for GTM
=========================================================

CREATE_LIBRARY
DROP_TABLE