For the last twenty years, I have managed to transform an obsession with PL/SQL into a paying job. How cool is that?

Tuesday, March 21, 2017

Tightening security in your PL/SQL code with 12c new features, part 1

Oracle Database 12c offers several enhancements to improve security in your PL/SQL program units. These features include:

Code-based access control: fine-tune access to database objects inside program units by granting roles to program units, rather than - or in addition to - roles granted to schemas.

Avoid privilege escalation: Use the INHERIT [ANY] PRIVILEGES privilege to make it impossible for a lower-privileged user to take advantage of a higher-privileged user via an invoker rights unit.

In part 1, I will explore the use of INHERIT [ANY] PRIVILEGES to clamp down on possible privilege escalation.

Which means, of course, that I should first give you an example of what privilege escalation is, how it can come about, and what sorts of damage it can do.

Suppose that there is a schema named POWERFUL_BOSS in the database instance, which is the boss's schema and has lots of privileges on many critical database objects, including the PERFORMANCE_REVIEWS table.

The instance also have a schema named LOWLY_WORKER, the owner of which works for POWERFUL_BOSS. I'll call them LW and PB for short.

PB has given LW a new task: create an invoker rights procedure to display a person's to-do list. In this fine company, each schema has its own TODO table, with the tasks for the person who owns the schema.

You'd think PB would congratulate LW on getting that procedure built so quickly, but no no - all LW ever hears are complaints.
PB doesn't like LW much, and the feeling is mutual. LW feels like PB is constantly giving her unjustifiably poor performance reviews. A month or two goes by. The show_todos procedure is used by everyone, constantly.

LW decides to take action. She modifies the todo procedure as follows (changes in bold and blue):

That's one mean performance review! Note that the update is performed via a dynamic PL/SQL block. As a result, the procedure compiles just fine, even though LW has no privileges on the performance_reviews table. In addition, the update will only be executed when the procedure is run by PB.

Okey dokey. The procedure is moved into production (that's right - they have very lax code review procedures in their group. How about you?).

Well, you get the idea, right? Once an invoker rights program unit has been put into place, it can (usually) be more easily and quietly modified. And by using dynamic SQL, one could "slip in" undesirable functionality that depends on privilege escalation - the fact that when another schema executes an invoker rights unit, that unit is executed with the privileges of the invoking schema, which could be considerably greater than those of the defining schema.

First, notice that even with "exception-swallowing" WHEN OTHERS clause, this exception is propagated out unhandled from the procedure. Oracle wants to make very sure you are aware of this possibly insecure situation, and take appropriate action.

In terms of action, well, obviously, if PB no longer trusts LW, he is also not going to have the LW schema owning common code. Any invoker rights code will have to be relocated to a trusted schema.

Note, however, that LW can still call her own procedure (for all the "good" it will do her). There is no inheritance of privileges going on in that scenario.

Here are some additional details on the INHERIT [ANY] PRIVILEGES feature, from the doc:

How the INHERIT [ANY] PRIVILEGES Privileges Control Privilege Access

The INHERIT PRIVILEGES and INHERIT ANY PRIVILEGES privileges regulate the privileges used when a user runs an invoker's rights procedure or queries a BEQUEATH CURRENT_USER view that references an invoker's rights procedure.

When a user runs an invoker's rights procedure, Oracle Database checks it to ensure that the procedure owner has either the INHERIT PRIVILEGES privilege on the invoking user, or if the owner has been granted the INHERIT ANY PRIVILEGES privilege. If the privilege check fails, then Oracle Database returns an ORA-06598: insufficient INHERIT PRIVILEGES privilege error.

The benefit of these two privileges is that they give invoking users control over who can access their privileges when they run an invoker's rights procedure or query a BEQUEATH CURRENT_USER view.

More to Come

In my next post on security-related enhancements in PL/SQL for Oracle Database 12c, I will explore code-based access control (granting roles to program units).

In the meantime, I hope you will agree that one lesson to take away from the above scenario is:

All modifications to code should be closely reviewed before applying them to your production application.

2 comments:

or, alternatively, if the system privilege INHERIT ANY PRIVILEGES were used, then

REVOKE INHERIT ANY PRIVILEGES FROM lowly_worker /

This feature is rather weird ... in the sense that normally the owner of an object is the one who decides what others can do with that object,and not the opposite.

Revoking the privilege from the procedure owner by the invoking user means that the invoking user will in fact prevent himself from running the procedure ... not only this procedure, but any other procedure owned by that same owner ...

This in fact means that, if you want to create really powerful invoker-rights routines, those have to be created always by a "very trusted" owner.

Regarding the exception ORA-06598, it is raised by the mere attempt to execute the procedure by user powerful_boss, and not by the procedure execution itself, so, if you want to handle that exception in the code shown above, this should be done in the anonymous block that called SHOW_TODOS.

And ... yes, all what you said about the boss above is true in real life, I do confirm it !

And, if code review will be done before deploying it to production, this will surely NOT be done by the boss :)