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

 

PGA Memory Operation Events

I’ve been working on putting together some performance test for my secure coding talk coming up at Hotsos and encountered something I can not quite explain. This test case does a bulk select into a type and returns the type to the calling program. When I execute the code through the API, I get 1,100+- PGA Memory Operation Events. When I don’t use the API, and just use straight pl/sql I get 600+- PGA Memory Operation Events.

First we need to build two schemas and one test user. I’m keeping these pretty simple. HR_CODE is to hold business logic. HR_API is to hold the API for the HR schema. The user executes the code in the business logic schema. Here is the basic architecture. NOTE: this is not the full Secure Architecture implantation in this test case. Code Based Access Control and accessible by has not been implemented in this test case. 

create user usr1 identified by x;
create user hr_code identified by x;
create user hr_api identified by x;
--
grant create session to usr1;
grant alter session to usr1;
-- create the required roles.
create role hr_big_table_all;
create role hr_big_table_sel;
-- create a test table in the hr schema
create sequence hr.big_table_seq;
create table hr.big_table (id number not null,
 c number not null,
 d number not null);
-- setup permissions.
grant select on hr.big_table to hr_big_table_sel, hr_api;
grant select, update, insert, delete on hr.big_table to hr_big_table_all;
grant hr_big_table_all to 
grant hr_big_table_sel to usr1;
-- populate the table with 1M rows
insert into hr.big_table (
     select hr.big_table_seq.nextval,
     ceil(sys.dbms_random.value(0,10)),
     sys.dbms_random.value(-1,5000)
     from dual
connect by rownum <= 1000000);
--
commit;
-- add in the constraints and indexes
alter table hr.big_table add constraint
big_table_pk primary key (id);
--
create index hr.big_table_idx1 on hr.big_table(c);
-- analyze the schema
exec sys.dbms_stats.gather_schema_stats(ownname => 'HR');
-- create the decls package. this is used so all schemas can 
-- reference common definitions.
create or replace package hr_decls.big_tab_decl as
 cursor big_tab_cur is
 select id,
         c,
         d
 from hr.big_table;
 subtype st_big_tab is big_tab_cur%rowtype;
 type tt_big_tab is table of st_big_tab index by pls_integer;
end;
/
-- grant execute on the decls package to expose definations
grant execute on hr_decls.big_tab_decl to hr_api, hr_code, usr1;
--
-- create the api packages
create or replace package hr_api.big_tab_api 
authid current_user as
 function sel_hr_big_tab_range(p_low number, p_high number)
 return hr_decls.big_tab_decl.tt_big_tab;
end big_tab_api;
/
-- grant execute on the api to the code schema
grant execute on hr_api.big_tab_api to hr_code;
--create the package body
--
create or replace package body hr_api.big_tab_api 
as

 function sel_hr_big_tab_range(p_low number, p_high number)
 return hr_decls.big_tab_decl.tt_big_tab is
 ltt_big_tab hr_decls.big_tab_decl.tt_big_tab;
 begin
   select id,
          c,
          d
   bulk collect into ltt_big_tab
   from hr.big_table
   where d between p_low and p_high;
 
   return ltt_big_tab;
 exception when no_data_found then
   -- <fixme> insert error handler
   raise_application_error(20000,'sel_hr_big_tab_range');
 end sel_hr_big_tab_range;
end big_tab_api;
/
-- create the code package. this holds the business logic 
-- to create a shell around the API.
-- you will notice, no business logic has been added in 
-- at this point. 
create or replace package hr_code.sel_perf_test
authid definer is
 function sel_hr_big_tab_range(p_low number, p_high number)
 return hr_decls.big_tab_decl.tt_big_tab;
end sel_perf_test;
/
create or replace package body hr_code.sel_perf_test is
 function sel_hr_big_tab_range(p_low number, p_high number) 
 return hr_decls.big_tab_decl.tt_big_tab is
 ltt_big_tab hr_decls.big_tab_decl.tt_big_tab;
 begin
   ltt_big_tab := hr_api.big_tab_api.sel_hr_big_tab_range(p_low => p_low,
                                                     p_high => p_high);
 return ltt_big_tab;
end sel_hr_big_tab_range;
end sel_perf_test;
/
-- grant execute on the business logic code to the user
-- The user only has one way to get to the data, that is 
-- through the business logic.
grant execute on hr_code.sel_perf_test to usr1;
-- test the user executing the business logic code.
conn usr1/x@demo
ALTER session SET timed_statistics=TRUE;
ALTER SESSION SET TRACEFILE_IDENTIFIER = "perf_test_api";
alter session set sql_trace=TRUE;
alter session set events='10046 trace name context forever, level 12';
-- test it.
declare
 ltt_big_tab hr_decls.big_tab_decl.tt_big_tab;
begin
-- ltt_big_tab := hr_code.sel_perf_test.sel_hr_big_tab_pk(p_id => 100);
-- ltt_big_tab := hr_code.sel_perf_test.sel_hr_big_tab_value(p_value => 4);
-- for i in 0 .. 50
-- LOOP
 ltt_big_tab := hr_code.sel_perf_test.sel_hr_big_tab_range(p_low => 50,
 p_high => 900);
-- END LOOP;
end;
/
-- to run the next test, the user will need select on hr.big_table.
conn rlockard@demo
grant hr_big_table_sel to usr1;
conn usr1/x@demo
ALTER session SET timed_statistics=TRUE;
ALTER SESSION SET TRACEFILE_IDENTIFIER = "perf_test_noapi";
alter session set sql_trace=TRUE;
alter session set events='10046 trace name context forever, level 12';
declare
 ltt_big_tab hr_decls.big_tab_decl.tt_big_tab;
begin
  -- for i in 0 .. 50
  -- LOOP
  select id,
         c,
         d
  bulk collect into ltt_big_tab
  from hr.big_table
  where d between 50 and 900;
  -- END LOOP;
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>

My upcoming Spring events @OracleACE #InfoSec

March 5 – 8: I will be speaking at the Hotsos Symposium in Dallas Texas.

https://www.hotsos.com/apex/f?p=200:61801:6152298924404 I will be showing how to secure your high performance code. We will be looking at some coding standards, what common errors we are making that makes our code less secure, and how to implement a trusted path for your data.

March 15: We have managed to wrangle Bobby Curtis, and Steven Feuerstein to come out to Oracle’s Columbia Maryland office to give a couple of presentations. Maybe it’s the Maryland Crab Cakes or could be they are really nice guys. 🙂

https://www.meetup.com/natcapoug-middleware/events/248008692/

Bobby Curtis, Oracle ACE Director Alumni and Product Manager for Oracle Golden Gate will be giving a presentation on Golden Gate Security.

Steven Feuerstein Oracle ACE Director Alumni and Oracle’s Developer Advocate for PL/SQL for Oracle DevGym. This will be a great opportunity for an Oracle DevGym Workout. Devgym.oracle.com Steven will go through the exercises with you and will be giving prizes for the best performers.

March 21 – 22: I will be speaking at Utah Oracle Users Group Training Days (and getting some Spring Skiing in.) http://utoug.org/TrainingDays I will be speaking on Holistic Database Security and Secure Coding. My Holistic Database Security presentation has come a long way over the past ten years. As new attack vectors, mistakes, mitigations come out I update this presentation. So, if you’ve seen this presentation before, don’t worry there is a lot of new material in there. My Secure Coding Presentation goes through coding standards, common errors, and how to implement a trusted path for you data.

April 18: I will be speaking at Twin Cities Oracle Users Group on Oracle Database Vault and a Hybrid Holistic Database Security presentation that will be focused at DBA’s. Many DBA’s fear or don’t like Oracle Database Vault, because it changes the paradigm of how they work. We are accustomed to being the God of our databases. We will be looking at how to make Database Vault your friend, and customizing it for your needs.

May 22 – 23: I will be speaking at Oracle Users Group Finland. http://www.ougf.fi/index.php/en/

Again I will be speaking on Holistic Database Security and Secure Coding.

And the BGOUG Spring Conference. I’m just waiting on the confirmation.

More to come. 🙂

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>

What is it like on the Oracle User Group Speaking circuit?

This is how most of my trips start, a ride to the airport with my headset on. Listen to some easy listening music such as AC/DC or Iron 20161114_195431Maiden to get me in the mood for travel. Once at the airport; check into the business class lounge at Air France or British Airways to have a glass of Champagne wile waiting for my flight.

UPDATE: I pay for my own business class travel. Thanks for pointing that out Kent Graziano.

