After a year of blogging and living out loud on Twitter, I have started to build a nice network and community. One of the people in my new network is Martin Berger (blog | Twitter). Earlier this week, Martin responded to my Toad Challenge with a question about ‘Before Select Triggers’ (conversation)

So let’s get this out of the way – there is no such thing as a ‘BEFORE SELECT’ Trigger. However, the challenge remains:

How Do We Prevent Users from Doing Stuff with SELECT?

Before you start to think outside-the-box, please look inside the box first. Databases have something called security and access to objects is controlled via privileges which can be revoked if the user abuses your trust. However, it’s not always so easy to control users with privs only.

Martin posed a very clever solution to this problem by taking advantage of Oracle’s Fine-Grained Access Control (FGAC) technology. Profiles or Virtual Private Databases allow users to be given a different view of the data in your database, and it’s completely transparent to the user.

One of the most popular ways to describe this deals with sensitive employee data. For the DBAs, we don’t want them to be able to see anyone’s salary in the database. Using FGAC, you can have a WHERE clause dynamically appended to any SELECT statement the user writes against the sensitive table or view data.

I was able to set this up in just a few minutes. Here’s an example of how to do it.

FGAC In 2 Minutes

First things first, go read the docs (Oracle Docs for FGAC)

I have a table that I want to restrict users to only see a specific range or rows. For the table, we’ll assume it’s the ubiquitous ‘SCOTT.EMP.’ Anytime someone runs

SELECT *
FROM SCOTT.EMP

I want them to only see rows where the DEPTNO field is equal to 20. By implementing FGAC, Oracle will append to my query

WHERE DEPTNO = 20

and the user will never know!

Step 1: Create a Policy Group
This allows you to manage your policies, you can assign a policy to a policy group.

Step 2: Create a Policy Function
This is the PL/SQL code that is executed by Oracle to implement your ‘baby-sitter’ policy. This can be in a package or a stand-alone function. I was just playing this feature, so I just created a function, but in the real world, a package containing all of your policies is probably a better idea.

Here’s the example that Oracle provides (Oracle Example Code), and here’s what I wrote:

CREATE OR REPLACE FUNCTION QUEST_OPTI.scott_emps( 
  schema_var IN VARCHAR2, --required
  table_var  IN VARCHAR2 --required
 )
 RETURN VARCHAR2
 IS
  return_val VARCHAR2 (400); --required to be VARCHAR2, and hopefully 400 is enough space for you!
 BEGIN
  return_val := 'DEPTNO=20'; --don't put WHERE here, just the actual clause
  RETURN return_val;
 END scott_emps;
/

Oracle recommends you do NOT create this program in the user schema that is going to be controlled. Think about it, if it is, the user would be able to change the function, and that kinda goes against the entire idea here.

Step 3: Create The Policy

I hope you know PL/SQL, and I hope you’re familiar with the SYS.DBMS_RLS package! If not, you can use a GUI too. I am a GUI guy, so here goes:

Creating the Oracle Policy


Step 3.5: Column Masking, Yes or No?

This is glossed over in the Oracle docs topic, but Oracle-Base to the rescue! To paraphrase, you can decide if you want the user to see the ‘uncontrolled’ data or not. Turning this on, they’ll see all rows except the controlled column. Leaving it off, the entire row is left out of the result set.

Either the user thinks rows are missing or non-existent, or the user is savvy enough to see the data they need to see and know not to ask too many questions about the rest.

I say column-masking - NO.

Ok code-sticklers, here’s the PL/SQL that Toad generates for me:

BEGIN
  SYS.DBMS_RLS.ADD_GROUPED_POLICY
    (
      Object_schema         => 'SCOTT'
     ,Object_name           => 'EMP'
     ,policy_group           => 'SCOTT_EMP'
     ,policy_name           => 'SCOTT_EMPS'
     ,function_schema       => 'QUEST_OPTI'
     ,policy_function       => 'SCOTT_EMPS'
     ,statement_types       => 'SELECT '
     ,policy_type           => dbms_rls.dynamic
     ,long_predicate        => FALSE
     ,sec_relevant_cols     => 'EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO'
     ,sec_relevant_cols_opt => NULL
     ,update_check          => FALSE
     ,enable                => TRUE
    );
END;

Step 4: Test

Warning! If you have a typo in your function, it will break access to the table for your controlled users! I know this, because I did it, oops. I had DEPTNO spelled as ‘DEPT_NO’ for some reason. This creates an invalid SQL statement.

Login as the affected user, and try to query the table.

Wait, there should be more data here!?!

Login as SYSDBA, and try to query the table.

The SYSDBA 'God' user sees all!

Consider Auditing SYSDBA Logins

As a Best Practice, I recommended never logging in as SYSDBA unless you really need to. Unless you are going to shut down the database or something very high level, don’t login as SYSDBA. If you have implemented FGAC in Oracle, logging in as SYSDBA will expose you to the data you shouldn’t be looking at. Just something to think about. If you take security seriously, keep an eye on who is logging in as SYSDBA. Hopefully you can trust the people who have this level of access, but trust should be earned. And in some cases you should keep an eye on things regardless. This might be one of those cases.

Advanced Options

Once you have the basics nailed down, you are ready to implement this for real. In a real-world environment you’ll want to be able to dynamically implement FGAC based on the user. Different users will need different levels of access to the data. This can be accomplished via Contexts and Logon Triggers to set said context. Read here for detailed instructions.