The book I’m currently working on is a technical book on Database Application Security. While writing, I frequently find myself trying to explain something and I can’t quite come up with a good explanation. This normally indicates I don’t understand something well enough to explain it; therefore it goes into my “What I don’t know book.” (we can discuss that another day.)
So I’m working on a book, and get stuck. The first thing I do is write down what I don’t know in the form of a question. Case in point, working on the Unified Audit section and I wanted to explain, “how come unified audit does not immediately write to the database, you have to turn immediate write on.” Many years ago, I learned not to go with the first answer that comes to my mind. It may be right, it may be wrong.
To keep me moving forward on the chapter, I could either 1) start researching the answer and wind up going down a rabbit hole. Or 2) I can insert the question into the chapter in the form of a question.
<QUESTION> why does unified audit pause writing to the database? </QUESTION> Then scribble that same question into the notebook I carry with me.
I choose to use #2, because it allows me to stay focused on writing. I can then come back to the question, do my research and get an accurate answer.
Inserting and tagging questions is a great way to keep the focus on the work you are currently doing. You can research later, then maybe talk a walk and ponder just how your are going to answer the question.
I’ve been doing this a long time, and there two infosec errors that I keep seeing. Granting DBA to an application and people using the application account. The problem of granting DBA to an application account is compounded when people actually logon to the application account to work.
Oracle has the DBMS_PRIVILEGE_CAPTURE package that is now licensed to Enterprise Edition. It’s a powerful tool to fix over privileged accounts; yet when someone logs on as the application to do dba work, then all bets are off.
1) Don’t grant DBA to application accounts. Figure out what privileges the account needs and grant those privileges.
2) Don’t use an application account to do your work.
3) Use the DBMS_PRIVILEGE_CAPTURE package to analyze what privileges your users are using and dial back over privileged accounts.
When using ENCRYPTION_PASSWORD with expdp and impdp, your history file (ie .bash_history) will store the password in plain text and if you’re sending the password over the wire, your network better be encrypted.!!!!!
It’s been a wild and crazy year. I could write this about all the data breaches we had in 2018, that would be too depressing. Instead I’m going to focus my favorite cities
I filled up my passport and now need a new one, so I ordered the larger size one. The down side is, I need to apply for another Russian visa, when I still have time left on my current visa. I hit all my major goals this year, made some new friends, had the privilege to speak at three universities, and added a couple more favorite cities to my list of, I’ll stop here for a couple days and rest.
Those cities are:
Moscow – UPDATE Sadly Russia is now too dangerous to visit and I don’t see the situation changing in the near future.
Lawmaker Leonid Kalashnikov July 2018: re: Maria #Butina‘s arrest: “Let’s arrest an American here and that’s it… They arrested Butina, let’s immediately arrest some Amerikashka here.” Now we have the arrest of Paul Whalan in December 2018.
There is the Bolshoi, the Metropol, food, wine, spirited people, rich history, decorated walking streets, and great friends. One thing I learned is Russians are a warm and friendly people. If your lost, need help, or just curious, I’ve found Russians are always willing to step up and help a stranger.
Belgrade – This city has a deep history. The food, people, architecture, art, great friends, and streets you can walk on.
Helsinki – when it’s warm. The people, friends, and food, just don’t ask the lady in the elevator where the polar bears are. Trust me, I asked; she looked at me like I was a very strange person.
Sofia – I always put on weight whenever I visit Bulgaria. The food is to die for, the people are friendly, and the streets that were made for walking.
Of course, there is also, London, Paris, and Kraków.
See y’all in 2019. Lets make it better than 2018, that’s saying a lot. 🙂
Did you know when you type commands in sqlplus or sqlcl that include a password; if your network is not encrypted, the password is sent in the clear. In fact, all sql commands are sent in the clear to the database if the network is not encrypted.
Great for a man in the middle attack. Make sure your network is encrypted before you start configuring the Oracle database or sending sql statements for that matter.
<code> c##sec_admin > administer key management set keystore open identified by SecretPassword;
Us technical nerds have a way of talking to each other, mostly we understand each other, sometimes we don’t and frequently we throw out buzzwords, thinking everyone must know what we’re talking about. This paper is going to address the subject of blockchain so anyone with a non-technical education can understand what it is and how it works. You may not be able to develop your own blockchain after reading this paper, but you’ll have a good handle on what us nerds are talking about..
There seems to be some confusion about just what blockchain is. Many people I speak with automatically assume blockchain is Bitcoin. First off, Bitcoin (and other cryptocurrencies) are not blockchain and blockchain is not bitcoin. Bitcoin uses the secure data structure of blockchain to protect the data. Now because blockchain was described by Satoshi Nakamoto in 2008 to describe a Peer to Peer electronic cash system, and Bitcoin was the first application to utilize the security aspects of blockchain, this confusion is understandable. Now that we have established that bitcoin is not blockchain and blockchain is not bitcoin, lets address what blockchain is. Blockchain is a way of linking transactions using a timestamp and cryptologic hash into a linked list to make the data immutable. Yea’ that’s a mouth full. But if you do much reading on blockchain, there have been some enhancements to what we can do besides making data immutable. The power of blockchain lies in, if you change anything in the blockchain, you break the cryptologic hash.
Basic Blockchain with two blocks.
Here we see two blocks that have been joined by block 1’s current hash to block 2’s previous hash. This is a simple linked list. Data is linked to other data by pointing to a unique value of its neighbor. Also a linked list can not have any branches, so in this instance we can not have two pieces of data pointing back to the same unique value of a neighbor.
Let’s start taking this apart, to see what is actually happening and I’m going to start by defining what a hash is, what are the qualities of a good hash, and a tiny bit about how hashes work.
A hash is a one way cryptologic function that produces a value from some input. So if I pass to a hash function, “the rain in Spain falls mainly in the planes.” the SHA256 hash function will produce: 4f5960e9f8aa23073bd14dfe85cce5020530e15f1f7dc4231d16cceb01d09e70. Now if I change one letter of the text to “The rain in Spain falls mainly in the planes.” (just switching the t from lower case to upper case) The SHA256 hash function will produce: 5cadf57561ce0e294dd4c7b982be6ffad0c81281f0d6ab6fa64efccdfaf51061. As you can see two totally different results that don’t even remotely resemble each other. I have read from many respected sources, the SHA256 hash is guaranteed to always return a unique value. This is not exactly a true statement. SHA256 produces an output of finite length, however there are an infinite number of combinations that can be passed to a hash function. Eventually, someone will find a hash collision (when two different inputs produce the same output) but for all practical purposes, it will produce a unique output for any given input.
The last thing you need to understand about hash functions, you should never be able to take the hash and find out what the original input was. That is if I was to give you this hash from SHA256 5cadf57561ce0e294dd4c7b982be6ffad0c81281f0d6ab6fa64efccdfaf51061 you should not be able to figure out what the input is. You may already see the problem with this statement. Because I used this hash in a previous example, you can go back and read what my input was. Because we know the input and we know the hash we can run it through the function to see if the message has changed. If they stay the same, we’re golden, if not, the text was altered.
Difficulty: Tthe time to calculate a hash is amazingly fast, my last test was producing a SHA256 hash on a small input in 0.001001119613647461 seconds,. Yup, that’s pretty quick. So, we actually need a way to slow down the ability to produce a hash so we introduce a difficulty factor to it. We do this by creating an artificial requirement that the hash must start with one or more zeros. In reality you can use anything you want; however the current standard people are using, is starting the hash with 0’s. Each time we increase the difficulty, the time to calculate the hash increases exponentially. So with a difficulty of two zero, the average time to hash is 0.003002166748046875 seconds and a difficulty of three zeros the average time to hash is 0.2151656150817871. A difficulty factor of four and it took 200.69477248191833 seconds to calculate the hash. This is a pretty old computer I’m working on right now, so faster speeds are possible, but you get the point, as the difficulty increases, it becomes much harder to calculate a hash with the required difficulty.
The reason we are adding in a difficulty factor is to slow down the ability to rebuild the blockchain and defeat the security that is built, by linking the data (blocks) with a hash. So, if the speed to calculate the hash is slow, a potential attacker would need much more CPU power then the machines building the blockchain to change some data and then recalculate all the hashes in the blockchain, and then get ahead of the machines building the blockchain.
I promise if your not a techie, this is not that painful. What I want you to see is the technique that I used to calculate the hash with a given difficulty.
def f_calc_hash(self, data, nounce, difficulty):
s_curr_hash = sha256(data.encode()).hexdigest()
while not s_curr_hash.startswith(‘0’*difficulty):
self.nounce += 1
data = data + str(self.nounce)
s_curr_hash = sha256(data.encode()).hexdigest()
return s_curr_hash
This is actually pretty easy to read, we have a function to calculate the hash with the required difficulty. It takes the data we want to hash, a nounce that I’ll get to in a minute and our difficulty factor. It starts by calculating the hash and checks to see if it starts with the required number of 0’s. If it does not, then it adds 1 to the nounce, and puts that number at the beginning of the data we are hashing. Because we have now changed the data, we will get a different hash. We continue adding 1 to the nounce and testing the hash to see if it meets the difficulty requirements. If it does, great, return the hash, if not, keep adding 1 to the nounce and getting the hash again. There is no way you can figure out what nounce to use, therefore, the only way to get a hash with the required difficulty is to use brute force.
So a Nounce is just a variable we use to add to the data we are hashing to get a different hash. That’s all. Where the term nounce came from, I don’t know.
We also add a Timestamp to the transaction to prove when the transaction was created and also in some cases to make sure the transaction is unique and we can get a unique hash for each transaction.
Transactions all go into a Block. You can have one or more transactions in a block, the number is up to you and the requirements for your system.
So let’s use the simple example of a check register. These two transactions can go into a block to be added to the blockchain.
Check#
Date
For
Amount
Deposit
256
05 May 2018
Gas
50.00
.
05 May 2018
Payroll Deposit
.
5,700
Before we go much further, we need to explain how to weld blocks together using a hash that meets the difficulty requirements.
All blockchains start with a Genesis Block, this serves as the anchor to the blockchain by providing the first hash needed to weld the blocks together. There is one special thing about the genesis block, it does not have a prior hash in its header. There is one other thing about the genesis block you should understand, The data in it may not be of any value. It’s just a seed for the rest of the blockchain. When we have built the genesis block, we get a hash that meets the difficulty requirements and put that in a field called current_hash.The Current_hash is the hash value we get when we calculate the hash with the required difficulty factor using the calculated nounce.
So when we add in block#2, it copies the current hash from the genesis block into the previous hash field. We then hash the block to get the current hash and nounce that meets the difficulty requirements. By joining block# 2 to the genesis block, we now have a blockchain with two blocks. Every block we add to the blockchain, we repeat this process.
Why is the data structure so powerful? Let’s start with the assumption that we have a blockchain with 10,000 blocks. If someone were to change one piece of data, say in block 500, then the current hash in block 500 would not be right anymore. Let’s say our hacker is clever and calculates a new nounce to get a hash that meets the difficulty requirements. Well then the previous hash for block 501 would not match the new hash that was calculated. So, the hacker now has to calculate a nounce for block 501 and to get a hash that meets the difficulty requirements. Then, 502, and 503, on and on. It would require a lot of CPU to recalculate the entire blockchain. If we did not have a difficulty factor built into the blockchain, the speed to calculate a hash is pretty darn quick. That difficulty factor puts a speed limit on just how fast you can write to a blockchain. Pretty darn clever if you ask me.
Anonymity vs Known Users or Public vs Private blockchains. We have the very basics of what a blockchain is, now we can look at a couple of different types of blockchains. First there is a public blockchain where anyone can add data to the blockchain. The typical public blockchain is cryptocurrencies such as Bitcoin. Note: Cryptocurrencies are also frequently called shadow currencies. In cryptocurrencies, anyone can add blocks to the blockchain and make money. Here everyone has a copy of the blockchain and when you add to the blockchain, you provide a proof of work and other nodes in the blockchain check your work. If you met the required difficulty and the nounce is correct, then your block is added, that is unless another node has added more blocks then you. Because we can not have branches on a blockchain, the network uses the node that is longest as the valid blockchain.
When we say branches, that means two or more blocks using the current hash of a previous blocks as their previous hash.
So now that we understand what a public blockchain is, let’s explore what a private blockchain is.
In a private blockchain only computers (nodes) that are invited in can participate. Private blockchains can be used to store medical information that has regulatory protections, supply chain management, and trade between businesses.
In the public blockchain, we used proof of work to add data to the blockchain. In the private blockchain we use Selective Endorsement by using Endorsing nodes. Endorsing nodes are the machines that are authorized to add data to the blockchain. The endorsing nodes can be one node, many nodes, or all the nodes on the private blockchain.
When we have multiple entities on a private blockchain, not everyone is allowed to see all the data in the blockchain. We enforce this by using channels. If you are dealing with financial or healthcare data, you must control who has access to the data. When a government, business, or person joins a private blockchain, they subscribe to a channel. The simplest way to describe a channel is it is a standalone blockchain that is shared betw*een players on the system.
A member of a private blockchain can subscribe to one or more channels. In this example Org1 is subscribed to both channel 1 and channel 2. Org2 is subscribed to channel 1 and Org3 is subscribed to channel 2. Now, the beauty of this is, the data has been segmented. That is to say, nothing in channel 1 can appear or be written to channel 2 and nothing in channel 2 can appear or be written to channel 1. Each of these channels is in fact, a private blockchain.
What can we do with this? Glad you asked, because the information has not been changed, we can use the data in the blockchain to trigger Smart Contracts.
Smart contracts are a way to trigger an event after conditions are met. A simple example of a smart contract would be to send payment to a vendor. This would be accomplished with a simple if .. then .. else statement. In a purchasing blockchain a Smart Contract may exists that says, IF Shipment Received AND Shipment Verified AND Invoice Received AND Matching Purchase Order THEN Pay Invoice. Smart Contract are a good way to speed up the flow of business processes by embedding logic into the blockchain that all parties agree to.
Of all the conferences that I speak at BGOUG is in the top two for technical content, environment, and all around great people. I’ll let you guess the other one of the top two. Hint, it’s in Poland. 🙂
Well the Polish Oracle Users Group conference was quite a success. This year they held it in Sopot right on the Baltic Sea. Here are a few of the stand out things about this conference and POUG in general.
The Baltic Sea is cold. It’s just my nature, if there is water, I’m going to get in. When I saw that POUG was holding the conference in Sopot, the first item to make it into my carry on was my swimsuit. I decided to take an early morning swim Wednesday, running across sand that was like talcum powder I dove right in, swam out about 20 meters and decided this water is just effen cold. So, out of the water and into a hot shower to bring my body temperature back to normal. 🙂
When I arrived to the speakers dinner, it looked like a who’s who of people in the Oracle community. These are some of the sharpest minds in the world. Heli was there to speak about machine learning. Bryn was there to talk about wrapping your data in a secure shell. Roger Macnical was there to talk about full table scans and so many more. The educational opportunity was only limited due to having to pick between sessions that were running in parallel.
The food, I had lost about 40 kilos, and after a week in Sopot, I found 5 of those kilos that I had lost. Who knew they went to Poland. 🙂 Really, the food in Poland is fantastic.
The sessions, Timur Akhmadeev did a great presentation of “Defaults, bloody defaults.” That exposed some gaps in my knowledge. It’s always good to learn something new.
The networking, I had been chewing on a problem that I’ve been having with analytics on blockchain, and ran into Christian Berg who is the analytics guru in Switzerland. We don’t get to chat as much as I like; however at the after party, we dove into the problems with running analytics on blockchain data and think we may have come up with a solution. Once we run some test in the lab, and confirm you’ll read more. At POUG, you have the opportunity to chat with experts from all over the world and resolve your issues.
The panel session at the end of the conference. Why do I keep volunteering to sit on panels? This is an opportunity to get opinions on may subjects. This time, the discussion revolved around why learn the internals. Of course you should understand how Oracle works. Now some people take it farther than I do. So when I need to understand something a bit deeper I know there are people I can go to and ask difficult questions; like Kamal Stawiarski. If he does not know the answer; you know he will find the answer and help you out.
The overall organization and execution of the conference. Luiza Nowak rocked it yet again. Luiza is the face, brain, and muscle behind making sure POUG goes smoothly. I’m constantly amazed by her ability to organize a complex conference and keep calm.
The best speakers gift, having a beer named after me.
Thanks guys, I’m looking forward to coming back next year.
The Apache Struts 2 vulnerability may impact you. Proof of concept code has been released on gethub and is actively being discussed in underground forums. No plugins are needed for this exploit. All the attacker needs is put together a url that will give access to the Apache Struts installation.
Here is a list of potential Oracle products that could be vulnerable. (this list is not exhaustive and I have not had time to validate every entry on this list)
This vulnerability was discovered August 22, 2018, and I have not been able to find a patch for it. Please do your research, if you are using Apache Struts 2, then keep a close eye for the patch, and once the patch is released, install it.
Known Attacks. There are two proof of concept attacks published on gethub.com. (I’m not posting the links for good reason.) and there are two known active attacks going on in the wild. ISC SANS and Netlab 360 is tracking attacks.
To Do: 1) Block port 7001 internally until patched.
This is an easily exploited flaw, that allows a user with low level privileges ( connect with network access via Oracle Net) can completely hijack the Oracle database. Affected versions 11.2.0.4, 12.1.0.2, 12.2.0.1, 18
BGOUG: 16-18.11.2018 (planned) Blockchain a primer. There is a lot of confusion about the blockchain. Blockchain is not crypto currency, block chain is the one part of the technology that makes crypto currency secure. We’ll chat about the technology and how to implement the technology.
BGOUG: 16-18.11.2018 (planned) The application of blockchain technologies to build faith and trust in thecriminal justice system. I’m excited about this one. We are going to go through a case study of securing e-justice systems using blockchain technology.
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;
Let’s start with some key facts. I learned this from my High School Civics teacher who made us learn a bit about journalism along with studying the Constitution.
Who: The Polish Oracle Users Group, hosted by some of the most awesome people you’ll ever want to get to know. The young lady in the blue jeans is Luiza Nowak; what Luiza says goes, she’s “The Boss.”
This team gets a lot done, they bring in the top speakers from all over the world. They work hard so everything goes smooth, everyone learns, and everyone has a good time.
What: The annual Oracle Users Group Conference. We will be talking about everything from Machine Learning, to Secure Coding and Information Security.
Why: That’s a pretty silly question. For me, it’s the opportunity to share what I know with a wide audience. This is one of those conferences where you are not only going to do a serious amount of learning from over 20 of the top speakers in the world. You will also have the opportunity to expand your network. You will definitively have a good time, Kamil will see to that.
How: Get here by plane, train or automobile. I’m told that tickets have already been sold out. But double check with the website, things do change.
When you get here, be sure to bring your thinking cap, your beer mug, and your sense of humor. You’ll need em’. Zobaczcie się za kilka tygodni.
There is a mistake that I’m seeing frequently. Loading a raw data file into an encrypted database then leaving the data file on an unencrypted device.
After loading the data into the database; if you don’t need the data file anymore, you should do a secure delete on the file. If you are going to need the data file again, then move the data file to an encrypted device then do a secure delete on the old data file. Better yet, when you bring the data file down, save it straight to an encrypted device and work from that device.
I’m more than a little disappointed at people not being serious about information security. One of my customers asked me to help load data from a school system into an apex application I designed for some years back. The excel spreadsheet received from the school system has teacher names and social security numbers. The excel file was not encrypted in any way shape or form. I’ll be contacting the school system on Monday to resolve this issue. If it’s not resolved in a short period of time, I’ll be sending the data to their local news paper. This has got to stop.
Chatting with a friend this morning, we were talking about todo list and being overwhelmed by everything that needs to get done. After sharing with her mindfulness meditation, that helps me keep the “chattering monkeys” at bay and has improved my focus. I mentioned that I’ve gotten away from todo list and now write out the outcome I want from today. Coming up with an outcome for the day can be quite difficult; but once you get the hang of it; the outcome for today will be self evident.
Back on 3/1 I needed to bring this practice back into focus and wrote this note to myself.
In my outcome for the day, I also establish a performance goal for the day. This performance goal can be related to my physical performance while exercising and/or my performance around people. An example of this would be even though my chair is very comfortable, I will do twenty minutes on the rowing machine and lift weight for thirty minutes. It will also include what distance will I do in those twenty minutes, and what weights / sets will I do in my exercise.
My professional outcomes for the day, may be; Catch the problem in production, before the customer catches it. This way, the customer is not surprised when there is an issue. They get an FYI instead of, them sending my team an email saying there is a problem.
The beauty of this is, when you defined the outcome; what you need to do to get that outcome should become self-evident.
Just a short post, if you design, develop, maintain, or administer applications, you need to read this document. The Ten Most Critical Web Application Security Risks.
If you’re like me, you frequently have many environments open at the same time in putty, sqlcl, sqldeveloper, or other tools. This happened quite a few years back, I was switching between my dev, test, and production environments, doing some work, then the “Oh S^#t” happened. I shutdown production when I thought I was in dev.
You can change your prompt to show what environment you are working in; yea’ that works … but I’ve taken this a couple steps further. Out of habit, if I’m not actively doing work in production, I close it. Second, I changed to colors of my screens to tell me what environment I’m in at a glance. Test is green, dev is blue and production is RED.
This way, if I’m about to do any work in production a cold sweat breaks out and I ask myself, do I really want to do that? You can do this in putty, and sqlcl.
In sqlcl (on windows), bring up the property dialog, click on the color tab, and select your foreground and background colors. Then click OK.
In putty, bring up the configuration dialog. Load the configuration for the environment, click on color under the Window tree, select default foreground, then modify button and select your foreground color. Then select default background, then modify button and select your background color. Go back to session tree and click save.
There you have it, this will make things a lot safer when you have multiple environments open.
I recommend closing your production connection if you are not actively using it. You can’t be too safe.
Grab a cup of coffee or a cup of tea. This is not a short post; There is a lot to explain, and many point are repeated. You need to understand all the in’s and out’s of CBAC. However; once you have an understanding, CBAC really quite easy to impalement.
A lot about Code Based Access Control is not intuitive. In fact a lot of people I talk to are confused about what CBAC is and what CBAC is not. The basic premise of CBAC is that a subprogram of a package can only execute only using the privileges that have been granted to the package through a role.
NOTE: What I’m explaining is a multi-schema model. I’m intentionally keeping the model simple to show just the CBAC aspect of a secure architecture. This model is using three schemas, hr that holds data and hr_api that is going to hold packages that will access the data, and hr_decls that holds common types that are used across schemas. Also note, this is not the full secure architecture. There are a number of elements of my secure architecture I am leaving out to focus on CBAC.
Before we go too far, we need to understand some subtleties about roles. In order to grant a role to a package, the role must first be granted to the owner of the package with either admin option, delegate option, or you must connect as sys to grant the role to the package and the owner of the package must have created the role. Yea’ that’s a bit to digest, so let’s examine the three different types of role grants that must be made.
Three different ways to grant a role to a package.
#1 --As a user with create role privileges and the ability to grant
-- the required privileges to the role. ie: DBA
conn rob_dba@democreate role <role>;grant <privilege> on <object owner>.<object name> to <role>;grant <role> to <package owner> with admin option;conn <package owner>@<instance>grant <role> to package <package name>;
#1 GRANTING THE ROLE TO THE PACKAGE OWNER WITH ADMIN OPTION: Granting the package owner the role with admin option works, but the package owner can grant the role to other schemas. Using a user with create role privilege and the ability to grant the required privileges on the underlying objects (I normally use my dba account rob_dba). This is an unnecessary security risk. Like I said, the package owner can now grant the role to other schemas, thereby making the trusted path weaker. I’m sure there are use cases for granting the package owner the role with admin option; however that is a corner case and should be justified, not the norm and quite frankly, I can not think of a use case that would justify using this method.
#2 -- as package owner, create the role.the package owner must have
-- create role privileges.conn <package owner>@<instance>create role <role>;conn sys@<instance> as sysdbagrant <role> to package <package owner>.<package name>;
#2 USING SYS TO GRANT THE ROLE TO A PACKAGE: The package owner must have the create role privilege and the package owner must have created the role. In this method, we are creating the role as the package owner and connecting as sys to grant the role to the package. I hope there is no need to explain what a bad idea it is to use the sys account. There is a huge security issue, doing work as sys.
#3 --As a user with create role privileges and the ability to grant -- the required privileges to the role. ie: DBAconn rob_dba@democreate role <role>;grant <privilege> on <object owner>.<object name> to <role>;grant <role> to <package owner> with delegate option;conn <package owner>@<instance>grant <role> to package <package name>;
#3 GRANTING THE ROLE TO THE PACKAGE OWNER WITH DELEGATE OPTION: This is the preferred method to grant a role to a package. It appears the same as #1; however we are granting the package owner the role with delegate option. Using this method, we can grant the package the required role, but the package owner can not grant the role outside of it’s schema. This is the least amount of privileges needed to execute the task.
Remember, we want to operate with the least amount of privilege and still be able to do our job.
The hr_decls schema:
My hr_decls schema is used to define common types that will be referenced between schemas. This is not required for using CBAC, however it makes referencing common types between schemas much easier and makes maintenance simpler. Once this package is created, execute is granted to a role (exec_emp_decl_role) and that role is granted to any users that need to use it and not compile code against it. Mainly this grant is for testing purposes. Note: PL/SQL grant execute through a role or grant execute directly? If you are executing a pl/sql package using an anonymous block, you can pick up the privilege through a role. If you are compiling a pl/sql package that references a pl/sql package in a different schema, you must grant execute on the package directly.
conn rob_dba@democreate role exec_emp_decl_role;conn hr_api@demo-- create the decls package. This package is used to create types -- that are used between schemas.create or replace package hr_decls.emp_declauthid current_user as cursor emp_cur is select * from hr.employees;
subtype st_emp is emp_cur%rowtype; type tt_emp is table of st_emp index by pls_integer;end emp_decl;/-- usr1 (our test user) required this role to reference the package-- through an anonymous block grant execute on exec_emp_decl_role to usr1;-- hr_api requires a direct grant on the package hr_decls.emp_declgrant execute on hr_decls.emp_decl to hr_api;
Your privilege on/off switch starts with authid current_user.
create package hr_api.emp_select_pkgauthid current_user as
Hey, we are granting a role to the package, why do we need to grant privileges on the underlying objects directly to the package owner? In order to compile your package, you need to grant privileges on the underlying object to the executing schema.
If you use authid definer (that is the default authid. So if you don’t specify current_user or definer, the package is created with definer’s rights.), the package will pick up the privileges from the schema it resides in. If we grant a lot of privileges to the schema the code resides in, or heaven forbid, the code resides in the schema with the data, and there is a flaw in your code; the hacker owns your database.
By using authid current_user, the package inherits the privileges of the user executing the code. If the user does not have any privileges on the underlying data objects then subprogram in a package can only execute with the privileges granted to the package through a role.
Now let’s start setting this up from the beginning. Connecting as a dba, or another appropriate user that has the proper privileges, we need to create a role, grant privileges to that role, and grant that role to hr_api with admin option.
We are going to create the role hr_emp_sel_role. Then we are going to grant to the role select on hr.employees.
create role hr_emp_sel_role;grant select on hr.employees to hr_emp_sel_role;
Once we have these roles and grants let’s grant it to hr_api with delegate option.
grant hr_emp_sel_role to hr_api with delegate option;
Here is a niggle; you need to get your package to compile. The schema, in this case hr_api needs to be granted select on the underlying data. So, we are going to grant select on hr.employees to hr_api. I don’t really like granting privileges directly to a schema, but it’s needed for the code to compile. Using this grant, the code that selects against hr.employees can compile. But remember, the code we are writing will be authid current_user. We now have the underlying roles with the appropriate grants to start building our application.
grant select on hr.employees to hr_api;
There is one more role you need. This is the role that will be granted execute on the api package, that will inturn be granted to the user.
create role EXEC_EMP_SEL_API_ROLE;
Here is the rolls and all the grants we have done so far.
conn rob_dba@democreate role hr_emp_sel_role;grant select on hr.employees to hr_emp_sel_role;grant hr_emp_sel_role to hr_api with delegate option;grant select on hr.employees to hr_api;create role EXEC_EMP_SEL_API_ROLE;grant execute on hr_decls.emp_decl to hr_api;grant EXEC_EMP_SEL_API_ROLE to usr1;
grant execute on hr_decls.emp_decl to exec_emp_decl_role;
grant exec_emp_decl_role to usr1;
Now we need to connect as hr_api and start building the application. In my environment, hr_api is granted create procedure through a password protected role. We are creating one function in the package, sel_hr_emp_phone that returns an employees information based on the phone number.
conn hr_api@demoSET role hr_api_admin_role identified by My#Supper7Secret#Password2;-- note authid current_user.create or replace package hr_api.sel_emp_phone_api authid current_user as function sel_hr_emp_phone(p_phone hr.employees.phone_number%type) return hr_decls.emp_decl.tt_emp;end sel_emp_phone_api;/
We have created the package, now it’s time to grant the hr_emp_sel_role to the package.
grant hr_emp_sel_role to package
sel_emp_phone_api;
We’ve flipped on the privilege the package needs. So sel_emp_phone_api can only execute with the privileges granted to it from the role, or privileges inherited from the user executing the package. (you must understand, the package can still inherit more privileges from the user.) Let’s finish off by filling in the package details.
create or replace package body hr_api.sel_emp_phone_api as function sel_hr_emp_phone(p_phone hr.employees.phone_number%type) return hr_decls.emp_decl.tt_emp is ltt_emp hr_decls.emp_decl.tt_emp; begin select * bulk collect into ltt_emp from hr.employees where phone_number = p_phone; return ltt_emp; exception when no_data_found then -- <fixme> insert error handler. raise_application_error(-20000,''); End sel_hr_emp_phone;end sel_emp_phone_api;/
We’ve got all the objects we need together, now we need two more grants to run this. We need to grant execute on hr_api.sel_emp_phone_api package to the exec_emp_sel_api_role and we need to grant the role to a user to execute the api. I have a test user usr1 that is used to test various security configurations. Here is the the setup that has already been done.This test user is granted create session, exec_emp_sel_api_role and exec_emp_decl_role.
conn rob_dba@democreate user usr1 identified by AHotRedKotenok;grant create session to usr1;create role exec_emp_decl_role;grant execute on hr_api.sel_emp_phone_api to exec_emp_sel_api_role;-- so the user can execute the api through an anonymous block.grant exec_emp_sel_api_role to usr1;-- so the user can reference the hr_decls.emp_decl package from an -- anonymous block.grant execute on hr_decls.emp_decl to exec_emp_decl_role;grant exec_emp_decl_role to usr1;
Now we can test this out. Connect as usr1, and call the function. Let’s see if we get anything back.
March 21 – 22: I will be speaking at Utah Oracle Users Group Training Days (and getting some Spring Skiing in.) http://utoug.org/TrainingDays I will be speaking on Holistic Database Security and Secure Coding. My Holistic Database Security presentation has come a long way over the past ten years. As new attack vectors, mistakes, mitigations come out I update this presentation. So, if you’ve seen this presentation before, don’t worry there is a lot of new material in there. My Secure Coding Presentation goes through coding standards, common errors, and how to implement a trusted path for you data.
April 18: I will be speaking at Twin Cities Oracle Users Group on Oracle Database Vault and a Hybrid Holistic Database Security presentation that will be focused at DBA’s. Many DBA’s fear or don’t like Oracle Database Vault, because it changes the paradigm of how they work. We are accustomed to being the God of our databases. We will be looking at how to make Database Vault your friend, and customizing it for your needs.
I will be in Moscow Russia and Helsinki Finland May 18 – 26. Speaking at Oracle Users Group Finland. http://www.ougf.fi/index.php/en/ Again I will be speaking on Holistic Database Security and Secure Coding.
I will be in Moscow Russia, Belgrade Serbia, and Borovets Bulgaria from June 1 – 10. I’ll be quite busy, Speaking in Borovets Bulgaria. When the agenda is finalized, I’ll fill y’all in on my talks.
I’m not even sure how to approach talking about this. One customer with a complex application that is both financial and regulatory was given a backdoor into the system to manually make changes to data without audit or validation. To keep the internal auditors from learning of this, the backdoor was put on the test application server, the user changes one parameter and the backdoor points to production. The purpose of this was, if there was an issue with processing a transaction, the user could just go into this backdoor, manually change the data and continue processing.
I learned about it when the business unit was complaining that the application server was down. Imagine my surprise, after checking the production application server several times to find there was no issue (with production). We were doing work on the test application server; causing the users to have issues getting into their backdoor.
We wound up having a discussion with the business unit that uses this application and backdoor. When I brought it up, on why this falls into “worst practices,” the users got stressed (stressed is an understatement) about losing their backdoor.I realize they have a business need to make changes to the data; so we came up with a compromise. 1) access to the backdoor will be tightly controlled to two people. 2) every time the customer is forced to use the backdoor, they need to open a ticket with a complete description of why they used the backdoor and what data was changed. 3) we are documenting the customer use cases for the backdoor, and developing an application for the user to make changes that are required,addressing audit, business rules, and data quality. 4) Transactions that have been closed will not be allowed to be changed. 5) The backdoor will be going away in the near future and be replaced with an application that meets the business units needs. See #3. This issue has become a top priority, the existence of a backdoor gives me the hebegeebees.
Why is this a bad idea?
Removing audit controls. We are relying on the integrity of the user. We would like to think that only honest employees are hired, however; that is not always the case. This customer has several documented incidents where a user defrauded the customer of several hundreds of thousands of dollars. (gee, wonder why)
Audit controls not only protect the customer, the controls are also in place to protect the user. When a transaction is posted and there are questions about it; with a good audit trail the user can confidently say “this is what was done, and this is why I did it.” The auditor will have data to backup what the user stated and it stops there. However; if the audit trail is in question, then the auditor is going to keep digging; generally making everyone’s life stressful.
We have a issue with data quality. Business rules are used to validate the data that is entered. There are several instances in this database where status codes don’t match known codes. There are also several instances in this database where data that should be not null have null values, and there are quite a few transactions that are no longer in balance. To overcome the not null issue, the columns were defined as null. The financial transactions that are out of balance, well because the audit trail is in question, it’s going to require a forensic auditor to sort that out. These transactions go back several years. Even though, there is a financial close, a user can go back and change data on transactions that have been closed out.
Guys, it’s a really bad idea to give users a backdoor.
“I will judge you by how you treat the people who serve you.”
I’ve spent a lot of time on social media helping some friends with their travel issues. Mostly I give people little hacks that I learned the hard way. These hacks and rules can make a trip more pleasant and if you break some of these rules, then things can get downright nasty.
Over the next few weeks, we’ll talk about, how you dress, your attitude, luggage, your flight attendants, comfort, TSA, customs and immigration, and dealing with jetlag.
We can all agree that over the past few years, airline travel has become less than pleasant. The seats are smaller, the flight attendants and customer service people don’t seem to really care, and dealing with TSA can be challenging. What’s more, after a ten hour flight, you have a multi-hour layover before your connection to your ultimate destination. Yea’ is it any wonder nobody has anything nice to say about the airlines these days.
How you dress and your attitude is as important as remembering your passport.
Economy class has been overbooked again, some are going to get bumped, some are going to take their seat, and some are going to get upgraded. At the end of the day, we will have some people who are not happy and people who think they are lucky. Luck has some to do with the outcome, however you can improve the odds. Or worse yet, your flight has been canceled, there is a fight out tonight that because of the cancellation is fully booked, and there is a flight tomorrow. Are you going to get out tonight or are you going to be spending the night?
Before I get into that all important catching your flight, getting bumped, or getting upgraded story. Let me tell about a few things that have gone wrong with my wardrobe while traveling. About four years ago, I was traveling to Estonia. I had just lost about forty five pounds and had not replaced my wardrobe because I was still losing weight. When it was time for me to get into the body scanner, I had taken my belt off and was holding up my pants with one hand. I stepped into the body scanner, raised my hands and my pants wound up around my knees. Everyone had a good laugh, well maybe not everyone, I felt a bit exposed.
About a year later, I was heading to Helsinki from Washington DC, I had an eight hour red eye to London, a four hour layover, followed by a three hour flight to Helsinki. By this time, I had mostly replaced my wardrobe, and was wearing a new pair of Levis 501 blue jeans. These were the worst pants to wear, they were tight, but they looked good, and the bunched at the crotch. I spent the next seventeen hours tugging at my jeans, trying to get comfortable and relieve the pain.
Now my travel uniform is a Tommy Copper compression shirt (because I have problems with my left shoulder), a comfortable pair of linen pants, either a silk or cashmere sport coat, and comfortable shoes that I can easily slip on or off. This is not high fashion, but it presents nice, I don’t get exposed, and I’m not tugging at my cloths for hours on end. That’s a win win in my book.
Okay, back to our getting bumped situation. A few year ago, I don’t remember what country I was in, I did not speak the language, and I just had a less than productive conference. I arrived at the airport two hours early, got through passport control and made my way over to the boarding area. There were two very long lines at the gate, noone was sitting down waiting to board.
A stone faced lady behind the counter was listening to people explain their situation, she would shake her head, say a few words a dejected customer would walk away muttering. I did not speak the language, but I could tell my flight had been canceled, my blood pressure was moving into the red, I was tired, and needed to get home. After what seems like hours watching people walk away either dejected or angry. (More than a few people wound up raise their voice to the stone faced lady. ) Finally the gentleman in front of me was chatting with her, I would see our stone faced lady was not moved by his predicament, she said something, shook her head and he walked away. It’s my turn, I really hoped she spoke english, I mustered by biggest smile, let my southern accent kick in, and as I was handing her my passport, I mentioned how much I enjoyed one of the local dishes and how much I appreciated the people in her city being so friendly and hospitable. This seemed to crack the stone face, she smiled and told me my flight had been canceled, but there was another flight later that evening it’s full and there is two flights tomorrow. She would see what she could do to get me home. As she was typing away, we made small talk about the food, what sites I got to see, why was I in town, you know, small talk. In all she was very pleasant, she even chucked at my southern accent. She handed me a boarding pass, I had been upgraded me to first class on tonight’s flight. I thanked her in her language (I mispronounced the word) our now friendly customer service representative thanked me for my feeble attempt at trying to speak some of the language. I then made my way to the business class lounge to have a glass of champagne and wait for my flight home.
What was the hack I used. First let’s consider the passengers before me who walked away dejected. Everyone of them, was dressed for comfortable travel, basically looking like they did not care how they looked. Yea’ I’m seeing a lot of people like that these days. Secondly, everyone approached her as it was a confrontation. Okay, her expression and body language told everyone that she was ready for a confrontation, that did not help.
I was in my “standard travel uniform.” Not dressed for a business meeting, but dressed like I actually cared about my appearance. Secondly, I recognized she had the power to either get me home or let me spend another night there. I smiled, was friendly, and said nice things about “her” city. On a side note, most people I have met around the world are very proud of their country and city. Recognizing and respecting this one thing will carry you a long way. Then after she helped me, I thanked her in her language. Really, it’s not a hack, I set myself apart from my fellow passengers. Someone once told me, if you want to be part of the one percent, you have to do what the other ninety nine percent don’t do. To set yourself apart you can start by looking like you actually care about your appearance and having good manners.
Now, picture yourself as the customer service representative, you have very few options to help the line in front of you. You have several seats on tomorrow’s flight and one maybe two first class seats for tonight’s flight. The person in front of you has an attitude, looks like they don’t care about their appearance, and is blaming you for things going wrong. Then you have someone who comes up, is friendly, cares about his appearance, and understands, you have no control over a flight being canceled. Who gets the flight tomorrow and who gets a first class upgrade on tonight’s flight?
I’ve been working on putting together some performance test for my secure coding talk coming up at Hotsos and encountered something I can not quite explain. This test case does a bulk select into a type and returns the type to the calling program. When I execute the code through the API, I get 1,100+- PGA Memory Operation Events. When I don’t use the API, and just use straight pl/sql I get 600+- PGA Memory Operation Events.
First we need to build two schemas and one test user. I’m keeping these pretty simple. HR_CODE is to hold business logic. HR_API is to hold the API for the HR schema. The user executes the code in the business logic schema. Here is the basic architecture. NOTE: this is not the full Secure Architecture implantation in this test case. Code Based Access Control and accessible by has not been implemented in this test case.
create user usr1 identified by x;
create user hr_code identified by x;
create user hr_api identified by x;
--
grant create session to usr1;
grant alter session to usr1;
-- create the required roles.
create role hr_big_table_all;
create role hr_big_table_sel;
-- create a test table in the hr schema
create sequence hr.big_table_seq;
create table hr.big_table (id number not null,
c number not null,
d number not null);
-- setup permissions.
grant select on hr.big_table to hr_big_table_sel, hr_api;
grant select, update, insert, delete on hr.big_table to hr_big_table_all;
grant hr_big_table_all to
grant hr_big_table_sel to usr1;
-- populate the table with 1M rows
insert into hr.big_table (
select hr.big_table_seq.nextval,
ceil(sys.dbms_random.value(0,10)),
sys.dbms_random.value(-1,5000)
from dual
connect by rownum <= 1000000);
--
commit;
-- add in the constraints and indexes
alter table hr.big_table add constraint
big_table_pk primary key (id);
--
create index hr.big_table_idx1 on hr.big_table(c);
-- analyze the schema
exec sys.dbms_stats.gather_schema_stats(ownname => 'HR');
-- create the decls package. this is used so all schemas can
-- reference common definitions.
create or replace package hr_decls.big_tab_decl as
cursor big_tab_cur is
select id,
c,
d
from hr.big_table;
subtype st_big_tab is big_tab_cur%rowtype;
type tt_big_tab is table of st_big_tab index by pls_integer;
end;
/
-- grant execute on the decls package to expose definations
grant execute on hr_decls.big_tab_decl to hr_api, hr_code, usr1;
--
-- create the api packages
create or replace package hr_api.big_tab_api
authid current_user as
function sel_hr_big_tab_range(p_low number, p_high number)
return hr_decls.big_tab_decl.tt_big_tab;
end big_tab_api;
/
-- grant execute on the api to the code schema
grant execute on hr_api.big_tab_api to hr_code;
--create the package body
--
create or replace package body hr_api.big_tab_api
as
function sel_hr_big_tab_range(p_low number, p_high number)
return hr_decls.big_tab_decl.tt_big_tab is
ltt_big_tab hr_decls.big_tab_decl.tt_big_tab;
begin
select id,
c,
d
bulk collect into ltt_big_tab
from hr.big_table
where d between p_low and p_high;
return ltt_big_tab;
exception when no_data_found then
-- <fixme> insert error handler
raise_application_error(20000,'sel_hr_big_tab_range');
end sel_hr_big_tab_range;
end big_tab_api;
/
-- create the code package. this holds the business logic
-- to create a shell around the API.
-- you will notice, no business logic has been added in
-- at this point.
create or replace package hr_code.sel_perf_test
authid definer is
function sel_hr_big_tab_range(p_low number, p_high number)
return hr_decls.big_tab_decl.tt_big_tab;
end sel_perf_test;
/
create or replace package body hr_code.sel_perf_test is
function sel_hr_big_tab_range(p_low number, p_high number)
return hr_decls.big_tab_decl.tt_big_tab is
ltt_big_tab hr_decls.big_tab_decl.tt_big_tab;
begin
ltt_big_tab := hr_api.big_tab_api.sel_hr_big_tab_range(p_low => p_low,
p_high => p_high);
return ltt_big_tab;
end sel_hr_big_tab_range;
end sel_perf_test;
/
-- grant execute on the business logic code to the user
-- The user only has one way to get to the data, that is
-- through the business logic.
grant execute on hr_code.sel_perf_test to usr1;
-- test the user executing the business logic code.
conn usr1/x@demo
ALTER session SET timed_statistics=TRUE;
ALTER SESSION SET TRACEFILE_IDENTIFIER = "perf_test_api";
alter session set sql_trace=TRUE;
alter session set events='10046 trace name context forever, level 12';
-- test it.
declare
ltt_big_tab hr_decls.big_tab_decl.tt_big_tab;
begin
-- ltt_big_tab := hr_code.sel_perf_test.sel_hr_big_tab_pk(p_id => 100);
-- ltt_big_tab := hr_code.sel_perf_test.sel_hr_big_tab_value(p_value => 4);
-- for i in 0 .. 50
-- LOOP
ltt_big_tab := hr_code.sel_perf_test.sel_hr_big_tab_range(p_low => 50,
p_high => 900);
-- END LOOP;
end;
/
-- to run the next test, the user will need select on hr.big_table.
conn rlockard@demo
grant hr_big_table_sel to usr1;
conn usr1/x@demo
ALTER session SET timed_statistics=TRUE;
ALTER SESSION SET TRACEFILE_IDENTIFIER = "perf_test_noapi";
alter session set sql_trace=TRUE;
alter session set events='10046 trace name context forever, level 12';
declare
ltt_big_tab hr_decls.big_tab_decl.tt_big_tab;
begin
-- for i in 0 .. 50
-- LOOP
select id,
c,
d
bulk collect into ltt_big_tab
from hr.big_table
where d between 50 and 900;
-- END LOOP;
end;
/
