Thursday, August 17, 2017

Referencing package-level variables inside the package body

I received this question yesterday:
Is there a shortcut for referencing package variables in the package body? In Java, as an example, you can use the "this" keyword as a reference to the current object. This came about as I needed to create a copy of a package for debug purposes and realized I had to rename all the references to the package name within the package body.
Before I answer, let's look at an example of what Tony is talking about. I create a package specification and then a body with package-level variable (declared outside any subprogram of the package):

CREATE OR REPLACE PACKAGE pkg
   AUTHID DEFINER
IS
   PROCEDURE proc (n_in IN NUMBER);
END;
/

CREATE OR REPLACE PACKAGE BODY pkg
IS
   smallest   NUMBER;

   PROCEDURE proc (n_in IN NUMBER)
   IS
   BEGIN
      IF n_in < pkg.smallest
      THEN
         DBMS_OUTPUT.put_line ('too small');
      END IF;
   END;
END;
/

Notice the line in blue and bold. I reference the package level variable, qualified with the package name (the approach that Tony has taken).

But then when Tony changes the name of the package, so he also has to change the dot-qualified references within the package.

Can you use "this" syntax, as is available in Java? No. You will see this error when trying to compile the package body:

PLS-00201: identifier 'THIS.SMALLEST' must be declared

"this"-style syntax is used only with object types in Oracle Database (our object-oriented extensions to the relational model), in which case you use "SELF" to reference the current object type instance.

So what should Tony do? Here are his (and mine, and your) options:
  • Continue to dot qualify with the package name, and do a (careful, very careful) global search and replace of the old package name to new. Like I say: CAREFUL.
  • Remove the package name. You don't need it.
That's right. You don't need to qualify the reference to this variable. In other words, this version of he package body will compile just fine:

CREATE OR REPLACE PACKAGE BODY pkg
IS
   smallest   NUMBER;

   PROCEDURE proc (n_in IN NUMBER)
   IS
   BEGIN
      IF n_in < smallest
      THEN
         DBMS_OUTPUT.put_line ('too small');
      END IF;
   END;
END;
/

The compiler will try to resolve the reference to smallest inside proc. When that doesn't work, it will check the "next level up" in scope: the package body. Ah ha! There it is. And the body compiles.

Of course, this will not work as desired if you declare a variable or constant with the same name inside proc. The following version will also compile, but that reference to smaller inside proc will not refer to the package level variable.

CREATE OR REPLACE PACKAGE BODY pkg
IS
   smallest   NUMBER;

   PROCEDURE proc (n_in IN NUMBER)
   IS
      smallest NUMBER;
   BEGIN
      IF n_in < smallest
      THEN
         DBMS_OUTPUT.put_line ('too small');
      END IF;
   END;
END;
/

In this case, you must qualify the variable with the scope you mean. You could qualify with the subprogram (procedure) or package name, by the way:

CREATE OR REPLACE PACKAGE BODY pkg
IS
   smallest   NUMBER;

   PROCEDURE proc (n_in IN NUMBER)
   IS
      smallest NUMBER;
   BEGIN
      IF n_in < proc.smallest -- references procedure's smallest
      THEN

      IF n_in < pkg.smallest -- references package level smallest
      THEN
         DBMS_OUTPUT.put_line ('too small');
      END IF;
   END;
END;
/

Of course, best of all is to avoid declaring variables with the same names at different scopes. That can be confusing - and then requires the use of dot-qualification. Another approach, frequently used, is to use different naming conventions for variables of different scope. For example, I commonly use "g_" to indicate a global variable in a package, and "l_" for local variables.

So my version of the package body would look like:

CREATE OR REPLACE PACKAGE BODY pkg
IS
   g_smallest   NUMBER;

   PROCEDURE proc (n_in IN NUMBER)
   IS
      l_smallest NUMBER;
   BEGIN
      IF n_in < l_smallest -- references procedure's smallest
      THEN

      IF n_in < g_smallest -- references package level smallest
      THEN
         DBMS_OUTPUT.put_line ('too small');
      END IF;
   END;
END;
/

The most important thing is to be consistent in your approach so others (including the Future You) can more easily understand your code.

A final comment regarding dot-qualifying variable names: when you reference PL/SQL variables and constants inside SQL statements in your PL/SQL blocks, you should always qualify them with their scope names. Example:

CREATE OR REPLACE PACKAGE BODY pkg
IS
   g_smallest   NUMBER;

   PROCEDURE proc (n_in IN NUMBER)
   IS
      l_salary NUMBER;
   BEGIN
      SELECT salary INTO l_salary
        FROM employees e
       WHERE e.employee_id = proc.n_in;
   END;
END;
/

By doing this, you give more information to the compiler that it can use to reduce the times when a program unit needs to be invalidated and recompiled. In the above package body, if I did not qualify my reference to n_in inside the SELECT, adding a column named "n_in" will cause the package body to be set to INVALID.

And upon recompilation, well, that reference to "n_in" will now be to the column and not your variable. Not good. By fully qualifying the reference, however, the compiler knows (via fine-grained dependency management, added in 11.1) that there can be no confusion, and adding a column to the employees table could not possibly disrupt the current behavior of the package body.

Thursday, August 10, 2017

No subqueries allowed in materialized view? No problem!

Have you ever run into the following error when trying to create a materialized view?

ORA-22818: subquery expressions not allowed here

Yes, it is true: you cannot have a scalar subquery in the SQL statement used to create your materialized view. Here's an example of what won't work (note: I am not claiming this query makes any sense):

CREATE MATERIALIZED VIEW hr_demo_mv
AS
     SELECT employee_id,
            (SELECT MAX (hire_date)
               FROM employees ce) maxhd
       FROM employees t
/
ORA-22818: subquery expressions not allowed here
22818. 00000 -  "subquery expressions not allowed here"
*Cause:    An attempt was made to use a subquery expression where these
           are not supported.
*Action:   Rewrite the statement without the subquery expression.

Rewrite my query without the subquery expression? But I just spent an hour putting it all together. Works great. Gives me exactly the results I want and need. Rewrite it? ARGH.

Calm yourself. While it is true that you will need to "rewrite the statement" that you provide in your CREATE MATERIALIZED VIEW statement, you will not have to abandon your subqueries and all your hard work.

All you have to do is create a view with the subqueries, and then create your materialized view based on the view:

CREATE VIEW hr_demo_v
AS
     SELECT employee_id,
            (SELECT MAX (hire_date)
               FROM employees ce) maxhd
       FROM employees t
/

View HR_DEMO_V created.

CREATE MATERIALIZED VIEW hr_demo_mv
AS
     SELECT * FROM hr_demo_v
/

Materialized view HR_DEMO_MV created.

I recommend this approach (the materialized view is "nothing more" than a select from a view), even if your materialized view query does not contain a subquery or anything else that would preclude the materialized view from being created.

By taking this approach, you can change the contents of the materialized view with the next refresh by doing nothing more than changing the query (instead of dropping and re-creating the materialized view).

Tuesday, August 8, 2017

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