Grab a cup of coffee or a cup of tea. This is not a short post; There is a lot to explain, and many point are repeated. You need to understand all the in’s and out’s of CBAC. However; once you have an understanding, CBAC really quite easy to impalement.

A lot about Code Based Access Control is not intuitive. In fact a lot of people I talk to are confused about what CBAC is and what CBAC is not. The basic premise of CBAC is that a subprogram of a package can only execute only using the privileges that have been granted to the package through a role.

NOTE: What I’m explaining is a multi-schema model. I’m intentionally keeping the model simple to show just the CBAC aspect of a secure architecture. This model is using three schemas, hr that holds data and hr_api that is going to hold packages that will access the data, and hr_decls that holds common types that are used across schemas. Also note, this is not the full secure architecture. There are a number of elements of my secure architecture I am leaving out to focus on CBAC.

Before we go too far, we need to understand some subtleties about roles. In order to grant a role to a package, the role must first be granted to the owner of the package with either admin option, delegate option, or you must connect as sys to grant the role to the package and the owner of the package must have created the role. Yea’ that’s a bit to digest, so let’s examine the three different types of role grants that must be made.

Three different ways to grant a role to a package.

#1 --As a user with create role privileges and the ability to grant 
   -- the required privileges to the role. ie: DBA
conn rob_dba@demo
create role <role>;
grant <privilege> on <object owner>.<object name> to <role>;
grant <role> to <package owner> with admin option;
conn <package owner>@<instance>
grant <role> to package <package name>;

#1  GRANTING THE ROLE TO THE PACKAGE OWNER WITH ADMIN OPTION: Granting the package owner the role with admin option works, but the package owner can grant the role to other schemas. Using a user with create role privilege and the ability to grant the required privileges on the underlying objects (I normally use my dba account rob_dba). This is an unnecessary security risk. Like I said, the package owner can now grant the role to other schemas, thereby making the trusted path weaker. I’m sure there are use cases for granting the package owner the role with admin option; however that is a corner case and should be justified, not the norm and quite frankly, I can not think of a use case that would justify using this method.

#2 -- as package owner, create the role.the package owner must have 
   -- create role privileges.
conn <package owner>@<instance>
create role <role>;
conn sys@<instance> as sysdba
grant <role> to package <package owner>.<package name>;

#2 USING SYS TO GRANT THE ROLE TO A PACKAGE: The package owner must have the create role privilege and the package owner must have created the role. In this method, we are creating the role as the package owner and connecting as sys to grant the role to the package. I hope there is no need to explain what a bad idea it is to use the sys account. There is a huge security issue, doing work as sys.

#3 --As a user with create role privileges and the ability to grant 
   -- the required privileges to the role. ie: DBA
conn rob_dba@demo
create role <role>;
grant <privilege> on <object owner>.<object name> to <role>;
grant <role> to <package owner> with delegate option;
conn <package owner>@<instance>
grant <role> to package <package name>;

#3 GRANTING THE ROLE TO THE PACKAGE OWNER WITH DELEGATE OPTION: This is the preferred method to grant a role to a package. It appears the same as #1; however we are granting the package owner the role with delegate option. Using this method, we can grant the package the required role, but the package owner can not grant the role outside of it’s schema. This is the least amount of privileges needed to execute the task.

Remember, we want to operate with the least amount of privilege and still be able to do our job.

The hr_decls schema:

My hr_decls schema is used to define common types that will be referenced between schemas. This is not required for using CBAC, however it makes referencing common types between schemas much easier and makes maintenance simpler. Once this package is created, execute is granted to a role (exec_emp_decl_role) and that role is granted to any users that need to use it and not compile code against it. Mainly this grant is for testing purposes. Note: PL/SQL grant execute through a role or grant execute directly? If you are executing a pl/sql package using an anonymous block, you can pick up the privilege through a role. If you are compiling a pl/sql package that references a pl/sql package in a different schema, you must grant execute on the package directly.

conn rob_dba@demo
create role exec_emp_decl_role;
conn hr_api@demo
-- create the decls package. This package is used to create types 
-- that are used between schemas.
create or replace package hr_decls.emp_decl
authid current_user as
  cursor emp_cur is
  select *
  from hr.employees;

  subtype st_emp is emp_cur%rowtype;
  type tt_emp is table of st_emp index by pls_integer;
end emp_decl;
/
-- usr1 (our test user) required this role to reference the package
-- through an anonymous block 
grant execute on exec_emp_decl_role to usr1;
-- hr_api requires a direct grant on the package hr_decls.emp_decl
grant execute on hr_decls.emp_decl to hr_api;

Your privilege on/off switch starts with authid current_user.

create package hr_api.emp_select_pkg
authid current_user as

Hey, we are granting a role to the package, why do we need to grant privileges on the underlying objects directly to the package owner? In order to compile your package, you need to grant privileges on the underlying object to the executing schema.

