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

 

Update to my earlier #quicktip on setting #plsql scope and warnings.

I noticed a error in my code for setting PLSCOPE_SETTINGS and PLSQL_WARNINGS. QuicTip Logon.sql What I did was get the instance name out of v$instance to figure out if I was connecting to a production environment or one of the lower environments. The problem with this is, not everyone is going to have permissions to select on sys.v_$instance. The better way to do this is to use sys_context to get the instance name. This way, you won’t have to chase down additional privileges from your DBA.

<code>

select sys_context('userenv','instance_name')
 into sInst
 from dual;

</code>

Here is the corrected code for my logon.sql

<code>

DECLARE
 sInst varchar2(1);
 BEGIN
 -- rlockard: 2018/02/23 commented out getting instance name from v$instance.
 -- used the more apporiate sys_context('userenv','instance_name')

--select upper(SUBSTR(instance_name, 1,1))
 --INTO sInst
 --FROM SYS.V_$INSTANCE;

select sys_context('userenv','instance_name')
 into sInst
 from dual;

-- test to see if this is a production instance
 -- all production instances start with P so ...
 -- if it's not a production instance set up
 -- session properties approiate for dev / test / sandbox.
 IF sInst != 'P' THEN
 execute immediate 'ALTER SESSION SET PLSCOPE_SETTINGS=' || '''IDENTIFIERS:ALL''';
 execute immediate 'ALTER SESSION SET PLSQL_WARNINGS=' || '''ENABLE:ALL''';
 END IF;
 END;
 /

</code>

The Impossible Password and default accounts. Oracle #LockDown #QuickTip #Infosec

Let’s revisit a customer who was about to go through an IG Audit. There is one finding that always seems to come up. Default accounts with default passwords. I don’t care if the accounts are expired and locked, if the user still has the default password set, then it’s a finding. Now you can go through all the default accounts and change the password if you want. I’m personally fond of having a script that does the work for me.

This is the script I use to go through and change all the default passwords. Now, once I’m done with this, I go back on set any necessary default accounts such as DBSNMP to a known password. Why, I find it easier to lock all the doors then methodically move through and unlock only the doors that are necessary.

First we are going to need a function that returns an impossible password. Well, in all honesty, it’s not impossible to crack, (that is because given enough horsepower any password can be eventually cracked.) This function will do one thing, return a 30 character randomly generated password of any printable character. There are a couple characters you can’t put in a password, so when we encounter those, we are going to replace them with an integer between 0 – 9.

Next we are going to go through all the default accounts and change the password.

<CODE>

DECLARE
    -- get the list of users with default passwords.
    CURSOR users_with_defpwd_cur IS
        SELECT username
        FROM sys.dba_users_with_defpwd;
    stmt     VARCHAR2(2000);    -- the base sql statement
    passwd   VARCHAR2(32);      -- the impossible_password.

    FUNCTION impossible_password RETURN VARCHAR2 AS
    -- will create a 30 character password wrapped in double quotes.
    passwd           VARCHAR2(32);        -- this is the password we are returning.
                                          -- we need 32 characters because we are
                                          -- wrapping the password in double quotes.
    p_invalid_char_3 VARCHAR2(1) := '"';  -- invalid password character 3 is '"'
    p_invalid_char_4 VARCHAR2(1) := ';';  -- invalid password character 4 is ';'
    BEGIN 
        passwd := SYS.dbms_random.STRING('p',30); -- get 30 printable characters. 
        -- find all the invalid characters and replace them with a random integer
        -- between 0 and 9.
        passwd := REPLACE(passwd, p_invalid_char_3, ceil(SYS.dbms_random.VALUE(-1,9)));
        passwd := REPLACE(passwd, p_invalid_char_4, ceil(SYS.dbms_random.VALUE(-1,9)));
        -- before we pass back the password, we need to put a double quote 
        -- on either side of it. This is because sometime we are going to 
        -- get a strange character that will cause oracle to cough up a hairball.
        passwd := '"' || passwd || '"';
        RETURN passwd;
    END;
