thatjeffsmith Database Stuff 9 Comments

Tell Others About This Story:

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

[sql] SELECT *

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

[sql] 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:

schema_var IN VARCHAR2, –required
table_var IN VARCHAR2 –required
return_val VARCHAR2 (400); –required to be VARCHAR2, and hopefully 400 is enough space for you!
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:

[sql] BEGIN
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_opt => NULL
,update_check => FALSE
,enable => TRUE

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.

Tell Others About This Story:

Comments 9

  1. Hopefully it is okay to comment on this older topic.
    Is there any way to leverage VPD to append a HAVING clause?
    I need to do something similar but the masking is conditional and to date, the only SQL I can build to simulate it has the masking condition in a HAVING clause.

    Here is an example:
    Time_Wk, Category, Brand, sum( Value_Sales )
    group by
    Time_Wk, Category, Brand
    count( distinct Retailer ) > 2
    and ( sum( Sensitive_Sales ) / sum( Value_Sales * 1.0 ) ) < .95

    1. Yes – you can since you can create a view of your aggregated data, and put a policy function on that.


      create or replace function
      my_security_function( p_schema in varchar2,
      p_object in varchar2 )
      return varchar2
      return ‘dist_retail > 2 and sensitive_ratio > 0.95’;

      drop table t purge;
      create table t as select * from dba_Objects;

      create or replace
      view v as select
      count(distinct object_type) dist_retail,
      sum(object_id) / sum( object_id )-1 sensitive_ratio
      from t
      group by owner;

      dbms_rls.drop_policy( user, ‘V’, ‘MY_POLICY’ );
      when others then null;
      ( object_schema => user,
      object_name => ‘V’,
      policy_name => ‘MY_POLICY’,
      function_schema => user,
      policy_function => ‘MY_SECURITY_FUNCTION’,
      statement_types => ‘select, insert, update, delete’,
      update_check => true );

      But dont forget… this means we will need to resolve the data in the view *first* and then apply the policy function.

      1. thatjeffsmith Post
  2. being a newbie please pardon my ignorance, what is the difference between VPD’s and Views in this scenario. Similar abstraction be done by views and we provide grants on views to user(here scott). allowing only specific columns or filering specific rows. ?

    1. JeffS Post
  3. Possible “next up”, using Resource Profiles/Manager as “DURING SELECT” trigger to prevent queries from sucking every last smidgen of the server.

    1. JeffS Post
  4. Wow, didn’t think this is possible! (but never checked before).
    Is it also possible (at least for the DBA) to see all existing policies, groups, maybe even relevant columns, without going to the DBA_ views directly?

    1. JeffS Post

      I can do that in Toad, there is a ‘Policies’ tab for each table in the browser. Of course, it reads that information from the data dictionary…

Leave a Reply

Your email address will not be published. Required fields are marked *