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.

Make 2017 Insanely Successful Part 1. Bullshit Rules

Question the Rules. I have a few rules in life, one of them is to break at least one rule a day. For years, heck decades there were rules in my life that had no basis in fact. It was not until I encountered a young lady on a flight to Moscow a couple years ago who questioned my reasons for not getting a visa so I could go into Moscow. At the time, I stood by my logic knowing I was right, as it turned out, I was only half right. What have I missed out on from following bullshit rules for the past fifty six years? There have been a lot of “sea lawyers” in my life. If you have never been in the Navy, a sea lawyer is a friend or acquaintance who passes knowledge to you for your own good. These rules are also rules that have carried on from childhood, where mom would say, “now Rob don’t play with alligators, bears and dynamite.” Well those rules were great for a five year old; now I take the rules and apply logic to them, if the rule defies logic or I can’t validate it through an independent and authoritative source, then that rule does not apply to me. (btw: I don’t play with alligators bears or dynamite .. anymore.)

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


Let’s address the Elephant, Donkey and Bear in the room. #Wikileaks

Instead of a year in review, I want to address the Elephant, Donkey and Bear in the room. Oh, what is the grade for the US News organizations and US Politicians? I’m going to give them a big fat F for manipulating data to push a narrative that does not pass the smell test. I also blame the American public for accepting the message without questioning the logic.

I have chatted with a few friends on the leaks from DNC and Hillary Clinton’s campaign. Many have questions, but very few have taken the time to think through what really happened. This is because most people in the US accept being spoon feed information from the political class and their preferred news source without question. The following is my professional opinion of what really happened.

The bottom line:

When looking at cases like this it’s best to apply Occam’s Razor to your choices. Now in the past I said the Russians did not do the hack. That was a very simplified statement based on my experience. The operative words are “the hack”, implying the hack that released data to Wikileaks. Applying Occam’s Razor, The Russians did not release the data to Wikileaks. But the Russians did hack the DNC, RNC, and Hillary Clinton’s email but again did not weaponize the data to attack Hillary Clinton. When I say weaponize the data, I mean, the Russians did not release the data to Wikileaks. The human intelligence aspect of this is beyond my expertise so I am not going to comment on any aspects of that.

The messaging that is coming out of Washington DC is taking information from separate events and combing them to make it look like one event.

Your Occam’s Razor choices:

  1. The Russians did hack the DNC, Hillary Clinton’s email server along with John Podesta’s email.
    1. The Russians weaponized the data to attack Hillary Clinton.
    2. The Russians did not weaponized the data to attack Hillary Clinton.
    3. The emails were leaked by one or more trusted insiders.
  2. The Russians did not hack the DNC, Hillary Clinton’s email server along with John Podesta’s email. If this is the case then choices a and b above do not apply, so we need to come up with how they came out.
    1. The emails were leaked by one or more trusted insiders.


Intelligence services gather intelligence. Every intelligence service, the Americans, the Russians, the French, the British, everyone uses the tools at their disposal to get information for strategic and tactical advantage. It’s a fact of life, accept it. The other fact of life you need to accept is, if a state intelligence service targets you, then you are screwed. They have the resources, discipline and patience to get the information they want. The third fact of life you need to accept is talking out of turn in universally frowned upon in the intelligence community.

The information gathered by state intelligence services is used for law enforcement, negations, etc. You also need to understand there is a very high cost of weaponizing data. When this occurs, you reveal sources and methods. Once sources and methods are known, countermeasures are implemented to nullify the sources and methods.

Here is an example: In the Banking Secrecy Act it is a felony to reveal the existence of a FinCEN SAR (Financial Crimes Enforcement Network Suspicious Activity Report) that is one of the tools that financial intelligence units use to track and find terrorist money, money laundering, etc. The other side of the equation, law enforcement is prohibited from using the data in a SAR in an investigation. Law enforcement can however use the data in a SAR to develop leads. This serves two purposes, provide a safe harbor for the provider (Bank, Casino, Financial Institution, etc) of the SAR, but also so not to reveal to the target where the information came from or the pattern that triggered the SAR. If a target, or group of potential targets knows certain patterns will trigger a SAR, they will avoid using those patterns, making the job of law enforcement and financial intelligence more difficult.

