Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Dec 12, 2006

Oracle 10g: A Simple Security Approach Part II

By DatabaseJournal.com Staff

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?



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM