Oracle中dbms_metadata.get_granted_ddl无法过滤SYS对象授权的问题

数据老陈

2026-7-30

Oracle Schema迁移：提取SYS对象授权的问题解决

问题原因

你当前的SQL写法逻辑存在误区：dbms_metadata.get_granted_ddl('OBJECT_GRANT', 'USERNAME')函数本身会返回指定用户的所有对象级授权，外层dba_tab_privs的查询只是用来触发函数调用，函数不会参考外层的grantor = 'SYS'过滤条件，所以无论是否添加该条件，都会返回该用户的全部授权。

解决方案

方案一：直接从数据字典生成精准授权语句

这种方式更高效且可控，直接从dba_tab_privs过滤SYS授予的权限并生成DDL：

set long 1120000 longchunksize 1120000 pagesize 0 linesize 1000 feedback off verify off trimspool on
column grant_ddl format a1000
select 'GRANT ' || privilege || ' ON ' || owner || '.' || table_name || ' TO ' || grantee ||
       case when grantable = 'YES' then ' WITH GRANT OPTION;' else ';' end as grant_ddl
from dba_tab_privs
where grantee = 'USERNAME'
  and grantor = 'SYS';

方案二：结合DBMS_METADATA和正则筛选

如果需要保留dbms_metadata的格式输出，可以先提取所有授权，再通过正则表达式筛选SYS对象的授权：

set long 1120000 longchunksize 1120000 pagesize 0 linesize 1000 feedback off verify off trimspool on
column ddl format a1000
begin
    dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR', true);
    dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'PRETTY', true);
end;
/
select regexp_substr(ddl, 'GRANT .*ON SYS\..*;', 1, level) as ddl
from (
    select dbms_metadata.get_granted_ddl('OBJECT_GRANT', 'USERNAME') as ddl
    from dual
)
connect by level <= regexp_count(ddl, 'GRANT .*ON SYS\..*;');