в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T10:01:04Z

1 версия

Новая страница

==Query DBA_ROLE_PRIVS, ROLE_TAB_PRIVS for Name of the object, Privilege granted,Was admin option granted==



<source lang="sql">

SQL> select
4 ROLE_TAB_PRIVS.Table_Name, /*Name of the object*/
5 ROLE_TAB_PRIVS.Privilege, /*Privilege granted*/
6 ROLE_TAB_PRIVS.Grantable /*Was admin option granted?*/
7 from DBA_ROLE_PRIVS, ROLE_TAB_PRIVS
8 where DBA_ROLE_PRIVS.Granted_Role = ROLE_TAB_PRIVS.Role
9 and DBA_ROLE_PRIVS.Grantee = "some username";
SQL>
SQL>
SQL>

</source>

==Query DBA_ROLE_PRIVS, ROLE_TAB_PRIVS for Recipient of the grant,Owner of the object==



<source lang="sql">

SQL> select
2 DBA_ROLE_PRIVS.Grantee, /*Recipient of the grant*/
3 ROLE_TAB_PRIVS.Owner /*Owner of the object*/
7 from DBA_ROLE_PRIVS, ROLE_TAB_PRIVS
8 where DBA_ROLE_PRIVS.Granted_Role = ROLE_TAB_PRIVS.Role
9 and DBA_ROLE_PRIVS.Grantee = "some username";
SQL>
SQL>
SQL>

</source>

==Query dba_role_privs table==



<source lang="sql">

SQL>
SQL> set verify off
SQL>
SQL> column grole format a20 heading "Role Granted"
SQL> column wadmin format a6 heading "Admin?"
SQL>
SQL> select granted_role grole, initcap(admin_option) wadmin
2 from dba_role_privs
3 where rownum < 50
4 /
Role Granted Admin?
-------------------- ------
CONNECT No
RESOURCE No
XDBADMIN No
PLUSTRACE Yes
XDBWEBSERVICES No
SCHEDULER_ADMIN Yes
EXP_FULL_DATABASE No
IMP_FULL_DATABASE No
DELETE_CATALOG_ROLE Yes
SELECT_CATALOG_ROLE Yes
EXECUTE_CATALOG_ROLE Yes
GATHER_SYSTEM_STATIS No
TICS
DBA Yes
CTXAPP Yes
CONNECT Yes
RESOURCE Yes
XDBADMIN Yes
PLUSTRACE Yes
OEM_ADVISOR Yes
OEM_MONITOR Yes
AQ_USER_ROLE Yes
HS_ADMIN_ROLE Yes
XDBWEBSERVICES Yes
SCHEDULER_ADMIN Yes
AUTHENTICATEDUSER Yes
EXP_FULL_DATABASE Yes
IMP_FULL_DATABASE Yes
DELETE_CATALOG_ROLE Yes
SELECT_CATALOG_ROLE Yes
EXECUTE_CATALOG_ROLE Yes
AQ_ADMINISTRATOR_ROL Yes
E
LOGSTDBY_ADMINISTRAT Yes
OR
RECOVERY_CATALOG_OWN Yes
ER
GATHER_SYSTEM_STATIS Yes
TICS
CTXAPP No
RESOURCE No
CONNECT No
RESOURCE No
RESOURCE No
CTXAPP Yes
RESOURCE No
OEM_MONITOR No
DBA No
DBA Yes
AQ_ADMINISTRATOR_ROL Yes
E
RESOURCE No
XDBWEBSERVICES No
CONNECT No
RESOURCE No
49 rows selected.
SQL>
SQL> --

</source>

==Query user granted roles==



<source lang="sql">

SQL>
SQL>
SQL> COL grantee FORMAT A8
SQL> COL granted_role FORMAT A30
SQL> COL grantor FORMAT A8
SQL> COL privilege FORMAT A12
SQL> COL owner FORMAT A4
SQL> COL table_name FORMAT A30
SQL>
SQL>
SQL> SELECT grantee
2 , granted_role
3 FROM dba_role_privs
4 WHERE grantee = "PLSQL";
GRANTEE GRANTED_ROLE
-------- ------------------------------
PLSQL CTXAPP
PLSQL CONNECT
PLSQL RESOURCE
3 rows selected.
SQL>
SQL> -- Query resources.
SQL> SELECT grantor
2 , owner
3 , table_name
4 , grantee
5 , privilege
6 FROM dba_tab_privs
7 WHERE grantee = "PLSQL";
no rows selected

</source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL/System Tables Views/dba role privs - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия