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;
SQL> grant all on scott.emp to hr;
Let's try to select from scott.emp
SQL> conn hr/hr
SQL> select count(*) from scott.emp; --user HR can select from scott.emp
SQL> delete from scott.emp; --user HR can also delete from scott.emp
15 rows deleted.
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)
CREATE OR REPLACE FUNCTION sec_emp (oowner IN VARCHAR2, ojname IN VARCHAR2)
RETURN VARCHAR2 AS
cond VARCHAR2 (200);
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
cond := ' SAL IS NOT NULL'; --Condition is set on SAL column
cond := ' SAL IS NULL';
END sec_emp ;
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
--In case the policy is already exist drop it first:
Secondly: Now let's check if user HR can access scott.emp data:
SQL> select count(*) from scott.emp;
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