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)) bwhere 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?
Previous
Back to DBAsupport.com
