A Brief Introduction of Oracle Role and Privilege

A Brief Introduction of Oracle Role and Privilege

2 YEARS AGO ORACLE VIEWED 23 TIMES.

This brief introduction will cover with the fundamentals of Oracle privilege as well as roles, including how to retrieve all privilege for a user, and how to grant necessary roles or privilege to a user.

1. Basic Views of privileges and roles

  • DBA_SYS_PRIVS
  • DBA_TAB_PRIVS
  • DBA_ROLE_PRIVS
  • ROLE_SYS_PRIVS
  • SESSION_PRIVS

When granting a privilege to user, the PRIVILEGE will infect immediate; but when granting a role to a user, already connected session have to reconnect or use set role all to refresh the new granted role privileges

2. Script to find all privileges

1
2
3
4
5
6
7
8
 
select privilege
  from dba_sys_privs
 where grantee = upper('&1')
union
select privilege
  from dba_sys_privs
 where grantee in
       (select granted_role from dba_role_privs where grantee = upper('&1'));

3. Analysis of specific roles

Roles privileges can be retrieved from role_sys_privs view:

Resource role does not have create view privilege, but have unlimited tablespace

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
 
sys@LINORA> select PRIVILEGE, role from role_sys_privs where role = upper('&1');
Enter value for 1: connect
old   1: select PRIVILEGE, role from role_sys_privs where role = upper('&1')
new   1: select PRIVILEGE, role from role_sys_privs where role = upper('connect')

PRIVILEGE			 ROLE
--------------------- ----------------------
SET CONTAINER			 CONNECT
CREATE SESSION			 CONNECT

-- resource role
Enter value for 1: resource
old   1: select PRIVILEGE, role from role_sys_privs where role = upper('&1')
new   1: select PRIVILEGE, role from role_sys_privs where role = upper('resource')

PRIVILEGE			 ROLE
------------------ --------------------------------------------------
CREATE TRIGGER			 RESOURCE
CREATE SEQUENCE 		 RESOURCE
CREATE TYPE			 RESOURCE
CREATE PROCEDURE		 RESOURCE
CREATE CLUSTER			 RESOURCE
CREATE OPERATOR 		 RESOURCE
CREATE INDEXTYPE		 RESOURCE
CREATE TABLE			 RESOURCE

-- olap user role
Enter value for 1: olap_user
old   1: select PRIVILEGE, role from role_sys_privs where role = upper('&1')
new   1: select PRIVILEGE, role from role_sys_privs where role = upper('olap_user')

PRIVILEGE					ROLE
------------------------ --------------------------------------------------
CREATE CUBE BUILD PROCESS	 OLAP_USER
CREATE CUBE DIMENSION	 	 OLAP_USER
CREATE MEASURE FOLDER	 	 OLAP_USER
CREATE JOB		 OLAP_USER
CREATE CUBE		 OLAP_USER
CREATE SEQUENCE	 OLAP_USER
CREATE VIEW		 OLAP_USER
CREATE TABLE		 OLAP_USER

Based on above information, we can grant application user OLAP_USER, CONNECT and RESOURCE roles.

4. Identify users with DBA role

1
2
3
 
set line 200
col grantee for a30
select * from dba_role_privs where granted_role='DBA';

EOF

posted @ 2021-11-19 04:41  耀阳居士  阅读(0)  评论(0)    收藏  举报