Oracle: rights and access to the database

Immediately after the creation of users in the database, the need to manage their access to various data objects arises. For example, an employee of the human resources department of an organization may have the right to view the salary data of employees, but should not have the authority to change the rates.

In Oracle, there are several means of controlling access to data, and the easiest way is to assign database rights and roles to database users.

Rights in Oracle database

Rights (rights) – is the ability to execute a specific type of SQL-operator or access a database object belonging to another user. In an Oracle database, the user shall be explicitly granted the rights to perform any actions, including connecting to the database or selecting, modifying, and updating data in any table, except its own.

There are two main types of Oracle rights: system rights and object rights. The GRANT operator is used to grant users both system and object rights. Both these types of Oracle rights are described in detail in the following sections of the article.

For your information! You can manage users using the Database Control interface by going to Database Control Home Page => Administration => Users in the Users and Privileges section.

System Rights (Users and Privileges)

The System Rights allow the user to perform a specific action in the database or action with any diagram object of a specific type. A good example of the first type of System Rights is the rights that allow connecting to the database named CONNECT rights.

Other rights of this type are CREATE TABLESPACE, CREATE USER, DROP USER, and ALTER USER. The second class of system rights gives users the right to perform operations that affect the objects in any scheme.

Examples of this type of system rights are ANALYZE ANY TABLE, GRANT ANY PRIVILEGE, NSERT ANY TABLE, GRANT ANY PRIVILEGE, INSERT ANY TABLE, DELETE ANY TABLE, etc. As you can see, system rights are a very powerful tool, and giving them to the wrong user can have a devastating impact on the database. Below are some of the most frequently used Oracle database rights:

• ADVISOR
• ALTER DATABASE
• ALTER SYSTEM
• AUDIT SYSTEM
• CREATE DATABASE LINK
• CREATE TABLE
• CREATE ANY INDEX
• CREATE SESSION
• CREATE TABLESPACE
• CREATE USER
• DROP USER
• ENTER ANY TABLE

Granting system rights

The GRANT operator is used to grant users system rights. When granting system rights to a user, the user may apply these rights immediately. In other words, the rights work dynamically.

Tip. OEM Database Control interface or SQL operators can be used to grant or withdraw system rights.

For example, the following operator shall be required to grant the CREATE SESSION system rights to an hr user by allowing him to log into the Oracle database:

SQL> GRANT CREATE SESSION TO hr;
Grant succeeded.
SQL>EATE SESSION TO HR; GRANT SUCCEEDED.

The CREATE SESSION rights allow the user to enter the Oracle database.

Tip. All system rights (except SELECT ANY DICTIONARY) can be given to the user by specifying ALL PRIVILEGES in the GRANT operator, for example:

SQL> GRANT ALL PRIVILEGES TO salapati;
Grant succeeded.
SQL>

The design of ALL PRIVILEGES itself is not a system power – it serves as a convenient way to grant all rights in one action. All system rights can be revoked in the same way with the REVOKE ALL PRIVILEGES operator.

The database administrator can also grant system rights to a PUBLIC user – in this case, all database users will be able to perform the actions permitted by the permissions. For example:

SQL> GRANT CREATE SESSION TO public;
Grant succeeded.
SQL>

Once the CREATE SESSION rights have been granted to the PUBLIC user, all users will be able to register in the database without being granted CREATE SESSION rights individually. As you can see, granting rights to a PUBLIC user involves risks, as all users will have such rights.

It is possible to grant system rights to a user if one of the following conditions is met.

• You are granted system rights using the WITH ADMIN OPTION design.
• You are granted system rights by GRANT ANY PRIVILEGE.

Here is an example of how the WITH ADMIN OPTION design can be used to grant system rights:

SQL> GRANT CREATE SESSION TO salapati WITH ADMIN OPTION;
Grant succeeded.
SQL>

GRANT ANY OBJECT rights – special system rights, which allow issuing (and recall) object rights for objects in any scheme. It’s interesting to note that when the recipient of these rights gives any rights for any object, it seems as if the rights were given by the scheme owner (this can be seen from the DBA_TAB_PRIVS representation). However, if you audit the use of the GRANT operator, it becomes clear which user has executed this operator. All users who have SYSDBA permissions automatically get GRANT ANY OBJECT permissions.

Revocation of system rights

The REVOKE operator is used to revoke system rights. The revocation of rights takes effect immediately. For example:

SQL> REVOKE DELETE ANY TABLE FROM pasowner;
Revoke succeeded.
SQL>

The REVOKE operator can be used to revoke only those rights that were previously granted to the user with the GRANT operator.

Only users with SYSDBA powers, or those who have been explicitly granted object rights, can access objects in the SYS scheme. Access to SYS objects can also be granted to other users by assigning them one of the following three roles. (Roles are named sets of rights)

• SELECT_CATALOG_ROLE. This role provides SELECT rights for data dictionary views.
• EXECUTE_CATALOG_ROLE. This role gives EXECUTE rights in relation to data dictionary packages.
• DELETE_CATALOG_ROLE. This role allows users to delete records from the SYSAUD$ audit table.

You can also use the SELECT ANY DICTIONARY system rights to give the user (usually the developer) the power to select data from any SYS schema object.

System rights SYSDBA and SYSOPER SYSASM

There are two powerful sets of administrative rights – SYSDBA and SYSOPER. Due to the huge possibilities provided by these permissions, there are certain restrictions on their management. You cannot use the WITH ADMIN OPTION option to assign these roles. Only a user connected as an SYSDBA can grant (or revoke) these rights to other users. Also, these system rights cannot be granted to any role.

The SYSDBA system rights include the RESTRICTED SESSION permissions and contain all system rights marked with the WITH ADMIN OPTION option, including the SYSOPER system rights. SYSDBA permissions allow for the following tasks.

• Perform STARTUP and STDOWN operations.
• Use the ALTER DATABASE command to open, mount, back up or change the character set.
• Use the CREATE DATABASE command.
• Perform ARCHIVELOG and RECOVERY operations.
• Create SPFILE.

The SYSOPER permissions also include the RESTRICTED SESSION permissions and allow them to perform the following.

• Execute STARTUP and STDOWN operations.
• Use the ALTER DATABASE command to perform opening, mounting, or system copy.
• Perform ARCHIVELOG and RECOVERY operations.
• Create SPFILE.

Tip. Several common database operations require users to constantly query the data dictionary tables. Therefore, during the development of database developers to provide a set of basic rights, assigning them the role of SELECT_CATALOG_ROLE. This role gives the data selection rights in all data dictionary views.

Besides SYSDBA and SYSOPER rights there are also SYSASM rights, which can be used for administration of ASM (Automatic Storage Management) instances. Although it is possible to work with ASM instances using SYSDBA rights, Oracle recommends separating database administration from ASM administration.

Object rights

Object rights are powers about different types of database objects. Object rights allow the user to perform actions with a specific table, view, materialized view, sequence, procedure, function, or package. Consequently, all database users need object rights, even if they do not need system permissions.

Several customary system rights apply to all database objects and a set of rights that apply only to certain objects. The following SQL operators may be used to grant object rights:

• ALTER
• SELECT
• DELETE
• EXECUTE
• INSERT
• REFERENCES
• INDEX

The following list contains various types of Oracle database object rights, basic object rights of each type, and examples of each object type.

Tabular rights. SELECT, ALTER, DELETE, INSERT, and UPDATE:

GRANT DELETE ON bonuses TO hr

Council. The powers of the INSERT and UPDATE may be granted at the column level. Below is an example of granting INSERT rights for the column salary table persons: SQL> GRANT INSERT (salary) ON persons to salapati; To grant row-level rights, you can use the Oracle virtual private database or the Oracle label security feature.

Presentation rights. SELECT, DELETE, INSERT, and UPDATE:

GRANT SELECT, UPDATE
ON emp_view TO PUBLIC;

Rights of sequence. ALTER and SELECT:

GRANT SELECT
ON oe.customers_seq TO hr;

Rights of procedure, functions, and package. EXECUTE and DEBUG:

GRANT EXECUTE ON employee_pkg TO hr;

Rights of materialized representation. SELECT and QUERY REWRITE:

GRANT QUERY REWRITE TO hr; SELECT and QUERY REWRITE TO hr.

Catalog rights. READ and WRITE:

GRANT READ ON DIRECTORY bfile_dir TO hr

If permissions are granted to a user using the GRANT OPTION optional design, the user may in turn grant permissions to other database users.

For example:

SQL> GRANT DELETE ON bonuses TO hr WITH GRANT OPTION;

Once an hr user gets the DELETE permissions in the bonuses table, as shown in the example above, hr can grant these permissions to any other user.

The owner of any object shall have all the rights about that object and may grant permission to any other database user to work with it. The right to grant these rights shall be granted to the owner of the scheme, but not to the database administrator or user SYSTEM or SYS. You may grant object authority to a user under one of the following conditions:

• you are the owner of the object;
• the object owner has granted you object permissions using the GRANT OPTION design;
• you have been granted system permissions by GRANT ANY OBJECT.

Just for the record! Object permissions cannot be granted for some objects in the scheme, such as clusters, indexes, triggers, and database communications. These types of objects are managed through system permissions. For example, to change a cluster, the user must be the owner of the cluster or have the ALTER ANY CLUSTER system permissions.

The owner of the object may add ALL design to the GRANT operator to give all possible powers for the object. For example, the following two GRANT operators are equivalent:

SQL> GRANT SELECT, INSERT, UPDATE, DELETE on EMPLOYEES TO oe;
SQL> GRANT ALL ON EMPLOYEES TO oe;

The scheme owner may grant one or all types of rights to any single object. Here are a few examples to illustrate how to grant object rights:

SQL> GRANT SELECT ON ods_process TO tester;
Grant succeeded.
SQL> GRANT INSERT ON ods_process TO tester;
Grant succeeded.
SQL> GRANT ALL ON ods_servers TO tester;
Grant succeeded.
SQL> GRANT INSERT ANY TABLE TO tester;
grant insert any table to tester
*
ERROR at line 1:
ORA-01031: insufficient
lack of benefits
SQL>

The ODS user can issue any permissions (SELECT, INSERT, UPDATE and DELETE) on the ods_servers table to the tester user using the GRANT ALL command. But it fails to give the tester user INSERT ANY TABLE because it requires system permissions (INSERT ANY TABLE) which the ODS user does not have. However, note that the system user can successfully grant these credentials, as can be seen in the following example:

SQL> CONNECT system/manager@finance1
Connected.
SQL> SHOW USER
USER is "SYSTEM"
SQL> GRANT INSERT ANY TABLE TO tester;
Grant succeeded.
SQL>

If the object owner grants the user object authority through the WITH GRANT design, the person who has received the authority receives the right to grant the same object authority to other users. For example:

SQL> GRANT INSERT ANY TABLE TO tester WITH GRANT OPTION

Objective credentials at the column level

Until now, when considering object rights, it was always assumed that there was a right to perform DML actions with the whole table. However, the user can also be granted permissions only for certain columns in the table, as shown in the following examples:

SQL> GRANT UPDATE (product_id) ON sales01 TO salapati;
Grant succeeded.
SQL>

Revocation of Object Rights

Withdrawal of object rights is similar to issuing powers. You just need to execute the REVOKE operator for each object right to be revoked.

SQL> CONNECT ods/ods@finance1;
Connected.
SQL> REVOKE SELECT, INSERT ON ods_process FROM tester;
Revoke succeeded.
SQL>

Please note that rights cannot be revoked at the column level, even if they are issued at that level. To revoke rights, you must use the table level, regardless of the level at which they were granted, as shown in the following example:

SQL> REVOKE UPDATE (hostname) ON ods_process FROM tester;
revoke update(hostname) on ods_process from the tester
*
ERROR at line 1:
ORA-01750: UPDATE/REFERENCES may only
be revoked from the whole table, not by column
the UPDATE/REFERENCES powers may be
revoked only for the whole table, not for the column
SQL> REVOKE UPDATE ON ods_process FROM tester;
Revoke succeeded.
SQL>

Powers of GRANT ANY OBJECT

The user who has system powers of GRANT ANY OBJECT may grant and withdraw any object powers as if he were the actual owner of the object. When connected as an SYSDBA (SYS user), this role is granted automatically by applying the WITH ADMIN OPTION design.

Caller and determinant rights

Created storage procedure in Oracle is performed using the powers of its creator. This behavior is defined by default and it is customary to say that the host procedure is created with determinant rights. When a user executes a procedure, it is executed with object permissions from the creator (determinant), not from a specific user.

But there may be a few situations where it is not desirable for all users to be able to execute the procedure with the same rights. The ability to access the procedure can be configured by creating it with caller rights, i.e. the procedure will be executed with a user and not creator rights for the procedure.

When creating a procedure with caller rights, the procedure will be executed in the context of user security, not the owner’s security context. As a result, any user who intends to execute the procedure from a different scheme will have object powers in all tables affected by the procedure. All DML powers for these tables must be given to the user directly, not through any role.

The AUTHID design in the CREATE PROCEDURE operator indicates that this procedure is created with the rights of the user or caller and not with the rights of the owner or determinant used by default. For example:

SQL> CREATE OR REPLACE PROCEDURE delete_emp
2 (p_emp_id number)
3 AUTHID current_user IS
4 BEGIN
5 DELETE FROM emp WHERE
6 emp_id = p_emp_id;
7 COMMIT;
8* END;
Procedure created.
SQL>

The AUTHID design inline 3 specifies that the procedure will be executed with the current_user user – the user calling the procedure. Obviously, for the procedure to be successful, the user must have explicit DELETE object powers in the emp table.

Roles

Although user powers are quite easy to manage by directly issuing and withdrawing them, this task can quickly become extremely time-consuming as new users are added and the number of objects increases. After a while, it is very difficult to track the current permissions of each user. Oracle solves this problem by applying roles that are named sets of rights that can be assigned to users.

Roles can be considered a set of rights that can be assigned and revoked with a single GRANT or REVOKE command. A role can contain both a set of rights and other roles. Roles make it easy to assign several rights to a user. A default role is a role that automatically takes effect when the user creates a session. It is possible to assign more than one default role to a user.

Tip. The DBA role, which is pre-defined in Oracle databases, is a set of WITH ADMIN OPTION system rights – i.e. a user who has this role can also grant these rights to other users. In most cases, this role is assigned to a group of users who perform database administration.

There are several predefined roles in the Oracle database, including EXP_FULL_DATABASE, IMP_FULL_DATABASE, and RECOVERY_CATALOG_OWNER. Also, each Oracle database contains three important roles to which the following powers relate.

• The CONNECT role. CREATE SESSION (previously, the CONNECT role contained several other rights, but now it supports only the CREATE SESSION powers).
• The role is RESOURCE. CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE.
• Role of DBA. All system powers with WITH ADMIN OPTION construction.

