Oracle PL/SQL Tutorial/System Tables Data Dictionary/dba sys privs

Материал из SQL эксперт
Перейти к: навигация, поиск

Oracle System Privilege Grants to Roles

   <source lang="sql">

SQL> SQL> SELECT privilege what_granted, 

 2         admin_option, grantee
 3  FROM   sys.dba_sys_privs
 4  WHERE  NOT EXISTS
 5  (SELECT "x"
 6  FROM    sys.dba_users
 7  WHERE   username = grantee)
 8  and     rownum < 50
 9  ORDER BY 1,2;

WHAT_GRANTED ADM GRANTEE

--- ------------------------------

ADMINISTER ANY SQL TUNING SET YES DBA ALTER ANY INDEXTYPE YES DBA ALTER ANY LIBRARY YES DBA ALTER ANY PROCEDURE YES DBA ALTER ANY RULE SET YES DBA ALTER ANY SQL PROFILE YES DBA ALTER DATABASE YES DBA ALTER PROFILE YES DBA ALTER ROLLBACK SEGMENT YES DBA ALTER SESSION YES DBA BECOME USER NO IMP_FULL_DATABASE WHAT_GRANTED ADM GRANTEE

--- ------------------------------

BECOME USER YES DBA CHANGE NOTIFICATION YES DBA CREATE ANY DIRECTORY YES DBA CREATE ANY JOB YES SCHEDULER_ADMIN CREATE ANY LIBRARY YES DBA CREATE ANY MATERIALIZED VIEW NO IMP_FULL_DATABASE CREATE ANY SYNONYM NO IMP_FULL_DATABASE CREATE DIMENSION YES DBA CREATE JOB NO OEM_ADVISOR CREATE ROLE YES DBA CREATE RULE YES DBA WHAT_GRANTED ADM GRANTEE

--- ------------------------------

CREATE RULE SET YES DBA CREATE SESSION YES DBA CREATE SYNONYM NO RECOVERY_CATALOG_OWNER DEBUG ANY PROCEDURE YES DBA DELETE ANY TABLE YES DBA DEQUEUE ANY QUEUE YES DBA DROP ANY INDEX YES DBA DROP ANY INDEXTYPE YES DBA DROP ANY LIBRARY NO IMP_FULL_DATABASE DROP ANY MATERIALIZED VIEW YES DBA DROP ANY TRIGGER NO IMP_FULL_DATABASE WHAT_GRANTED ADM GRANTEE

--- ------------------------------

DROP ANY TRIGGER YES DBA DROP ROLLBACK SEGMENT YES DBA DROP TABLESPACE YES DBA EXECUTE ANY EVALUATION CONTEXT YES DBA EXECUTE ANY LIBRARY YES DBA EXECUTE ANY PROCEDURE NO IMP_FULL_DATABASE EXECUTE ANY PROCEDURE YES DBA EXPORT FULL DATABASE YES DBA FORCE ANY TRANSACTION YES DBA INSERT ANY TABLE YES DBA MANAGE ANY QUEUE NO OEM_MONITOR WHAT_GRANTED ADM GRANTEE

--- ------------------------------

READ ANY FILE GROUP NO EXP_FULL_DATABASE SELECT ANY SEQUENCE YES DBA SELECT ANY TABLE YES DBA UNDER ANY TYPE YES DBA UPDATE ANY TABLE YES DBA 49 rows selected. SQL></source>


Oracle System Privilege Grants to Users

   <source lang="sql">

SQL> SQL> SELECT b.privilege what_granted, 

 2         b.admin_option, a.username
 3  FROM   sys.dba_users a, sys.dba_sys_privs b
 4  WHERE  a.username = b.grantee
 5  and    rownum< 50
 6  ORDER BY 1,2;

WHAT_GRANTED ADM USERNAME

--- ------------------------------

ALTER ANY EVALUATION CONTEXT YES SYS ALTER ANY RULE SET YES SYS ALTER DATABASE NO FLOWS_020100 ALTER SESSION NO XDB ALTER SESSION NO FLOWS_020100 ALTER SESSION NO HR ALTER SYSTEM NO FLOWS_020100 ANALYZE ANY NO SYS CREATE ANY CONTEXT YES FLOWS_020100 CREATE ANY DIRECTORY NO FLOWS_020100 CREATE ANY EVALUATION CONTEXT YES SYS WHAT_GRANTED ADM USERNAME

--- ------------------------------

