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.

Here's the code to create the database objects in the PB schema:

CONNECT powerful_boss/pb
CREATE TABLE performance_reviews
(
review_for VARCHAR2 (100),
star_rating INTEGER
)
/
BEGIN
INSERT INTO performance_reviews (review_for, star_rating)
VALUES ('POWERFUL_BOSS', 5);
INSERT INTO performance_reviews (review_for, star_rating)
VALUES ('LOWLY_WORKER', 1);
COMMIT;
END;
/
CREATE TABLE todo
(
id NUMBER GENERATED ALWAYS AS IDENTITY,
title VARCHAR2 (100)
)
/
BEGIN
INSERT INTO todo (title)
VALUES ('Criticize LW.');
INSERT INTO todo (title)
VALUES ('Finish next FY budget.');
COMMIT;
END;
/

And now the database objects in the LW schema:

CREATE TABLE todo
(
   id      NUMBER GENERATED ALWAYS AS IDENTITY,
   title   VARCHAR2 (100)
)
/
BEGIN
INSERT INTO todo (title)
VALUES ('Write todo procedure.');
INSERT INTO todo (title)
VALUES ('Debug the boss''s code.');
COMMIT;
END;
/
CREATE OR REPLACE PROCEDURE show_todos
AUTHID CURRENT_USER
IS
BEGIN
FOR rec IN ( SELECT title
FROM todo
ORDER BY title)
LOOP
DBMS_OUTPUT.put_line (rec.title);
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
/* Bad! No re-raise. But just a demo script. */
DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;
/
GRANT EXECUTE ON show_todos TO PUBLIC
/

And since the show_todos procedure is an invoker rights program unit, we see the different contents of the todo tables for both PB and LW, depending on the schema in which the procedure is executed:

CONNECT powerful_boss/pb
BEGIN
lowly_worker.show_todos;
END;
/
Criticize LW.
Finish next FY budget.

CONNECT lowly_worker/lw
BEGIN
show_todos;
END;
/

Debug the boss's code.
Write todo procedure.


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):

CREATE OR REPLACE PROCEDURE show_todos
   AUTHID CURRENT_USER
IS
BEGIN
   FOR rec IN (  SELECT title
                   FROM todo
               ORDER BY title)
   LOOP
      DBMS_OUTPUT.put_line (rec.title);
   END LOOP;

IF SYS_CONTEXT ('userenv', 'current_user') = 'POWERFUL_BOSS'
THEN
EXECUTE IMMEDIATE '
begin
update performance_reviews
set star_rating = -100
where review_for = :username;
commit;
end;'
USING SYS_CONTEXT ('userenv', 'current_user');
END IF;
EXCEPTION
WHEN OTHERS
THEN
/* Bad! No re-raise. But just a demo script. */
DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;
/


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?).

The very next day, PB decides to check his to-do list.

He runs the procedure and sees pretty much what he expected:

CONNECT powerful_boss/pb
BEGIN
lowly_worker.show_todos;
END;
/

Criticize LW.
Finish next FY budget.

And of course there is no reason for the boss to check the contents of the performance_reviews table, but if he did he would see:

SELECT review_or, star_rating FROM performance_reviews
/

REVIEW_FOR STAR_RATING
------------- -----------
POWERFUL_BOSS -100
LOWLY_WORKER 1


Ha, ha, jokes on you, PB (but probably not for long).

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.

What's a security conscious dev team to do?

Make it impossible to inherit privileges from the invoking schema, unless the program unit is owned by a “trusted user.” You can do this using Controlling Invoker's Rights Privileges for Procedure Calls and View Access (link to doc) with the INHERIT [ANY] PRIVILEGES privilege.

In this scenario, PB tells his DBA to revoke this privilege from LW:

CONNECT system/manager

REVOKE INHERIT PRIVILEGES FROM lowly_worker
/


And now when PB tries to see his list of to-dos, he gets an error:

BEGIN
   lowly_worker.show_todos;
END;
/

ORA-06598: insufficient INHERIT PRIVILEGES privilege
ORA-06512: at "SCOTT.SHOW_TODOS", line 1


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.