In this scenario I'll explain how to hide data on a table from unauthorized users even if these unauthorizes users have DBA privilege.
After hiding the data from unauthorized users whenever they try to select from the secured table they will receive "no rows selected" message.
The advantage of this way that the secured table will look like having no rows, at all will likely be less attractive to intruders than a table that has records but that hides or masks "interesting" columns.
In this demonstration I'll use SCOTT schema.
Firstly: let's give some powerful privileges to user HR on scott.emp table:
SQL> grant dba to hr;
Grant succeeded.
SQL> grant all on scott.emp to hr;
Grant succeeded.
Let's try to select from scott.emp
SQL> conn hr/hr
Connected.
SQL> select count(*) from scott.emp; --user HR can select from scott.emp
COUNT(*)
----------
15
SQL> delete from scott.emp; --user HR can also delete from scott.emp
15 rows deleted.
SQL> rollback;
Rollback complete.
Now we will not revoke any privileges from HR but we will hide the data from him only in two steps.
Step1: Create predicate generation function:
This function will allow/block the access on table data upon specified conditions like (logon username, IP address, machine name ,..etc)
conn scott/tiger
CREATE OR REPLACE FUNCTION sec_emp (oowner IN VARCHAR2, ojname IN VARCHAR2)
RETURN VARCHAR2 AS
cond VARCHAR2 (200);
BEGIN
IF (SYS_CONTEXT('USERENV','OS_USER')='JAMES' AND --OS Username
-- SYS_CONTEXT('USERENV','IP_ADDRESS')='192.168.1.211' AND --IPADRESS
SYS_CONTEXT('USERENV','SESSION_USER')<>'HR') --not allowed users(check the note below)
-- AND SYS_CONTEXT('USERENV','TERMINAL')='PC77') --MACHINE NAME
-- AND SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER')='Administrator') --SESSION IDENTIFIER
THEN
cond := ' SAL IS NOT NULL'; --Condition is set on SAL column
ELSE
cond := ' SAL IS NULL';
END IF;
RETURN (cond);
END sec_emp ;
/
SHOW ERRORS;
Note:
In the line# 7 "SYS_CONTEXT('USERENV','SESSION_USER')='SCOTT')"
You can specify the users who only allowed to access scott.emp data and block the others:
e.g. if users scott,Smith,John are the only ones allowed to access scott.emp table, line# 7 will be like that:
SYS_CONTEXT('USERENV','SESSION_USER') in ('SCOTT','SMITH','JOHN')
And vice versa, you can specify the users who are not allowed to access scott.emp and allow the other users:
e.g. if Users HR,Emma,Lee shouldn't access scott.emp data and the others are allowed, line# 7 will be like that:
SYS_CONTEXT('USERENV','SESSION_USER') not in ('HR','EMMA','LEE')
Restrictions are NOT limited to usernames, as you can see in the function above you can restrict by machine name,IP, operating system username,... the thing that gives you the power in securing sensitive data.
Step2: Add a security policy:
Next we will add a policy to enforce the function conditions on SCOTT.EMP table:
Connect as sysdba
BEGIN
DBMS_RLS.ADD_POLICY(object_schema=>'SCOTT', object_name=>'EMP',
policy_name=>'HIDE_EMP', function_schema=>'SYS',
policy_function=>'SEC_EMP');
END;
/
--In case the policy is already exist drop it first:
EXEC dbms_rls.drop_policy(object_schema=>'SCOTT',object_name=>'EMP',policy_name=>'HIDE_EMP');
Secondly: Now let's check if user HR can access scott.emp data:
SQL> select count(*) from scott.emp;
COUNT(*)
----------
0
SQL> delete from scott.emp;
0 rows deleted.
User HR can issue the commands on scott.emp table this means he have no problem with his privileges, but he can't access or play with the data inside scott.emp table, Now user HR will get an indication that table scott.emp is empty so it's highly probable that he will not do further investigations on that table ;-)
For more information and techniques please check this paper
http://www.oracle.com/technetwork/articles/idm/jucan-security-094705.html