CREATE ANY RULE SET YES SYS CREATE ANY SYNONYM NO FLOWS_FILES CREATE CLUSTER YES FLOWS_020100 CREATE DATABASE LINK NO HR CREATE DIMENSION YES FLOWS_020100 CREATE INDEXTYPE YES FLOWS_020100 CREATE LIBRARY NO MDSYS CREATE MATERIALIZED VIEW NO SYSTEM CREATE MATERIALIZED VIEW YES FLOWS_020100 CREATE PUBLIC SYNONYM NO MDSYS CREATE ROLE NO FLOWS_020100 WHAT_GRANTED ADM USERNAME

--- ------------------------------

CREATE RULE YES SYS CREATE RULE SET YES SYS CREATE SEQUENCE NO HR CREATE SESSION NO MDSYS CREATE SESSION NO XDB CREATE SESSION NO DIP CREATE SESSION NO sqle CREATE SESSION NO OUTLN CREATE SYNONYM NO HR CREATE TABLE NO MDSYS CREATE TABLE YES FLOWS_020100 WHAT_GRANTED ADM USERNAME

--- ------------------------------

DEQUEUE ANY QUEUE YES SYS DROP ANY RULE SET YES SYS ENQUEUE ANY QUEUE YES SYS EXECUTE ANY PROCEDURE NO FLOWS_020100 EXECUTE ANY RULE SET YES SYS EXECUTE ANY TYPE NO SYS LOCK ANY TABLE NO SYS MANAGE ANY QUEUE YES SYS SELECT ANY TABLE YES SYS UNLIMITED TABLESPACE NO DBSNMP UNLIMITED TABLESPACE NO XDB WHAT_GRANTED ADM USERNAME

--- ------------------------------

UNLIMITED TABLESPACE NO FLOWS_FILES UNLIMITED TABLESPACE NO PLSQL UNLIMITED TABLESPACE NO OUTLN UNLIMITED TABLESPACE NO MDSYS UPDATE ANY TABLE NO SYS 49 rows selected. SQL> SQL></source>


User System Privileges

   <source lang="sql">

SQL> SQL> SET FEEDBACK OFF SQL> SET PAGESIZE 20 SQL> SET LINESIZE 77 SQL> SET HEADING ON SQL> SQL> COLUMN username FORMAT A12 HEADING "User" SQL> COLUMN privilege FORMAT A20 HEADING "Privilege" SQL> TTITLE LEFT "User System Privileges" RIGHT "Page " FORMAT 999 SQL.PNO SKIP 2 SQL> SQL> SELECT username, privilege 

 2  FROM dba_users, dba_sys_privs
 3  WHERE username = grantee
 4  ORDER BY username, privilege;

User System Privileges Page 1 User Privilege

--------------------

ANONYMOUS CREATE SESSION CTXSYS ALTER SESSION CTXSYS CREATE PUBLIC SYNONY 

            M

CTXSYS CREATE SESSION CTXSYS CREATE SYNONYM CTXSYS CREATE VIEW CTXSYS DROP PUBLIC SYNONYM CTXSYS UNLIMITED TABLESPACE DBSNMP CREATE PROCEDURE DBSNMP CREATE TABLE DBSNMP SELECT ANY DICTIONAR 

            Y

User System Privileges Page 2 User Privilege

--------------------

DBSNMP UNLIMITED TABLESPACE DEFINER UNLIMITED TABLESPACE DIP CREATE SESSION FLOWS_020100 ALTER DATABASE FLOWS_020100 ALTER SESSION FLOWS_020100 ALTER SYSTEM FLOWS_020100 ALTER USER FLOWS_020100 CREATE ANY CONTEXT FLOWS_020100 CREATE ANY DIRECTORY FLOWS_020100 CREATE CLUSTER FLOWS_020100 CREATE DATABASE LINK FLOWS_020100 CREATE DIMENSION FLOWS_020100 CREATE INDEXTYPE FLOWS_020100 CREATE JOB FLOWS_020100 CREATE LIBRARY User System Privileges Page 3 User Privilege

--------------------

FLOWS_020100 CREATE MATERIALIZED 

            VIEW

FLOWS_020100 CREATE OPERATOR FLOWS_020100 CREATE PROCEDURE FLOWS_020100 CREATE PUBLIC SYNONY 

            M

FLOWS_020100 CREATE ROLE FLOWS_020100 CREATE SEQUENCE FLOWS_020100 CREATE SYNONYM FLOWS_020100 CREATE TABLE FLOWS_020100 CREATE TABLESPACE FLOWS_020100 CREATE TRIGGER FLOWS_020100 CREATE TYPE SQL> SQL> SET FEEDBACK ON</source>

Источник — «http://sqle.ru/index.php?title=Oracle_PL/SQL_Tutorial/System_Tables_Data_Dictionary/dba_sys_privs&oldid=3910»