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;

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