Saturday, July 10, 2010

Row Level Security

Oracle Row level security (known as Virtual Private Databse- also known as fine grained access control), which comes as part of Oracle Enterprise Edition. The database can be set up the way that users authorized to access data not only at a table but at the each record level as well.
Transparently modifying requests for data to present a partial view of the tables to the users based on a set of defined criteria.

Oracle Label Security - optional add-on for providing easy to use interface for row-level security. no coding needed. Enables to enfore security, directly on tables, views and synonyms. Allows to define which rows users may have access to.

VPD was introduced in Oracle 8i Version 8.1.5 as a new solution to enfore granular access control
of data at server level.

Oracle 9i Expanded the features.
- Oracle Policy manager
- partitioned fine-grained access control
- global application context
- VPD support of synonyms

Oracle 10g makes the following three major enhancements in VPD

Column level Privacy
It Increases performance by limiting the number of queries that the database rewrites.
rewrite occur when the statement references relevant columns. this feature also leads to more
privacy.

Cutomization
with the introduction of four new types of policies, you can customize VPD to always
enfore the same predicate with a static policy or you can have VPD predicates that change
dynamically with a non-static policy.

Shared Policies
you can apply a single VPD policy to multiple objects, and therfore reduce administration
costs.

Why use VPD

Protect confidential and secret information. Control the delivery of the data to the right people.

VPD Components
- Application context
- PL/SQL Function
- Security Policies

Application Context
Holds Environment variables
- Application name
- USername
Gathers information using dbms_session.set_context
How VPD Works
The virtual private database enabled by associating one or more security policies with tables or views. Direct or indirect access to a table with an attached security policy causes the database to consult a pl/sql function that implements the policy. The policy function returns an access condition known as as predicate (a WHERE Clause), which the database appends to the user's sql statements, thus dynamically modifying the user's data access.
Can implement VPD by writing a stored procedure to append a SQL predicate to each sql statement that controls row level access for that statement.
VPD Policy can be implemented as a pl/sql function. VPD policy function that automatically adds WHERE clause to an incoming select query to limit the data access.

Thursday, July 1, 2010

Connect by Clause

  • The start with connect by clause can be used to select data that has a hierarchical relationship. Recurse condition can make use of the keyword perior.
  • Start with specifies the rows to be identified as a root
  • Siblings by preserves any ordering specified in the hierarchical query clause and then applies the order_by_clause to the siblings of the hierarchy