This blog posting will be about Privileges in the database - this is a pretty old topic, but Oracle12c introduces some new things - which are very interesting:

New system privilege INHERIT PRIVILEGES

Roles can be granted to PL/SQL objects

BEQUEATH CURRENT_USER clause for views

Code Based Access Control for PL/SQL objects

Today I will write about the new system privilege INHERIT PRIVILEGES; which has
a very special usecase, and for which you have to think twice, to get
the idea behind it.

INHERIT PRIVILEGES is important for PL/SQL code which runs in the
privilege context of the invoking user ( AUTHID CURRENT_USER). By default,
PL/SQL code runs with the privileges of its owner, regardsless who actually
called it. Such a procedure or function is called Definers Rights (DR).
An Invokers Rights (IR) procedure will run with the privilege of the
invoking user - such a procedure might be owned by SYS, but when SCOTT runs
it, it will execute with SCOTT's privileges. Of couse, SCOTT needs an EXECUTE
privilege on that procedure (Documentation). So far, so good - nothing new in 12c.

For an IR procedure, let's imagine the following: We have an application
developer who authors a package to be used by the DBA - this might be a procedure
for some administrative tasks. So, the procedure is owned by the user SCOTT (or APPUSER01, or whatever)
and is intended to being used by a DBA. The code looks as follows:

As you can see, that package is being created with the AUTHID CURRENT_USER clause,
so we have an IR package. Looking carefully at the code, we can see, that the
developer is not such a nice
guy, he added a GRANT DBA TO SCOTT command. Since the DBA runs this IR procedure,
the statement will execute in a DBA's privilege context and therefore without errors - so:
"SCOTT" becomes DBA. DBA's should know the PL/SQL procedures they are about to execute, shouldn't they?

In Oracle12c, execution of IR code is controlled by the INHERIT PRIVILEGES privilege.
INHERIT PRIVILEGES ON SYS allows IR code, owned by the grantee, to run in the
privilege context of SYS. Without it, execution fails with an error message. So, running the above procedure
in Oracle12c leads to the following error message.

Upon creating a new user, Oracle12c grants INHERIT PRIVILIGE ON {new user} to
PUBLIC automatically - this applies to all users except SYS. That means,
that IR procedures in Oracle12c will work as in 11g - with the exception of SYS - when
SYS calls another users' IR procedure, he will get the above error message. Therefore
we need to grant INHERIT PRIVILEGES ON SYS to the owner of the IR code (SCOTT).

And that is the tricky point: SCOTT needs to be granted a privilege,
in order to enable SYS to call PL/SQL. This can be confusing - but it's important
to get it right.

SQL> grant inherit privileges on user systo scott;
Grant succeeded.

It's worth to take one more look at this statement: It mentions two database users - the first (ON USER {user})
is the one, whose privilege context can be inherited by the
second (the grantee). After executing this, SYS can run the procedure without errors.

Let's assume, we have another power user, for which we also want to make sure, that
no other user can inherit its privilege context. When we create it (CREATE USER), Oracle
automatically grants INHERIT PRIVILEGES on that user to PUBLIC. We cannot prevent this, but
we can reverse it by revoking INHERIT PRIVILEGES on that user from PUBLIC.

But this approach needs to be done carefully - missing INHERIT PRIVILEGES grants would
lead to broken applications. So, first, we need to determine the IR proceduces and their
owners. These are the grantees for INHERIT PRIVILEGES.

Next, we must find out, which other users are about to execute the
IR procedures. These are the users for the ON USER clause in the
GRANT INHERIT PRIVILEGES statement. Having this information, we could
build the GRANT statements.

There is also an INHERIT ANY PRIVILEGES system privilege; granting
this to a database user allows its code to run within the privilege context
of any other database user - expressed otherwise: That users' IR procedures
can be executed by any other user.

Summarized: By default, the new system privilege INHERIT PRIVILEGES has only
limited effect to an existing (upgraded) Oracle instance. You will encounter the effects when
you are about to execute IR procedures, owned by a "normal" database user,
as SYS. Calling them as other users works as in previous releases.

The new concept is particularly useful for databases with many applications
which are not known to the DBA - in such an environment, a DBA cannot trust
PL/SQL code owned by a database user and therefore he cannot execute it. The
new INHERIT PRIVILEGES system allows the DBA to express "trust" to a specific
code owner - the database only allows calling IR code of "trusted" owners.

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle Corporation or Oracle Germany. The views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine.