当前的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