iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
Oracle Virtual Private Database (VPD) enforces row-level access by attaching a policy function to a table, view, or synonym. The function returns a SQL predicate, and Oracle applies that predicate when a covered statement accesses the object. This moves row filtering into the database instead of relying on every application query to add the right condition—but protection is limited to the objects and statement types the policy actually covers.
What Oracle VPD does
A VPD policy combines two parts: a function that generates a predicate and a policy that attaches the function to a database object. Oracle evaluates the policy when a user accesses that object and applies the resulting condition to the effective statement. For example, a policy might restrict a session to rows belonging to its tenant. The application does not have to repeat that row condition in every query.
This is database-side enforcement, not an unconditional guarantee against every way of accessing data. The policy must be attached to the relevant object, must cover the statement type in use, and must operate within the surrounding privilege and security design.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Clear out junk files and repair common Windows errors3Scan for outdated or missing drivers - takes under a minuteHow a VPD policy uses identity and context
A typical policy function is a PL/SQL function that returns a VARCHAR2 predicate. Oracle calls it with the schema and object name. The function can use secure application context to tailor the predicate to session attributes such as a user or tenant identifier. Oracle describes the policy function as a definer-rights function.
#1 Best Overall
The context is a trust boundary: the application or other trusted component must establish it securely. A user-controlled value must not be treated as an authenticated identity merely because it is present in a session. If the context can be set or changed by an untrusted caller, a predicate based on it may enforce the wrong access rule.
Oracle advises keeping the policy function pure: base its result on application context and its arguments, not package variables, and do not query the protected table from the function. Keep the function focused on returning the predicate rather than introducing hidden, mutable state.
Rank #2
Attaching and managing policies with DBMS_RLS
Oracle’s DBMS_RLS package manages VPD policies. DBMS_RLS.ADD_POLICY attaches a policy function to an object and sets policy options. Other package procedures can enable, alter, refresh, or drop policies; policy groups can organize multiple application policies.
Recommended Free Tools
- Choose the protected objects. Identify every table, view, or synonym through which the data can be accessed, and decide which objects need the same or different rules.
- Define the trusted session attributes. Decide which identity or tenant attributes the predicate needs and how a trusted part of the application establishes them.
- Write the policy function. Return a predicate for the object and context; avoid package variables and queries against the protected table.
- Attach and configure the policy. Use
DBMS_RLS.ADD_POLICYto associate the function with the object, then set the statement coverage and any column options deliberately. - Test coverage and rollout. Exercise each relevant statement type and access path under representative identities, then account for dependent-object invalidation and recompilation during deployment.
This is a configuration outline, not a drop-in script: exact package arguments and applicable behavior should be checked in the documentation for the Oracle Database release and service in use.
Which statements a policy covers
Oracle documents VPD policy coverage for SELECT, INSERT, UPDATE, DELETE, and INDEX. By default, the configured statement set covers SELECT, INSERT, UPDATE, and DELETE; it does not include INDEX. If statement_types is specified, a policy intended to cover MERGE needs all three of INSERT, UPDATE, and DELETE, or the setting can be omitted.
| Operation or setting | Coverage detail | What to verify |
|---|---|---|
| Default statement set | SELECT, INSERT, UPDATE, and DELETE |
Confirm these are the operations your applications use; do not assume the default includes index operations. |
INDEX |
Not included by default | Decide explicitly whether index operations need policy coverage. Oracle warns of risks from index maintenance without INDEX policy coverage. |
MERGE with explicit statement_types |
Requires INSERT, UPDATE, and DELETE in the setting |
Test the actual statement and configured policy rather than assuming a single MERGE entry provides coverage. |
Audit policy attachment and statement settings against real application behavior, including maintenance operations. VPD’s row filtering does not by itself establish what happens for every privileged account, exemption, or database access path; validate those rules against the target release and the overall security design.
Rank #4
Row filtering and column masking are different
Ordinary VPD row filtering restricts which rows a query returns or can affect. Column-level VPD offers a separate option: when a specified sensitive column is referenced, the default behavior restricts rows. With the ALL_ROWS option, Oracle instead returns the rows but presents protected values as NULL. Oracle documents this masking behavior as SELECT-only and limited to a simple Boolean condition.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →- Choose row filtering when the rule is about whether a row is accessible.
- Consider
ALL_ROWSmasking when users may see the row but should not see a protected column’s value. - Review application behavior that depends on the value, including NULL-sensitive calculations and code that treats NULL as an ordinary value.
Masking a column is not equivalent to excluding rows: the row remains in the result, and a NULL value can affect expressions and application logic.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Policy types and predicate caching
Oracle Database 19c documents five VPD policy types. They differ in when Oracle can reuse predicates and how often it runs the policy function. Choose based on how the predicate varies and the workload, rather than assuming one type is always faster.
| Policy type | General reuse behavior | Design question |
|---|---|---|
| Dynamic | The predicate is generated dynamically rather than reused as a static predicate. | Does the rule need to reflect changing session or application state at each relevant evaluation? |
| Static | A predicate can be reused as a static policy result. | Is the predicate stable enough for static reuse? |
| Shared static | A static policy result can be shared. | Can the same static result safely serve the relevant objects or policy uses? |
| Context-sensitive | Predicate reuse responds to application-context changes. | Which context changes must cause the predicate to be refreshed? |
| Shared context-sensitive | Context-sensitive behavior is combined with shared reuse. | Can the required context-sensitive results be shared safely for this design? |
Policy-function execution can consume significant resources, according to Oracle. The documentation does not establish a universal performance figure; measure the chosen configuration against the actual workload and verify that caching behavior remains correct when context changes.
Release, edition, and deployment considerations
The implementation details above draw primarily on Oracle Database 19c documentation. Its DBMS_RLS reference lists the package as available with Enterprise Edition only. Edition availability and licensing are version- and service-dependent, so verify the exact deployment and contract before making a purchase or architecture decision.
Oracle’s 19c Security Guide sets a maximum of 255 policies per object. It also warns that adding a policy can invalidate dependent objects and trigger recompilation, with possible performance effects. Plan policy changes as database changes: assess dependencies, rollout timing, and the effect of recompilation in the target environment.
Oracle Database 26 Security Guide describes a newer direction: “Oracle Deep Data Security extends and modernizes Oracle Virtual Private Database and Real Application Security, moving from earlier procedural PL/SQL and API-driven controls to declarative policies in SQL.” Oracle recommends Deep Data Security for identity propagation, database-enforced authorizations, and audit compliance. This is Oracle’s stated recommendation for Database 26; it does not establish that every VPD deployment must migrate or that the newer approach has identical features in every case.
Quick Recap
Practical review checklist
- List every protected object and confirm the policy is attached at the needed access points.
- Check statement coverage, particularly
INDEXand anyMERGEusing an explicit statement-type setting. - Trace how identity and tenant context are set, and ensure untrusted users cannot choose the values treated as authenticated.
- Decide whether each sensitive-column rule should filter rows or return rows with NULL-masked values.
- Choose a policy type that matches predicate variability, then test correctness and performance with representative context changes and workload.
- Confirm release, edition, licensing, policy-count, dependency, and recompilation implications for the target service.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
