Can Oracle row-level security work with user session credentials?

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.

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.

StackExchange Database Administrators Q#2286, answer score: 3