About rlockard

Robert Lockard is a professional Oracle Designer, Developer and DBA working in the world of financial intelligence. In 1987 his boss called him into his office and told him that he is now their Oracle Wizard then handed him a stack of Oracle tapes and told him to load it on the VAX. Sense then, Robert has worked exclusively as an Oracle database designer, developer and Database Administrator. Robert enjoys flying vintage aircraft, racing sailboats, photography, and technical diving. Robert owns and fly’s the “Spirit of Baltimore Hon” a restored 1948 Ryan Navion and lives in Glen Burnie Maryland on Marley Creek

Critical #Oracle Database flaw needs to be patched today. #infosec #exploit #java

Critical Oracle Database flaw needs to be patched. The patch is in the July 2018 CPU patch.

The exploit is in the Oracle Java VM. Read:  https://nvd.nist.gov/vuln/detail/CVE-2018-3110

This is an easily exploited flaw, that allows a user with low level privileges ( connect with network access via Oracle Net) can completely hijack the Oracle database. 
Affected versions 11.2.0.4, 12.1.0.2, 12.2.0.1, 18

Patch Information: http://www.oracle.com/technetwork/security-advisory/cpujul2018-4258247.html

Upcoming Talks

POUG: 7-8.09.2018 (booked) PL/SQL Secure Coding Practices

ECOUG: 18-19.09.2018 (booked) Holistic Database Security

BGOUG: 16-18.11.2018 (planned) Blockchain a primer. There is a lot of confusion about the blockchain. Blockchain is not crypto currency, block chain is the one part of the technology that makes crypto currency secure. We’ll chat about the technology and how to implement the technology. 

BGOUG: 16-18.11.2018 (planned) The application of blockchain technologies to build faith and trust in the criminal justice system. I’m excited about this one. We are going to go through a case study of securing e-justice systems using blockchain technology.

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

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;

#POUG2018 is right around the corner.

http://poug.org/en/edycja/poug-2018/

Let’s start with some key facts. I learned this from my High School Civics teacher who made us learn a bit about journalism along with studying the Constitution. 

Who: The Polish Oracle Users Group, hosted by some of the most awesome people you’ll ever want to get to know. The young lady in the blue jeans is Luiza Nowak; what Luiza says goes, she’s “The Boss.”

This team gets a lot done, they bring in the top speakers from all over the world. They work hard so everything goes smooth, everyone learns, and everyone has a good time.

What: The annual Oracle Users Group Conference. We will be talking about everything from Machine Learning, to Secure Coding and Information Security.

When: 7-8 września 2018, Sopot

Where: Gdańsk Poland. http://zatokasztuki.pl/

Why: That’s a pretty silly question. For me, it’s the opportunity to share what I know with a wide audience. This is one of those conferences where you are not only going to do a serious amount of learning from over 20 of the top speakers in the world. You will also have the opportunity to expand your network. You will definitively have a good time, Kamil will see to that.

How: Get here by plane, train or automobile. I’m told that tickets have already been sold out. But double check with the website, things do change.

When you get here, be sure to bring your thinking cap, your beer mug, and your sense of humor. You’ll need em’. Zobaczcie się za kilka tygodni.

Common mistake when loading data into an #encrypted database.

Hacker attacking internet

There is a mistake that I’m seeing frequently. Loading a raw data file into an encrypted database then leaving the data file on an unencrypted device.

After loading the data into the database; if you don’t need the data file anymore, you should do a secure delete on the file. If you are going to need the data file again, then move the data file to an encrypted device then do a secure delete on the old data file. Better yet, when you bring the data file down, save it straight to an encrypted device and work from that device.

This is an easy thing to fix.

#infosec Name and SSNs sent in the clear.

I’m more than a little disappointed at people not being serious about information security. One of my customers asked me to help load data from a school system into an apex application I designed for some years back. The excel spreadsheet received from the school system has teacher names and social security numbers. The excel file was not encrypted in any way shape or form. I’ll be contacting the school system on Monday to resolve this issue. If it’s not resolved in a short period of time, I’ll be sending the data to their local news paper. This has got to stop.

Outcomes instead of todo lists.

Chatting with a friend this morning, we were talking about todo list and being overwhelmed by everything that needs to get done. After sharing with her mindfulness meditation, that helps me keep the “chattering monkeys” at bay and has improved my focus. I mentioned that I’ve gotten away from todo list and now write out the outcome I want from today. Coming up with an outcome for the day can be quite difficult; but once you get the hang of it; the outcome for today will be self evident.

Back on 3/1 I needed to bring this practice back into focus and wrote this note to myself.

In my outcome for the day, I also establish a performance goal for the day. This performance goal can be related to my physical performance while exercising and/or my performance around people. An example of this would be even though my chair is very comfortable, I will do twenty minutes on the rowing machine and lift weight for thirty minutes. It will also include what distance will I do in those twenty minutes, and what weights / sets will I do in my exercise.

My professional outcomes for the day, may be; Catch the problem in production, before the customer catches it. This way, the customer is not surprised when there is an issue. They get an FYI instead of, them sending my team an email saying there is a problem.

The beauty of this is, when you defined the outcome; what you need to do to get that outcome should become self-evident.

Where am I, Dev, Test, Production? #quicktip #putty #sqlcl

If you’re like me, you frequently have many environments open at the same time in putty, sqlcl, sqldeveloper, or other tools. This happened quite a few years back, I was switching between my dev, test, and production environments, doing some work, then the “Oh S^#t” happened. I shutdown production when I thought I was in dev.

You can change your prompt to show what environment you are working in; yea’ that works … but I’ve taken this a couple steps further. Out of habit, if I’m not actively doing work in production, I close it. Second, I changed to colors of my screens to tell me what environment I’m in at a glance. Test is green, dev is blue and production is RED.

This way, if I’m about to do any work in production a cold sweat breaks out and I ask myself, do I really want to do that? You can do this in putty, and sqlcl.

 

 

In sqlcl (on windows), bring up the property dialog, click on the color tab, and select your foreground and background colors. Then click OK.

 

 

 

In putty, bring up the configuration dialog. Load the configuration for the environment, click on color under the Window tree, select default foreground, then modify button and select your foreground color. Then select default background, then modify button and select your background color. Go back to session tree and click save.

There you have it, this will make things a lot safer when you have multiple environments open.

I recommend closing your production connection if you are not actively using it. You can’t be too safe.

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