-- main procedure.
BEGIN
    FOR users_with_defpwd_rec IN users_with_defpwd_cur LOOP
        passwd := impossible_password;
        stmt := 'alter user ' || users_with_defpwd_rec.username || ' identified by ' || passwd;
        EXECUTE IMMEDIATE stmt;
    END LOOP;
EXCEPTION WHEN OTHERS THEN
    sys.dbms_output.put_line(sqlerrm);
    sys.dbms_output.put_line(stmt);
END;
/
</CODE>

@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

2017 European Security Tour, #Moscow, #London, #Paris, #Helisnki

My 2017 speaking schedule is starting out with a bang.

My first stop will be in Moscow Russia where I am trying to arrange a short speaking engagement in conjunction with the Russia Oracle Users Group. Hopefully we can arrange something. I’ll be there on Tuesday January 24th and departing on Wednesday January 25th. If we can’t get a speaking engagement put together, feel free to drop me an email rob@oraclewizard.com and we can meet for dinner / drinks the evening of the 24th. Please put “European Security Tour Moscow” on the subject line so your email does not get buried under the other 500+ emails I get every day.

On Wednesday evening January 25th, I will be in London to meetup with the folks at UKOUG. If you like to join us that evening, contact Martin Widlake his twitter handle is @MDWidlake. We will be drinking beer and discussing many aspects of Oracle, including how to integrate beer into your Oracle Presentation, Martin is an expert on that.

On Thursday January 26th, I will be in Paris for the French Oracle Users Group meetup. I will be presenting a combination of Holistic Database Security and Secure PL/SQL coding practices. Once I have a URL and details on time / location I will update the post.

From Paris, I will be off to Helsinki Finland to present Holistic Database Security on 30 January with Technopolis and Oracle Users Group Finland. This is a free event. The url is

http://elink.technopolis.fi/m/1/68193884/02-t16337-0e7fb58a4cfc4c1e86c0f54a161c28be/0/1/1

From Helsinki I’m heading north to Rovaniemi and doing a private event for Finland National Bureau of Investigation. Then it’s back south to Helsinki for another engagement for a database security conference on February 2nd. I do not have the URL for the database security conference yet. As soon as I have it, I will update this post.

Now if that were not enough, once I get back to Baltimore, I will have about enough time to do a load of laundry then will be heading to Denver for RMOUG where I will be presenting Holistic Database Security and PL/SQL Secure Coding Practices.

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

Four things a developer can do now to improve their applications #infosec posture.

Lets face it, we have deadlines to meet and millions of lines of code in production. I get it, I’ve been a working PL/SQL developer off and on for over 20 years. If we get into the habit of using some of the security features in the language along with some practices, we can improve the security of you code. So, lets get into it.

1) Use packages. Steve Feuerstein http://www.oracle.com/technetwork/issue-archive/index-087690.html has been saying for years to move those functions and procedures to packages and there is good security reasons to do that. If you have a SQL Injection bug in your application, I can get to ALL_SOURCE and read your code and if I can get to your code, I can find other exploits.

So we can read the code in Functions and Procedures.

