Oracle收集用户的权限
Jun 07, 2016 pm 05:27 PM系统上线前准备工作。一般迁移数据的时候经常使用导入dump文件的方法。由于是一个一个用户导入进去的,所以在导入过程中会报权限
【使用场景】系统上线前准备工作。一般迁移数据的时候经常使用导入dump文件的方法。由于是一个一个用户导入进去的,所以在导入过程中会报权限问题的错误(可以忽略),还必须在生产库授权系统才能正常工作。使用下面的脚本可以得到授权语句。
在UAT环境执行select语句,在生产环境执行grant语句,根据实际用户做相应的修改。
注意:查询的用户需要有查看dba_开头数据字典的权限,否则报出表或者视图不存在。
SELECT 'GRANT ' || T1.GRANTED_ROLE || ' TO ' || T1.GRANTEE || ';' AS TEXT
FROM DBA_ROLE_PRIVS T1
WHERE T1.GRANTEE IN ('ABOQ',
'AMLM',
'BOP',
'CREDIT',
'DATACORE',
'MDR2',
'METABASE',
'METABASE_CREDIT',
'REPORT',
'UPRR',
'WORK')
UNION ALL
SELECT 'GRANT ' || T2.PRIVILEGE || ' TO ' || T2.GRANTEE || ';' AS TEXT
FROM DBA_SYS_PRIVS T2
WHERE T2.GRANTEE IN ('ABOQ',
'AMLM',
'BOP',
'CREDIT',
'DATACORE',
'MDR2',
'METABASE',
'METABASE_CREDIT',
'REPORT',
'UPRR',
'WORK')
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 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');
如果遇到输出文本问题请参考:

Hot Article

Hot tools Tags

Hot Article

Hot Article Tags

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

Reduce the use of MySQL memory in Docker

How do you alter a table in MySQL using the ALTER TABLE statement?

How to solve the problem of mysql cannot open shared library

What is SQLite? Comprehensive overview

Run MySQl in Linux (with/without podman container with phpmyadmin)

Running multiple MySQL versions on MacOS: A step-by-step guide

How do I secure MySQL against common vulnerabilities (SQL injection, brute-force attacks)?

How do I configure SSL/TLS encryption for MySQL connections?
