查看Teradata数据库用户对于表的操作权限

通过dbc.allrights表中的UserName列，DatabaseName列，TableName列和AccessRight列的查询可以获取指定用于对于指定数据库中指定表的操作权限。可用于在执行某条SQL语句之前，判定当前用户是否有执行此语句的权限，在权限不足时还可以尝试自动授权（不太安全，执行完应当revoke）等措施。

AccessRight列缩写词对应列表（共40个）：

AccessRight	含义
AF	ALTER FUNCTION
AP	ALTER PROCEDURE
AS	ABORT SESSION
CD	CREATE DATABASE
CF	CREATE FUNCTION
CG	CREATE TRIGGER
CM	CREATE MACRO
CO	CREATE PROFILE
CP	CHECKPOINT
CR	CREATE ROLE
CT	CREATE TABLE
CU	CREATE USER
CV	CREATE VIEW
D	DELETE
DD	DROP DATABASE
DF	DROP FUNCTION
DG	DROP TRIGGER
DM	DROP MACRO
DO	DROP PROFILE
DP	DUMP
DR	DROP ROLE
DT	DROP TABLE
DU	DROP USER
DV	DROP VIEW
E	EXECUTE
EF	EXECUTE FUNCTION
I	INSERT
IX	INDEX
MR	MONITOR RESOURCE
MS	MONITOR SESSION
PC	CREATE PROCEDURE
PD	DROP PROCEDURE
PE	EXECUTE PROCEDURE
RO	REPLICATION OVERRIDE
R	RETRIEVE/SELECT
RF	REFERENCE
RS	RESTORE
SS	SET SESSION RATE
SR	SET RESOURCE RATE
U	UPDATE

示例SQL语句：

select username, databasename, tablename, accessright from dbc.allrights 

    where databasename='systemfe' and username='dbc' and tablename='opt_ras_table';

上述语句的执行结果为：

*** Query completed. 12 rows found. 4 columns returned.

*** Total elapsed time was 1 second.
 

UserName                        DatabaseName                    TableName                       AccessRight

------------------------------  ------------------------------  ------------------------------  -----------

DBC                             SystemFe                        opt_ras_table                   DT

DBC                             SystemFe                        opt_ras_table                   U

DBC                             SystemFe                        opt_ras_table                   DG

DBC                             SystemFe                        opt_ras_table                   RF

DBC                             SystemFe                        opt_ras_table                   RS

DBC                             SystemFe                        opt_ras_table                   R

DBC                             SystemFe                        opt_ras_table                   I

DBC                             SystemFe                        opt_ras_table                   CG

DBC                             SystemFe                        opt_ras_table                   ST

DBC                             SystemFe                        opt_ras_table                   DP

DBC                             SystemFe                        opt_ras_table                   D

DBC                             SystemFe                        opt_ras_table                   IX

如下的SQL语句可以自动构建出授予权限的SQL语句（即GRANT语句）：

SEL 
 
TRIM(username) 
 
,TRIM(databasename)
 
,TRIM(tablename)
 
,'GRANT '|| CASE 
 
WHEN AccessRight = 'AF ' THEN 'ALTER FUNCTION' 
 
WHEN AccessRight = 'AP ' THEN 'ALTER PROCEDURE' 
 
WHEN AccessRight = 'AS ' THEN 'ABORT SESSION' 

WHEN AccessRight = 'CD ' THEN 'CREATE DATABASE' 

WHEN AccessRight = 'CF ' THEN 'CREATE FUNCTION' 

WHEN AccessRight = 'CG ' THEN 'CREATE TRIGGER' 

WHEN AccessRight = 'CM ' THEN 'CREATE MACRO' 

WHEN AccessRight = 'CO ' THEN 'CREATE PROFILE' 

WHEN AccessRight = 'CP ' THEN 'CHECKPOINT' 

WHEN AccessRight = 'CR ' THEN 'CREATE ROLE' 

WHEN AccessRight = 'CT ' THEN 'CREATE TABLE' 

WHEN AccessRight = 'CU ' THEN 'CREATE USER' 

WHEN AccessRight = 'CV ' THEN 'CREATE VIEW' 

WHEN AccessRight = 'D ' THEN 'DELETE' 

WHEN AccessRight = 'DD ' THEN 'DROP DATABASE' 

WHEN AccessRight = 'DF ' THEN 'DROP FUNCTION' 

WHEN AccessRight = 'DG ' THEN 'DROP TRIGGER' 

WHEN AccessRight = 'DM ' THEN 'DROP MACRO' 

WHEN AccessRight = 'DO ' THEN 'DROP PROFILE' 

WHEN AccessRight = 'DP ' THEN 'DUMP' 

WHEN AccessRight = 'DR ' THEN 'DROP ROLE' 

WHEN AccessRight = 'DT ' THEN 'DROP TABLE' 

WHEN AccessRight = 'DU ' THEN 'DROP USER' 

WHEN AccessRight = 'DV ' THEN 'DROP VIEW' 

WHEN AccessRight = 'E ' THEN 'EXECUTE' 

WHEN AccessRight = 'EF ' THEN 'EXECUTE FUNCTION' 

WHEN AccessRight = 'I ' THEN 'INSERT' 

WHEN AccessRight = 'IX ' THEN 'INDEX' 

WHEN AccessRight = 'MR ' THEN 'MONITOR RESOURCE' 

WHEN AccessRight = 'MS ' THEN 'MONITOR SESSION' 

WHEN AccessRight = 'PC ' THEN 'CREATE PROCEDURE' 

WHEN AccessRight = 'PD ' THEN 'DROP PROCEDURE' 

WHEN AccessRight = 'PE ' THEN 'EXECUTE PROCEDURE' 

WHEN AccessRight = 'RO ' THEN 'REPLICATION OVERRIDE' 

WHEN AccessRight = 'R ' THEN 'RETRIEVE/SELECT' 

WHEN AccessRight = 'RF ' THEN 'REFERENCE' 

WHEN AccessRight = 'RS ' THEN 'RESTORE' 

WHEN AccessRight = 'SS ' THEN 'SET SESSION RATE' 

WHEN AccessRight = 'SR ' THEN 'SET RESOURCE RATE' 

WHEN AccessRight = 'U ' THEN 'UPDATE' 

END || ' ON '||TRIM(databasename)||'.'||TRIM(tablename)||' to '||TRIM(username)||';' AS Permission

FROM dbc.AllRights

WHERE DatabaseName = 'DBNAME' and USERNAME = 'LOGGEDINUSERNAME' AND TABLENAME = 'TABLENAME';