2017 was a crazy year, 2018 is going to be challenging

It’s been a crazy year. In 2017 I’ve done talks in Paris France, Helsinki and Rovaniemi Finland, Sofia Bulgaria, Moscow Russia, Denver Colorado, Las Vegas Nevada, San Antonio Texas, Krakow Poland, Dushanbe Tajikistan, and Raleigh North Carolina. A few of these cities, (Helsinki, Moscow, and Sofia) getting there more than one time this year. And these don’t count the side trips to places like Saint Petersburg and London to see the ballet, visit friends, visit a pub, or see a football game. It’s now coming on the end of the year and thankfully I don’t have any travel planned for the rest of the year. You should see my frequent flier statement, but we all know it does not compare to Heli’s frequent flier statement.

Also in 2017, I made Oracle ACE Director. Now if you do the math, estimating there are over 500,000 Oracle Customers and if each customer has five Oracle professionals on staff, that would mean there are over 2.5 Million Oracle professionals in the world. As of this day there are 107 Oracle ACE Directors (the top tier professional advocates) in the world and I am one of them.

Robert Lockard – Oracle ACE Director

Then last month, I was asked by Oracle Magazine about doing a peer-to-peer profile. That was published this morning.

Oracle Magazine Peer-To-Peer

Now when Oracle Magazine asked me to do the Peer-To-Peer, it also included doing a short video. It’s hard to believe, but this short video took me four hours to shoot.

Oracle Magazine Peer-To-Peer Video

What is the plan for 2018? Well it’s going to be hard to beat 2017, But I’m up to the challenge. I hope to spend a little more time in Saint Petersburg Russia to explore the city some more. This is a seriously beautiful city and I think late May would be the perfect time to explore it. Fortunately, it will be just in time for White Nights.

I’ve decided that in 2018 I will be focusing more on the smaller Oracle Users Groups. Yes, Collaborate, KSCOPE, and RMOUG are great conferences, and I am not abandoning them for good, I’ll return to them perhaps in 2019. On January 1 when I’ll be heading back to Europe for a week that will include another new place for me, Tbilisi Georgia and I also have tickets to see the Nut Cracker in Moscow that week. I have two papers into the UKOUG Ireland event in March. I also plan on speaking in Utah, Ohio, Atlanta Georgia, Helsinki, Moscow, Poland, Paris, London, and of course, Bulgaria again. Maybe I’ll also get to do a talk in Saint Petersburg while I’m there.

What else will I be doing? Well, I’m working hard to bring speakers to the Baltimore Maryland area. I’ve already have Steven Feuerstein lined up for January 18th in Baltimore, Bobby Curtis and I are talking about him coming out in March. I would also love to get SQL Maria out here to do a deep dive into the Oracle Optimizer. Yes, 2018 is going to be a busy year, and I promise I also will be flying my plane more.

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>

Five things that make me just want to scream. #Infosec

When evaluating the security of a database, and/or the security of an application, there are a few things that will make me start pounding my head against my desk. What prompted this? A customer asked me help evaluate a system that is about to go through an IG Audit and they want to be ahead of the curve. The good part is I was able to come up with a lockdown procedure that is specific to this customer and their needs. The bad part is, I keep seeing these same mistakes over and over again. So, lets sit down and go through five of the mistakes that I keep seeing over and over again.

  1. A COTS application required any of the following privileges to get installed or operating. I’m sorry guys, if you have not figured out what permissions your application really needs, then it’s not ready to be put on the market. I was able to do a privilege analysis and find out what this application really needed.
    1. DBA – I have a hard time justifying granting DBA to an application to be installed or operate.
    2. SELECT / CREATE / UPDATE / DROP ANY <FILL IN YOUR TYPE>. Hey, this is another privilege that just makes me shake my head. Because the ANY any privilege allows the grantee to operate on ANY object in the database, this just tells me you have not thought it through. Many databases have multiple applications running in an instance. Why would your application need to access schemas that are not part of your applications?
      1. Oh and as a side note, why did the application need both DBA and all these ANY privileges?
    3. Okay there are a whole bunch of privileges that should never be granted to an application, these are just a few.
  2. Not cleaning up users that have left. Please tell me why you need to keep a user in the system who left two years ago. If someone can give me a good excuse, I’m willing to listen, but I got to tell you, in thirty years in this business, I have not heard any good reason to keep a user in the system.
    1. Go ahead, drop those users, including the test, sit, and dev instances. They don’t need to be there.
  3. Connecting as the application to do your job. I see this so often that it really makes me wonder about the technical ability of the people doing the job. Please tell me why you need to connect to the application to do your work? If there are permissions that you need that are missing, we can resolve that.
  4. Mixing Data and Code in the same schema. Why do you need to do this? You really should separate your data and code. All you need is one little sql injection bug for a bad guy to find, then she owns your database. Oh, and it’s highly likely you have a sql injection bug, you just don’t know it yet. We have a plan to separate the code from the data, it’ll take a while, but this should keep the auditors happy.
  5. Writing queries in production. I get it, it happens; you are supporting your users and the user needs something from the database quick. There is not a report available that answers the users question. You connect to the production instance through sql plus, sqlcl, sql developer or some other tool. You then type your query, hit enter and production slams on the breaks. Yea’ I have lost count of the number of times I’ve seen this, you forgot to join those two really big tables and just got a cartesian product. This happened while I was sitting there. You have a test instance, please use it before running in production.

Yet another breach through #SQLInjection

The following quote bothered me a lot. “No amount of best practices or prohibitive steps is going to stop a determined hacker.” While this is a true statement, what it leaves out is if you make it difficult by securing the information, the hacker will move onto easier target.

Here is the full article: Yet another data breach

SQL Injection attacks continue to be successful. To secure your data from a sql injection attack, you can start by implementing secure coding standards. Here is a link to my write up on using a secure architecture that to date has been immune from sql injection.

Secure Coding, Code Based Access Control and using multiple schema

Upcoming #POUG17 and #tajoug #techconftj* #Oracle #plsql #infosec #fun

99% of my presentations are technical, addressing our information security needs. Then every now and again, I get to do something fun. There was “Hacking The Human Brain” last June at BGOUG.

My next trip in September is taking me to Krakow Poland, Moscow Russia and Dushanbe Tajikistan. While in Krakow, I’ll be presenting on “Secure Coding in the Cloud” and taking part of a panel discussion. On to Moscow, to see a concert on Red Square, and dinner with friends. Then off to Dushanbe where I’m privileged to present, “Secure Coding in the Cloud,” “Holistic Database Security,” and something fun. “Make a Difference, My 10 rules for a full life.” This will be a fifteen minute talk on, the rules I live by. Come on out to the Polish Oracle Users group or Tajikistan Oracle Users Group, September 7th. We are going to learn a lot and have fun doing it.

If you can’t make it to either of these two fine conferences, meet me in Moscow for a beer or two at Hotel Metropol.

Code Based Access Control, #securecoding #oracle #plsql #MultipleSchemas

You can download the full code from gethub.com CBAC Simple 

This example depends on the HR Demo schema being loaded. We will be building on this example over the next several months to present a full blown application that includes many of the secure coding features I have been talking about.

We need to do better. I keep walking into customers, where I keep hearing: “It’s harder to do.” “We have deadlines to make.” “We will address that sql injection flaw after we go production; because we are behind schedule.” Now guys, I get it! I have been down in the trenches for about thirty years coding, designing, and administering some sensitive information systems. (They are all sensitive in my view.) We really need to get on top of this, now.

First lets go back and see where we started this discussion. In part 1 and part 2, I introduced you to the better Architecture of using multiple schemas then we discussed definers rights and invokers rights.

Secure Coding Part 1

Secure Coding Part 2

Lets start: the first part of the code we are going to be doing some clean up. This way we can run the code again and again without getting error messages.

