Privileges

TOC

Display

-- All Users with DBA Role

SELECT * FROM dba_role_privs WHERE granted_role='DBA';

-- User

SELECT owner, grantee, table_name, privilege
FROM dba_tab_privs
WHERE grantee='UserName|RoleName';

-- System

SELECT grantee, privilege, admin_option
FROM dba_sys_privs
WHERE grantee='UserName'
ORDER BY grantee, privilege;

-- Get Privs

SELECT * FROM USER_ROLE_PRIVS WHERE username = 'SCOTT';
SELECT * FROM USER_TAB_PRIVS  WHERE grantee  = 'SCOTT';
SELECT * FROM USER_SYS_PRIVS  WHERE username = 'SCOTT';

Grant

GRANT privilege ON <SchemaObject> TO <Username|Rolename>; 

GRANT select ON hr.emp TO scott; 
GRANT connect,resource,dba TO scott;
GRANT create procedure TO scott;
GRANT UNLIMITED TABLESPACE TO scott;

CREATE PROCEDURE privilege includes create function and create package.

RESOURCE Is Now Deprecated - Use One of the Below Methods

  ALTER USER scott QUOTA UNLIMITED ON MyTablespace;
  GRANT UNLIMITED TABLESPACE TO scott;

DML Option

 GRANT ALL ON <Object> TO <User|Role>;
 -- Is the same as:
 GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX ON <Object> TO <User|Role>;

 -- Example
 GRANT ALL ON hr.emp TO scott;

 -- Grant All but sysdba Privs to a User
 GRANT SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, 
 DELETE ANY TABLE, EXECUTE ANY PROCEDURE, ALTER ANY PROCEDURE, 
 CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, CREATE ANY PROCEDURE, 
 DROP ANY PROCEDURE, CREATE ANY VIEW, DROP ANY VIEW, CREATE ANY INDEX, 
 DROP ANY INDEX, CREATE ANY SEQUENCE, DROP ANY SEQUENCE, 
 CREATE ANY TRIGGER, DROP ANY TRIGGER TO scott;

Data Steward Option

 -- This Allows a User to Perform DDL and DML on All User Tables in Database.
 GRANT ALL PRIVILEGES TO <Username|Role>;

Does not allow many DBA operations however.

Bulk Grant Privs via a Script

Confirm Schema Object (Tables) to Be Granted SELECT table_name FROM dba_tables WHERE owner='HR' ORDER BY table_name;

Create Script

spool C:\app\scripts\sql\spool\grants_APP1-to-HR.sql

SELECT 'GRANT SELECT ON HR.' || table_name || ' TO APP1;'  
FROM dba_tables 
WHERE owner='HR' 
ORDER BY table_name;

spool off

Example 2

SELECT 'GRANT SELECT ON ' || owner ||'.'|| table_name || ' TO RO_ROLE;'  
FROM dba_tables 
WHERE owner IN('AAA','BBB','CCC') 
OR    owner IN('XXX','YYY,'ZZZ') 
ORDER BY owner,table_name;

Review script removing any header info and then run it. @C:\app\scripts\sql\spool\grants_APP1-to-HR.sql2

Revoke

REVOKE Object_priv: <SELECT, INSERT, UPDATE, DELETE...> 
ON [schema.]object FROM <user|role|PUBLIC>;
----
REVOKE System_priv FROM <UserName, | rolename, | PUBLIC>;
REVOKE RoleName    FROM <UserName, | rolename, | PUBLIC>;

REVOKE select ON hr.emp FROM scott;
REVOKE imp_full_database FROM scott;
REVOKE dba FROM scott;

Create: User\Role\Priv Session

  -- Create User
 CREATE USER BI IDENTIFIED BY "************"
 DEFAULT   TABLESPACE BI
 TEMPORARY TABLESPACE temp;

 GRANT connect,resource TO BI;
 ALTER USER BI QUOTA UNLIMITED on BI;

 connect BI/************@DBTEST
 @/u01/app/scripts/tools/testdata.create.sql

 -- UPDATE BI.TEST1 SET name='Test000' WHERE test1_id=100;
 -- COMMIT;
 -- SELECT name FROM BI.TEST1 WHERE test1_id=100;

  -- Test
 CREATE USER x15 IDENTIFIED BY "************"
 DEFAULT   TABLESPACE BI
 TEMPORARY TABLESPACE temp;
 GRANT connect,resource TO x15;
 ALTER USER x15 QUOTA UNLIMITED on BI;

  -- Create Role
 CREATE ROLE BI_RW;

  -- GRANT Privs
 GRANT ALL ON BI.TEST1 TO BI_RW;
 GRANT BI_RW TO x15;

 connect x15/************@DBTEST

 -- UPDATE BI.TEST1 SET name='Test111' WHERE test1_id=100;
 -- COMMIT;
 -- SELECT name FROM BI.TEST1 WHERE test1_id=100;

 SYS> GRANT CREATE TABLE ON BI.TEST1 TO BI_RW;

 X15>
 CREATE TABLE BI.test2 (
 test2_id   number,
 title      varchar2(50) CONSTRAINT test_title_nn  NOT NULL,
 CONSTRAINT test2_pk PRIMARY KEY("TEST2_ID") USING INDEX TABLESPACE BI
 )
 TABLESPACE BI;

For more info see this.

<- Security