For this post, we are going to focus on definers rights and invokers rights. Most developers already know about these privilege modifiers, but sadly I rarely see these being used at customer sites.
Key to understanding how to secure your code is understanding definers and invokers along with inherit privileges, the accessible by clause along with a few other things. We are going to use these privilege modifiers to help implement a trusted path to your data. Here is the Oracle documentation on Definers Rights and Invokers Rights. Managing Security for Definer’s Rights and Invoker’s Rights.
Definers rights and invokers rights are pretty easy to understand. Lets start with a simple example. If a package, procedure or function (You should always use packages) is created using definers rights, then the code will execute with the privileges that are giving to the owner of the package. If the package is created with invokers rights then the package will execute with the privileges of the invoker (user who executed the code).
We are going to create an application user usr1 and an application code schema app. Once we have done that we will grant select on hr.employees to the app user.
1 SQL> create user usr1 identified by usr1; 2 User USR1 created. 3 SQL> grant create session to usr1; 4 Grant succeeded. 5 SQL> create user app identified by app; 6 User APP created. 7 SQL> grant select on hr.employees to app; 8 Grant succeeded.
Lets create a package with a function that does one simple thing; return the number of employees in the hr.employees table that have a salary that is greater than or equal to the passed parameter. Once we have this package we are going to grant execute on the package to the user usr1.
1 create or replace package app.emp_api 2 authid definer -- this is the default, but it's nice 3 -- to be specific 4 as 5 function fEmpCount(pAmt IN NUMBER) return number; 6 end; 7 / 8 9 create or replace package body app.emp_api as 10 function fEmpCount(pAmt IN NUMBER) return number is 11 x number; -- just a dumb variable to hold the count. 12 begin 13 select count(*) 14 into x 15 from hr.employees 16 where salary >= pAmt; 17 return x; 18 exception when others then 19 sys.dbms_output.put_line(sqlerrm); 20 return -1; 21 end; 22 end; 23 / 24 Package Body EMP_API compiled 25 SQL> grant execute on app.emp_api to usr1; 26 Grant succeeded. 27
Now, when usr1 executes the app.emp_api.fEmpCount it will work fine. We don’t need to grant permissions to usr1 to access the employees table, because the package executes with the permissions of the user app, that does have select on hr.employees. Using this scheme, we have effectively locked the user into using the application.
1 SQL> conn usr1/usr1@demo1 2 Connected. 3 SQL> set serveroutput on 4 SQL> declare 5 x number; 6 begin 7 x := app.emp_api.fEmpCount(pAmt => 100); 8 sys.dbms_output.put_line(to_char(x)); 9 end; 10 / 11 107 12 PL/SQL procedure successfully completed. 13
So that works just fine, now lets try it when we use invokers rights, we get table or view does not exists.
1 SQL> conn rlockard@demo1 2 Password? (**********?) *********** 3 Connected. 4 SQL> create or replace package app.emp_api 5 authid current_user -- all we need to do is change the 6 -- package spec 7 8 as 9 function fEmpCount(pAmt IN NUMBER) return number; 10 end; 11 / 12 Package EMP_API compiled 13 SQL> conn usr1/usr1@demo1 14 Connected. 15 SQL> set serveroutput on 16 SQL> declare 17 2 x number; 18 3 begin 19 4 x := app.emp_api.fEmpCount(pAmt => 100); 20 5 sys.dbms_output.put_line(to_char(x)); 21 6 end; 22 7 / 23 ORA-00942: table or view does not exist 24 -1 25
Because the code executed with the same rights as the invoker (usr1) we get the ora-00942: table or view does not exists. For this to work we need to grant select on hr.employees to usr1.
1 SQL> grant select on hr.employees to usr1; 2 3 Grant succeeded. 4 5 SQL> conn usr1/usr1@demo1 6 Connected. 7 SQL> set serveroutput on 8 SQL> declare 9 x number; 10 begin 11 x := app.emp_api.fEmpCount(pAmt => 100); 12 sys.dbms_output.put_line(to_char(x)); 13 end; 14 / 15 107 16 17 18 PL/SQL procedure successfully completed. 19
Now we have that down stay tuned for the next post on inherit privileges, because when usr1 has powerful privileges, when we use invokers rights the inherits those powerful privileges.