1 -- clean up before we start. 2 declare 3 procedure Drop_User(pUser in varchar2) is 4 User_Doesnt_Exist exception; 5 pragma Exception_Init(User_Doesnt_Exist, -01918); 6 begin 7 execute immediate 'drop user '||pUser||' cascade'; 8 exception when User_Doesnt_Exist then null; 9 end Drop_User; 10 begin 11 Drop_User('hr_api'); 12 Drop_User('usr1'); 13 end; 14 / 15 16 declare 17 Role_Doesnt_Exist exception; 18 pragma exception_init(Role_Doesnt_Exist, -01919); 19 begin 20 execute immediate 'drop role hr_emp_select_role'; 21 execute immediate 'drop role hr_backup_role'; 22 execute immediate 'drop role api_admin_role'; 23 exception when Role_Doesnt_Exist then null; 24 end; 25 -- done cleaning up. 26 27

Now lets get the two schemas we will be using in this example.

Line 3: we are creating a HR_API schema that will hold all the code to access data objects in the HR schema.

Line 5: we are creating the USR1 user that will make calls to code in the HR_API schema. (In a future post, I’ll be adding in HR_CODE schema that will hold the business logic. But for now, we are keeping it simple to demo CBAC.

1 -- this is going to be my api schema that will 2 -- have access to the hr objects. 3 create user hr_api identified by x; 4 -- this will be my executing user. 5 create user usr1 identified by x; 6

Now lets get to the roles we are going to need.

Line 3 we are creating the role HR_EMP_SELECT_ROLE. This role will have select on hr.employees. We will be granting this role to the api package that accesses the hr.employees table.

Line 6 we are creating the HR_BACKUP_ROLE. This role will be granted create any table, and then the role will be granted to the api package that does the backup. I really don’t like granting create any table, but for this purpose it is required to create a table in a different schema.

Line 8 is the API_ADMIN_ROLE. This role will have the create procedure privilege and will be assigned to the HR_API schema.

1 -- the hr_emp_select_role will have select in hr.ermployees. 2 create role hr_emp_select_role; 3 -- the hr_backup_role has create any table privilege. I really don't 4 -- like that, but that is what the role needs to create a table in 5 -- a diffrent schema. 6 create role hr_backup_role; 7 -- the api_admin_role has create procedure privilege. 8 create role api_admin_role; 9

.Now lets get into the grants we will need.

Lines 4 – 6 we are granting create session to usr1. At this time, this is the only privilege this user will need. Once we have the API package compiled, we will grant execute on that package to usr1

Lines 10 – 12 we are granting create procedure to the API_ADMIN_ROLE. This will be needed for HR_API to create a package in it’s schema.

Lines 16 – 18 we are granting select on hr.employees to the HR_EMP_SELECT_ROLE. This role will be granted to the HR_API schema with delegate option. I’ll discuss more about that later.

Lines 20 – 21 we are granting select on hr.employees to the HR_API schema. I really don’t like this, but it’s needed in order to compile the package.

Lines 26 – 28 we are granting create session to the HR_API schema. This will be needed to so HR_API can connect and create the required objects with only the permissions needed.

Line 32 we are granting create any table to the HR_BACKUP_ROLE. I really don’t like granting create any. This role will be granted to the API package that does some dynamic sql. I really need to think about tightening this down a bit. But for this iteration, we are going to use this to create a backup table in the hr schema.

Lines 36 – 39 here we are granting the HR_EMP_SELECT_ROLE and HR_BACKUP_ROLE to the HR_API schema with delegate option. This is important, you need to use either the DELEGATE OPTION or the ADMIN OPTION in order to be able to grant the roles to the HR_API packages.

Lines 41 – 43 and finally on our grants, we are granting the API_ADMIN_ROLE to the HR_API schema so HR_API can create the required packages.

1 -- the user usr1 will only need create session. after we've created 2 -- the package in the hr_api schema, we will grant execute on the 3 -- package to usr1. 4 grant 5 create session 6 to usr1; 7 -- 8 -- the api_admin_role will need the create procedure privilege. 9 -- this will be granted to hr_api. 10 grant 11 create procedure 12 to api_admin_role; 13 -- 14 -- this will give the hr_emp_select role the privilege 15 -- it needs to execute. 16 grant 17 select on hr.employees 18 to hr_emp_select_role; 19 -- 20 -- this will be needed to compile the code in the api schema. 21 grant 22 select 23 on hr.employees to hr_api; 24 -- 25 -- we are going to revoke create session after we are done. 26 grant 27 create session 28 to hr_api; 29 -- 30 -- the hr_bacup_role is used to demenstrate 31 -- using dynamic sql. 32 grant create any table to hr_backup_role; 33 -- 34 -- hr_api needs the roles with delegate option (or admin option) 35 -- to be able to grant the role to a package. 36 grant 37 hr_emp_select_role, 38 hr_backup_role 39 to hr_api with delegate option; 40 -- 41 grant 42 api_admin_role 43 to hr_api; 44 -- 45

The final step to setup our users and roles is to alter the user HR_API so there are no default roles. This is going to be done, because in the next installment we will be looking at creating password protected roles.

1 -- during normal operating, the hr_api schema does not 2 -- need any privileges. 3 alter user hr_api 4 default role none; 5

Now lets get into building our HR_API schema. It starts simple enough,

Line 1 connect to hr_api

Line 4 We set the roles we will need to create the api package, and the roles that will be granted to the api package.

1 -- connect as hr_api 2 conn hr_api/x@orcl 3 4 SET role hr_emp_select_role, api_admin_role, hr_backup_role; 5

Lines 1 – 8 we are creating a simple package that does two things. One get the phone number of a person based on first and last name. The second package is used to make a backup of the hr.employees table.

Line 12 Now that we have the package specification, we can grant the role HR_EMP_SELECT_ROLE and HR_BACKUP_ROLE to the hr_api.pgk_emp_select package.

1 create or replace package hr_api.pkg_emp_select 2 authid current_user AS 3 PROCEDURE pGetPhone(pFname IN VARCHAR2, 4 pLname IN VARCHAR2, 5 pPhone OUT VARCHAR2); 6 PROCEDURE pBackupEmp; 7 END; 8 / 9 10 -- we are going to grant the hr_emp_select_role 11 -- to pkg_emp_select 12 GRANT hr_emp_select_role, hr_backup_role to package pkg_emp_select; 13

Lines 3 – 24 don’t do anything very interesting, this procedure will get a phone number based on first and last name. My exception handler is really quite simple and does not do much. We’ll address that in a later post, because as you know, returning errors to the user is not the best practice from a infosec point of view.

Lines 30 – 39 is using dynamic sql to create a backup of the hr.employees table. We granted HR_BACKUP_ROLE to the package, this way, the package can create a table in the hr schema. I’m going to have to rethink this example, I don’t like it all that much because granting CREATE ANY TABLE is not really a good practice.

1 CREATE OR REPLACE PACKAGE BODY hr_api.pkg_emp_select AS 2 3 PROCEDURE pGetPhone(pFname IN VARCHAR2, 4 pLname IN VARCHAR2, 5 pPhone OUT VARCHAR2) IS 6 x INTEGER; 7 BEGIN 8 BEGIN 9 SELECT phone_number 10 INTO pPhone 11 FROM hr.employees 12 WHERE first_name = pFname 13 AND last_name = pLname; 14 EXCEPTION WHEN no_data_found then 15 pPhone := 'xxx'; 16 WHEN too_many_rows THEN 17 pPhone := 'yyy'; 18 WHEN others THEN 19 -- we can add in the help desk error handler later, again this 20 -- is just to demo granting roles to packages. 21 sys.dbms_output.put_line('pGetPhone raised an exception ' || sqlerrm); 22 END; 23 -- 24 END pGetPhone; 25 -- 26 -- this is a very simple procedure, create a backup table using execute 27 -- immediate. (dynamic sql) the only way this procedure is going to work 28 -- is if the package has create any table privilege to be able to 29 -- create a table in another schema. 30 PROCEDURE pBackupEmp IS 31 -- This is the date string 20170805 32 dt VARCHAR2(8); 33 BEGIN 34 dt := to_char(sysdate,'rrrrmmdd'); 35 execute immediate 'create table hr.employees' ||dt|| ' as select * from hr.employees'; 36 sys.dbms_output.put_line('create table success'); 37 exception when others then 38 sys.dbms_output.put_line('create table error ' || sqlerrm); 39 END pBackupEmp; 40 end pkg_emp_select; 41 / 42

Now that we have all that, lets’ test it.

1 conn usr1/x@orcl 2 set serveroutput on 3 declare 4 -- to hold the phone number, because we can't reference 5 -- hr.employees we can not use phone_number%type. 6 lPhone VARCHAR2(20); 7 begin 8 sys.dbms_output.put_line('testing cbac select on emp'); 9 hr_api.pkg_emp_select.pGetPhone(pFname => 'Jose Manuel', 10 pLname => 'Urman', 11 pPhone => lPhone); 12 sys.dbms_output.put_line(lPhone); 13 end; 14 / 15 16 -- if you run this twice on the same day you are going to get 17 -- a ORA-00955 error. this is because the package creates 18 -- a backup of the hr.employees table by appending the date 19 -- to the table name. So, if you are going to run it twice 20 -- you need to drop or rename the backup table. 21 begin 22 sys.dbms_output.put_line('testing dynamic sql'); 23 hr_api.pkg_emp_select.pBackupEmp; 24 sys.dbms_output.put_line('done'); 25 exception when others then 26 sys.dbms_output.put_line('daaaa ' || sqlerrm); 27 end; 28 / 29

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 #sqldev 17.2.0.188 MAJOR improvement

This is going to be quick, I don’t get a lot of time to read new features documentation. I normally reserve that for once a month, print em’ out and read over a nice glass of Scotch. Hey it works for me.

So today, things seem a bit odd, so I opened the SQL Developer Instance Viewer to get a picture of what the database was doing. WOW, thanks Jeff, Kris and team. Love the new look. Check out Top SQL.

Kinda disturbed by an article I just read. #infosec #rant

As most of you know I live on the defense side of infosec. As attack vectors are exposed, I study them to learn how to design systems that can defend against them. There are some biggies, sql injection, cross site scripting, insider threat and encrypted data leaking that burn a lot of my time.

I just read an article giving a detailed technical description on how to use randomized proxy chains to avoid detection. And by using proxies in different countries, it makes it difficult to find out who did the attack. Now of course this article said this should only be used for white hat pen testing and you should not use this for any illegal purposes.

Make no mistake, articles like this are not helping the good guys secure systems. You are only helping the bad guys.

Exciting times. #poug #tajoug #techconftj*

I will making my first trip to Krakow Poland and Dushanbe Tajikistan. This trip will cover over 24,000 miles in air travel and five different airlines.

POUG High Five. The Polish Oracle Users Group will be having their annual tech conference in Krakow Poland September 1 and 2. http://poug.org/en/edycja/high-five-poug/ I’ll be speaking on secure pl/sql coding and taking part in a panel discussion. There is a very impressive group of speakers coming in from all over the world. This is a first class conference that will be held in a brewery.

Tajikistan TechConf. I don’t have a URL for this conference yet, it will be held Thursday September 7 in Dushanbe Tajikistan. Heli “From Finland” Helskyaho and I will speaking along with some local speakers. Heli is famous for her dynamic speaking style; she will teach you new information that will make you better at your job.

Come on out to either one of these two excellent conferences and I’ll help you wrap your brain around Oracle Database Security.

Between POUG and Tajikistan TechConf, I will be spending a couple of days in Moscow to go to a concert and visit with some friends.