Oracle PL/SQL/System Tables Views/dba sys privs

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

Query dba_sys_privs table

   <source lang="sql">
 

SQL> SQL> set verify off SQL> SQL> column priv format a30 heading "Privilege" SQL> column wadmin format a6 heading "Admin?" SQL> SQL> select privilege priv, initcap(admin_option) wadmin

 2    from dba_sys_privs
 3   where rownum < 50
 4  /

Privilege Admin?


------

CREATE VIEW No ALTER SESSION No CREATE SESSION No CREATE SYNONYM No CREATE SEQUENCE No CREATE DATABASE LINK No UNLIMITED TABLESPACE No ADVISOR Yes AUDIT ANY Yes DROP USER Yes RESUMABLE Yes ALTER USER Yes CREATE JOB Yes ANALYZE ANY Yes BECOME USER Yes CREATE ROLE Yes CREATE RULE Yes CREATE TYPE Yes CREATE USER Yes CREATE VIEW Yes ALTER SYSTEM Yes AUDIT SYSTEM Yes CREATE TABLE Yes DROP PROFILE Yes ALTER PROFILE Yes ALTER SESSION Yes DROP ANY ROLE Yes DROP ANY RULE Yes DROP ANY TYPE Yes DROP ANY VIEW Yes QUERY REWRITE Yes ALTER ANY ROLE Yes ALTER ANY RULE Yes ALTER ANY TYPE Yes ALTER DATABASE Yes CREATE ANY JOB Yes CREATE CLUSTER Yes CREATE LIBRARY Yes CREATE PROFILE Yes CREATE SESSION Yes CREATE SYNONYM Yes CREATE TRIGGER Yes DROP ANY INDEX Yes DROP ANY TABLE Yes GRANT ANY ROLE Yes LOCK ANY TABLE Yes MERGE ANY VIEW Yes UNDER ANY TYPE Yes UNDER ANY VIEW Yes 49 rows selected. SQL> SQL> --

 </source>
   
  


select distinct privilege from dba_sys_privs

   <source lang="sql">
 
                                                                                                                                                                                                                                                           VARCHAR2(3)

SQL> select distinct privilege

 2  from dba_sys_privs
 3  where rownum < 50
 4  order by privilege;

PRIVILEGE


ADVISOR ALTER SYSTEM ALTER USER ANALYZE ANY AUDIT ANY AUDIT SYSTEM BECOME USER CREATE JOB CREATE ROLE CREATE RULE CREATE TABLE PRIVILEGE


CREATE TYPE CREATE USER CREATE VIEW DROP USER RESUMABLE 16 rows selected.

 </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>