1 SQL> select text from all_source where owner = user and name = 'PARSE_STRING'; 2 procedure parse_string(p_string varchar2) AS 3 CURSOR col_cur IS 4 select distinct (instr(p_string||',',',',1,level)) loc 5 FROM dual 6 CONNECT BY LEVEL <= LENGTH(REPLACE(p_string,','))+1 7 ORDER BY 1; 8 l_col varchar2(65); 9 BEGIN 10 for col_rec in col_cur 11 loop 12 l_col := substr(p_string, col_rec.loc, instr(p_string, col_rec.loc+1)); 13 dbms_output.put_line(l_col || 'col pos ' || col_rec.loc); 14 end loop; 15 end; 16 17 14 rows selected. 18 19 SQL> create user u2 identified by MY##56SecurePassword; 20 21 User created. 22 23 SQL> grant create session to u2; 24 25 Grant succeeded. 26 27 SQL> grant execute on parse_string to u2; 28 29 Grant succeeded. 30 31 SQL> conn u2@orcl 32 Enter password: 33 Connected. 34 SQL> select text from all_source where owner = 'RLOCKARD' 35 2 and name = 'PARSE_STRING'; 36 procedure parse_string(p_string varchar2) AS 37 CURSOR col_cur IS 38 select distinct (instr(p_string||',',',',1,level)) loc 39 FROM dual 40 CONNECT BY LEVEL <= LENGTH(REPLACE(p_string,','))+1 41 ORDER BY 1; 42 l_col varchar2(65); 43 BEGIN 44 for col_rec in col_cur 45 loop 46 l_col := substr(p_string, col_rec.loc, instr(p_string, col_rec.loc+1)); 47 dbms_output.put_line(l_col || 'col pos ' || col_rec.loc); 48 end loop; 49 end; 50 51 14 rows selected. 52 53 SQL> 54

Now when we put this into a package, the only thing I can extract from it is the package specification.

First lets put it into a package.

1 SQL> sho user 2 USER is "RLOCKARD" 3 SQL> create or replace package rlockard.utility AS 4 procedure parse_string(p_string varchar2); 5 end; 6 / 7 8 Package created. 9 10 SQL> create or replace package body rlockard.utility AS 11 12 procedure parse_string(p_string varchar2) IS 13 CURSOR col_cur IS 14 select distinct (instr(p_string||',',',',1,level)) loc 15 FROM dual 16 CONNECT BY LEVEL <= LENGTH(REPLACE(p_string,','))+1 17 ORDER BY 1; 18 l_col varchar2(65); 19 BEGIN 20 for col_rec in col_cur 21 loop 22 l_col := substr(p_string, col_rec.loc, instr(p_string, col_rec.loc+1)); 23 sys.dbms_output.put_line(l_col || 'col pos ' || col_rec.loc); 24 end loop; 25 end; 26 end; 27 / 28 29 Package body created. 30 31 SQL>

Now, lets test to see what we can see.

1 SQL> grant execute on utility to u2; 2 3 Grant succeeded. 4 5 SQL> conn u2@orcl 6 Enter password: 7 Connected. 8 SQL> select text from all_source where owner = 'RLOCKARD' 9 and name = 'UTILITY'; 10 2 11 TEXT 12 -------------------------------------------------------------------------------- 13 package utility AS 14 procedure parse_string(p_string varchar2); 15 end; 16

As we can see, now you only get the package specification. This is really more that I would like to get out, but it’s much better than getting all your source code.

2) Split up your packages into smaller packages based on function. I normally split them up by UTILITY, SENSITIVE and NON SENSITIVE. If there are functions / procedures against sensitive tables those will go into the sensitive packages. You can further break down you sensitive packages. ie: CUSTOMER_API_PKG that would be your interface into your customers table.

Here is a good post by Steve Feuerstein on breaking packages down and keeping them compatible with existing code. http://www.oracle.com/technetwork/issue-archive/2015/15-jan/o15plsql-2398996.html

3) Limit the execution rights to a package and what a user can do with a package.

3a) We have been granting execute to packages for decades now. Then Oracle 11g gave us Invoker and Definer rights. When you create a package and don’t specify invoker or definer rights, the package is created with definer rights as the default. That’s all well and good, but let’s think this through. If I execute a package with definer rights and that package updates the customers table, even thought I don’t have update on the customers table, the package will work.

