Kamil is passionate about IT, Oracle Databases, and information security. Kamil will be talking about how he got into IT, what challenges he’s facing, and what it takes to succeed. Tune in Friday March 27th at 1430 (GMT-5)
It’s been a year long process now the book is finally been released. There are a few things I would have written different and a few other subjects I would have liked to cover. Perhaps that will come in my next book or future posts.
In this book we cover Secure Coding, setting up Encryption, and audit. We also dive deep into performing privilege analysis.
Schema only accounts. There is no good reason for anyone to connect to an application schema as the owner. In Oracle 18c, we now have schema only accounts. Okay, I had to double check if this was available on 12c this morning. Yea’ it did not work.
Now in 18c, we have the schema only account. Now, just what is a schema only account. It is an account that can hold database objects and there is no password to connect to the account. Therefore, to do maintenance on the account, you need to connect through a proxy user.
To connect to the schema only account, use <proxy user>[<schema user>]. In this case, we are already connected to sqlcl, so we’re going to use conn rlockard[test_data]@orcl. Once connected, the account is limited to the privileges to what was given to the schema only account. In this case, the schema only account is granted connect and create table. So, when we try to access sys.dba_objects, we get an ORA-00942 error.
You can maintain an application schema and nobody needs to connect at the application owner.
Make sure your private information is not exposed. Your disk still has all your information even if you delete the data. When you delete information from your disk, what happens is the index pointing to your data is deleted, your data is still on the disk. If you’re like me, you have photos, documents, and emails. I don’t know about you, but I don’t like people reading my private emails to friends, customers, and acquaintances.
Blancco and data recovery company Ontrack performed a study of 159 disk drives purchased on ebay. Ontrack recovered information from 42% of the drives. Now, personally I don’t sell my used equipment; however you might do that. There is an easy and safe way to sell your old devices and not worry about exposing your information to strangers.
I use the free version of CCleaner. (Note: I have not received any compensation for this, it’s just the tool that I use) CCleaner has the ability to securely delete information on the disk and also keep your disk from getting cluttered with junk that windows does not clean up. Personally I run this about once a week.
When you select wipe free space, You’ll get a popup window that will tell you the process will take a long time. So, this is not something you want to run on a regular basis. When I’ve run it in the past, it has taken three days to run on the 1T drive in my laptop.
Once you’ve installed CCleaner, select Options, then under settings select the Secure Delete and in the pulldown select how secure you want your deletion. I use Advanced Overwrite 3 pass, this is pretty secure. This option will overwrite your deleted files three times with random data and if you select wipe free space, it will overwrite your free space three times with random data.
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.
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.
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()
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.
|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.
POUG: 7-8.09.2018 (booked) PL/SQL Secure Coding Practices
ECOUG: 18-19.09.2018 (booked) Holistic Database Security
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 the criminal 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.
"'ON COMMIT REFRESH'" OCR,
"'INHERIT PRIVILEGES'" IPRV,
"'QUERY REWRITE'" QR,
FROM (SELECT R.GRANTEE "GRANTEE_TO",
FROM DBA_TAB_PRIVS T,
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',
'ON COMMIT REFRESH',
ORDER BY TABLE_NAME;
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.
This is an easy thing to fix.
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@demo create 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 sysdba grant <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: DBA conn rob_dba@demo create 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@demo create 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_decl authid 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_decl grant execute on hr_decls.emp_decl to hr_api;
create package hr_api.emp_select_pkg authid 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@demo create 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@demo SET 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; /
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@demo create 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.
conn usr1@demo declare ltt_emp hr_decls.emp_decl.tt_emp; begin ltt_emp := hr_api.sel_emp_phone_api.sel_hr_emp_phone( p_phone => '+1.800.555.1212'); end; /