What is the benefit of creating an Oracle context over just granting package execution?

Question

I have been programming oracle row-level security using DBMS_RLS for several years, but I have actually skipped creating contexts. I have seen the intermittent recommendations to CREATE CONTEXT and now I'm trying to pull the trigger.

But I'm not seeing much benefit to a context at session level. I attach a package, but I still have to grant execute on it to any users I want to be able to use it. What am I missing?

As far as I can tell, the only thing that creating the context with the package is that the package can issue DBMS_SESSION.SET_CONTEXT naming that context. After that a call to SYS_CONTEXT with that context will get the value set.

However, SYS_CONTEXT is itself kind of a hack. It only returns a VARCHAR2 rather than a typed value. Why not just add a function to the same package with the right data type?

Anything I put into the context with the context package can also be available through that same package.

Now, creating a global context seems like it might have benefits sharing across sessions, except that's not my goal here.

Answer 1

But I'm not seeing much benefit to a context at session level. I attach a package, but I still have to grant execute on it to any users I want to be able to use it. What am I missing?

Security

IMHO - Using SYS_CONTEXT() (instead of a Package) is a Security thing. The only way to set SYS_CONTEXT() values is through a Package. This package will most likely have access to data that the user does not.

This extra access can be done by:

• Creating the Package as a Definer's Rights package
• Creating the Package as an Invoker's Rights package and Grant it a Role (12.1+).

For extra security, the Context Package is not create in any of your DATA_SCHEMAs, CODE_SCHEMAs, or Parsing Schemas (eg the connected user or APEX Application Parsing Schema). As such, I recommend you use place it in a separate "Security Schema".