Skip to main content

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 ANY PRIVILEGES FROM lowly_worker
/
Or you can be more selective and revoke privileges more specifically (but only if the specific privilege had previously been granted):
CONNECT system/manager

REVOKE INHERIT PRIVILEGES ON USER powerful_boss 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.

Comments

  1. Hello Steven,

    Just a short remark regarding the REVOKE statement.

    Shouldn't it look like the following:

    REVOKE INHERIT PRIVILEGES ON USER powerful_boss FROM lowly_worker
    /


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

    Cheers & Best Regards,
    Iudith

    ReplyDelete
    Replies
    1. Thanks, Iudith. You are correct. The revoke statement should have included ANY or used the FROM syntax. I will correct that. And your explanation about the exception is also spot on. Thanks!

      Delete

Post a Comment

Popular posts from this blog

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel