学步园

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

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

示例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';