Oracle收集用户的权限

【使用场景】系统上线前准备工作。一般迁移数据的时候经常使用导入dump文件的方法。由于是一个一个用户导入进去的，所以在导入过程中会报权限问题的错误（可以忽略），还必须在生产库授权系统才能正常工作。使用下面的脚本可以得到授权语句。

在UAT环境执行select语句，在生产环境执行grant语句,根据实际用户做相应的修改。

注意：查询的用户需要有查看dba_开头数据字典的权限，否则报出表或者视图不存在。SELECT "GRANT " || T1.GRANTED_ROLE || " TO " || T1.GRANTEE || ";" AS TEXT

FROM DBA_ROLE_PRIVS T1WHERE T1.GRANTEE IN （"ABOQ","AMLM","BOP","CREDIT","DATACORE","MDR2","METABASE","METABASE_CREDIT","REPORT","UPRR","WORK"）UNION ALLSELECT "GRANT " || T2.PRIVILEGE || " TO " || T2.GRANTEE || ";" AS TEXT

FROM DBA_SYS_PRIVS T2WHERE T2.GRANTEE IN （"ABOQ","AMLM","BOP","CREDIT","DATACORE","MDR2","METABASE","METABASE_CREDIT","REPORT","UPRR","WORK"）UNION ALLSELECT "GRANT " || T3.PRIVILEGE || " ON " || T3.OWNER || "." ||

T3.TABLE_NAME || " TO " || T3.GRANTEE || ";" AS TEXT

FROM DBA_TAB_PRIVS T3WHERE T3.GRANTEE IN （"ABOQ","AMLM","BOP","CREDIT","DATACORE","MDR2","METABASE","METABASE_CREDIT","REPORT","UPRR","WORK"）;这样写比较死板，使用‘&’替换变量也很不好用，可以建立一个存储过程。create or replace procedure user_priv（username in varchar2） as
cursor v_cur is
SELECT "GRANT " || T1.GRANTED_ROLE || " TO " || T1.GRANTEE || ";" AS TEXT
FROM DBA_ROLE_PRIVS T1
where T1.grantee = upper（username）
union all
SELECT "GRANT " || T2.PRIVILEGE || " TO " || T2.GRANTEE || ";" AS TEXT
FROM DBA_SYS_PRIVS T2
WHERE T2.GRANTEE = upper（username）
union all
SELECT "GRANT " || T3.PRIVILEGE || " ON " || T3.OWNER || "." ||
T3.TABLE_NAME || " TO " || T3.GRANTEE || ";" AS TEXT
FROM DBA_TAB_PRIVS T3
where T3.grantee = upper（username）;
/*select privilege from dba_sys_privs where grantee in （select granted_role from dba_role_privs where grantee=upper（username））;如果有必要可-以收集角色中的权限*/

begin
for v_t in v_cur loop
begin
dbms_output.put_line（v_t.TEXT）;
end;
end loop;
end;

最后直接运行procedure即可 execute user_priv （username => "hr"）;如果遇到输出文本问题请参考：http://www.linuxidc.com/Linux/2013-04/82174.htm更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12Oracle完整性约束ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes相关资讯 Oracle用户权限 Oracle权限

Oracle 11g用户权限管理学习笔记（10/02/2015 10:11:44）
Oracle--用户管理与权限分配（11/27/2013 12:05:48）
Oracle用户的单张表的读写权限控制（10/29/2013 19:16:27）

Oracle查看用户权限（07/16/2014 09:49:34）
Oracle用户管理与权限设置（11/26/2013 11:50:31）
Oracle中的角色和权限授予（09/26/2013 17:48:04）

本文评论查看全部评论（0）