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

Oracle Database 12c offers several enhancements to improve security in your PL/SQL program units. These features include:
  • 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. I covered this topic here.
  • Code-based access control: fine-tune access to database objects inside program units by granting roles to program units (doc), rather than - or in addition to - roles granted to schemas. That's the topic for this post.
Note: Oracle Magazine also offers this content (both blog posts) in a single article here.

Securing your database – and properly restricting access to the data and data structures within your database – ranks at the very top of the "most important things to do" list when building applications.

The best way to avoid unintended access or actions is to apply the "least privilege" principle: give a user the smallest number of (and most narrowly defined) privileges on database objects and the data inside those objects.

Oracle Database has always offered a very robust security mechanism: you can only access objects you own or those to which you were granted access. Within a PL/SQL program unit, you can choose the definer rights model (a user executes your code with your privileges) or the invoker rights model (a user executes your code with their privileges).  But the granularity of this mechanism operates at the schema level, making it difficult to apply the "least privilege" principle.

With Oracle Database 12c, you can now restrict privileges as tightly as you would like, right down to the individual program unit, by granting roles to program units, and not just to schemas. I'll explore this feature for both definer rights and invoker rights program units.

First, with definer rights, suppose that the HR schema was initially granted just two privileges: CREATE SESSION and CREATE PROCEDURE. I could then compile the following procedure in HR:

   table_name_in IN VARCHAR2)
      'CREATE TABLE ' || table_name_in || '(n NUMBER)';

But when I try to create a table using the procedure, I see an error:


   create_table ('my_table');

ERROR at line 1: ORA-01031: insufficient privileges

Prior to Oracle Database 12c, the only way that HR could use this procedure would be to grant the CREATE TABLE procedure to the schema itself. But this means that any program unit defined in HR could then create a table, which the Chief Security Officer finds unacceptable.

With Oracle Database 12c, however, I can take a much more fine-grained approach, by granting privileges to the procedure itself, and not its owning schema.

Here’s how:

1. Create a role from a schema with the authority to do so, and grant it the CREATE TABLE privilege.

CREATE ROLE create_table_role

GRANT CREATE TABLE TO create_table_role

2. Grant the role to the procedure. This can be done as SYSDBA. It can also be done from the HR schema, if the role is granted to HR with the admin option. Here’s the grant as SYSDBA:

GRANT create_table_role TO PROCEDURE hr.create_table

To grant from HR, first execute this as SYSDBA:

GRANT create_table_role TO hr WITH ADMIN OPTION


Then execute the grant from HR:

GRANT create_table_role TO PROCEDURE create_table

And now I can execute the procedure and successfully create the table:

   create_table ('my_table');

PL/SQL procedure successfully completed.

But if I try to create the table directly, I see the same, earlier privileges error:


ERROR at line 1: ORA-01031: insufficient privileges

The only way to create a table from the HR schema is by calling this one procedure: a very targeted assignment of privileges. Now let's take a look at using code-based access control with an invoker rights module.

With invoker rights, the privileges of the invoking schema are used to determine what the module will be allowed to do. I need to give users the ability to display non-confidential information about employees: namely, they can see employee names and emails, but not salary information.

I can do this by creating a view on top of the EMPLOYEES table and only granting SELECT on the view. But I can also achieve this effect through code based access control, thereby avoiding the need to create a view.

Here's the invoker rights procedure for displaying appropriate employee information, owned by HR, which also owns the employees table.

CREATE OR REPLACE PROCEDURE show_employees (department_id_in IN INTEGER)
   DBMS_OUTPUT.put_line (
      'Employees in Department ' || department_id_in);

   FOR rec IN (SELECT e.last_name, e.email FROM hr.employees e
                WHERE e.department_id = department_id_in
                ORDER BY e.last_name)
      DBMS_OUTPUT.put_line (rec.last_name || ' - ' || rec.email);

I'll let everyone execute the procedure:


No other schemas have been granted SELECT on employees, so if, for example, a user connected to the SCOTT schema tries to execute this procedure, she will see an error:

   hr.show_employees (10);

ERROR at line 1:
ORA-00942: table or view does not exist

Prior to Oracle Database 12c, to get this to work, you would have to do one of the following:
  • Grant SELECT on this table to SCOTT, but that would give SCOTT access to confidential information.
  • Create a view on top of EMPLOYEES that does not include the confidential information, and then grant SELECT on that view to SCOTT.      
With Oracle Database 12c and higher, I can instead create a role that has the SELECT privilege on the EMPLOYEES table, and then assign the role to just that single procedure. Assuming HR has the CREATE ROLE privilege, here are the steps:

CREATE ROLE view_employees_role

GRANT SELECT ON employees TO view_employees_role

GRANT view_employees_role TO PROCEDURE show_employees

   hr.show_employees (10);

Employees in Department 10

Now users can access the employee information appropriate to them, but I have not provided any other opportunities to access the employees table. I have, in other words, kept the attack surface (the number of points through which an unauthorized user can try to get at the table) to a minimum.


  1. Hello Steven,

    For allowing a procedure owner to grant/revoke a role to a program unit in his schema,
    there exists a new option in 12c that allows a schema to do just this, as follows:

    GRANT create_table_role TO hr WITH DELEGATE OPTION

    This is a "weaker" privilege than granting the role with WITH ADMIN OPTION.

    So, the "security of the security management" itself
    was also tightened :)

    Thanks a lot & Best Regards,

  2. Does this only work if execute remains granted to public ?

    When I attempt to grant to other roles, the PLS-00201 identifier must be declare error is raised.

    Granting to public is discouraged at my place of employment.

    1. Sorry about the lengthy delay in responding. You do not need to grant to public. Check out Rob Lockard's extensive exploration into CBAC for more details. http://oraclewizard.com/Oraclewizard/2018/03/19/putting-codebasedaccesscontrol-to-work-cbac-database-infosec-oracle/

  3. Hi, guys!
    The following link provides the way to get all references to an Oracle object
    and how to fix ORA-00942 error




Post a Comment

Popular posts from this blog

Table Functions, Part 1: Introduction and Exploration

Recommendations for unit testing PL/SQL programs

The future of Oracle PL/SQL: some thoughts on Sten Vesterli's thoughts