Oracle 10g: A Simple Security Approach Part II

by JP Vijaykumar


Though the title says “A Simple Security Approach Part II”, the topics covered in this article are COMPLEX in nature. Welcome to the world of complexities in database access and security.


This document describes setting up column level / row level security of data through custom built views, and a critical review on the creation and maintenance of procedures for truncating tables in third party schemas.



A detailed discussion on the setup and implementation of Virtual Private Database / Transparent Data Encryption is beyond the scope of this document.


Please refer to Oracle’s Metalink for a detailed discussion on Virtual Private Database setup and Transparent Data Encryption setup


0200 Special requirements


0201 Protecting sensitive / confidential data using column level / row level security through custom built views.


0202 Granting truncate privileges on tables in others’ schemas.


Managing specific and special requirements of application developers is always a challenge. The present day DBA needs to know the practicability of these requirements in the Oracle environment. Weigh the merits and demerits of implementation of such special requirements and their impact on the application.


0201 Protecting sensitive / confidential data using column level / row level security through custom built views


For my designing and modeling of views, I work with scott.emp table. Assume that all users logging into the database have their employee details available in scott.emp table. All users are given select permissions on the view. Any user whose employee details are not available in the scott.emp table will get 0 rows as output from the custom built view.

Connect as scott
select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
—– —– ———- ——— ———- ——— ———- ———-
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
 
14 rows selected.
Create a user for accessing the data from our custom built view.
Create user blake identified by blake account unlock;
Grant create session to blake;
 

Imposing ROW Level security:


Version I – Only the login user’s details are displayed.

create or replace view      emp_jp_view as
select a.empno, a.ename, a.job, a.mgr, a.hiredate,a.sal,a.comm,a.deptno
from scott.emp a,
(select sys_context(‘USERENV’,’SESSION_USER’) username from dual) b
where a.ename = b.username;
Grant select on emp_jp_view to blake;
connect blake/blake
select * from j2000.emp_jp_view;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
——- —– ——- —- ——— —- —– ——–
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

Version II – Details of the login user and users who work for this user are also displayed

create or replace view      emp_jp_view  as  
select a.empno, a.ename, a.job, a.mgr, a.hiredate,a.sal,a.comm,a.deptno
from scott.emp a,
(select ename from scott.emp
Connect by prior empno = mgr
Start with ename in (select sys_context(‘USERENV’,’SESSION_USER’)  username from dual)) b
where a.ename = b.ename;
 
connect as blake
select * from j2000.emp_jp_view;
 
     EMPNO ENAME        JOB              MGR          HIREDATE         SAL       COMM       DEPTNO
———- ———-   ———        ———-   ———      ———- ———- ———-
      7698 BLAKE        MANAGER         7839          01-MAY-81       2850                      30
      7499 ALLEN        SALESMAN        7698          20-FEB-81       1600        300           30
      7521 WARD         SALESMAN        7698          22-FEB-81       1250        500           30
      7654 MARTIN       SALESMAN        7698          28-SEP-81       1250       1400           30
      7844 TURNER       SALESMAN        7698          08-SEP-81       1500          0           30
      7900 JAMES        CLERK           7698          03-DEC-81        950                      30
6 rows selected.

Imposing COLUMN Level security:


Version III – Only the login user’s details are displayed.

create or replace view
emp_jp_view
as
select
a.empno,
decode(b.username,’KING’,a.ename,’***’) ename,
decode(b.username,’KING’,a.job,’***’) job,
decode(b.username,’KING’,a.mgr,0) mgr,
a.hiredate,
decode(b.username,’KING’,a.sal,0) sal,
a.comm,
a.deptno
from scott.emp a,
(select sys_context(‘USERENV’,’SESSION_USER’) username from dual) b;

As per the logic implemented in the view creation, only the user KING is authorized to see the sensitive and confidential columns in the scott.emp table. Any user logging in as KING can see all the columns in the tables. All others can see the columns EMPNO, HIREDATE and DEPTNO, the rest of the columns in the view are masked.


We can also include a list of employees into our logic, who can see all the columns from the view.

Connect as blake
Select * from j2000.emp_jp_view;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— —– ——— —– ———- ———-
7369 *** *** 0 17-DEC-80 0 20
7499 *** *** 0 20-FEB-81 0 300 30
7521 *** *** 0 22-FEB-81 0 500 30
7566 *** *** 0 02-APR-81 0 20
7654 *** *** 0 28-SEP-81 0 1400 30
7698 *** *** 0 01-MAY-81 0 30
7782 *** *** 0 09-JUN-81 0 10
7788 *** *** 0 19-APR-87 0 20
7839 *** *** 0 17-NOV-81 0 10
7844 *** *** 0 08-SEP-81 0 0 30
7876 *** *** 0 23-MAY-87 0 20
7900 *** *** 0 03-DEC-81 0 30
7902 *** *** 0 03-DEC-81 0 20
7934 *** *** 0 23-JAN-82 0 10
14 rows selected.

As the connected user is not KING, all the columns of the view are not displayed.


Imposing ROW Level and COLUMN level security:


Version IV – Only the login users details are displayed.

 create or replace view
emp_jp_view
as
select
a.empno,
case when a.ename in (select sys_context(‘USERENV’,’SESSION_USER’) from dual) then a.ename
else ‘***’ end ename,
case when a.ename in (select sys_context(‘USERENV’,’SESSION_USER’) from dual) then a.job
else ‘***’ end job,
case when a.ename in (select sys_context(‘USERENV’,’SESSION_USER’) from dual) then a.mgr
else 0 end mgr,
a.hiredate,
case when a.ename in (select sys_context(‘USERENV’,’SESSION_USER’) from dual) then a.sal
else 0 end sal,
a.comm,
a.deptno
from scott.emp a;
View created.
Connect as blake
select * from j2000.emp_jp_view
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 *** *** 0 17-DEC-80 0 20
7499 *** *** 0 20-FEB-81 0 300 30
7521 *** *** 0 22-FEB-81 0 500 30
7566 *** *** 0 02-APR-81 0 20
7654 *** *** 0 28-SEP-81 0 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 *** *** 0 09-JUN-81 0 10
7788 *** *** 0 19-APR-87 0 20
7839 *** *** 0 17-NOV-81 0 10
7844 *** *** 0 08-SEP-81 0 0 30
7876 *** *** 0 23-MAY-87 0 20
7900 *** *** 0 03-DEC-81 0 30
7902 *** *** 0 03-DEC-81 0 20
7934 *** *** 0 23-JAN-82 0 10

Version V – Details of the login user and others who work for this user are also displayed

Create or replace view
emp_jp_view
as
select
a.empno,
case when a.ename in (select ename from scott.emp connect by prior empno = mgr Start with ename in
(select sys_context(‘USERENV’,’SESSION_USER’) username from dual))
then a.ename else ‘***’ end ename,
case when a.ename in (select ename from scott.emp Connect by prior empno = mgr Start with ename in
(select sys_context(‘USERENV’,’SESSION_USER’) username from dual))
then a.job else ‘***’ end job,
case when a.ename in (select ename from scott.emp connect by prior empno = mgr Start with ename in
(select sys_context(‘USERENV’,’SESSION_USER’) username from dual))
then a.mgr else null end mgr,
a.hiredate,
case when a.ename in (select ename from scott.emp connect by prior empno = mgr Start with ename in
(select sys_context(‘USERENV’,’SESSION_USER’) username from dual))
then a.sal else 0 end sal,
a.comm,
a.deptno
from scott.emp a;
 
connect as blake
select * from j2000.emp_jp_view
 
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
—– —- ———- ——— ———- ——— ———- ———-
7369 *** *** 17-DEC-80 0 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 *** *** 02-APR-81 0 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 *** *** 09-JUN-81 0 10
7788 *** *** 19-APR-87 0 20
7839 *** *** 17-NOV-81 0 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 *** *** 23-MAY-87 0 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 *** *** 03-DEC-81 0 20
7934 *** *** 23-JAN-82 0 10
14 rows selected.

0202 Granting Truncate privileges on tables in others’ schemas.


Any user with DBA role or DROP ANY TABLE system level privilege can TRUNCATE tables in others’ schemas. Any schema owner can truncate tables in his/ her schema.


In secure production environments, it is not a good practice to grant DBA role / DROP ANY TABLE system level privilege to users, for this purpose.


The workaround is to create a procedure in a scheme, which is granted DBA role or DROP ANY TABLE system level privilege. In turn, grant execute permissions on the truncate table procedure to the users, who need truncate privileges on other schemas’ tables.

CREATE OR REPLACE PROCEDURE TRUNCATE_TABLE_PROC (oname IN varchar2,
tname IN varchar2 ) as
/**********************************
AUTHOR JP Vijaykumar
Oracle DBA
DATE 10-05-2006
**********************************/
begin
execute immediate ‘truncate table ‘||oname||’.’||tname;
exception
when others then
raise_application_error(-20001,’Insufficient privileges’);
end;

The above truncate table procedure is a simple procedure. Whoever is granted EXECUTE privileges on the procedure can truncate any table in the database. It is a time bomb.


In the event that this procedure is misused in a production / or secure environment, imagine the trouble to recover the lost data


It is not a good practice to use generic truncate table procedures in production / or secure environments. For a more secure truncate table procedure, please refer to my article, Secure Truncate Table Procedure.


Day in and day out, sensitive and confidential data is stolen from everywhere. In protecting the sensitive and confidential data from the unauthorized and hackers, using data modeling and custom built views, the options are endless and unimaginable.


If my prime objective is to protect confidential data from hackers and unauthorized access, my simple approach is DEVIDE AND CONQUER. I will keep my trillion dollars in a safe that can only be opened with 10 KEYS–NOT WITH A SINGLE KEY. Even if I loose 9 KEYS out of 10, there is no harm. My money is safe. However, if the safe can be opened with a SINGLE key and that single key is lost. Then all is lost.


Depending on the sensitivity of data and business requirements, the hierarchical structure of employee details may be stored in one denormalized table or in more normalized tables. It is a good practice to store the employee details in more normalized tables than in one denormalized table. To further protect the data from unauthorized access and hackers, create the tables in different schemas. You can even move these different schema tables into different databases.


No matter how hard you protect the data in its entirety, it is theft prone. So dismember your data and protect it. Redefine the database access to each schema. Only allow a few users to access all these schema tables to create the full picture. Grant SELECT permission on the custom built view to the end users. In addition, users accessing the view can only see their own data. No other’s data is visible. Create public synonym for the view.


When you partition the table horizontally, the records in their entirety are segregated into separate partitions, depending on the partition key ranges.


Partition the table vertically into smaller tables with a few columns each. Unless all of the partitions are joined together, the complete record cannot be constructed. Data from a single vertical partition is not complete.


So I will cut my scott.emp table, which holds sensitive and confidential data, into vertical partitions.


Like

Desc scott.name  [scott.n101]      {scott.n101@test}  (scott.n101@dallas)
Empno varchar2(10)
Firstname varchar2(20)
Lastname varchar2(20)
Desc harry.job [harry.j102] {harry.j102@test} (harry.j102@austin)
Empno varchar2(10)
Job varchar2(20)
Mgr varchar2(10)
Desc bruce.time [bruce.t103] {bruce.t103@test} (bruce.t103@houston)
Empno varchar2(10)
Hiredate date
Reviewdate date
 
Desc tom.sal [tom.s104] {tom.s104@test} (tom.s104@arlington)
Empno varchar2(10)
Sal number(10,2)
Comm Number(10,2)
Bonus number(10,2)
Incentives number(10,2)

It is a good strategy to keep the view and its parent tables in different schemas / or databases. The viability of an application to populate the vertically partitioned scott.emp table across schemas / databases is to be taken care of before moving the vertical partitions across schemas / databases.


For this secure data model to work, the application needs to be modified / re-written to populate the dismembered EMP table pieces across the schemas / databases.


No select privileges should be granted to schema owners scott, bruce, tom and harry on others’ schema tables. Only the view_admin user can access the tables in scott, tom, bruce and harry’s schemas to create the required row level / column level view.


Do not allow any users to login as the view_admin user. Only users, who are granted select privileges on the custom built view are allowed to login to the database.


Everything has its merits and demerits. If you keep everything in one box, the hackers’ job is made easy. If you take extra care and distribute the data, at least all is not lost.


You can further complicate the security model and implement more levels and masking with synonyms and making the access more complicated with grants and profiles.


Audit all sessions accessing the sensitive and confidential tables.


Imagine my architecture.

STAGE03  PUBLIC Synonym          EMPLOYEE

STAGE02 View JP.N101_J102_T103_S_104_VIEW

STAGE01
scott.n101@dallas harry.j102@cleveland bruce.t103@buffalo tom.s104@austin


Let us analyze the scenarios; an intruder accessed the database at –


STAGE01 – any schema owner can access one vertical partition of the table. As the table’s names are meaningless, it is difficult to access the right table. As any schema owner is not permitted to access data from other schema’s tables, the rest of the data is safe and secure.


STAGE02 – all of the user logins as VIEW_ADMIN are blocked. Unless someone connects as sys or system and obtains the view definition for all my important tables, data is not lost. Such an occurrence is very remote. I am auditing all users accessing secure and confidential data.


STAGE03 – all users with select permission on the view can access his or her own data from the view.


Don’t you think this data model is secure and safe from unauthorized access and hackers?


Do you want me to complicate things further?

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles