如何获取Oracle中指定SCHEMA下所有SEQUENCE的授权DDL语句

我使用以下查询获取SEQUENCE的权限信息：

select * from dba_tab_privs where owner='TEST' and TYPE='SEQUENCE';

查询结果如下：

GRANTEE      OWNER  TABLE_NAME  GRANTOR PRIVILEGE   GRANTABLE   HIERARCHY   COMMON  TYPE    INHERITED

READ_WRITE   TEST   TEST_SEQ    TEST    SELECT       NO         NO            NO    SEQUENCE    NO

我需要生成类似这样的授权DDL语句：

grant select on TEST.TEST_SEQ to READ_WRITE;

之前尝试用dbms_metadata.get_ddl获取DDL，但得到的是SEQUENCE本身的定义，不是授权语句：

SELECT dbms_metadata.get_ddl(object_type, object_name, owner) || ';' AS object_ddl
FROM DBA_OBJECTS
WHERE OWNER = 'TEST' AND OBJECT_TYPE IN ('SEQUENCE')
ORDER BY OWNER, OBJECT_TYPE, OBJECT_NAME;

解决方案 #

直接从dba_tab_privs表拼接字段生成授权语句即可，该表包含了生成GRANT语句所需的全部信息：

SELECT 'grant ' || PRIVILEGE || ' on ' || OWNER || '.' || TABLE_NAME || ' to ' || GRANTEE || ';' AS grant_ddl
FROM dba_tab_privs
WHERE owner='TEST' AND TYPE='SEQUENCE';

这个查询会直接输出符合要求的授权DDL，若存在多条权限记录，也能批量生成对应的语句。

内容的提问来源于stack exchange，提问作者Satscreate