Alternatives to Oracle Fine Grained Access Control Donald K. Burleson

Oracle’s new fine-grained access control mechanism has had several names.  It is also known as Virtual Private Database (VPD), and row-level security. The benefits of row-level security include:

• Dynamic security – No need to maintain complex roles and grants.

• Multiple security - Place more than one policy on each object, as well as stack them upon other base policies.

• Web Apps – In Web apps, a single user accesses the database.  Hence, row-level security can easily differentiate between users.

• No back-doors - Users no longer bypass security policies embedded in applications, because the security policy is attached to the data.

As we may know, Oracle has three ways to control data access:

• GRANT privileges – Access is granted to specific database objects

• EXECUTE privileges – Grantee takes on authority of procedure owner

• POLICY privileges – Policy restricts access at runtime

When users access a table (or view) that has a security policy, the Oracle server calls the policy function, which returns a “predicate.” A predicate is a WHERE clause that qualifies a particular set of rows within the table.

Oracle8i then dynamically rewrites the query by appending the predicate to users' SQL statements.

While policies provide row-level access control, you can do the same thing without policies by creating custom views on the target tables:

create or replace view

   acctg_view

as

   select * from emp where job=‘ACCOUNTING’;

create or replace view

   clerk_view

as

   select * from emp where job=‘CLERK’;

grant acctg_view to acctg_role;

grant clerk_view to clerk_role;

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link: