Skip to main content

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:

CREATE OR REPLACE PROCEDURE create_table (
   table_name_in IN VARCHAR2)
   AUTHID DEFINER
IS
BEGIN
   EXECUTE IMMEDIATE
      'CREATE TABLE ' || table_name_in || '(n NUMBER)';
END;

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

CONNECT HR/*****

BEGIN
   create_table ('my_table');
END;
/

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
/

ALTER USER hr DEFAULT ROLE ALL EXCEPT create_table_role
/

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:

BEGIN
   create_table ('my_table');
END;
/

PL/SQL procedure successfully completed.

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

CREATE TABLE my_table2 (n NUMBER)
/

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)
   AUTHID CURRENT_USER
AS
BEGIN
   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)
   LOOP
      DBMS_OUTPUT.put_line (rec.last_name || ' - ' || rec.email);
   END LOOP;
END;
/

I'll let everyone execute the procedure:

GRANT EXECUTE ON show_employees TO PUBLIC
/

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:

BEGIN
   hr.show_employees (10);
END:
/

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
/

BEGIN
   hr.show_employees (10);
END:
/

Employees in Department 10
Whalen – JWHALEN@MY_COMPANY.COM

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.

Comments

  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,
    Iudith

    ReplyDelete
  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.

    ReplyDelete
    Replies
    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/

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

    http://dbpilot.net/2018/01/23/getting-all-child-objects-within-an-object/

    ...

    ReplyDelete

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