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

NATCAP-OUG @HelenJSanders is coming to Columbia Maryland

Mark you calendars; Friday October 20th Helen Sanders will be coming to the Columbia Maryland to speak to National Capital Area Oracle Users Group. Helen is an interesting and dynamic speaker who will captivate you with tales from behind the keyboard.

Title: The Good, the Bad & the Ugly: A SQL Saga

Coding Standards? Good testing procedures? Data validation? Who needs them? We all do! Come and learn from some of the bad coding practices and sloppy SQL mistakes that I have seen (and maybe a few I’ve committed!). We’ll share a few laughs and most likely some groans, and also talk about how testing and more testing can help you check yourself.

Helen is an Assistant Director of Applications Development and Support Services in the Student Collaboration Center at Temple University. She is also an Oracle database developer and enthusiast.

Helen has been active in the Oracle Development Tools User Group (ODTUG) for several  years,  is  a graduate of their Leadership Program, served as the Program Coordinator for the 2016-2017 year, and is very active in their Database and Women in Technology Communities.

Helen obtained her M.Ed. at Temple University in 2013, and is currently pursuing an MS-MBA in Business Analytics at the Fox School of Business.

Helen is a track lead for the 2016-2017 Pennsylvania Banner Users Group conference(s), and will be a content chair for the Database track for Kscope18, and has served as a content reviewer for several Oracle Users Group’s conferences.
In her spare time Helen enjoys spending time with her husband, Joe, their blended family of six children, Steven, Julie, Sarah, Bryn, Kelly and Jillian,  and their two dogs-Charlie and Prince Eugene. You can read her tech-based blog at https://helenjsanders.com/ You can also follow Helen on twitter at https://twitter.com/HelenJSanders

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

Getting to know you, getting to know all about you. #infosec #windows10

Windows 10 has the capability to record everything you say, everything you type and everything you write. Okay, I have a fundamental problem with this. Microsoft implemented this feature in the Beta release of Windows 10 to help the product development troubleshoot problems and improve the product. The product development team loved it so much, they kept it in the production release of Windows 10.

I am not going to discuss Microsoft’s motivation for keeping this in the production product. I will give you my opinion: Information is leaking like a water from a broken colander, so why would we keep this around? Saving this information on your device and the cloud is a massive issue. Here is what you need to do, turn it off and remove the voice, writing, typing from the cloud.

Go to Windows Settings -> Privacy -> Speech, Inking, & typing. If you see Stop getting to know me then click on it and it will turn it off and remove all the data from your local drive. You also need to remove all the data from the cloud. So on that same screen “Go to Bing and manage personal info for all your devices” to clear the Getting to Know You Data from your Microsoft account.

This combined with the setting “Send Microsoft info about how I write” has me very concerned. So turn that off too. On the same screen click on “General” then the third item on the right, “Send Microsoft info about how I write to help us improve typing and writing in the future” Turn that off.

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

#quicktip #oracle #sqlcl logon.sql #PLSCOPE_SETTINGS and #PLSQL_WARNINGS

Quick Tip. My logon.sql file.

I don’t want plscope_settings and plsql_warnings set when I’m in production; but if I’m in my test / dev / sandbox environments, then I do turn them on.

This helps. Enjoy; this should be self explanatory.

set linesize 90
set pagesize 1000
col table_name format a35
col owner format a20
set timing on
--
DECLARE
 sInst varchar2(1);
BEGIN
 select upper(SUBSTR(instance_name, 1,1))
 INTO sInst
 FROM SYS.V_$INSTANCE;

-- test to see if this is a production instance
 -- all production instances start with P so ...
 -- if it's not a production instance set up
 -- session properties approiate for dev / test / sandbox.
 IF sInst != 'P' THEN
   execute immediate 'ALTER SESSION SET PLSCOPE_SETTINGS=' || '''IDENTIFIERS:ALL''';
   execute immediate 'ALTER SESSION SET PLSQL_WARNINGS=' || '''ENABLE:ALL''';
 END IF;
END;
/
--
define _editor=vi
SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER > "

-- setup aliases for sqlcl
alias tab=select table_name from user_tables;