Using the above logic, it would not make sense for the Russians to have weaponized this data. What about a rogue actor in Russian intelligence who is trying to gain favor from Vladimir Putin? Again, it would not make sense for a rogue actor to do this. People in the intelligence community know talking out of turn is frowned upon, hell if you talk out of turn you are down right hated. Mr Putin came from the intelligence community and would not tolerate someone talking out of turn aka leaking intelligence that was received from an operation, that would be a career killer, everyone in the community knows that. What about Vladimir Putin approving the release of the information to Wikileaks? Again that does not make sense. If you have sources and methods that gives you intel on a subject, you don’t expose those sources and methods. Because once those sources and methods are exposed, you can’t use them again.

Another aspect of this is the John Podesta emails were pretty damn boring. There was nothing in there that I would call a smoking gun against Hillary Clinton. Yea they did have some juicy behind the scenes talk, some of them disrespectful but nothing that I would describe as can be used to directly attack Hillary Clinton. Heck, if Russian intelligence wanted to do that, then they would have released emails written by Hillary that were damaging.


The leak came from a trusted insider. Who that was, I don’t know and quite frankly don’t care to know.

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

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.


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


AUTHID current_user 



-- constant declarations

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




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.

November is going to be a busy month. #ECOUG and #BGOUG

I stopped tracking the the miles I fly years ago. It seems every other month I’m in another timezone at a conference, learning from the best in the industry. Well in November I will be fortunate to stay in my timezone; speaking at the East Coast Oracle Users Group in Raleigh, North Carolina. Once I return from ECOUG, I’m on a flight to Sofia Bulgaria for the Bulgarian Oracle Users Group Autumn conference, seven hour time difference from Baltimore, Maryland.

What do we get out of my spending so much time traveling?

  1. I get privilege of teaching people how to secure their data. We are seeing the same security mistakes everywhere we go. Therefore I’ve dedicated some of my time to show you these mistakes and how to correct them.
    1. Setting up the Oracle Database environment. A lot of these mistakes are the result of an attitude of, we have strong parameter security, so the proper effort is not put into securing the database. Other mistakes are the result of not understanding how the database works. Sample question: If you take existing data and put it into an encrypted tablespace, is your data encrypted? The answer is yes and no. Yes your data is now encrypted but you still have unencrypted ghost data. I’ll walk you through how to address this ghost data. Most of these mistakes can be easily corrected, others require more engineering.
    2. Application Architecture. We have been designing applications the same way for over thirty years and then wonder why data is spilling. We need to fundamentally rethink how we design database applications. I will show you a secure application architecture that will dramatically improve the security of your data.
    3. Coding Standards. The biggest issue I see in database applications is SQL Injection bugs. I remember sitting in a meeting when the Director of Application Development told me. “There are no SQL Injection bugs in our applications.” We started the pen test and it did not take us very long to extract all of their source code from the database and from there we started extracting their data. Don’t be too confidant, there will always someone out there smarter then you (and smarter than me.)
  2. We get the privilege of learning from the best in the industry. The list of speakers at ECOUG AND BGOUG reads like a Who’s Who from the Oracle space. There are ACE Directors, ACE’s, ACE Associates and Oracle Product Mangers from all over the world along with some that are rising to ACE status. If you want to learn from the best of the best, come on out. Here is the best part, these guys are not only the smartest they are also the nicest people you will encounter. We are always happy to sit down over a beer or two and discuss your specific situation or just chew the fat.

Do you want to kick your career up a several notches? Ask us how to get involved with speaking at Oracle Users Groups.

The BGOUG Conference Agenda:

The ECOUG Conference Agenda: