如何检查分配给Schema，oracle数据库中的角色的对象的特权(DDL，DML，DCL)?

问题描述

大多数时候，我们都在愚蠢的事情上挣扎，以获取关于Schema，Role及其对象的特权的详细信息，并尝试找到一些简单的方法来获取有关它的所有详细信息以及伪查询代码以在其中生成授权语句.批量执行.所以我们要得到它.

Most of the time we struggle with silly things to get the detail of privileges on Schema, Role and their Objects and try to find some easy way to get all the detail about it along with there pseudo queries code to generate grant statements in bulk for further execution. So here we are to get it.

推荐答案

有关数据字典视图前缀的简短说明:

ALL_    -Describes PUBLIC Object grants.
USER_   -Describes current user Object grants.
DBA_    -Describes all object grants in the database.

有用的观看信息:

ROLE_ROLE_PRIVS     -describes the roles granted to other roles. 
ROLE_SYS_PRIVS      -describes system privileges granted to roles.
ROLE_TAB_PRIVS      -describes table privileges granted to roles. 
DBA_ROLE_PRIVS      -describes the roles granted to all users and roles in the database.
DBA_SYS_PRIVS       -describes system privileges granted to users and roles.
DBA_TAB_PRIVS       -describes all object grants in the database.
DBA_COL_PRIVS       -describes all column object grants in the database.

要了解有关 PRIVS 视图的更多信息，请访问这里.

To know more about PRIVS views visit here.

查询:

-关于用户/模式状态

select username,account_status, created from dba_users where username in ('SCOTT');

-检查为角色和架构分配的角色

select * from DBA_ROLE_PRIVS where grantee in ('SCOTT','RESOURCE');

-检查角色权限

select * from ROLE_ROLE_PRIVS where role in ('RESOURCE','CONNECT');    
select * from ROLE_TAB_PRIVS  where role in ('RESOURCE','CONNECT');
select * from ROLE_SYS_PRIVS  where role in ('RESOURCE','CONNECT');

Pseudo Code:
select 'grant '||privilege||' to ROLE_SLAVE;' from ROLE_SYS_PRIVS where role in ('RESOURCE','CONNECT');
select 'grant '||privilege||' to ROLE_SLAVE;' from ROLE_TAB_PRIVS where role in ('RESOURCE','CONNECT');

-检查已授予对象的模式特权

select * from DBA_SYS_PRIVS where grantee in ('SCOTT');
select * from DBA_TAB_PRIVS where grantee in ('SCOTT');
select * from DBA_COL_PRIVS where grantee in ('SCOTT');

Pseudo Code: 
select 'grant '||privilege||' to SCOTT_SLAVE;' from DBA_SYS_PRIVS where grantee in ('SCOTT');
select 'grant '||privilege||' on '||owner||'.'||table_name||' to SCOTT_SLAVE;' from DBA_TAB_PRIVS where grantee in ('SCOTT');
select 'grant '||privilege||' ('||column_name||') '||' on '||owner||'.'||table_name||' to SCOTT_SLAVE;' from DBA_COL_PRIVS where grantee in ('SCOTT');

谢谢！

Thank You!

这篇关于如何检查分配给Schema，oracle数据库中的角色的对象的特权(DDL，DML，DCL)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！