20170515_131521 20161107_114624
On some of my trips in the US, I’ll fly my 1948 Ryan Navion to the conference site. Many OSH1times while in Europe, I get asked if I flew my plane there. The answer is no. Single engine airplanes don’t do well when flying over thousands of miles of open ocean.
Who are you going to meet when you are out speaking. The most interesting people in the world. These people are some of the top experts in the world who volunteer their time to educate people. Once the best side effect of hanging around them, is they will inspire you to be better everyday. All if these people here have one other thing in common. They are all wonderful people who I’m happy to call friends.
20160124_132354 20170129_195220
20170509_185627 20170517_174938
20170601_213052 20170603_210720
20170626_121538 IMG_3554
20170524_134852 20170624_213408
20170207_212104 20170207_195927
What happens when you get there. Well, there is the hotel you need to check into. Then you are going to need to find out where the reception is, head out to see the city, find out what the local beer is, and have a bit of fun.
20170520_080839 20161029_160629
20170530_195313 20170520_130123
20161113_222936 20170527_215202
FB_IMG_1484440268333 FB_IMG_1499123988544
20170527_183403 20170529_201317
20170530_122345 20160123_191934
IMG_5640 20161108_205224
You’ve found the reception, and taken in some of the city’s culture – it’s time to get ready to speak. No matter how many times you have done a presentation, it’s important to go through your presentation and demos before you get in front of your audience. You need to be ON. This may be your 20th time giving this presentation, but remember this is the first time this audience has heard it. Deliver your presentation like your reputation depends on it. Also remember, you may be asked a question that you’ve heard a hundred times. Again, the person you are talking to does not know the answer, that is why they are asking; so show that person the respect he/she deserves. You will also be asked questions that you don’t know the answer to. Write down the question, go back and research it; then provide that person the answer to their question.
FB_IMG_1492451855539 FB_IMG_1499123977327
FB_IMG_1499123992635 IMG_3020
IMG_5469 IMG_5471
You did a great presentation, you got to see the city and make new friends but don’t forget all the other great session that are being offered. Besides, because you are a speaker, you get to sit in on these sessions too. You also need to find time to get some work done.
IMG_5314 IMG_6104
FB_IMG_1499123999647 20170510_131025
IMG_5319 20161103_111804
It’s done, you finally made it home, you have customers that you need to take care of. In the past week, you traveled the equivalent of round the world. Get some rest, you really need it. The customer can wait for tomorrow, they wont mind. Smile

20161229_220300

#Oracle #Privilege analysis #QuickTip

Here is a quick tip on Oracle privilege analysis. Frequently I want to find out all of the ways a user can get to an object for any privilege. DBA_TAB_PRIVS and DBA_ROLE_PRIVS are the two views I go to. I want to also see all the privileges that are granted on any object. This is good for starting at the user tracking privileges to the object, it’s also good for starting at an object and walking back to the user.

This query does a pivot on the users and roles to get the path to the object and what privileges are associated with that path.

<CODE>
SELECT OWNER,
       TYPE,
       TABLE_NAME,
       GRANTEE_FROM,
       GRANTEE_TO,
       "'SELECT'" SEL,
       "'UPDATE'" UPD,
       "'INSERT'" INS,
       "'DELETE'" DEL,
       "'EXECUTE'" EXE,
       "'FLASHBACK'" FLSH,
       "'ON COMMIT REFRESH'" OCR,
       "'ALTER'" ALTR,
       "'DEQUEUE'" DEQ,
       "'INHERIT PRIVILEGES'" IPRV,
       "'DEBUG'" DBG,
       "'QUERY REWRITE'" QR,
       "'USE'" US,
       "'READ'" RD,
       "'WRITE'" WT,
       "'INDEX'" IDX,
       "'REFERENCES'" REF
FROM
       (SELECT R.GRANTEE "GRANTEE_TO",
              T.GRANTEE GRANTEE_FROM,
              T.GRANTABLE,
              T.owner,
              T.table_name,
              T.TYPE,
              T.PRIVILEGE
       FROM DBA_TAB_PRIVS T,
            DBA_ROLE_PRIVS R
       WHERE T.GRANTEE = R.GRANTED_ROLE (+)
       AND t.grantee != 'SYS'
       AND t.grantee != 'SYSTEM'
       AND R.GRANTEE != 'SYS'
       AND R.GRANTEE != 'SYSTEM'
       )
PIVOT (COUNT(PRIVILEGE)
FOR PRIVILEGE IN ('SELECT',
              'UPDATE',
              'INSERT',
              'DELETE',
              'EXECUTE',
              'FLASHBACK',
              'ON COMMIT REFRESH',
              'ALTER',
              'DEQUEUE',
              'INHERIT PRIVILEGES',
              'DEBUG',
              'QUERY REWRITE',
              'USE',
              'READ',
              'WRITE',
              'INDEX',
              'REFERENCES'))
ORDER BY TABLE_NAME;
</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

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