There are also two other predefined roles – EXP_FULL_DATABASE and IMP_FULL_DATABASE – that allow the user to perform streaming export and import operations (Data Pump Export and Data Pump Import) on the database level.

Traditionally, the role of DBA is assigned to all employees of the organization who are engaged in database administration tasks. However, Oracle has warned that in future versions, the DBA, CONNECT, and RESOURCE roles may not be available, and therefore it is recommended to create your roles instead.

For the record! By default, no system permissions are granted to any user except those assigned to the DBA role.

Role creation

If you have a given DBA role or special system rights CREATE ROLE, the role can be created as follows:

SQL> CREATE ROLE new_dba;
Role created.
SQL>CREATE ROLE NEW_DBA; ROLE CREATED.

The newly created role new_dba has no rights attached to it, so now it needs to be given the required permissions. It is even possible to assign other predefined roles to it. Roles are empty “vessels” that can be filled with any number of system and object powers.

Once a role is created, it is sufficient to assign it to the user, and the user will inherit all the powers contained in the role. Listing 12.9 illustrates the granting of various database rights to a new role.

SQL> GRANT CONNECT TO new_dba;
Grant succeeded.
SQL> GRANT SELECT ANY TABLE TO new_dba;
Grant succeeded.
SQL> GRANT UPDATE ANY TABLE TO new_dba;
Grant succeeded.
SQL> GRANT select_catalog_role TO new_dba;
Grant succeeded.
SQL> GRANT exp_full_database TO new_dba;
Grant succeeded.
SQL> GRANT imp_full_database TO new_dba;
Grant succeeded.
SQL> GRANT IMP_FULL_DATABASE TO NEW_DBA; GRANT SUCCEEDED.

To give the salapati user all the powers described, it is enough to do the following:

SQL> GRANT new_dba TO salapati;
Grant succeeded.
SQL>

The user may be assigned more than one role, and all of them will be active when the user logs on to the database.

Authorization of a Role

In the example given in the previous section, you did not need a password to use the role. However, it is possible to require that a role be authorized before it can be used. Authorization of a role can be specified in several ways.

• Using database authorization. When a role is authorized by the database, a password shall be used as shown in the following example:

CREATE ROLE clerk IDENTIFIED BY password;

• Through database authorization using the PL/SQL package. The developer may create a role and specify that the PL/SQL package shall be used to authorize it. In the following example, the admin_role role is activated by the module that is defined in the PL/SQL package hr.admin:

CREATE ROLE admin_role IDENTIFIED USING hr.admin;

• Externally, by the operating system, network, or another external source. You can require that before it can be activated, the role is authenticated by an external source, as shown in the following example:

CREATE ROLE accts_rec IDENTIFIED EXTERNALLY;

• Global, enterprise directory service. The role can also be defined as global, i.e., user authorization to use the role can only be performed by the enterprise directory service. The following operator creates a global role, which can be authorized by the enterprise directory service:

CREATE ROLE supervisor IDENTIFIED GLOBALLY;

Provision of a role using the WITH ADMIN OPTION construct

If a role is assigned using the WITH ADMIN OPTION design, the person who receives it can perform the following actions.

Provide or withdraw a role for any user or other role in the database

• Provide a role using the WITH ADMIN OPTION design.
• Modify or delete a role.
• Provide a role for another role

Usually, the role is given to the user. In this case, the user can immediately use all the powers covered by the role. However, a role can also be given to another role. In this case, the database will add all the powers of the granted role to the host rights domain.

User Group and PUBLIC Roles

When giving a role to the PUBLIC group, the database makes the role available to all database users. If you need to assign specific permissions or roles to all database users, it is sufficient to grant these permissions or roles to the PUBLIC user group, which by default exists in each database. However, for obvious reasons, this method of granting permissions is not recommended.

Disabling and enabling the role

The user role can be disabled by inserting the appropriate line in the Product_User_Profile table of the SYSTEM scheme. Listing 12.10 shows the insertion of a line in this table to disable the TEST123 role assigned to the user by TESTER.

SQL> INSERT INTO PRODUCT_USER_PROFILE(PRODUCT,userid,attribute,char_value)
2* VALUES('SQL*Plus', 'TESTER', 'ROLES', 'TEST123');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> CONNECT tester/tester@finance1
Connected.
SQL> SELECT * FROM hr.regions;;
select * from hr.regions
*ERROR at line 1:
ORA-00942: table or view does not exist
table or view does not exist

As you can see, after disabling the TEST123 role, the TESTER user is deprived of the right to choose from the database tables and when trying to execute the SELECT operator an error message is displayed.

To enable the TEST123 role again it is enough to delete the corresponding row from the Product_User_Profile table:

SQL> DELETE FROM product_user_profile
2 WHERE userid='TESTER'.
3* AND char_value = 'TEST123';
1 row deleted.
SQL> commit;
Commit complete.

Deleting the role

