Putting #CodeBasedAccessControl to work. #CBAC #Database #infosec #Oracle #TrustedPath

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;
/

 

Code Based Access Control, #securecoding #oracle #plsql #MultipleSchemas

You can download the full code from gethub.com CBAC Simple 

This example depends on the HR Demo schema being loaded. We will be building on this example over the next several months to present a full blown application that includes many of the secure coding features I have been talking about.

We need to do better. I keep walking into customers, where I keep hearing: “It’s harder to do.” “We have deadlines to make.” “We will address that sql injection flaw after we go production; because we are behind schedule.” Now guys, I get it! I have been down in the trenches for about thirty years coding, designing, and administering some sensitive information systems. (They are all sensitive in my view.) We really need to get on top of this, now.

First lets go back and see where we started this discussion. In part 1 and part 2, I introduced you to the better Architecture of using multiple schemas then we discussed definers rights and invokers rights.

Secure Coding Part 1

Secure Coding Part 2

Lets start: the first part of the code we are going to be doing some clean up. This way we can run the code again and again without getting error messages.

1 -- clean up before we start. 2 declare 3 procedure Drop_User(pUser in varchar2) is 4 User_Doesnt_Exist exception; 5 pragma Exception_Init(User_Doesnt_Exist, -01918); 6 begin 7 execute immediate 'drop user '||pUser||' cascade'; 8 exception when User_Doesnt_Exist then null; 9 end Drop_User; 10 begin 11 Drop_User('hr_api'); 12 Drop_User('usr1'); 13 end; 14 / 15 16 declare 17 Role_Doesnt_Exist exception; 18 pragma exception_init(Role_Doesnt_Exist, -01919); 19 begin 20 execute immediate 'drop role hr_emp_select_role'; 21 execute immediate 'drop role hr_backup_role'; 22 execute immediate 'drop role api_admin_role'; 23 exception when Role_Doesnt_Exist then null; 24 end; 25 -- done cleaning up. 26 27

Now lets get the two schemas we will be using in this example.

Line 3: we are creating a HR_API schema that will hold all the code to access data objects in the HR schema.

Line 5: we are creating the USR1 user that will make calls to code in the HR_API schema. (In a future post, I’ll be adding in HR_CODE schema that will hold the business logic. But for now, we are keeping it simple to demo CBAC.

1 -- this is going to be my api schema that will 2 -- have access to the hr objects. 3 create user hr_api identified by x; 4 -- this will be my executing user. 5 create user usr1 identified by x; 6

Now lets get to the roles we are going to need.

Line 3 we are creating the role HR_EMP_SELECT_ROLE. This role will have select on hr.employees. We will be granting this role to the api package that accesses the hr.employees table.

Line 6 we are creating the HR_BACKUP_ROLE. This role will be granted create any table, and then the role will be granted to the api package that does the backup. I really don’t like granting create any table, but for this purpose it is required to create a table in a different schema.

Line 8 is the API_ADMIN_ROLE. This role will have the create procedure privilege and will be assigned to the HR_API schema.

1 -- the hr_emp_select_role will have select in hr.ermployees. 2 create role hr_emp_select_role; 3 -- the hr_backup_role has create any table privilege. I really don't 4 -- like that, but that is what the role needs to create a table in 5 -- a diffrent schema. 6 create role hr_backup_role; 7 -- the api_admin_role has create procedure privilege. 8 create role api_admin_role; 9

.Now lets get into the grants we will need.

Lines 4 – 6 we are granting create session to usr1. At this time, this is the only privilege this user will need. Once we have the API package compiled, we will grant execute on that package to usr1

Lines 10 – 12 we are granting create procedure to the API_ADMIN_ROLE. This will be needed for HR_API to create a package in it’s schema.

Lines 16 – 18 we are granting select on hr.employees to the HR_EMP_SELECT_ROLE. This role will be granted to the HR_API schema with delegate option. I’ll discuss more about that later.

Lines 20 – 21 we are granting select on hr.employees to the HR_API schema. I really don’t like this, but it’s needed in order to compile the package.

Lines 26 – 28 we are granting create session to the HR_API schema. This will be needed to so HR_API can connect and create the required objects with only the permissions needed.

Line 32 we are granting create any table to the HR_BACKUP_ROLE. I really don’t like granting create any. This role will be granted to the API package that does some dynamic sql. I really need to think about tightening this down a bit. But for this iteration, we are going to use this to create a backup table in the hr schema.

Lines 36 – 39 here we are granting the HR_EMP_SELECT_ROLE and HR_BACKUP_ROLE to the HR_API schema with delegate option. This is important, you need to use either the DELEGATE OPTION or the ADMIN OPTION in order to be able to grant the roles to the HR_API packages.

Lines 41 – 43 and finally on our grants, we are granting the API_ADMIN_ROLE to the HR_API schema so HR_API can create the required packages.

1 -- the user usr1 will only need create session. after we've created 2 -- the package in the hr_api schema, we will grant execute on the 3 -- package to usr1. 4 grant 5 create session 6 to usr1; 7 -- 8 -- the api_admin_role will need the create procedure privilege. 9 -- this will be granted to hr_api. 10 grant 11 create procedure 12 to api_admin_role; 13 -- 14 -- this will give the hr_emp_select role the privilege 15 -- it needs to execute. 16 grant 17 select on hr.employees 18 to hr_emp_select_role; 19 -- 20 -- this will be needed to compile the code in the api schema. 21 grant 22 select 23 on hr.employees to hr_api; 24 -- 25 -- we are going to revoke create session after we are done. 26 grant 27 create session 28 to hr_api; 29 -- 30 -- the hr_bacup_role is used to demenstrate 31 -- using dynamic sql. 32 grant create any table to hr_backup_role; 33 -- 34 -- hr_api needs the roles with delegate option (or admin option) 35 -- to be able to grant the role to a package. 36 grant 37 hr_emp_select_role, 38 hr_backup_role 39 to hr_api with delegate option; 40 -- 41 grant 42 api_admin_role 43 to hr_api; 44 -- 45

The final step to setup our users and roles is to alter the user HR_API so there are no default roles. This is going to be done, because in the next installment we will be looking at creating password protected roles.

1 -- during normal operating, the hr_api schema does not 2 -- need any privileges. 3 alter user hr_api 4 default role none; 5

Now lets get into building our HR_API schema. It starts simple enough,

Line 1 connect to hr_api

Line 4 We set the roles we will need to create the api package, and the roles that will be granted to the api package.

1 -- connect as hr_api 2 conn hr_api/x@orcl 3 4 SET role hr_emp_select_role, api_admin_role, hr_backup_role; 5

Lines 1 – 8 we are creating a simple package that does two things. One get the phone number of a person based on first and last name. The second package is used to make a backup of the hr.employees table.

Line 12 Now that we have the package specification, we can grant the role HR_EMP_SELECT_ROLE and HR_BACKUP_ROLE to the hr_api.pgk_emp_select package.

1 create or replace package hr_api.pkg_emp_select 2 authid current_user AS 3 PROCEDURE pGetPhone(pFname IN VARCHAR2, 4 pLname IN VARCHAR2, 5 pPhone OUT VARCHAR2); 6 PROCEDURE pBackupEmp; 7 END; 8 / 9 10 -- we are going to grant the hr_emp_select_role 11 -- to pkg_emp_select 12 GRANT hr_emp_select_role, hr_backup_role to package pkg_emp_select; 13

Lines 3 – 24 don’t do anything very interesting, this procedure will get a phone number based on first and last name. My exception handler is really quite simple and does not do much. We’ll address that in a later post, because as you know, returning errors to the user is not the best practice from a infosec point of view.

Lines 30 – 39 is using dynamic sql to create a backup of the hr.employees table. We granted HR_BACKUP_ROLE to the package, this way, the package can create a table in the hr schema. I’m going to have to rethink this example, I don’t like it all that much because granting CREATE ANY TABLE is not really a good practice.

1 CREATE OR REPLACE PACKAGE BODY hr_api.pkg_emp_select AS 2 3 PROCEDURE pGetPhone(pFname IN VARCHAR2, 4 pLname IN VARCHAR2, 5 pPhone OUT VARCHAR2) IS 6 x INTEGER; 7 BEGIN 8 BEGIN 9 SELECT phone_number 10 INTO pPhone 11 FROM hr.employees 12 WHERE first_name = pFname 13 AND last_name = pLname; 14 EXCEPTION WHEN no_data_found then 15 pPhone := 'xxx'; 16 WHEN too_many_rows THEN 17 pPhone := 'yyy'; 18 WHEN others THEN 19 -- we can add in the help desk error handler later, again this 20 -- is just to demo granting roles to packages. 21 sys.dbms_output.put_line('pGetPhone raised an exception ' || sqlerrm); 22 END; 23 -- 24 END pGetPhone; 25 -- 26 -- this is a very simple procedure, create a backup table using execute 27 -- immediate. (dynamic sql) the only way this procedure is going to work 28 -- is if the package has create any table privilege to be able to 29 -- create a table in another schema. 30 PROCEDURE pBackupEmp IS 31 -- This is the date string 20170805 32 dt VARCHAR2(8); 33 BEGIN 34 dt := to_char(sysdate,'rrrrmmdd'); 35 execute immediate 'create table hr.employees' ||dt|| ' as select * from hr.employees'; 36 sys.dbms_output.put_line('create table success'); 37 exception when others then 38 sys.dbms_output.put_line('create table error ' || sqlerrm); 39 END pBackupEmp; 40 end pkg_emp_select; 41 / 42

Now that we have all that, lets’ test it.

1 conn usr1/x@orcl 2 set serveroutput on 3 declare 4 -- to hold the phone number, because we can't reference 5 -- hr.employees we can not use phone_number%type. 6 lPhone VARCHAR2(20); 7 begin 8 sys.dbms_output.put_line('testing cbac select on emp'); 9 hr_api.pkg_emp_select.pGetPhone(pFname => 'Jose Manuel', 10 pLname => 'Urman', 11 pPhone => lPhone); 12 sys.dbms_output.put_line(lPhone); 13 end; 14 / 15 16 -- if you run this twice on the same day you are going to get 17 -- a ORA-00955 error. this is because the package creates 18 -- a backup of the hr.employees table by appending the date 19 -- to the table name. So, if you are going to run it twice 20 -- you need to drop or rename the backup table. 21 begin 22 sys.dbms_output.put_line('testing dynamic sql'); 23 hr_api.pkg_emp_select.pBackupEmp; 24 sys.dbms_output.put_line('done'); 25 exception when others then 26 sys.dbms_output.put_line('daaaa ' || sqlerrm); 27 end; 28 / 29

@Oracle 12.2.0.1 Cool new features to improve security. Part 1 Enhanced Whitelists PL/SQL

In Oracle 12.1 the ACCESSIBLE BY clause was introduced to the PL/SQL language. This gives the developer the ability mark a package, procedure, function, or type with what was allowed to call it. 12.2 gives us fine grained control over what can the specific functions and procedures in a package.

Here is what 12.1 gave us. As you can see in this example the package getEmpInfo and EmpMaint can both call the package emp_api. I love it, now we have a way to limit what can call a piece of code. But wait, in 12.2 it gets even better, look at example for 12.2

1 CREATE OR REPLACE PACKAGE emp_api 2 ACCESSIBLE BY (getEmpInfo, EmpMaint) 3 AUTHID CURRENT_USER AS 4 FUNCTION fGetEmpPhone( pFname IN VARCHAR2, 5 pLname IN VARCHAR2) 6 RETURN VARCHAR2; 7 8 FUNCTION fGetEmpManager(pEmployeeId IN NUMBER) RETURN NUMBER; 9 10 FUNCTION fInsEmp(pFirstName IN VARCHAR2, 11 pLastName IN VARCHAR2, 12 pEmail IN VARCHAR2, 13 pPhoneNumber IN VARCHAR2, 14 pHireDate IN DATE, 15 pJobId IN NUMBER, 16 pSalary IN NUMBER, 17 pCommissionPct IN NUMBER, 18 pManagerId IN NUMBER, 19 pDempartmentId IN NUMBER) 20 RETURN BOOLEAN; 21 22 FUNCTION fDelEmp(pEmployeeId IN NUMBER) 23 RETURN BOOLEAN; 24 25 FUNCTION fUpdateEmp(pEmployeeId IN NUMBER, 26 pFirstName IN VARCHAR2, 27 pLastName IN VARCHAR2, 28 pEmail IN VARCHAR2, 29 pPhoneNumber IN VARCHAR2, 30 pHireDate IN DATE, 31 pJobId IN NUMBER, 32 pSalary IN NUMBER, 33 pCommissionPct IN NUMBER, 34 pManagerId IN NUMBER, 35 pDempartmentId IN NUMBER) 36 RETURN BOOLEAN; 37 38 END;

In 12.2 we now have fine grained control over what can call the individual functions and procedures in our package. In the emp_api package the package getEmpInfo can call the functions fGetEmpPhone and fGetEmpManager. The package EmpMaint can call the functions, fDelEmp, fInsEmp, and fUpdateEmp. Now we have fine grained control over what can call the functions and procedures in a specific package.

1 create or replace PACKAGE emp_api 2 AUTHID CURRENT_USER 3 AS 4 FUNCTION fGetEmpPhone(pFname IN VARCHAR2, 5 pLname IN VARCHAR2) 6 RETURN VARCHAR2 ACCESSIBLE BY (PACKAGE getEmpInfo); 7 8 FUNCTION fGetEmpManager(pEmployeeId IN NUMBER) 9 RETURN NUMBER ACCESSIBLE BY (PACKAGE getEmpInfo); 10 11 FUNCTION fInsEmp(pFirstName IN VARCHAR2, 12 pLastName IN VARCHAR2, 13 pEmail IN VARCHAR2, 14 pPhoneNumber IN VARCHAR2, 15 pHireDate IN DATE, 16 pJobId IN NUMBER, 17 pSalary IN NUMBER, 18 pCommissionPct IN NUMBER, 19 pManagerId IN NUMBER, 20 pDempartmentId IN NUMBER) 21 RETURN BOOLEAN ACCESSIBLE BY (PACKAGE EmpMaint); 22 23 FUNCTION fDelEmp(pEmployeeId IN NUMBER) 24 RETURN BOOLEAN ACCESSIBLE BY (PACKAGE EmpMaint); 25 26 FUNCTION fUpdateEmp(pEmployeeId IN NUMBER, 27 pFirstName IN VARCHAR2, 28 pLastName IN VARCHAR2, 29 pEmail IN VARCHAR2, 30 pPhoneNumber IN VARCHAR2, 31 pHireDate IN DATE, 32 pJobId IN NUMBER, 33 pSalary IN NUMBER, 34 pCommissionPct IN NUMBER, 35 pManagerId IN NUMBER, 36 pDempartmentId IN NUMBER) 37 RETURN BOOLEAN ACCESSIBLE BY (PACKAGE EmpMaint); 38 39 END;

Reference: http://docs.oracle.com/database/122/LNPLS/ACCESSIBLE-BY-clause.htm#LNPLS-GUID-9720619C-9862-4123-96E7-3E85F240FF36

PL/SQL Security Coding Practices. Introduction to a better architecture part 2

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.

PL/SQL Security Coding Practices. Introduction to a better architecture part 1.

I have been seeing this database architecture for over thirty years and it’s high time we stopped using it. Before I go too far, let me tell you I get it, you have pressure to get the application out the door and working in a defined timeframe. I still design and develop systems and the pressure to take shortcuts can be great. This short cut is a security killer.

So what have we been doing wrong for all these decades? Put all of the database objects and application code into once schema. This is just a bad idea all around. All it takes is one security bug and the bad guy owns your database. You might as well, put pretty gold wrapping paper with a bow around it and write the bad guy a gift card. If you come to any of my talks, I’ll be happy to demonstrate owning a database, including all your source code and all your data in just a couple of easy commands. But because this is not intended on being a lesson in hacking a database, I wont go into it here.

The power and security configuration of using an API (1)

There is an architecture, that will drastically improve the security of your database. By segmenting your application code from your data and use an API to access the data. Oracle 12c has several PL/SQL enhancements that will make your code much more secure. Oracle 12c PL/SQL now allows you to assign roles to packages, procedures and functions (But you should only be using packages). PL/SQL also now allows you to white list what can execute code. For years, we granted execute to a user, but now you can define what PL/SQL package can call another PL/SQL package using the accessible by clause. We are going to leverage these new features along with authid to define a trusted path that is controllable, fast and secure.

My next several post will move through this architecture, and explaining how to implement it effectively.

The power and security configuration of using an API

#ORACLE PL/SQL Secure Coding Practices #INFOSEC – Please tell me how your database system is designed @bgoug will get this presentation first

The more you tell me, the more ways I can find I can find to attack your system. All I need is one little sql injection bug and trust me, it is most likely there, you just don’t know it yet.

execute process_row(’EMPLOYEES where 1=2 union select owner, name, text from all_source order by owner, name, line –’);

Problem #1 for you, Opportunity #1 for bad guys. Guess what, all of your source code just leaked out from your database.

Problem #2 for you, Opportunity #2 for bad guys. Not putting your your code into packages. If you put your pl/sql into a procedure or a function, I can extract your code from all_source, learn about your system and tailor my attack.

What do you need to do? Put your code into packages. If the code is in a package, the only thing I can get is the package specification.

Problem #2 for you, Opportunity #2 for bad guys. Comments in your package specification. Hey, I’ve been <humor> smacking junior developers with a boat paddle </humor> for years about not commenting their code. The good part is they eventually get it and put in comments. The bad part is comments are being put into the package specification. Some comments are quite verbose and <humor> I really like that</humor>. You are telling the bad guys everything they need to know to exploit your system.

What you need to do? Move all of your comments to the top of the package body and use inline comments in the package body. Again, when I extract your source code, if it’s in a package then I can only get the package specification.

Here is a sample of one of my packages specifications. You are not going to derive too much information from this except maybe what calls it.

CREATE OR REPLACE PACKAGE TARGETAPP.REPORTTARGET

AUTHID current_user 

ACCESSIBLE BY (TARGETAPP.PROCESSTARGET)

AS

-- constant declarations

sVersion CONSTANT VARCHAR2(10) := '20161026.1';

FUNCTION MAIN(arg1 IN VARCHAR2, arg2 IN NUMBER) RETURN NUMBER;

FUNCTION WHAT_VERSION RETURN VARCHAR2;

END;

When I put on my penetration testing hat, all of your source code and comments make my job much easier. I learn exactly how your system is designed and coded and that lets me find all kinds of ways to exploit your system. <humor>Please don’t make my pen testing work too easy, customers will start thinking they are paying me too much money.</humor> And please for goodness sake, make the bad guys life harder; because if you do, they will likely move on to an easier target.

Turn off the #http #listener in #Oracle #STIG


Fatal error: Call to private method CodeColorer::performHighlightCodeBlock() from context '' in /data/1/0/37/13/37176/user/37892/htdocs/Oraclewizard/wp-content/plugins/codecolorer/codecolorer-core.php on line 70