Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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 '11 at 16:17
add comment

3 Answers 3

up vote 2 down vote accepted

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.

share|improve this answer
    
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 '11 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 '11 at 17:09
    
The comment "...if your middle tier is handling ... correctly." is critical to this solution. –  Leigh Riffel Apr 20 '11 at 17:25
    
Indeed, but the alternative is "no you can't do this" –  Gaius Apr 20 '11 at 17:33
add comment

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.

share|improve this answer
    
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 '11 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. –  Leigh Riffel Apr 20 '11 at 19:26
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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