1 SQL> create or replace package rlockard.cust_api AS 2 function update_customers_credit_limit(pID in number, pCredit in number) return number; 3 end; 4 / 5 Package created. 6 7 SQL> create or replace package body rlockard.cust_api AS 8 9 function update_customers_credit_limit(pID in number, pCredit in number) return number is 10 retVal number; 11 begin 12 update customers set credit = pCredit where id = pId; 13 return 1; 14 exception when no_data_found 15 then 16 retVal := helpdesk.utility.log_error(pPkg => $$PLSQL_UNIT, pLine => $$PLSQL_LINE, 17 pParm => 'pID = ' || to_char(iID) || 18 ' pAmount= ' || to_char(pCredit), 19 pErr => sqlcode); 20 return retVal * -1; -- we are flipping the sign to indacate it's an error to caller. 21 end; 22 end; 23 / 24 Package body created. 25 SQL> 26

I am going to grant execute to the user U2 that we create earlier and test this.

1 SQL> grant execute on rlockard.cust_api to u2; 2 3 Grant succeeded. 4 5 SQL> conn u2@orcl 6 Enter password: 7 Connected. 8 SQL> declare 9 2 x number; 10 3 begin 11 4 x:=rlockard.cust_api.update_customers_credit_limit(pId => 1770, pCredit => 1000000); 12 5 end; 13 6 / 14 15 PL/SQL procedure successfully completed. 16 17 SQL> select credit from rlockard.customers where id = 1770; 18 select credit from rlockard.customers where id = 1770 19 * 20 ERROR at line 1: 21 ORA-00942: table or view does not exist 22 23 24 SQL> conn rlockard@orcl 25 Enter password: 26 Connected. 27 SQL> select credit from rlockard.customers where id = 1770; 28 29 CREDIT 30 ---------- 31 1000000 32 33 SQL> 34

Did you expect that to happen?  How are we going to tighten this down. We are going to set the package to use invokers rights. By adding AUTHID CURRENT_USER to the package specification, the package executes with U2’s rights. U2 does not have any rights on the customers table, the package fails with ORA-00942: table or view does not exists.

1 SQL> create or replace package rlockard.cust_api 2 AUTHID CURRENT_USER 3 AS 4 function update_customers_credit_limit(pID in number, pCredit in number) return number; 5 end; 6 / 7 2 3 4 5 6 8 Package created. 9 10 11 SQL> conn u2@orcl 12 Enter password: 13 Connected. 14 SQL> declare 15 x number; 16 begin 17 x := rlockard.cust_api.update_customers_credit_limit(pId => 1770, pCredit => 2500000); 18 exception when others then 19 sys.dbms_output.put_line(sqlerrm); 20 end; 21 / 22 ORA-00942: table or view does not exist 23 24 SQL> conn rlockard@orcl 25 Enter password: 26 Connected. 27 SQL> select credit from customers where id = 1770; 28 29 CREDIT 30 ---------- 31 1000000 32 SQL> 33

3b) In Oracle 12c we were given the ability to grant roles to packages. (procedures and functions too, but you should be using packages) Now, when we have sensitive tables in another schema, we can create a role that a package needs and grant that role to a package.

1 CREATE ROLE update_customers; 2 3 grant update_customers to rlockard; 4 5 GRANT SELECT 6 ON customers 7 TO update_customers; 8 9 GRANT update_customers TO PACKAGE cust_api; 10 11 declare 12 x number; 13 begin 14 x := rlockard.cust_api.update_customers_credit_limit(pId => 1770, pCredit => 2500000); 15 exception when others then 16 sys.dbms_output.put_line(sqlerrm); 17 end; 18 /

3c) Oracle 12c also gave us the accessible by clause. This creates a white list of the packages that can call a package. This way you are narrowing down the ways a package can get called, creating a trusted path to your secure data. So here the public package can call the private package, but if anything else tries to call it a PLS-00904 error will be raised.

accessable_by

1 SQL> create or replace package public_package AS 2 procedure update_customers(pId in number, 3 pColumn in varchar2, 4 pValue in varchar2); 5 end; 6 / 7 8 Package created. 9 10 SQL> create or replace package body public_package as 11 procedure update_customers(pId in number, 12 pColumn in varchar2, 13 pValue in varchar2) IS 14 x number; -- we know it's a function that returs a number. 15 begin -- this is simplistic to demo accessable_by 16 if pColumn = 'CREDIT' then 17 x := rlockard.cust_api.update_customers_credit_limit(pId => pId, pCredit => pValue); 18 end if; 19 end; 20 end; 21 / 22 23 Package body created. 24 25 SQL> create or replace package rlockard.cust_api 26 accessible by (public_package) AS 27 function update_customers_credit_limit(pID in number, pCredit in number) return number; 28 end; 29 / 30 31 Package created. 32 33 SQL> 34 35 SQL> declare 36 x number; 37 begin 38 x := rlockard.cust_api.update_customers_credit_limit(pId => 1770, pCredit => 2500000); 39 exception when others then 40 sys.dbms_output.put_line(sqlerrm); 41 end; 42 / 43 x := rlockard.cust_api.update_customers_credit_limit(pId => 1770, pCredit => 2500000); 44 * 45 ERROR at line 4: 46 ORA-06550: line 4, column 8: 47 PLS-00904: insufficient privilege to access object CUST_API 48 ORA-06550: line 4, column 3: 49 PL/SQL: Statement ignored 50 51 52 SQL> begin 53 public_package.update_customers(pId => 1771, pColumn => 'CREDIT', pValue => '200'); 54 end; 55 / 56 57 PL/SQL procedure successfully completed. 58 59 SQL> 60

But when we call it from the package in the accessible by clause, then it works fine. Again, we are limiting the paths to get to the sensitive information.

4a) We are getting down to the meat of what every shop should be doing. Reviewing code. You should be looking for dynamic code that is concatenating variables together. This is a painfully bad piece of code with a major SQL Injection bug.

1 create or replace PROCEDURE UserLogin 2 (p_email logins.email%type DEFAULT NULL, 3 p_password logins.password%type DEFAULT NULL) 4 AS 5 6 STMT CONSTANT VARCHAR2(4000) := 7 'SELECT email 8 FROM logins 9 WHERE email = ''' || p_email || 10 ''' AND password = ''' || p_password || ''''; 11 12 l_result logins.email%type; 13 BEGIN 14 15 dbms_output.put_line ('SQL STMT: ' || STMT); 16 17 EXECUTE IMMEDIATE STMT INTO l_result; 18 19 dbms_output.put_line ('Logon succeeded.'); 20 21 EXCEPTION WHEN OTHERS THEN 22 null; -- OH NO HE DID NOT 23 END UserLogin;

How would I fix this. Well, lets’ change the dynamic SQL and put in some bind variables. We can still do a lot more with this code, but this fixes the SQL Injection bug and a couple other issues.

1 create or replace function UserLogin2 2 (p_email logins.email%TYPE DEFAULT NULL, 3 p_password logins.PASSWORD%TYPE DEFAULT NULL) 4 RETURN NUMBER AS 5 kount number; -- a dumb variable to hold a count 6 BEGIN 7 8 SELECT count(*) 9 INTO kount 10 FROM logins 11 WHERE email = p_email 12 and password = p_password; 13 14 IF kount = 1 THEN 15 sys.dbms_output.put_line ('Logon succeeded.'); 16 RETURN kount; 17 ELSE 18 return -1; 19 end if; 20 21 END UserLogin2;

Now you will find I love code reviews. Frequently we learn a way to do something and because it works, we continue doing it. Heck, I loved cursor for loops until I learned better in a code review. Code reviews should be approached as learning opportunities. You are going to learn a lot more tricks reading other peoples code and you may catch something that will improve the security of your system.

So in review the steps you can do now to improve the security posture of your applications are: Control the rights to executing code. Put everything in packages. Split up your packages. Do code reviews.