Every Which Way But Loose


Or, “How to add a policy in Oracle that a user can select all data from table and only can insert, update, delete his data with condition”

This rather lengthy title came from a post on stackoverflow.com. The author was trying to devise a Virtual Private Database policy that would apply to the following simple table.

connect app_schema/password

CREATE TABLE EMPHOLIDAY 
   (    EMPNO NUMBER(5) NOT NULL ENABLE, 
        NAME NVARCHAR2(60), 
        HOLIDAY DATE
   );

INSERT INTO empholiday VALUES (1,'HANN','02-JAN-2019');
INSERT INTO empholiday VALUES (2,'ANNU','12-MAY-2019');
INSERT INTO empholiday VALUES (3,'THEOTA','26-AUG-2019');

I have added the primary and unique constraints, which I will explain shortly.

The author also specified the following business rules be applied:

I created 3 users: HANN, ANNU,THEOTA I want to buid a VPD policy for these users:

  • ANNU only can select and modify her data.
  • THEOTA cannot select and modify any data.
  • HANN can select all data but only can modify (insert, delete, update) in column ‘Holiday’ with a date larger than current date (cannot modify date in the part)

And they had taken a stab at a VPD policy that would let ANNU see their data and prevent THEOTA from seeing any data. They were stuck on how to deal with the business logic associated with HANN’s requirements.

First Rule: If you don’t want someone to have access to something, simply don’t grant them access to it in the first place.

The biggest issue with the author’s approach is trying to solve all of these rules with a single solution, when in fact several are required. To fully understand, let’s break it down user by user, starting with THEOTA, who is the simplest case to deal with. Poor THEOTA may be able to do other things in our fictional database, but she is not allowed any access to the empholiday table for some reason. We’re going to give her a role called “no_holiday_user_role”.

create role no_holiday_user_role;
grant connect to no_holiday_user_role;
grant no_holiday_user_role to theota;
alter user theota default role all;

The no_holiday_user_role has absolutely no privileges whatsoever on the empholiday table. This is known as the Principle of Least Privilege, and it can be summed up as, “if they don’t need it, don’t give it to them.” By following this rule, our first requirement that THEOTA not be able to select from the table or make any updates is satisfied.

Second Rule: Use the right tool for the job

The next rule applies to both ANNU and HANN. ANNU should be able to select only their own data, while HANN should be able to select all of the data. This kind of row level access control is what Virtual Private Database was designed for. Let’s assume that each user represents a different user type, represented by two more roles: holiday_user_role and holiday_hr_role.

create role holiday_user_role;
grant select on empholiday to holiday_user_role;
grant holiday_user_role to annu;
alter user annu default role all;

create role holiday_hr_role;
grant select on empholiday to holiday_hr_role;
grant holiday_hr_role to hann;
alter user hann default role all;

The basic privileges for each role are the same, but our VPD policy will define the difference for us:

create or replace function 
   empholiday_policy_func (p_schema varchar2, p_obj varchar2)
return varchar2 as
   l_count number;
   l_return varchar2(100);
