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.


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:

Turn off the #http #listener in #Oracle #STIG

Locking down a database (applying STIGs) you need to check to see if the listener is running http. If you don’t need the http service, turn it off. Turning off http will reduce the attack surface.

Step 1) Is http running?
[oracle@vbgeneric db_1]$ lsnrctl stat | grep HTTP
[oracle@vbgeneric db_1]$

Step 2) Turn off http
RLOCKARD@orcl> select version from v$instance;

RLOCKARD@orcl12c> sho parameter dispatchers

———————————— ———– ——————————
dispatchers string (PROTOCOL=TCP)
max_dispatchers integer

RLOCKARD@orcl12c> exec dbms_xdb.sethttpport(0);
PL/SQL procedure successfully completed.

RLOCKARD@orcl12c> exit
Disconnected from Oracle Database 12c Enterprise Edition Release – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SYS@orcl12c> sho parameter dispatchers

———————————— ———– ——————————
dispatchers string (PROTOCOL=TCP)
max_dispatchers integer

[oracle@vbgeneric db_1]$ lsnrctl stop

LSNRCTL for Linux: Version – Production on 15-SEP-2016 09:25:29

Copyright (c) 1991, 2014, Oracle. All rights reserved.

The command completed successfully
[oracle@vbgeneric db_1]$ lsnrctl start

LSNRCTL for Linux: Version – Production on 15-SEP-2016 09:25:34

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Starting /u01/app/oracle/product/ please wait…

TNSLSNR for Linux: Version – Production
System parameter file is /u01/app/oracle/product/
Log messages written to /u01/app/oracle/diag/tnslsnr/vbgeneric/listener/alert/log.xml
Notice it’s gone
Version TNSLSNR for Linux: Version – Production
Start Date 15-SEP-2016 09:25:34
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
Default Service orcl12c
Listener Parameter File /u01/app/oracle/product/
Listener Log File /u01/app/oracle/diag/tnslsnr/vbgeneric/listener/alert/log.xml
Listening Endpoints Summary…
Services Summary…
Service “orcl12c” has 1 instance(s).
Instance “orcl12c”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully

That was easy.

#infosec #Oracle #Migration #Encryption #2MTT

I have seen this twice in the past week. A customer requirement is to migrate their Oracle database to a new server and they want encryption implemented.The steps defined in the request is Migrate then Encrypt. This is backwards. You should setup your encryption then migrate your data into the new server. Why is this? Well if you move your data and then put it into encrypted tablespaces you are going to be chasing ghost data.

Here is a two minute tech tip I did for Oracle Technology Network explaining the problem.