Database Security

Created May 23, 2024 by Girija Narasimhan

Unit Objective

This unit describes Authorization concept such as create role, grant role, revoke role and drop role. It also explain system privilege and object privilege and column privilege. It show various data dictionary view DBA_TAB_PRIVS, ROLE_ROLE_PRIVS AND DBA_COL_PRIVS. 

3.1 Authorization

The authorization gives permission to user to access database, modify the data or display the information.  It also controls user to access another user object schemas such as tables or rows or resources like connection, idle times.  There is two methods to grant privileges to user one is grant privileges to user explicitly and another method is grant privileges to a role. And then grant role to one or group of users in the database.

The privileges are classified into two features as System privilege and Object Privilege. 

Figure 3-1 privilege
fig3-1-privilege.jpgFigure 3-1 privilege

Figure 3-1 privilege


3.2 Role

In Oracle roles are created by database administrator. The user privileges are grant permission to execute specific type of SQL statement or access another user’s database object. The roles are storing group of privileges. The roles are assign to group of user or another roles.  Roles can be created with or without password. 

Figure 3-2 create role
fig3-2 create role.jpgFigure 3-2 create role

Figure 3-2 create role

Assign password to a role will protect authorization in the database. Using SET ROLE statement explicitly to role will enable the role. For disabling the role, the user assign proper password to the role.  

SET ROLE salesmanager IDENTIFIED BY sm123;

SET ROLE
fig3-11_qsF7hYs.jpgSET ROLE

SET ROLE

Within a database, each role name must be unique. we already created role salesrep, again create the role it will give error.

Figure 3-3 Role should be unique
fig3-3.jpgFigure 3-3 Role should be unique

Figure 3-3 Role should be unique

Role and User name should be unique, don’t have same name.   Username arvind is exist in the database, role should be different than user name.

Figure 3-4 Role and User Name should be unique
fig3-4.jpgFigure 3-4 Role and User Name should be unique

Figure 3-4 Role and User Name should be unique


3.3 Functionality of Roles

  • A role can be granted system or object privileges.


system privilege
fig3-1a.jpgsystem privilege

system privilege


object privilege
fig3-1b.jpgobject privilege

object privilege


  • Any role can be granted to any database user. 


Grant role to user
fig3-5.jpgGrant role to user

Grant role to user


  • A role can be granted to other roles.


Grant role to another role

fig3-6_AzgcxmI.jpgGrant role to another role

Grant role to another role


  • A role cannot be granted itself


Role cannot grant itself

fig3-7.jpgRole cannot grant itself

Role cannot grant itself


  •   A role cannot be circular


Role can't be Circular

fig3-8.jpgRole can't be Circular

Role can't be Circular


  •  If a role is not password authenticated or a secure application role, then you can grant the role indirectly to the user. User Arvind has direct role salesrep and indirect role cashier.


Direct and Indirect role
fig3-9.jpgDirect and Indirect role

Direct and Indirect role


  • Assign directly or indirectly granted role as default role.  Before assign to user as default role, first you should grant role to user. Here Cashier is indirect role and salesrep is direct role. But Salesmanager is not grant to user arvind, if try to assign as default role then it will give error


Default role
fig3-10_MYHmIub.jpgDefault role

Default role


  • The data dictionary view DBA_ROLE_PRIVS -Lists roles granted to users and roles . The ROLE_ROLE_PRIVS -This view describes roles granted to other roles. Information is provided only about roles to which the user has access. 


Data dictionary view
FIG3-12.jpgData dictionary view

Data dictionary view


Display the data Dictionary view
fig3-13.jpgDisplay the data Dictionary view

Display the data Dictionary view


3.4 Type of Role Authorization


Type of Role Authorization

fig3-14.jpgType of Role Authorization

Type of Role Authorization


3.5 Change the authorization method

It is possible to shift from one type of authorization  to another type of authorization.  For changing authorization the user has ALTER ANY ROLE system privilege or the user has ADMIN privilege.  

Shifting role authorization
fig3-15.jpgShifting role authorization

Shifting role authorization


3.6 Drop Role

For dropping role the user should have DROP ANY ROLE system privilege or ADMIN option.  The dropping role has indirectly granted role, all the granted roles also be removed from dropping role. Only privileges are removed, but indirect role is available in the database.

Dropping Role
fig3-16.jpgDropping Role

Dropping Role


3.7 Authorization of Object privilege

The schema object are table, views, indexes, triggers and database links, dimension, materialized view etc., For accessing another user schema object the user need object privilege it will granted through role for user or group of users.  Every user by default has all object privileges for his/her own Schema containing objects.  

grant object privilege role
fig3-19.jpggrant object privilege role

grant object privilege role

The object privileges can be grant specifically or all the object privilege assign to user using ALL privilege, it will assign privilege select, update, debug, flashback, query rewrite, on commit refresh and delete, references etc.,

All and specific object privilege
fig3-20.jpgAll and specific object privilege

All and specific object privilege

If user has GRANT ANY OBJECT PRIVILEGE , then the user can grant any specified object privilege to another user.  Using GRANT statement with or without GRANT OPTION the privilege to assign to user.  If user has GRANT ANY OBJECT PRIVILEGE by default the user has to revoke any object privilege that was granted by the owner of the object.  

Figure 3-17 Grant grant any object privilege
fig3-17.jpgFigure 3-17 Grant grant any object privilege

Figure 3-17 Grant grant any object privilege

Without GRANT option means the user is having GRANT ANY OBJECT PRIVILEGE can’t pass any grant object privilege to other user, otherwise it will show ORA-01031 error.

with grant option
fig3-21_abvqtTL.jpgwith grant option

with grant option


Login Sinduja and check insert privilege it gives error, because sinduja has only select privilege to grant any user or role not insert privilege this is the difference between grant any object privilege and grant only particular privilege. 

SQL> grant insert on hr.company to lakshmi;

grant insert on hr.company to lakshmi

                   *

ERROR at line 1:

ORA-01031: insufficient privileges

3.8 With GRANT option Vs. with ADMIN option


with grant option vs. with admin option


fig3-18_pQAyVfz.jpgwith grant option vs. with admin option

with grant option vs. with admin option


3.9 Data Dictionary view of DBA_TAB_PRIVS

DBA_TAB_PRIVS describes all object grants in the database. Object means tables, view, index, procedures and packages etc.,

DBA_TAB_PRIVS

FIG3-22.jpgDBA_TAB_PRIVS

DBA_TAB_PRIVS


3.9.1 Grantor, Grantee and Owner

Owner - The owner of the object, in HR schema  the table object COMPANY is created.

Grantor - The user who granted the privilege ,  the grantor is not owner of the object. The user Sinduja has Grant option privilege of HR.COMPANY.

Grantee- The Privilege is grantable by the grantee, Using grant option Sinduja grant privilege to Lakshmi then Lakshmi is Grantee and Sinduja is Grantor.


Grantor, Owner, Grantee
fig3-31.jpgGrantor, Owner, Grantee

Grantor, Owner, Grantee


3.10 Granting Column Privileges

Using GRANT statement the user can apply INSERT, UPDATE and DELETE column privileges.  The ORA-00969 error will occur while grant SELECT privilege for column to the user.  Instead of SELECT privilege column, the user create VIEW for specific column of the schema object and then grant the view to the user.  Compview  view is created under "SYS" user. That is why sys.compview is used in select statement.

column Privilege
fig3-24_Dulc3RF.jpgcolumn Privilege

column Privilege


Using REVOKE individually column privilege can't be revoked, revoke used only for object privilege not for column object privilege. It display error ORA-01750. In this case revoke entire object and then grant only specific column.

REVOKE COLUMN
fig3-27_TG0TDbl.jpgREVOKE COLUMN

REVOKE COLUMN

The DBA_COL_PRIVS data dictionary view display the granted table column privilege information

Column Data Dictionary view
FIG3-28_eg42WPc.jpgColumn Data Dictionary view

Column Data Dictionary view

The ROLE_ROLE_PRIVS data dictionary view display how many role granted to another role.

Role Data Dictionary view
fig3-29_RipL6yh.jpgRole Data Dictionary view

Role Data Dictionary view


3.11 ALL Shortcut

"ALL" is  a shortcut method to use single grant or revoke statement to assign privilege. 

 

All
fig3-26_h1zbBRN.jpgAll

All

The REVOKE ALL privilege revoke only what all are the privilege given using GRANT ALL object privilege, it doesn't revoke already given specific privileges . In Figure 3-26b still compview object privilege is available, 

revoke all


revoke all


fig3-26b.jpgrevoke all


3.12  SYNONYM Object Privilege

Crete synonym statement will create alternative name for schema objects like view, tables, sequences, procedures and packages etc., Like granting table object the synonym to be grant to user.  Here synonym is created in HR schema, even though synonym is granted to user ARVIND while selecting mention schema.object name HR.COMP_SYN

Create Synonym
fig3-30_hXgWhNH.jpgCreate Synonym

Create Synonym


Unit Summary

 This unit cover creating and grant, dropping roles.  At the end of the unit reader get clarity about various authorization such as system and object privilege and column privilege.  The readers have knowledge about data dictionary view of DBA_ROLE_PRIVS, DBA_TAB_PRIVS, ROLE_ROLE_PRIVS. 

Practical Exercise

1.Create Role Manager. 

2.  Assign role Manager to create table  privilege.

3. create user induja which has unlimited tablespace and assign 100m quota. 

4. Assign manager role to user induja.

5.  Connect the user session Induja and create table STAFF and insert values.

CREATE  TABLE STAFF (STAFF_ID NUMBER(4) PRIMARY KEY, STAFF_NAME VARCHAR2(15), SALARY NUMBER(8));

INSERT INTO STAFF VALUES(11,'NABRAS', 40000);

INSERT INTO STAFF VALUES(12,'SENTHIL',60000);

INSERT INTO  STAFF VALUES(13,'AMBU',55000);

6. Display Staff table

7. Modify the salary of Staffid 13 as 65000

8. The user sinduja assign create table privilege to user arvind; [Hint: conn sinduja and give privilege]

9. Login Arvind, create course table  and insert values [hint: assign tablespace unlimited]

create table course(course_id number(5) primary key, course_name varchar2(10));

insert into course values(1,'DB');

insert into course values(2,'SE');

insert into course values(3,'NT');

10. Delete course_id 3

11. Insert course_id as 13 and course_name is "NT"

12. Modify the course name as "BUSS" for course_id 13

13. Create user Meenu, assign select privilege of staff_id, salary column of staff table;    [Hint: create view staffview]

14. Display Object privileges assign to user Meenu.

15. Assign Update column privilege of staff_name, salary of staff table to new role admin_staff.

16. Remove update privilege of salary column  from role admin_staff

17. Create role "HR_HEAD", assign "HR_HEAD" to manager. Assign manager role to Meenu.

18. Display Role assign to user Meenu.

19. Display Role assign to role Manager

20. Assign all the privilege of course table to role admin_staff

Video Tutorial Link

1. YouTube Tutorial: Authorization Part 1

2. YouTube Tutorial: Authorization Part 2

3. YouTube Tutorial: Authorization Part 3

Lecture Notes

Lecturer Notes: Authorization

References

[1] Patricia Huey (2014)  Oracle Database, Security Guide 11g Release 2 (11.2).

[2] http://www.dba-oracle.com/t_with_grant_admin_privileges.htm

Return to top