Removing a role is not very difficult. To do this, just use the DROP ROLE command:

SQL> DROP ROLE admin_user;
Role dropped.
SQL>

Use of views and stored procedures for rights management

In addition to user roles and rights, Oracle provides data security through the use of views and stored procedures. Basic table views or a combination of simple tables can not only mask the complexity of queries but also provide a significant degree of data security.

Using DBA views to manage users, roles and rights

The OEM Manager is very handy when managing users in a database. However, from time to time it may be necessary to use SQL script to collect information about users. Specific data dictionary views can help in obtaining information about who is assigned what roles and what powers a particular role has. It is also possible to find out what system and object powers are given to a certain user. The main data dictionary views that can be used to manage users, permissions, and roles in the database are listed in the table below.

Detailed data access control

Traditional data security tools (using rights, roles, views, etc.) are quite successful but have certain limitations. The main disadvantage of this approach is that most security support tools are general. This leads to excessive restriction of users’ capabilities, while the main goal is to provide users with free access to the information they need.

In addition to traditional concepts of roles and rights, Oracle offers more detailed data security technologies at a lower level. For example, it is possible to allow all users access to a central table, such as payroll, but also to implement security policies that allow individual users access only to the rows in the table that belong to their department. Such restrictions are transparent to database users.

To provide detailed security management within the database, Oracle uses two related mechanisms: application context and fine-grained access control (FGAC) policy. The term virtual private database is used in Oracle to describe the implementation of fine-grained access control policies through application contexts.

Often, the concepts of fine-grained access control, virtual private database, and line-level security are used interchangeably to refer to Oracle’s fine-grained access control at the line level rather than the table level.

Using Oracle granular access control, security policies can be fine-tuned in the most complex ways. Detailed access control can be used for the following purposes.

• To implement string level access control through SELECT, INSERT, UPDATE, and DELETE operators.
• To create a security policy that controls access based on a certain column value.
• To create both policies that are always applied equally and policies that are dynamically changed during the execution of a query.
• To create security policies called security groups.

Oracle allows you to control access to database objects at the line level using the virtual private database (VPD) tool. Application of the VPD concept allows limiting the possibility of viewing table data by each user to a certain part only. This row-level security is implemented by linking the security policy directly with the database object, such as a table, view, or synonym.

Regardless of the means of access to the database used by the user (SQL*Plus, a specialized query program or reporting program), the user is not able to bypass this protection at the line level, implemented by the database server. Since the database implements the VPD concept, it provides much more reliable protection than application-based security.

To restrict users’ access to certain rows of tables and views, VPD uses a kind of rewrite requests. The security policy communicates with the table or tables that need to be accessed and the corresponding stored procedures serve to modify any SQL statements executed with the tables of interest. When a user issues UPDATE operator to a table with such security policy, Oracle will dynamically supplement it with a predicate (WHERE construct) to restrict user access to this table.

For example, if a user who is a sales employee executes the UPDATE EMPLOYEE SET salary=salary*1.10 operator, the security policies associated with the EMPLOYEE table will cause the operator to be supplemented with the WHERE dept=’ SALES’ detailed security control construction, ensuring that the change is applied only to sales employee records. In other words, if the original request looks like:

UPDATE EMPLOYEE SET salary=salary*1.10

then the modified operator looks like this:

UPDATE EMPLOYEE SET salary=salary*1.10 WHERE dept='SALES'.

To create a VPD, you need to create a so-called application context and then implement detailed access control to implement low-level protection for tables and database views. The application context helps create security policies that apply to various aspects of user-session information.

For example, when a user logs on to a database, his or her identification is based on the user’s ID and, based on this information, the application security policy sets limits on the actions that the user can perform within the database. VPD is just an implementation of the application context using detailed access control.

For your information! VPD policies can be applied to SELECT, INSERT, UPDATE, INDEX, and DELETE operators.

Application context

Application context allows you to define a set of application attributes (usually a set of session environment variables) that can be used to control application access to a database. Using application attributes, you can provide the appropriate predicate values for detailed access control policies.

Oracle uses the built-in USERENV application context namespace containing a set of predefined session attributes. These predefined attributes are used by Oracle for access control. When a user registers, the database automatically extracts the basic attributes of the session, such as user name, computer name, and IP address, from the USERENV application context.

The session-related information about any user can be obtained from the USERENV application context, as shown in the listing examples below. In the first example, the TERMINAL attribute shall display the name of the terminal from which the user accesses the database.

In the second example, the OS_USER attribute displays the name of the operating system account used by the database user. The third example retrieves the authentication name of the current user from the SESSION_USER attribute.

SQL> CONNECT system/system_passwd;
Connected.
SQL>CONNECT SYSTEM/SYSTEM_PASSWD; CONNECTED.
SQL> SELECT sys_context ('USERENV', 'TERMINAL')
2 FROM DUAL;
SYS_CONTEXT('USERENV','TERMINAL')
----------------------------------
NTL-ALAPATISAM
SQL>
SQL> SELECT sys_context ('USERENV', 'OS_USER') FROM DUAL;
SYS_CONTEXT('_USERENV', 'CURRENT_USER')
-------
oracle
SQL>
SQL> CONNECT fay/fay1;
Connected.
SQL>
SQL> SELECT first_name,last_name,employee_id FROM employees
2 WHERE UPPER(last_name)=sys_context('USERENV', 'SESSION_USER');
3
FIRST_NAME LAST_NAME EMPLOYEE_ID
-------------- --------- ------------
Pat Fay 202
1 row selected.
SQL>

In addition to the TERMINAL, CURRENT_USER and SESSION_USER attribute listed in the listing examples above, the USERENV namespace contains several important predefined attributes. Some of the frequently used predefined attributes are listed in the table below.

When registering a user, it is useful to identify the type of the user and intercept certain basic user attributes. Subsequently, this information can be used in security policies related to database objects. USERENV’s built-in namespace is ideal for intercepting such information.

Of course, the USERENV namespace is only one of the namespaces of the application context available for use. To be able to determine which attributes to use when setting your security policies, you will need to create your application context. To define your application context, you need to do the following.

• Create a PL/SQL package that sets the context using functions.
• Create the application context, which uses the created package.

Create a package to set the context

To set the application context for user hr, you need to create a PL/SQL package. The listing below shows how to create a simple HR_CONTEXT package to set the application context. The package includes a single procedure that selects the employee_id column value in the enum variable.

Since this SELECT operator is based on the WHERE construct that defines the last_name attribute based on the value of the SESSION_USER attribute, the employee_id value shall correspond to the user name by which the current user was authenticated by the database.

SQL> CONNECT hr/hr
Connected.
SQL> CREATE OR REPLACE PACKAGE hr_context AS
2 PROCEDURE select_emp_no ;
3* END;
SQL> /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY hr_context as
2 PROCEDURE select_emp_no IS
3 empnum number;
4 BEGIN
5 SELECT employee_id INTO empnum FROM employees WHERE
6 UPPER(last_name) =
7 sys_context('USERENV', 'SESSION_USER');
8 dbms_session.set_context('employee_info', 'emp_num', empnum);
9 END select_emp_no;
10* END;
SQL> /
Package body created.
SQL>

Create application context

The application context is a named set of pairs with a variable=value specific to the session. After creating a package (HR_CONTEXT) that makes it easy to set the application context, you can move on and create the application context itself, as shown below. Note that user hr applies the package created in the previous section to create the context of the application employee_info.

SQL> CONNECT system/system_passwd;
Connected.
SQL> GRANT CREATE ANY CONTEXT TO hr;
Grant succeeded.
SQL> CONNECT hr/hr;
Connected.
SQL> CREATE CONTEXT employee_info USING hr.context;
Context created.
SQL>CONNECT HR/HR; CONNECTED.

There are two ways to set a user application context. The first is to implement the application context itself, without applying detailed access control.

To do this, simply create an event trigger for the user to log on to the database so that the user calls the SELECT_EMP_NO procedure from the HR_CONTEXT package when logging on to the database. The logon trigger to set the initial context for the user shall be created in the following way:

SQL> CREATE OR REPLACE TRIGGER hr.security_context.
2 AFTER LOGON ON DATABASE
3 BEGIN
4 hr_context.select_emp_no;
5* END;
SQL> /
Trigger created.
SQL>

This input trigger uses the SELECT_EMP_NO procedure from the previously created HR_CONTEXT package to capture the user’s employee_id value and save it to the emp_num variable.

The second way to set or reference the application context is to perform this task as an integral part of VPD, using a policy function that implements detailed access control. This method is described in detail in the next section.

Detailed access control

Traditionally, security policies have been applied to applications in general. Users were assigned roles or permissions by which they could access the application tables. It was always possible to bypass security protocols and modify data in database tables by users using tools like SQL*Plus.

Moreover, implementing security at the application level meant that it was necessary to manage the policy of granting/revoking access rights of each system user to all database tables.

There are situations where it may be necessary to restrict access to application data for certain groups of users. Of course, views could be created to accomplish this task, but managing views involves several issues, such as maintenance and auditing.

Fine-grained access control (FGAC) allows you to restrict Oracle users so that they can only use the data they need to access and modify. FGAC is implemented by applying policy features associated with tables or views that need to be protected.

Detailed access control uses dynamically changing operators to restrict users to specific parts of a table, view, or synonym. When parsing SQL statements executed by a user, FGAC forces Oracle to automatically analyze policy functions (a table can be linked to more than one policy). If necessary, Oracle will execute a user query after its dynamic change.

Take a note! FGAC allows for implementing detailed data protection. Using this functionality, it is possible to implement a low-level security policy.

FGAC implies the following actions.

1. You create a policy function that will automatically add a predicate to the user’s DML operator. A predicate is a WHERE construct constructed using some operation (=, !=, IS, IS NOT, >, >=, EXIST, BETWEEN, IN, NOT IN, etc.). Here is an example of such a function:

cust_no = (SELECT custno FROM orders
WHERE custname = SYS_CONTEXT ('USERENV', 'SESSION_USER').

The package implementing the security function will dynamically add a predicate to all SELECT operators executed concerning the ORDERS table, returning only those orders that match the user’s client number (cust_no).

2.  The user shall enter the operator similar to the following one:

SELECT * FROM orders;

Oracle applies the created security function to dynamically change the user’s operator. For example, the operator shown in step 2 would be changed by the policy function created in step 1 as follows:

SELECT * FROM orders WHERE custno = (
SELECT custno FROM customers
WHERE custname = SYS_CONTEXT('USERENV', 'SESSION_USER')).

3. Oracle uses the username returned by SYS_CONTEXT(‘USERENV’, ‘SESSION_USER’) and executes the modified original query, thus limiting the data returned from the ORDERS table to the data of a specific client only.

Create a package that will address the context

Let’s consider a simple FGAC package. This FGAC implementation will use a policy that allows an employee to view only the relevant data in the employee table.

First, let’s create an hr_security package which will later be applied to access the application context. This package is a basic element of security at a low level because it generates predicates dynamic access to the table. Listing 12.13 shows the creation of the hr_security package.

SQL> CREATE OR REPLACE PACKAGE hr_security AS
2 FUNCTION empnum_sec (A1 VARCHAR2, A2 VARCHAR2)
3 RETURN varchar2;
4 END;
5*/
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY hr_security AS
2 FUNCTION empnum_sec (A1 VARCHAR2, A2 VARCHAR2)
3 RETURN varchar2
4 IS
5 d_predicate varchar2 (2000);
6 BEGIN
7 d_predicate:= 'employee_id =
8 SYS_CONTEXT("EMPLOYEE_INFO", "EMP_NUM")';
9 RETURN d_predicate;
10 END empnum_sec;
11 END hr_security;
12* /
Package body created.
SQL>

The hr_security package created in Listing 12.13 will use the employee_info context (which was created earlier in the section “Creating the application context”) to extract the emp_num variable. As mentioned in the previous section, the employee_info context extracts the emp_num variable from the USERENV namespace (SESSION_USER attribute of the USERENV namespace).

The d_predicate predicate in the hr_security package specifies a conversion to be applied to any query executed by any employee whose employee_id matches the emp_num variable derived from the employee_info context. For example, if the user salapati issues the following command:

SQL> SELECT * FROM employees;

the predicate (d_predicate) converts it in the following way:

SQL> SELECT * FROM employees
2* WHERE employee_id = SYS_CONTEXT ('EMPLOYEE_INFO', 'EMP_NUM');

Create a security policy

The hr_security package created in the previous section allows you to attach a dynamic predicate (WHERE employee_id = SYS_CONTEXT (‘EMPLOYEE_INFO’, ‘EMP_NUM’) to any SQL statements that can be used by employees whose employee_id matches emp_num obtained from the employee_info context.

However, we have not yet linked the security policy to the employee table. So now we need to specify which SQL statements and which tables the hr_security package should apply to.

In previous versions of Oracle, all security policies were dynamic, i.e. the database had to perform a policy function for each DML operator. Naturally, the repeated execution of the policy functions required additional system resources and could hurt the performance in a loaded OLTP database.

Oracle now offers several options to choose which type of policy to use. Using the POLICY_TYPE parameter of DBMS_RLS.ADD_POLICY procedure, you can define the following five types of security policies.

Dynamic. Each time a security policy function is analyzed or an operator is executed, it is rerun. This type of security policy is used by default and can be specified either by setting POLICY_TYPE to DBMS_RLS.DYNAMIC or by simply omitting this parameter completely.

Static (static). This type of policy function shall only require a single execution when a user first accesses a database object. After that, the value of the policy function is cached in the SGA area and all users accessing the object will receive the same predicate. This type can be selected by setting POLICY_TYPE to DBMS_RLS.STATIC. The function must be deterministic so that the returned predicate always remains the same: if the function has a branch that can return different predicates, you must specify a dynamic security policy.

Shared static. This type is identical to the static policy and applies to several objects. Split policies reduce the administrative overhead as they allow single security policy to cover multiple database objects. This policy can be activated by setting the POLICY_TYPE parameter to DBMS_RLS.SHARED_STATIC.

Context-sensitive. In this type of security policy, the policy predicate may change depending on the changes of certain context attributes within a user session. The database caches the policy predicate in the SGA domain. This type is selected by setting the POLICY_TYPE parameter to DBMS_RLS.CONTEXT_SENSITIVE.

A shared context is sensitive. This type of policy is similar to context-sensitive policies but applies to multiple objects. This type is selected by setting POLICY_TYPE to DBMS_RLS.SHARED_CONTEXT_SENSITIVE.

You can add a security policy to the database using the DBMS_RLS package (RLS is the acronym for row-level security) provided by Oracle. This package allows you to manage security policies – i.e. add and delete policies, policy groups, or application contexts.

At the same time, you need to specify the name of a table, view, or synonym to which you want to apply the security policy as well as the security policy to implement FGAC. It is also necessary to specify the specific type of SQL operators to which the policy will be applied, such as SELECT, INSERT, UPDATE, DELETE, CREATE INDEX, or ALTER INDEX operators.

The main procedures of the DBMS_RLS package are listed below:

• DBMS_RLS.ADD_POLICY – adds a policy to a table, view or synonym;
• DBMS_RLS.CREATE_POLICY_GROUP – creates a policy group;
• DBMS_RLS.ADD_POLICY_CONTEXT – adds context to the application.

A security policy can be created using the DBMS_RLSADD_POLICY procedure, as shown in the following example:

SQL> CONNECT system/system_passwd
Connected.
SQL> EXECUTE dbms_rls.add_policy('hr', 'employees', 'manager_policy', 'hr',-.
'hr_security.empnum_sec', 'select');

PL/SQL procedure was completed.
Note that the above operator could also be executed as follows; the result would be equivalent:

SQL> BEGIN
2 dbms_rls.add_policy
3 (object_schema => 'hr',
4 object_name => 'employees',
5 policy_name => 'manager_policy',
6 function_schema => 'hr',
7 policy_function => 'hr_security.empnum_sec',
8 statement_types => 'select');
9* END;
SQL> /

The procedure DBMS_RLS.ADD_POLICY in the above operators creates manager_policy in the scheme hr. This security policy is actually implemented by the empnum_sec function, which is part of the previously created hr_security package. The security policy indicates that it applies to all SELECT operations executed in the employees table.

Simply put, the new security policy we have created (manager_policy) will limit all SELECT operators executed with the hr.employees table to information related to the employee_id of the user who issued the request.

The success of the new policy can be verified by executing the following query:

SQL> SELECT object_name, policy_name, sel, ins, upd, del, enable
FROM all_policies;
OBJECT_NAME POLICY_NAME SEL INS UPD DEL ENABLED
----------- -------------- ---- --- ---- ---- --------
EMPLOYEES MANAGER_POLICY YES NO NO YES
SQL>

The query output shows that all SELECT operators executed with the employee’s table are now controlled by the manager_policy security policy.

To make the security policy functions available to the PUBLIC group to be used by all users accessing the database, the following rights can be granted:

SQL> GRANT EXECUTE ON hr_security TO public;
Grant succeeded.

VPD at the column level

So, we looked at applying line-level security every time we access the table. Oracle also allows you to use a column-level VPD to apply row-level security when the request only affects a specific column or columns. Column-level VPD can be applied to a table or view.

Creating a column-level security policy is almost identical to creating a normal security policy – it is enough to add operator SEC_RELEVANT_COLS to the procedure DBMS_RLS.ADD_POLICY to specify the corresponding columns to which the security policy should apply. To create a column-level security policy, the DBMS_RLS.ADD_POLICY procedure is applied as follows.

SQL> BEGIN
2 dbms_rls.add_policy
3 (object_schema => 'hr',
4 object_name => 'employees',
5 policy_name => 'manager_policy',
6 function_schema => 'hr',
7 policy_function => 'hr_security.empnum_sec',
8 statement_types => 'select,insert',
9 sec_relevant_cols => 'salary');
10 END;
SQL> /

The column-level security policy created in the example above will only take effect if you access the column in the table. Suppose that the user then issues the next request:

SQL> SELECT fname, lname, salary FROM employees;

Column-level VPD policy takes effect when a reference to the salary column is displayed in the query. In this case, the security function implementing the column level security policy returns the WHERE salary predicate =’my_salary’, thus converting the query to the following view:

SQL> SELECT fname, lname, salary FROM employees WHERE salary = 'my_salary';

Policy groups

When accessing the table Oracle searches the application context (policy context) to find out which policy group and therefore which policy should be applied. There is one policy group defined by default, SYS_DEFAULT, which cannot be removed from the database. By default, each security policy belongs to this group.

Using Oracle Policy Manager

To administer the security of Oracle labels (discussed later) and to create VPD security policies, you can use the Oracle Policy Manager graphical interface. The Oracle Policy Manager interface helps to easily create application contexts and complex security policies to implement detailed data security. No doubt, this method is much more convenient to create application contexts and security policies manually.

When using OEM to create a VPD policy, you need to create the application context and specify the table name (or view or synonym), the policy name, the name of the function that generates the predicate, and the types of operators to which the policy applies (SELECT, INSERT, UPDATE or DELETE). Oracle Policy Manager performs the DBMS_RLS.ADD_POLICY function to create an FGAC policy that supports VPD.

Label-based access control

Oracle allows you to mark parts of data and permit users to access data with certain labels. Security policies are implemented for a single column that represents a tag. Oracle Label Security, based on the previous software product Trusted Oracle, is built on the same components that help create a VPD. With it, you can easily create tags to restrict access to rows in a particular table and use authorization and permissions with tags to define a tag-based security policy. The Oracle Policy Manager GUI is primarily designed to create and administer Oracle Label Security policies.