Can row-level security in Oracle be controlled by a user's session credentials or user context? Here's an example to illustrate what I'm looking for:

Our database contains a bunch of sensitive company information, including salaries. HR administrators should be able to see salaries in their search results, but facilities management staff shouldn't, even if they use the same search parameters. The catch is that our app only has a single database user, and all requests go through it, so the security can't be set up to simply check the database user's ID. We'd have to pass the info in as, say, a user context.

We're considering moving to Oracle Access Manager/WebLogic, if it makes a difference.

  • Apologies if this is missing information. I don't usually deal with the db side of things. I'll try to update/edit quickly in response to any questions, though.
    – Pops
    Apr 20, 2011 at 16:17

3 Answers 3


From the documentation:

Oracle database server provides a built-in application context namespace (USERENV) that provides access to predefined attributes. These attributes are session primitives, which is information that the database captures regarding a user session. Examples include the user name, the IP address from which the user connected, and a proxy user name if the user connection is proxied through a middle tier.

Predefined attributes are useful for access control. For example, a three-tier application creating lightweight user sessions through OCI or thick JDBC can access the PROXY_USER attribute in USERENV.

So yes, if your middle tier is handling the proxy username correctly.

  • Maybe I'm missing something, but doesn't USERENV depend on the database user? The page you linked mentions the db user under both PROXY_USER and SESSION_USER, and I need to restrict access based on external user information.
    – Pops
    Apr 20, 2011 at 17:06
  • The proxy user is set by the middle tier, it is the username that the user logs into that as.
    – Gaius
    Apr 20, 2011 at 17:09
  • 1
    The comment "...if your middle tier is handling ... correctly." is critical to this solution. Apr 20, 2011 at 17:25
  • Indeed, but the alternative is "no you can't do this"
    – Gaius
    Apr 20, 2011 at 17:33

Since the app knows the user, can you just have it do the search differently for HR Administrators?

If you need to prevent access on the database side you could call a package that enables a role, but only call it when the user is an HR Administrator.

An application role (secure application role) can be enabled only by applications using an authorized PL/SQL package. Application developers do not need to secure a role by embedding passwords inside applications. Instead, they can create an application role and specify which PL/SQL package is authorized to enable the role.

To create a role enabled by an authorized PL/SQL package, use the IDENTIFIED USING package_name clause in the CREATE ROLE SQL statement.

  • To answer your first question, yes. But we're looking into "do we even need to do this or can we make Oracle do it for us" at the moment.
    – Pops
    Apr 20, 2011 at 18:23
  • In that case Gaius is correct, SYS_CONTEXT is the only way to do it assuming the application has a different user or computer when used by an HR Administrator. Apr 20, 2011 at 19:26

The idea to depend only on attributes set in connection (=os username/proxy user/ip address...) might have really bad performance impact on you application. You may have fairly complicated structure to hold information about access privileges/roles/business info/... needed to decide which columns/rows hide and you certainly do not want to query it before each "business" call to database.

It is good to get such a application session information during user's login and then store it in you middle tier in some sort of application context and then pass this context into the DB before each "business" call.

To hold such a context info create your own application context in DB and implement PL/SQL package in DB for setting the context.

To implement "column hiding" based on context info, read Virtual Private Database documentation. You can access this application context info everywhere in DB using SYS_CONTEXT function.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.