Next month is going to be a busy – Atlanta, Helsinki, Saint Petersburg, Moscow, Sofia Bulgaria, and London

May 10th I will be speaking In Atlanta Georgia on Holistic Database Security at Georgia Oracle Users Group Tech Days 2017. I have not decided if I’m flying 81K down or flying commercial yet. I may just go ahead and fly commercial, because I won’t be able to get down to Atlanta until the afternoon of the 9th.

http://gaoug.strikingly.com/

Next up, spend a few days at home, then fly over to Helsinki Finland for Harmony 2017 May 17-18. I’ll be speaking on PL/SQL Secure coding practices. We always have fun at Harmony, this time we are also planning on heading over to Heli and Marko’s place to have a BBQ. I’ll be cooking Firehouse Hamburgers for everyone.

http://www.ougf.fi/index.php/en/

The morning of the May 19, getting on the train to head over to Saint Petersburg to see the ballet “A Midsummers Night Dream” at The Mariinsky Theatre, spend the weekend and see the city.

May 22 taking the train down to Moscow. I’ve been working with the Oracle Office there to put together a three-hour security workshop for Oracle customers and partners. I’m really looking forward to this event. This workshop will be live translated to Russian. I’ve never had a talk translated, so this will be a new experience. Now I’ve been to Moscow a couple times for layovers, but never spent more than a day there so this time, I’ll spend a bit over a week and celebrate the 57th birthday there. I checked for tickets to the Bolshoi Theatre for Friday night to celebrate my birthday, but there were quite expensive, so I opted to get tickets to a modern ballet Saturday night at the Bolshoi Theatre.

May 31 it’s off to Sofia Bulgaria for BGOUG’s spring conference June 2-4. This conference is one of the finest in the world. If you are in or near Bulgaria, I highly recommend attending. I’ll be speaking on Cloud Security and Hacking the Human Brain.

http://www.bgoug.org

June 4th off to Home, great part is, I’ll have a 23 hour layover in London, so get to see a bunch of friends at some random London pub. Life is good.

As most of you know, I prefer to fly Business Class Aeroflot on my trips to Europe, but this time it’s going to be British Airways so I can get my layover in London to see some friends.  See y’all soon.

@Oracle 12.2.0.1 Cool new features to improve security. Part 2 TDE support to encrypt SYSTEM, SYSAUX, TEMP and UNDO tablespaces. #infosec

Are you gathering statistics on your data? Are you running Transparent Data Encryption, then it’s time to upgrade to Oracle 12.2.0.1.

Pretty cool, eh? So what’s happening here? Statistics that were gathered on the hr.employees table are stored in the SYSAUX tablespace. If you are running Oracle 12.1 or bellow, SYSAUX can not be encrypted; therefore data that should be encrypted (statistics data) is spilling over to the unencrypted tablespace SYSAUX. Oracle 12.2 now supports encrypting SYSTEM, SYSAUX, TEMP and UNDO tablespaces to help prevent the spillage of data.

Every now and then “It happens.”

I have lost count of the number of times I’ve given the Holistic Database Security talk. The talk has evolved over the years, it continues to evolve, and is a mature presentation. Yesterday at Collaborate 2017, It Happened!

I was asked to have my presentation recorded to put out on the web. I’m always very agreeable to having my presentations recorded to reach a larger audience. A representative from Collaborate came up to me to plug a thumb drive into my laptop with the recording software. My reaction was, you got to be kidding, how do I know that thumb drive is safe? I travel with a sacrificial lamb computer, so I was not too worried about malware.  Turns out allowing this software to run on this machine was not such a great idea.

When I do my presentations, I typically have an Oracle 12c VM running to do my demos along with Chrome to display my slides. My sacrificial lamb computer is not the newest, or fastest laptop out there, in fact I’ve been using it for a few years now.

About 10 minutes into my presentation I noticed something was amiss; my laptop was running much slower than normal then locked up for several minutes. This through me for a loop. Finally, I pulled the thumb drive, and did a hard reboot of my computer. (holding the power button down, yea it was that bad)

Lessons learned:

  • Always have a hard copy of my presentation available.
  • Things go wrong, have a disaster recovery plan.
  • Doing a presentation in front of an audience is PRODUCTION and should be treated like production. Don’t introduce something to the environment that has not been fully tested.

@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 packege 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 induvual functions and procedures in our package. In the emp_api package the package getEmpInfo can call the functions fGetEmpPhone and fGetEmpManager. The package EmpMaint can call the functions, fDelEmp, fInsEmp, and fUpdateEmp. Now we have fine grained control over what can call the functions and procedures in a specific package.

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

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

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

For this post, we are going to focus on definers rights and invokers rights. Most developers already know about these privilege modifiers, but sadly I rarely see these being used at customer sites.

Key to understanding how to secure your code is understanding definers and invokers along with inherit privileges, the accessible by clause along with a few other things. We are going to use these privilege modifiers to help implement a trusted path to your data. Here is the Oracle documentation on Definers Rights and Invokers Rights. Managing Security for Definer’s Rights and Invoker’s Rights.

Definers rights and invokers rights are pretty easy to understand. Lets start with a simple example. If a package, procedure or function (You should always use packages) is created using definers rights, then the code will execute with the privileges that are giving to the owner of the package. If the package is created with invokers rights then the package will execute with the privileges of the invoker (user who executed the code).

We are going to create an application user usr1 and an application code schema app.  Once we have done that we will grant select on hr.employees to the app user.

1 SQL> create user usr1 identified by usr1; 2 User USR1 created. 3 SQL> grant create session to usr1; 4 Grant succeeded. 5 SQL> create user app identified by app; 6 User APP created. 7 SQL> grant select on hr.employees to app; 8 Grant succeeded.

Lets create a package with a function that does one simple thing; return the number of employees in the hr.employees table that have a salary that is greater than or equal to the passed parameter. Once we have this package we are going to grant execute on the package to the user usr1.

1 create or replace package app.emp_api 2 authid definer -- this is the default, but it's nice 3 -- to be specific 4 as 5 function fEmpCount(pAmt IN NUMBER) return number; 6 end; 7 / 8 9 create or replace package body app.emp_api as 10 function fEmpCount(pAmt IN NUMBER) return number is 11 x number; -- just a dumb variable to hold the count. 12 begin 13 select count(*) 14 into x 15 from hr.employees 16 where salary >= pAmt; 17 return x; 18 exception when others then 19 sys.dbms_output.put_line(sqlerrm); 20 return -1; 21 end; 22 end; 23 / 24 Package Body EMP_API compiled 25 SQL> grant execute on app.emp_api to usr1; 26 Grant succeeded. 27

Now, when usr1 executes the app.emp_api.fEmpCount it will work fine. We don’t need to grant permissions to usr1 to access the employees table, because the package executes with the permissions of the user app, that does have select on hr.employees. Using this scheme, we have effectively locked the user into using the application.

1 SQL> conn usr1/usr1@demo1 2 Connected. 3 SQL> set serveroutput on 4 SQL> declare 5 x number; 6 begin 7 x := app.emp_api.fEmpCount(pAmt => 100); 8 sys.dbms_output.put_line(to_char(x)); 9 end; 10 / 11 107 12 PL/SQL procedure successfully completed. 13

So that works just fine, now lets try it when we use invokers rights, we get table or view does not exists.

1 SQL> conn rlockard@demo1 2 Password? (**********?) *********** 3 Connected. 4 SQL> create or replace package app.emp_api 5 authid current_user -- all we need to do is change the 6 -- package spec 7 8 as 9 function fEmpCount(pAmt IN NUMBER) return number; 10 end; 11 / 12 Package EMP_API compiled 13 SQL> conn usr1/usr1@demo1 14 Connected. 15 SQL> set serveroutput on 16 SQL> declare 17 2 x number; 18 3 begin 19 4 x := app.emp_api.fEmpCount(pAmt => 100); 20 5 sys.dbms_output.put_line(to_char(x)); 21 6 end; 22 7 / 23 ORA-00942: table or view does not exist 24 -1 25

Because the code executed with the same rights as the invoker (usr1) we get the ora-00942: table or view does not exists. For this to work we need to grant select on hr.employees to usr1.

1 SQL> grant select on hr.employees to usr1; 2 3 Grant succeeded. 4 5 SQL> conn usr1/usr1@demo1 6 Connected. 7 SQL> set serveroutput on 8 SQL> declare 9 x number; 10 begin 11 x := app.emp_api.fEmpCount(pAmt => 100); 12 sys.dbms_output.put_line(to_char(x)); 13 end; 14 / 15 107 16 17 18 PL/SQL procedure successfully completed. 19

Now we have that down stay tuned for the next post on inherit privileges, because when usr1 has powerful privileges, when we use invokers rights the inherits those powerful privileges.

#Infosec Virus Delivery via Email

I’ve been getting a lot of these emails lately. If you receive an email with an attachment and you did not expect it, or in this case if you did expect it. Take a closer look. This Christmas season, a lot of packages were sent out Fedex, so I’m guessing these spammers wanted to take advantage of that. The bottom line, just delete the email.

This email has several problems.  Any one of these three signs would make me delete the email.

1) The from email address does not match the from name.

2) The signature line is just not right for something coming from Fedex.

3) It has a zip attachment. DO NOT OPEN THE ZIP FILE.

spam

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

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

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

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

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

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

The power and security configuration of using an API

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.

#Hacking The Human Brain

Hacking the Human Brain presentation is coming together, We are going to have a lot of fun in this one. We have programmed our brains with a lot of bullshit rules so we need to question all the rules in our lives. Are those rules there because “I’m mom and I said so, or is there good logic behind the rules?”

When we were growing up mom had a few rules, they included don’t play with alligators, bears and dynamite. Some rules we need to question, others have to do with our survival as a species, or in this case, the survival of three little Lockard boys. Do little boys listen to their mother? We survived childhood in spite of ourselves.

hackingthehumanbrain

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