Three Tier Oracle Security in London ~ Paul M. Wright

(nix, oracle, java, www, cloud ) intersect (safety, security, reliability, integrity)

Three Tier Oracle Security in London ~ Paul M. Wright RSS Feed
 

VPD vite

A lot of folks think that implementing VPD has to be complex and time consuming. This is not the case.
VPD can be used to implement a DENY on a table very quickly indeed.

For example, in order to stop a person selecting a table just need to create the function which adds a predicate which is added to all queries which select from the target table.

CREATE OR REPLACE FUNCTION TEST_VPD_ACL(
p_schema IN VARCHAR2, p_object IN VARCHAR2)
RETURN VARCHAR2
AS
BEGIN
RETURN 'USER NOT IN(''DENIEDUSERNAME'')';
END;
/

Then add the policy that associates this function with a particular table in a particular schema using DBMS_RLS.

BEGIN
DBMS_RLS.add_policy
(object_schema => 'SCOTT',
object_name => 'TABLE_NAME',
policy_name => 'SCOTTS_POL',
policy_function => 'TEST_VPD_ACL');
END;
/

That’s it! What could be easier? VPD comes free with the Enterprise edition of Oracle DB and can be implemented without a heavy performance hit. The VPD policy can be made more performant by using a static policy as detailed in Effective Oracle Database 10g Security by Design By David Knox

If you need to drop the policy then just as easy:

BEGIN
DBMS_RLS.drop_policy
(object_schema => 'SCOTT',
object_name => 'TABLE_NAME',
policy_name => 'SCOTTS_POL');
END;
/

These are the main views pertinent to VPD:

SELECT * FROM all_policies;
SELECT * FROM dba_policies;
SELECT * FROM v$vpd_policy;

Lastly, bear in mind that VPD can be legitimately bypassed by any user that has the EXEMPT ACCESS POLICY system privilege. But which users have this system privilege?

SQL> select grantee from dba_sys_privs where privilege='EXEMPT ACCESS POLICY';
no rows selected

No one!? DBAs (and SYSDBAs) do not appear to be granted this system privilege. So no one can bypass the VPDs..? If a user can run code as DBA then they can bypass VPDs because even though it is not listed as a granted system privilege of the DBA role, a DBA can simply grant it to themselves or whomever.

SQL> select grantee from dba_sys_privs where privilege='EXEMPT ACCESS POLICY';
no rows selected
SQL> sho user
USER is "SCOTT"
--
SQL> select * from user_role_privs;
USERNAME GRANTED_ROLE ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
PUBLIC OTS_ROLE NO YES NO
SCOTT CONNECT NO YES NO
SCOTT DBA NO YES NO
SCOTT RESOURCE NO YES NO
--
SQL> grant EXEMPT ACCESS POLICY to scott;
Grant succeeded.
--
SQL> select grantee from dba_sys_privs where privilege='EXEMPT ACCESS POLICY';
GRANTEE
------------------------------
SCOTT

Given that EXEMPT ACCESS POLICY is such a powerful system privilege that it can bypass VPDs I think it would be a good idea to list it as one of the system privileges that the DBA role effectively has in DBA_SYS_PRIVS.

On a related note, David’s book has a free chapter on VPD security, available at this URL http://www.databasesecurity.com/dbsec/ohh-defeating-vpd.pdf

For a good presentation with detail on implementing VPD please refer to Pete’s work on VPD.

So, that’s it for now. Preparations for SANS London and UKOUG 2009.

Cheers,
Paul

Leave a Reply

You must be logged in to post a comment.