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.

3 Answers
3

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.

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.
–
GaiusApr 20 '11 at 17:09

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.

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.