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

#GDPR – RIGHT TO ACCESS. Security is a feature #3 Right to Access Part 1 of 2

The GDPR Right to access can get a bit complicated as it covers a few things that provide some challenges for us.

What is the purpose of the processing? Lets face it, we process data on people for a number of reasons.

First we are going to use the example of an online storefront. When a person places an order, a number of things happen with that person’s data. 1) Is there payment information accurate? 2) Do they have a store credit? 3) Does that person get a discount? 4) The parts that were ordered need to be shipped. 5) Store a history of the persons order, so the next time they want to order something, the system can make recommendations based on past orders.

If the shipping is being done by a third party such as Fedex or UPS, the person’s data is going to be transmitted to the shipping company. So now a person’s data is being held at both the online vendor and the shipping company.

What is the category of the data being processed? For the online storefront, we have payment, order and shipping information. For the shipping company, there is shipping and value information.

The recipients or categories of recipient to whom the personal data have been or will be disclosed, in particular recipients in third countries or international organisations. So in this case, let’s say that the package was shipped by FEDEX and let’s assume FEDEX data processing is done in the United States. (NOTE: this is an assumption, not a statement of fact.) Anyway the person’s shipping information has now left the EU for the United States of America. From the perspective of the online store vendor, the shipper will need to address Article 44 of the GDPR. I’ll get to Article 44 later.

Where possible, the envisaged period for which the personal data will be stored, or, if not possible, the criteria used to determine that period. So, how long will the data be stored? Certain types need to be kept for a defined period of time. ie. Financial information may need to kept for five or seven years that is defined by either applicable law or regulation. Other types of information may be kept for very short periods of time. I once worked on a system where the data was only resident for thirty days. This system packaged up the data and sent it to downstream system for further processing. Once the data was sent downstream, it was no longer needed. But, once the data was sent downstream, we would need to track what downstream systems received the personal data.

The existence of the right to request from the controller rectification or erasure of personal data or restriction of processing of personal data concerning the data subject or to object to such processing. You should be seeing a pattern here. It is going to be critical that we identify all PII (Personally Identifiable Information) in our systems. If a person identifies information we hold that is inaccurate, there needs to be a process in place to correct the information. If a person wants their information removed from our system, there needs a process in place to remove the information without corrupting or compromising the integrity of the system. Reference the Right to be forgotten and Article 17 of GDPR.

The right to lodge a complaint with a supervisory authority. We need to get into the definition of “Supervisory Authority.” I’ll address that in a later post. What kind of complaints can we expect? And what is the process to resolve those complaints? We need to spend time developing the process to address data complaints.

Where the personal data are not collected from the data subject, any available information as to their source. We feed downstream systems, and to be honest, there are a number of companies that sell our personal data. Say your company purchases mailing and phone list from a series of companies, you are now going to need to track the source of that data, so when the question arises, and it will; you can answer the question. What was the source of the data?

More to come.

#SQLDeveloper Unit Testing

I’ll get back to the GDPR series tomorrow.

Can I get a show of hands, How many people do unit testing? I’ve been in a lot of shops where unit testing and code reviews have been passed up in the name of Agile. First off, this is a misunderstanding of what Agile is. You do need to do Unit Test when doing Agile and code reviews are still highly recommended. Because this is not a post on Agile, that’s all I’ll say on that.

I’ve been working on a fairly complex problem for the past few months, that involves advanced analytics, and some simple test against a data set. This wound up being several thousand lines of code that had to be fast, accurate, secure and stable. Here is a shameless plug for SQL Developer, there is an awesome and flexible unit testing module built in to it. It’s quite easy to setup, and give you testing that is repeatable. Jeff Smith has a good write up on setting up and using Unit Testing. Oh, and did I mention, it’s free! Thanks guys.

So after building all my code, I was able to design some test to exercise all the procedures and functions for both successful execution and generate exceptions; so I know my code and deal with any exceptions that may come up.

If any test fail, the entire test fails. When all the test pass, than the testing has past. Really this made my job a lot easier. Identifying what code has bugs is easy, knowing what the input is, what is expected and what failed is easy.

Now that unit testing is complete, integration testing can start. Life is good.

#GDPR is coming Security is a feature #2 Data Masking

#GDPR is coming Security is a feature #2 Data Masking

In the context of GDPR, data masking should be done so that the all the data attributes on a person should not be able to define that person. Most of us know this, but we rarely articulate it; as you add more attributes to data it becomes much easier to define a person.

Use case: my full name is Robert Price Lockard, but let’s say there are a whole bunch of Roberts’ in the universe, therefore Robert does not uniquely identify me. Now let’s start adding attributes.

Let’s go further. I live in the United States of America; that narrows it down even more. Here is the frequency distribution of the top 10 names in the USA. So, I’m one of 5,497,484 Roberts’ in the US.

Rank Name Frequency
1 John 7,556,152
2 Marry 7,474,295
3 James 5,714,116
4 Robert 5,497,484
5 Michael 4,942,065
6 Christopher 4,747,669
7 William 4,665,950
8 Joseph 4,619,701
9 Elizabeth 4,270,062
10 Richard 4,109,367


I am a Pilot, okay, we’ve narrowed down the universe of Roberts’ to Roberts who are a pilot. There are still quite a few Roberts’ out there who are pilots.

Okay, so we have this big universe of Roberts’, how about we add my birth date of May 26, 1960. (Yea’ I’m pretty old). Now we are starting to narrow it down a bit, but in 1960, Robert was a very popular name and still is, so there are still quite a few Roberts’ in the population of Pilots and born May 26, 1960.

I live in the state of Maryland; we are narrowing down down the universe of Roberts’ even more. Say I purchase a lot of stuff from Aircraft Spruce and Specialty Company that would only be used for a 1948 Ryan Navion. With this information, you can likely make an educated guess to uniquely identify me.

Now let’s add in my zip code (postal code) of 21060. You just identified me. This is where things start to get tricky (or interesting depending on your point of view.)

Robert — there are a bunch

Pilot — there still are a bunch

USA — fewer, but still a bunch

Date of Birth — narrowed it down, but still not enough info to identify me.

State of Maryland — narrowing it down quite a bit, and most likely take an educated guess.

Zip code 21060 — nailed it, now you know who I am..

Now when we start thinking about masking data in the context of GDPR, we are also going to need to look at the type of business we are dealing with. If you are a bank, you may be able to mask down to zip code, this is because the vast majority of people in a zip code (postal code) have bank accounts.

But if you are a company like Aircraft Spruce and Specialty company (that gets a lot of my money) you should mask zip code and even go as far as masking the state. Why, because if you look at my purchase history from Aircraft Spruce, you would be able to determine the type of aircraft I own. And because my aircraft is pretty rare (I don’t know of any other 1948 Ryan Navions’ in the state of Maryland) it would be easy to determine who I am based on my purchase history.

Why would we mask data anyway? If you are using production to refresh lower environments; then you really need to start masking your data. This has as much to do with GDPR and your overall security profile; because guess what; when attacking a system only an amateur would go after your production system first. A hacker is coming for your DEV and TEST systems first, these environments are real noisy and it’s quite easy to hide in them for months on end. So if you are not masking your data in lower environments, then start doing it now. The other reason is under the “right to be forgotten.” It would be much easier to clean your production data of a person then to have to go through all of your lower environments to find all instances of a person to clean.

—– FOOTNOTE——

Name Frequency Distribution

#GDPR is coming Security is a feature #1

Security is feature. Over the next few weeks, I’ll be looking how some of the issues on how GDPR will be impacting us as technical professionals.

There are a lot of things that I find technically interesting in GDPR such as “Right to be forgotten.” How do we track all instances of a person we have in our systems? When we delete a person from our system, what about the artifacts in backups, and archive logs? We can’t go back and remove a person from our backups.

If a person made a transaction, and that person wants to be forgotten. We should address replacing the person to be forgotten with a generic person. Do you keep sex, age, postal code tied to the transaction details? The generic person attributes need to be addressed by your organization.

If a person downloads personal information to their workstation; we need to track that and ensure the data is scrubbed from the workstation. Therefore audit needs to configured so we know who has accessed the person’s data.

Yet another Phishing Attack #DontClickThatLink #Infosec

So I get up this morning, check email and what is waiting for me. A phishing email. Okay lets do a little analysis to see how to detect this really quick.

1) My paypal account is not linked to this email address, so that’s a big give away.

2) The from line is just amateurish. It does not even say paypal.com

3) When I hover the mouse over the link, I can see the actual text of the url in the bottom left had corner. Yea’ I’m pretty sure paypal does not use a .army address.

Phishing1

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