begin
   -- will return 0 for no roles, 1 for holiday_user_role, 
   -- 2 for holiday_hr_role, or 3 for both roles
   select sum(decode(role, 
             'HOLIDAY_USER_ROLE',1,
             'HOLIDAY_HR_ROLE',2
             ,0)) into l_count 
     from session_roles;

   case 
      -- when no roles then hide all data; this will protect
      -- data from users with "select any table" privilege
      when l_count = 0 then
         l_return := '1=0';
      -- when only holiday_user_role, show users' data
      when l_count = 1 then
         l_return := 'name=''' || 
                     SYS_CONTEXT('USERENV','SESSION_USER') || 
                     '''';
      -- when holiday_hr_role or both then show all data
      when l_count > 1 then
         l_return '1=1';
   end case;
   return l_return;
end;
/

begin
  dbms_rls.add_policy
    (object_schema=>'app_schema' 
    ,object_name=>'empholiday'  
    ,policy_name=>'empholiday_policy' 
    ,policy_function=>'empholiday_policy_func' 
    ,statement_Types=>'SELECT,UPDATE,INSERT,DELETE'  
    );
end;

So a user with only the HOLIDAY_USER_ROLE will only be able to view their own data, while a user with the HOLIDAY_HR_ROLE (or both roles) activated will be able to view any data in the table. This satisfies the requirements about what ANNU and HANN are allowed to see.

Third Rule: Be prepared to use all of the tools you’ve been given

To satisfy the requirements for what ANNU and HANN are allowed to modify, we need more complicated business logic that is best handled in PL/SQL stored procedures. Remember that ANNU should be able to update any of her data, while HANN should only be allowed to update the Holiday column (of data that is not his own) and then only to increase the date to a value larger than the current value.

As we look at how to do this, it is important to consider that we can identify each user uniquely. This is where the primary key and unique name constraints come into play. They allow us to define the table in such a way that one user cannot reassign their data to someone else, or that one user could have more than a single holiday defined. This will help prevent the small PL/SQL API we’re about to build from being misused.

CREATE TABLE EMPHOLIDAY 
   (    EMPNO NUMBER(5) NOT NULL ENABLE, 
        NAME NVARCHAR2(60), 
        HOLIDAY DATE, 
        CONSTRAINT EMPHOLIDAY_PK PRIMARY KEY (EMPNO)
           USING INDEX ENABLE,
        CONSTRAINT "EMPHOLIDAY_UK1" UNIQUE (NAME)
           USING INDEX ENABLE
   );

Note: I made assumptions about the original author’s requirements here because they seemed to make sense, but every application is unique so always be sure what your requirements are before starting to write real code.

Using SQL Developer, I can quickly generate a basic table API in PL/SQL by right-clicking on the table name and selecting “Table / Generate Table API…” from the popup menu.

CREATE OR REPLACE PACKAGE empholiday_tapi IS
    TYPE empholiday_tapi_rec IS RECORD (
        holiday   empholiday.holiday%TYPE,
        empno     empholiday.empno%TYPE,
        name      empholiday.name%TYPE
    );
    TYPE empholiday_tapi_tab IS
        TABLE OF empholiday_tapi_rec;

    -- insert
    PROCEDURE ins (
        p_holiday   IN   empholiday.holiday%TYPE DEFAULT NULL,
        p_empno     IN   empholiday.empno%TYPE,
        p_name      IN   empholiday.name%TYPE DEFAULT NULL
    );

    -- update
    PROCEDURE upd (
        p_holiday   IN   empholiday.holiday%TYPE DEFAULT NULL,
        p_empno     IN   empholiday.empno%TYPE,
        p_name      IN   empholiday.name%TYPE DEFAULT NULL
    );

    -- delete
    PROCEDURE del (
        p_empno IN empholiday.empno%TYPE
    );

END empholiday_tapi;
/

CREATE OR REPLACE PACKAGE BODY empholiday_tapi IS
    -- does user have holiday_user_role?
    -- does user have holiday_hr_role?
    FUNCTION get_privilege RETURN NUMBER IS
        l_return NUMBER;
    BEGIN
        -- generate binary privilege code
        -- 0: no role
        -- 1: holiday_user_role
        -- 2: holiday_hr_role
        -- 3: holiday_user_role + holiday_hr_role
        SELECT
            SUM(decode(role,
               'HOLIDAY_USER_ROLE', 1, 
               'HOLIDAY_HR_ROLE', 2,
               0))
        INTO l_return
        FROM
            session_roles;

        RETURN l_return;
    END get_privilege;
    
    -- insert
    PROCEDURE ins (
        p_holiday   IN   empholiday.holiday%TYPE DEFAULT NULL,
        p_empno     IN   empholiday.empno%TYPE,
        p_name      IN   empholiday.name%TYPE DEFAULT NULL
    ) IS
    BEGIN
        IF get_privilege IN (1,3)
          AND p_name = sys_context('USERENV', 'SESSION_USER') THEN
            INSERT INTO empholiday (
                holiday,
                empno,
                name
            ) VALUES (
                p_holiday,
                p_empno,
                p_name
            );
        ELSE
            -- raise application error
        END IF;
    END ins;   

    -- update
    PROCEDURE upd (
        p_holiday   IN   empholiday.holiday%TYPE DEFAULT NULL,
        p_empno     IN   empholiday.empno%TYPE,
        p_name      IN   empholiday.name%TYPE DEFAULT NULL
    ) IS
        l_holiday   empholiday.holiday%TYPE;
    BEGIN
        -- if user owns data and has user privilege then update
        CASE
        WHEN get_privilege IN (1,3)
         AND p_name = sys_context('USERENV', 'SESSION_USER') THEN
            UPDATE empholiday
            SET
               holiday = p_holiday,
               name = p_name
            WHERE
               empno = p_empno;
        WHEN get_privilege IN (2,3)
         AND p_name != sys_context('USERENV', 'SESSION_USER') THEN
            select holiday into l_holiday from empholiday
             where empno = p_empno;

            IF 
              AND p_holiday > l_holiday then
                UPDATE empholiday
                SET
                   holiday = p_holiday
                WHERE
                   empno = p_empno;
            ELSE
                -- raise application error
            END IF;
        ELSE
            -- raise application error
        END;
    END upd;

    -- delete
    PROCEDURE del (
        p_empno IN empholiday.empno%TYPE
    ) IS
    BEGIN
        -- if user owns data and has user privilege then update
        IF get_privilege IN (1,3)
          AND p_name = sys_context('USERENV', 'SESSION_USER') THEN
            DELETE FROM empholiday
            WHERE
                empno = p_empno;
        ELSE
            -- raise application error here
        END IF;
    END del;

END empholiday_tapi;

So rather than grant DML privileges on the empholiday table directly to roles or users, I now grant execute privileges on my API. As a result, my various user privilege assignments now look like this:

create role no_holiday_user_role;
grant connect to no_holiday_user_role;
grant no_holiday_user_role to theota;
alter user theota default role all;

create role holiday_user_role;
grant select on empholiday to holiday_user_role;
grant execute on empholiday_tapi to holiday_user_role;
grant holiday_user_role to annu;
alter user annu default role all;

create role holiday_hr_role;
grant select on empholiday to holiday_hr_role;
grant execute on empholiday_tapi to holiday_hr_role;
grant holiday_hr_role to hann;
alter user hann default role all;
Summary

Rather than a one-size fits all solution – trying to handle everything through a Virtual Private Database policy – a proper security plan involves the use of a variety of techniques, each with their own place in the model:

  • Roles to define user types
  • Enforcing the Principle of Least Privilege
  • Virtual Private Database to control which data users can select based on their role
  • Table Constraints to ensure uniqueness or other simple business logic
  • PL/SQL Table APIs to control which data users can modify using complex business logic

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.