If you use authid definer (that is the default authid. So if you don’t specify current_user or definer, the package is created with definer’s rights.), the package will pick up the privileges from the schema it resides in. If we grant a lot of privileges to the schema the code resides in, or heaven forbid, the code resides in the schema with the data, and there is a flaw in your code; the hacker owns your database.

By using authid current_user, the package inherits the privileges of the user executing the code. If the user does not have any privileges on the underlying data objects then subprogram in a package can only execute with the privileges granted to the package through a role.

Now let’s start setting this up from the beginning. Connecting as a dba, or another appropriate user that has the proper privileges, we need to create a role, grant privileges to that role, and grant that role to hr_api with admin option.

We are going to create the role hr_emp_sel_role. Then we are going to grant to the role select on hr.employees.

create role hr_emp_sel_role;
grant select on hr.employees to hr_emp_sel_role;

Once we have these roles and grants let’s grant it to hr_api with delegate option.

grant hr_emp_sel_role to hr_api with delegate option;

Here is a  niggle; you need to get your package to compile. The schema, in this case hr_api needs to be granted select on the underlying data. So, we are going to grant select on hr.employees to hr_api. I don’t really like granting privileges directly to a schema, but it’s needed for the code to compile. Using this grant, the code that selects against hr.employees can compile. But remember, the code we are writing will be authid current_user. We now have the underlying roles with the appropriate grants to start building our application.

grant select on hr.employees to hr_api;

There is one more role you need. This is the role that will be granted execute on the api package, that will inturn be granted to the user.

create role EXEC_EMP_SEL_API_ROLE;

Here is the rolls and all the grants we have done so far.

conn rob_dba@demo
create role hr_emp_sel_role;
grant select on hr.employees to hr_emp_sel_role;
grant hr_emp_sel_role to hr_api with delegate option;
grant select on hr.employees to hr_api;
create role EXEC_EMP_SEL_API_ROLE;
grant execute on hr_decls.emp_decl to hr_api;
grant EXEC_EMP_SEL_API_ROLE to usr1;

grant execute on hr_decls.emp_decl to exec_emp_decl_role;
grant exec_emp_decl_role to usr1;

Now we need to connect as hr_api and start building the application. In my environment, hr_api is granted create procedure through a password protected role. We are creating one function in the package, sel_hr_emp_phone that returns an employees information based on the phone number.

conn hr_api@demo
SET role hr_api_admin_role identified by My#Supper7Secret#Password2;
-- note authid current_user.
create or replace package hr_api.sel_emp_phone_api 
authid current_user as
  function sel_hr_emp_phone(p_phone hr.employees.phone_number%type)
  return hr_decls.emp_decl.tt_emp;
end sel_emp_phone_api;
/

We have created the package, now it’s time to grant the hr_emp_sel_role to the package.

grant hr_emp_sel_role to package 
sel_emp_phone_api;

We’ve flipped on the privilege the package needs. So sel_emp_phone_api can only execute with the privileges granted to it from the role, or privileges inherited from the user executing the package. (you must understand, the package can still inherit more privileges from the user.) Let’s finish off by filling in the package details.

create or replace package body hr_api.sel_emp_phone_api 
as

  function sel_hr_emp_phone(p_phone hr.employees.phone_number%type)
  return hr_decls.emp_decl.tt_emp is
  ltt_emp hr_decls.emp_decl.tt_emp;
  begin
    select *
    bulk collect into ltt_emp
    from hr.employees
    where phone_number = p_phone;
    return ltt_emp;
  exception when no_data_found then
    -- <fixme> insert error handler.
    raise_application_error(-20000,'');
  End sel_hr_emp_phone;
end sel_emp_phone_api;
/

We’ve got all the objects we need together, now we need two more grants to run this. We need to grant execute on hr_api.sel_emp_phone_api package to the exec_emp_sel_api_role and we need to grant the role to a user to execute the api. I have a test user usr1 that is used to test various security configurations. Here is the the setup that has already been done.This test user is granted create session, exec_emp_sel_api_role and exec_emp_decl_role.

conn rob_dba@demo
create user usr1 identified by AHotRedKotenok;
grant create session to usr1;
create role exec_emp_decl_role;
grant execute on hr_api.sel_emp_phone_api to exec_emp_sel_api_role;
-- so the user can execute the api through an anonymous block.
grant exec_emp_sel_api_role to usr1;
-- so the user can reference the hr_decls.emp_decl package from an 
-- anonymous block.
grant execute on hr_decls.emp_decl to exec_emp_decl_role;
grant exec_emp_decl_role to usr1;

Now we can test this out. Connect as usr1, and call the function. Let’s see if we get anything back.  

conn usr1@demo
declare
ltt_emp hr_decls.emp_decl.tt_emp;
begin
  ltt_emp := hr_api.sel_emp_phone_api.sel_hr_emp_phone(
              p_phone => '+1.800.555.1212');
end;
/