Call: +44 (0)7759 277220 Call
Blog

Pete Finnigan's Oracle Security Weblog

This is the weblog for Pete Finnigan. Pete works in the area of Oracle security and he specialises in auditing Oracle databases for security issues. This weblog is aimed squarely at those interested in the security of their Oracle databases.

Oracle Can Generate 6 Password Hashes When a User is Added or Password Changed in 12.1.0.2 and Above

In a 12.2.0.2 database it's possible that Oracle generates 6 different password hashes for one password for one user under certain circumstances when a password is changed or created (user is created). I will layout the 6 different ones first and then for the rest of this post I will focus on one of them; i will tell you which one i want to focus on shortly after i have laid out all six generally.

The normal wisdom in a 10g database would have been that Oracle generates a single password hash for each user when a user is created or it would update that password hash when a users password was changed. This was the password hash stored in SYS.USER$.PASSWORD column and this uses the DES based HASH. The username is the SALT and is concatenated with the PASSWORD and then encrypted with DES CBC using a standard key and once the first round is complete the process starts again using the last input vector as the new key. This means that whilst an encryption algorithm is used (DES CBC) it is actually a one way HASH as there is no way to decrypt the password. The standard method to crack this is to use a cracker and either use a dictionary based approach - i.e. test each password generated from a dictionary of words and compare the generated hash with the one stored in the database. Another possibility is to use brute force; i.e. try a password of A, then B and then C and then AA, AB, AC... and AAA, AAB, AAC and so on until all possible passwords have been tried for the keyspace. A simple PL/SQL password cracker is available here. This is an older version of my PL/SQL cracker; version 1.5; if you have been on two day my training class - How to perform a security audit of an Oracle database - then you would have the latest version which is faster and has more default passwords and more features - A benefit of attending my classes is to get all the latest scripts and tools. I will be teaching this class in my home city of York at the beginning of July; come along! I may release the later version of this cracker on here soon; so watch out for a blog post on that.

So, 10g database, thats one password hash. When Oracle database 11.1 was released Oracle added case sensitive passwords and the new (At the time) SHA1 password hash. This is stored in SYS.USER$.SPARE4 and is identified as the "S:" string. This is just SHA1 on the actual password and with the addition of a SALT to make the password that is stored different to other hashes of the same password. So if i set the SYS password to TEST and then the SYSTEM password to TEST even though the hash is only of the password the hash is different as Oracle generates a random SALT each time a password is hashed. This SALT is also stored in SYS.USER$.SPARE4 with the hashed password. I created a simple SHA1 hash test back in 2007 when 11.1 was released. This script is sha1.sql and is here.

In 11.2 this situation continued; so that DES and the SHA1 hashes are available in SYS.USER$. When 12.1.0.1 was released an additional password hash was added in SYS.USER$.SPARE4 - this one identified by the "H:" - This is an HTTP Digest for use in web based applications authentication. In 12.1.0.1 this could not be turned off; so you got a HTTP digest whether you liked it or not. In 12.1.0.2 Oracle changed the HTTP digest slightly if a multi-tennent database were created. In 12.1.0.1 the hash for common users (default users as well as ones created by you) was visible in the PDB; in 12.1.0.2 this is now only visible in the CBD for common users; in a PDB the HTTP digest is still available for LOCAL users. In 12.2.0.2 the HTTP digest is not created by default anymore BUT can be created with a new additional syntax - I will come back to this in a later post just about this hash. So as of Oracle 12.1.0.1 there were 3 core password hashes.

In 12.1.0.2 added the new SHA2 password algorithm. This is a combination of the PBKDF2 multiple rounds and then a final SHA2 hash. As with the SHA1 in 11.1 this password hash is stored in SYS.USER$.SPARE4 and is the "T:" password hash. Also as with SHA1 in 11g the SALT used is also stored in the same column with the hashed password. More in a later post about this algorithm. So as of 12.2.0.1 whilst the HTTP Digest is normally turned off and the default is that the DES hash is not generated it is possible to get all 4; either through an upgrade or because you use the syntax to create the HTTP digest or because you have set the network authentication in the sqlnet.ora to default to an earlier setting to get the DES hash also.

OK, but thats just 4 password hashes what about the other two. Well the 5th is generated by Oracle internally and not stored in a public place where you can view it. If an account is a default account then every time a password changes (CREATE or ALTER) then Oracle creates an un-salted SHA1 hash and uses it to compare with an internal list to see if the account is a default and whether the password is a default password. Oracle does this to detect default passwords. The hash is not stored as far as I can see BUT the accounts status is updated to indicate that the password is a default. I will come back to this in a more detailed review in another post soon. So this is 5 password hashes generated in 12.1.0.2 and above.

The one i want to focus on for the rest of this blog is the 6th password hash created for a user. This hash is only created if you run a DDL trigger on CREATE and / or ALTER or generally an un-constrained DDL trigger. If you collect the ORA_DES_ENCRYPTED_PASSWORD trigger attribute in the DDL trigger for a password change then this trigger attribute is populated. As the name implies it should be a DES hash or DES encrypted password. If it is indeed encrypted then that also implies decryption. Lets have a look and test the ORA_DES_ENCRYPTED_PASSWORD trigger attribute.

First I installed PFCLATK our Oracle audit trail toolkit into my Oracle 12.2.0.1 database:


PFCLATK: Release 1.7.2.0 - Production on Wed Jun 13 18:07:26 2018
Copyright (c) 2009 - 2018 PeteFinnigan.com Limited. All rights reserved.

SECTION-[1] - Remove existing schemas and users
[1-1] Drop The ATK Admin user
[1-2] Drop the ATK Reports User
[1-3] Run schema drop script
SECTION-[2] - Create the Schema owner ATK (Functional owner)
[2-1] Create ATK Schema
[2-2] Perform ATK Grants
SECTION-[3] - Create schema owner ATKD (Data owner)
[3-1] Create ATKD schema
[3-2] Perform ATKD Grants
...
SECTION-[17] - Install the user policies
SECTION-[18] - Install the user factors
SECTION-[19] - Install the PUL jobs/policies
SECTION-[20] - Clean up...
[20-1] Purge the PFCLAudit Audit table
[20-2] Print out existing audit, except OLS and DV
SQL>

This gives me a very detailed policy driven audit trail in my database that allows certain actions to be raised as exceptions and alerts fired. We are not going to explore the audit trail in this blog post but i will come back and discuss this toolkit in a future blog post. We use it to help customers through consulting engagements develop and deploy a rich policy driven audit trail with alerts and escalations quickly and easily. Currently it's a PL/SQL based command line tool but a GUI is coming soon to allow customers to manage, choose and design their own policies or to amend or simply use ours. There will be an admin GUI and a dashboard to allow you to monitor multiple databases. The toolkit can be deployed to a target database to audit it or it can be be deployed to a central database so that all target databases can send their audit trails to it for centralised storage and reporting. More soon here in the blog on the toolkit; but if you are interested now to have us help you design and deploy very useful audit trails email info@petefinnigan.com for availability and costs.

We installed PFCLATK because it already knows how to collect the ORA_DES_ENCRYPTED_PASSWORD field when a user is created (password first set) or when the password is changed. So lets create a user called TEST and set his password to A:


SQL> create user TEST identified by A;

User created.

SQL> col err_sql for a40
SQL> col ora_dict_obj for a40
SQL> col ora_des for a32
SQL> set lines 220
SQL> select err_sql,ora_dict_obj,ora_des from atkd.pfclatk_audit where ora_des is not null;

ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ---------------------------------------- --------------------------------
create user TEST identified by * TEST a0fe3035825f3637aad3b435b51404ee

SQL>

This doesn't tell us much so far. Lets change the password to B, then C, then D and see what happens:

SQL> alter user TEST identified by B;

User altered.

SQL> alter user TEST identified by C;

User altered.

SQL> alter user TEST identified by D;

User altered.

SQL> select err_sql,ora_dict_obj,ora_des from atkd.pfclatk_audit where ora_des is not null;

ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ---------------------------------------- --------------------------------
create user TEST identified by * TEST a0fe3035825f3637aad3b435b51404ee
alter user TEST identified by * TEST a6b47b9609ea127baad3b435b51404ee
alter user TEST identified by * TEST ce0beda1542358c2aad3b435b51404ee
alter user TEST identified by * TEST 5a7c34dd04a4c3adaad3b435b51404ee

SQL>

Now we are getting somewhere. As you can see the first 16 characters of the DES encrypted password change as each new password is set BUT the last 16 characters do not. This implies that a short password is only encrypted into the first 16 characters (8 bytes) and a longer one into the next. But before we explore that rollover what if we set the same 4 passwords for the user ORABLOG:

SQL> alter user ORABLOG identified by A;

User altered.

SQL> alter user ORABLOG identified by B;

User altered.

SQL> alter user ORABLOG identified by C;

User altered.

SQL> alter user ORABLOG identified by D;

User altered.

SQL> select err_sql,ora_dict_obj,ora_des from atkd.pfclatk_audit where ora_des is not null;

ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ---------------------------------------- --------------------------------
create user TEST identified by * TEST a0fe3035825f3637aad3b435b51404ee
alter user TEST identified by * TEST a6b47b9609ea127baad3b435b51404ee
alter user TEST identified by * TEST ce0beda1542358c2aad3b435b51404ee
alter user TEST identified by * TEST 5a7c34dd04a4c3adaad3b435b51404ee
alter user ORABLOG identified by * ORABLOG a0fe3035825f3637aad3b435b51404ee
alter user ORABLOG identified by * ORABLOG a6b47b9609ea127baad3b435b51404ee
alter user ORABLOG identified by * ORABLOG ce0beda1542358c2aad3b435b51404ee
alter user ORABLOG identified by * ORABLOG 5a7c34dd04a4c3adaad3b435b51404ee

8 rows selected.

SQL>

The same 4 passwords were set for ORABLOG as i set for the user TEST. The same 4 DES encrypted passwords were created. This means that the encryption is just for the password and the username is not involved and also it means that there is no SALT. What if we now set the passwords using lower case a, b, c and d:

SQL> alter user TEST identified by a;

User altered.

SQL> alter user TEST identified by b;

User altered.

SQL> alter user TEST identified by c;

User altered.

SQL> alter user TEST identified by d;

User altered.

SQL> select err_sql,ora_dict_obj,ora_des from atkd.pfclatk_audit where ora_des is not null;

ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ---------------------------------------- --------------------------------
create user TEST identified by * TEST a0fe3035825f3637aad3b435b51404ee
alter user TEST identified by * TEST a6b47b9609ea127baad3b435b51404ee
alter user TEST identified by * TEST ce0beda1542358c2aad3b435b51404ee
alter user TEST identified by * TEST 5a7c34dd04a4c3adaad3b435b51404ee
alter user ORABLOG identified by * ORABLOG a0fe3035825f3637aad3b435b51404ee
alter user ORABLOG identified by * ORABLOG a6b47b9609ea127baad3b435b51404ee
alter user ORABLOG identified by * ORABLOG ce0beda1542358c2aad3b435b51404ee
alter user ORABLOG identified by * ORABLOG 5a7c34dd04a4c3adaad3b435b51404ee
alter user TEST identified by * TEST a6b09e2de7820bedaad3b435b51404ee
alter user TEST identified by * TEST 94326650a42f2bd8aad3b435b51404ee
alter user TEST identified by * TEST e6ea6b41dedd8560aad3b435b51404ee

ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ---------------------------------------- --------------------------------
alter user TEST identified by * TEST cd961c7f88ea81c7aad3b435b51404ee

12 rows selected.

SQL>

This is interesting as the encrypted passwords are not the same as the upper case ones. This means that the encryption used must be CASE SENSITIVE unlike the DES algorithm used in the database password hash from Oracle 6 upwards; actually the algorithm supports upper and lower case and specials etc but the double quote syntax was needed to access that. You will have noticed that the last 16 characters (8 bytes) of the DES encrypted password is always the same; before we play with that lets test if this also works for SYS.

This is interesting as the reason I first looked into this area was to see if there was any way I could simulate password management in any other way than the password management built into Oracle; this built in password management does not work for SYS; so i was looking to see if i could simulate it in any other way. More on this in another blog post and i will show you what I worked out for SYS. So lets try this for SYS:

SQL> alter user sys identified by a;

User altered.

SQL> alter user sys identified by b;

User altered.

SQL> alter user sys identified by c;

User altered.

SQL> alter user sys identified by d;

User altered.

SQL> select err_sql,ora_dict_obj,ora_des from atkd.pfclatk_audit where ora_des is not null;

ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ---------------------------------------- --------------------------------
create user TEST identified by * TEST a0fe3035825f3637aad3b435b51404ee
alter user TEST identified by * TEST a6b47b9609ea127baad3b435b51404ee
alter user TEST identified by * TEST ce0beda1542358c2aad3b435b51404ee
alter user TEST identified by * TEST 5a7c34dd04a4c3adaad3b435b51404ee
alter user ORABLOG identified by * ORABLOG a0fe3035825f3637aad3b435b51404ee
alter user ORABLOG identified by * ORABLOG a6b47b9609ea127baad3b435b51404ee
alter user ORABLOG identified by * ORABLOG ce0beda1542358c2aad3b435b51404ee
alter user ORABLOG identified by * ORABLOG 5a7c34dd04a4c3adaad3b435b51404ee
alter user TEST identified by * TEST a6b09e2de7820bedaad3b435b51404ee
alter user TEST identified by * TEST 94326650a42f2bd8aad3b435b51404ee
alter user TEST identified by * TEST e6ea6b41dedd8560aad3b435b51404ee

ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ---------------------------------------- --------------------------------
alter user TEST identified by * TEST cd961c7f88ea81c7aad3b435b51404ee
alter user sys identified by * SYS a6b09e2de7820bedaad3b435b51404ee
alter user sys identified by * SYS 94326650a42f2bd8aad3b435b51404ee
alter user sys identified by * SYS e6ea6b41dedd8560aad3b435b51404ee
alter user sys identified by * SYS cd961c7f88ea81c7aad3b435b51404ee

16 rows selected.

SQL>

So, yes it works for SYS and the results are the same as the passwords for TEST. So next, lets truncate the audit trail and lets try extending the password from A to 16 A's and see what happens:

SQL> truncate table atkd.pfclatk_audit;

Table truncated.

SQL> alter user TEST identified by A;

User altered.

SQL> alter user TEST identified by AA;

User altered.

SQL> alter user TEST identified by AAA;

User altered.

SQL> alter user TEST identified by AAAA;

User altered.

SQL> alter user TEST identified by AAAAA;

User altered.

SQL> alter user TEST identified by AAAAAA;

User altered.

SQL> alter user TEST identified by AAAAAAA;

User altered.

SQL> alter user TEST identified by AAAAAAAA;

User altered.

SQL> alter user TEST identified by AAAAAAAAA;

User altered.

SQL> alter user TEST identified by AAAAAAAAAA;

User altered.

SQL> alter user TEST identified by AAAAAAAAAAA;

User altered.

SQL> alter user TEST identified by AAAAAAAAAAAA;

User altered.

SQL> alter user TEST identified by AAAAAAAAAAAAA;

User altered.

SQL> alter user TEST identified by AAAAAAAAAAAAAA;

User altered.

SQL> alter user TEST identified by AAAAAAAAAAAAAAA;

User altered.

SQL> alter user TEST identified by AAAAAAAAAAAAAAAA;

User altered.

SQL> select err_sql,ora_dict_obj,ora_des from atkd.pfclatk_audit where ora_des is not null;

ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ---------------------------------------- --------------------------------
alter user TEST identified by * TEST a0fe3035825f3637aad3b435b51404ee
alter user TEST identified by * TEST e59f3bfac2082d77aad3b435b51404ee
alter user TEST identified by * TEST f984b70b43584870aad3b435b51404ee
alter user TEST identified by * TEST 00629bf7623f9c45aad3b435b51404ee
alter user TEST identified by * TEST 5a52d459aeb64441aad3b435b51404ee
alter user TEST identified by * TEST 8ee7deffee86298baad3b435b51404ee
alter user TEST identified by * TEST 1e3528942decf14daad3b435b51404ee
alter user TEST identified by * TEST 1e3528942decf14da0fe3035825f3637
alter user TEST identified by * TEST 1e3528942decf14de59f3bfac2082d77
alter user TEST identified by * TEST 1e3528942decf14df984b70b43584870
alter user TEST identified by * TEST 1e3528942decf14d00629bf7623f9c45

ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ---------------------------------------- --------------------------------
alter user TEST identified by * TEST 1e3528942decf14d5a52d459aeb64441
alter user TEST identified by * TEST 1e3528942decf14d8ee7deffee86298b
alter user TEST identified by * TEST 1e3528942decf14d1e3528942decf14d
alter user TEST identified by * TEST 1e3528942decf14d1e3528942decf14d
alter user TEST identified by * TEST 1e3528942decf14d1e3528942decf14d

16 rows selected.

SQL>

This shows some interesting information and facts:

  • First the known string in the second 16 characters (8 bytes) aad3b435b51404ee remains the same until the password is 8 characters long. This means that all passwords 7 characters and less are encrypted into the first 16 characters (8 bytes) and the second 16 characters (8 bytes) remain the standard blank value. This is odd, why 7 characters and not 8 which is a standard input to DES? possibly the password is a standard C string with a NULL terminator and the terminator is also encrypted?

  • After and including the 8 character password the second 16 characters change. This is interesting as if we take the first 16 characters of the password A and the second 16 characters of the password AAAAAAAA (8 A's) the they are the same a0fe3035825f3637. So the second half of the algorithm used is the same as the first half. So it does not encrypt AAAAAAAA (8 A's) as one string; it takes the first 7 A's and encrypts to 1e3528942decf14 and then encrypts the 8th A on its own as a0fe3035825f3637 so is the same password DES string as a single A. So the password is definitely split before encryption and the same encryption is used on each half

  • Passwords 7 (7 A's) and onwards all have the same first half no matter what as they are all the same password half (AAAAAAA).

  • When we get to a 14 character password the second 16 characters of the encrypted password are the same as the first i.e. we have AAAAAAA + AAAAAAA = 1e3528942decf14d + 1e3528942decf14d

  • A password that is 15 characters or higher; its clear that the characters above 14 are ignored!!


If i repeat the output with the passwords also its a little clearer:

alter user TEST identified by * TEST a0fe3035825f3637aad3b435b51404ee 1 - (A)
alter user TEST identified by * TEST e59f3bfac2082d77aad3b435b51404ee 2 - (AA)
alter user TEST identified by * TEST f984b70b43584870aad3b435b51404ee 3 - (AAA)
alter user TEST identified by * TEST 00629bf7623f9c45aad3b435b51404ee 4 - (AAAA)
alter user TEST identified by * TEST 5a52d459aeb64441aad3b435b51404ee 5 - (AAAAA)
alter user TEST identified by * TEST 8ee7deffee86298baad3b435b51404ee 6 - (AAAAAA)
alter user TEST identified by * TEST 1e3528942decf14daad3b435b51404ee 7 - (AAAAAAA)
alter user TEST identified by * TEST 1e3528942decf14da0fe3035825f3637 8 - (AAAAAAAA) (2nd 16 chars change)
alter user TEST identified by * TEST 1e3528942decf14de59f3bfac2082d77 9 - (AAAAAAAAA)
alter user TEST identified by * TEST 1e3528942decf14df984b70b43584870 10 - (AAAAAAAAAA)
alter user TEST identified by * TEST 1e3528942decf14d00629bf7623f9c45 11 - (AAAAAAAAAAA)
alter user TEST identified by * TEST 1e3528942decf14d5a52d459aeb64441 12 - (AAAAAAAAAAAA)
alter user TEST identified by * TEST 1e3528942decf14d8ee7deffee86298b 13 - (AAAAAAAAAAAAA)
alter user TEST identified by * TEST 1e3528942decf14d1e3528942decf14d 14 - (AAAAAAAAAAAAAA) (2nd 16 = 1st 16 chars)
alter user TEST identified by * TEST 1e3528942decf14d1e3528942decf14d (15 - (AAAAAAAAAAAAAAA)
alter user TEST identified by * TEST 1e3528942decf14d1e3528942decf14d (16 - (AAAAAAAAAAAAAAAA)

16 rows selected.

So the passwords seem to be split into 7 character strings and the separately DES encrypted or hashed? and then concatenated again as a HEX string. The testing that i have done above is in 12.2.0.1. Lets try the same 16 passwords in 11.2.0.4:

SQL> create user test identified by A;

User created.

SQL> col err_sql for a40
SQL> col ora_dict_obj for a30
SQL> col ora_des for a32
SQL> set lines 220
SQL> select err_sql,ora_dict_obj,ora_des from atkd.pfclatk_audit where ora_des is not null;

ERR_SQL ORA_DICT_OBJ ORA_DES
---------------------------------------- ------------------------------ --------------------------------
create user test identified by * TEST a0fe3035825f3637aad3b435b51404ee

SQL>

In 11.2.0.4 the hash is exactly the same as in 12.2.0.1 for a password of A. We can therefore rely on the values to analyse the passwords of users as they are changed. As we can do this in a much better way with a password verify function and the password management settings this is sort of redundant but as i said the reason i started to look into this was to do password management of sorts for SYS; more of that in a later post.

In summary we could easily tell if a password is not at least 8 characters long as the last 16 characters of the ORA_DES_ENCRYPTED_PASSWORD are known if its length is seven or less. We can see that the password is split into 7 character blocks so in rare cases we might be able to detect repeat patterns or passwords all of the same character. There is no SALT but there is case sensitivity.

If we could test the actual algorithm and detect its implementation then we could perhaps decrypt a password up to 14 characters. But if it were decrypted at 14 characters but the password were 16 then it would be simple to test that the password is not cracked so therefore must be longer. Therefore we could brute force the extra characters easily - so a 16 character password would be like cracking a 2 character password if we knew the first 14 only.

The testing implies the same key is always used as the first 16 characters were the same as the second so it implies its not a random key used but a fixed key; knowing Oracle thats probably 0123456789ABCDEF. A bit of testing and trial and error could possibly reveal the algorithm used so that the password could be decrypted but i have not done this.

So, 6 password hashes for each user in some circumstances in 12.2.0.1. There are other cases as well of encrypted passwords such as LINKS.

Need Help with Oracle Security GDPR Training and Services

I talked here a few days ago about GDPR in general and I also published my slides from my talk GDPR for the Oracle DBA. We have been helping clients secure data in their Oracle databases and training people for many years.

GDPR does not call for much that is different than we have been helping people with for many years in terms of Oracle Security but it adds some new elements and also adds a focus to securing personal data. We have added a GDPR services page to our website that lists at a high level the services that we can help you with for GDPR. The services page is also available from the services menu on the home page of our website. As part of the services offering for GDPR we have also created a new one day training class called Oracle Security for GDPR. This is a one day class to get your Oracle staff and your security staff who need to understand Oracle Security up to speed with GDPR and Oracle security. If you would like to book us to come to your site to teach this please get in contact by emailing info@petefinnigan.com for more details or to book a class. Alternately watch out for public training dates on our site.

Grants WITH GRANT

The ability to make grants on objects in the database such as tables, views, procedures or others such as SELECT, DELETE, EXECUTE and more is the cornerstone of giving other users or schemas granular access to objects. I say granular as there is always the possibility to do context based security in many ways such as using products such as Virtual Private Database (VPD) or Oracle Label Security (OLS) or by hand coding into the packages or views; lots of options are possible to make access down to the row level, column level or even cell level. But the focus of this blog post is the ability to do GRANT {something} ON {something} TO {something} WITH GRANT OPTION; This has quite a few issues.

The biggest issue for me is when this is done the security moves out one layer from the owner of the object; so becomes harder to control and understand the true scope of the security. Unfortunately the reason often this syntax is used is to allow the creation of a view in SCHEMA_A with access to a table in SCHEMA_B and then with the requirement to grant access to the view in SCHEMA_A to USER_C. This cannot be done unless the access to the table in SCHEMA_B is granted WITH GRANT OPTION; Lets see an example:


SQL> connect sys/oracle1@//192.168.1.95:1539/orcl.localdomain as sysdba
Connected.
SQL>
SQL>
SQL> create user schema_a identified by schema_a;

User created.

SQL> grant create session, create view to schema_a;

Grant succeeded.

SQL> create user schema_b identified by schema_b;

User created.

SQL> grant create session, create table to schema_b;

Grant succeeded.

SQL> create user user_c identified by user_c;

User created.

SQL> grant create session to user_c;

Grant succeeded.

SQL> alter user schema_a quota unlimited on users;

User altered.

SQL> alter user schema_b quota unlimited on users;

User altered.

SQL>


Now connect as SCHEMA_B and create the table that SCHEMA_B will create a view on and grant SELECT to SCHEMA_A:


SQL> connect schema_b/schema_b@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> create table test01 (col_01 number);

Table created.

SQL> insert into test01 values ('1');

1 row created.

SQL> insert into test01 values ('2');

1 row created.

SQL> commit;

Commit complete.

SQL> grant select on test01 to schema_a;

Grant succeeded.

SQL>


Now we have a table in SCHEMA_B connect as SCHEMA_A and create the view and test it:


SQL> connect schema_a/schema_a@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> create view view01 as select * from schema_b.test01;

View created.

SQL> select * from view01;

COL_01
----------
1
2

SQL>


Now the final step; grant SELECT on the view to USER_C and test access to the data is SCHEMA_B.TEST01 via SCHEMA_A.VIEW01:


SQL> grant select on view01 to user_c;
grant select on view01 to user_c
*
ERROR at line 1:
ORA-01720: grant option does not exist for 'SCHEMA_B.TEST01'


SQL>


This is where the problem lies; so in order to fix this we must instead grant select on SCHEMA_B.TEST01 to SCHEMA_A WITH GRANT OPTION and then it works:


SQL> connect schema_b/schema_b@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on test01 to schema_a with grant option;

Grant succeeded.

SQL> connect schema_a/schema_a@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on view01 to user_c;

Grant succeeded.

SQL>


So now it works and USER_C can access the data in SCHEMA_B.TEST01 via SCHEMA_A.VIEW01:


SQL> connect user_c/user_c@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> select * from schema_a.view01;

COL_01
----------
1
2

SQL>


So this is how to have tables in one schema and access views in another and then grant select on the tables accessing the views to a third user. The problem now is that the security on SCHEMA_B.TEST01 table is no longer just controlled by SCHEMA_B but SCHEMA_A can also now make grants on SCHEMA_Bs table. First connect as SYS and create some more users to become GRANTEEs:


SQL> connect sys/oracle1@//192.168.1.95:1539/orcl.localdomain as sysdba
Connected.

SQL> grant create session to ua identified by ua;

Grant succeeded.

SQL> grant create session to ub identified by ub;

Grant succeeded.

SQL> grant create session to uc identified by uc;

Grant succeeded.

SQL> grant create session to ud identified by ud;

Grant succeeded.

SQL>


Connect as SCHEMA_A (the grantee of the WITH GRANT OPTION) and pass on grants:


SQL> connect schema_a/schema_a@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on schema_b.test01 to ua with grant option;

Grant succeeded.

SQL> grant select on schema_b.test01 to ub;

Grant succeeded.

SQL>


Now connect as user UA and grant WITH GRANT OPTION to user UC and then connect to user UC and make a grant to user UD:


SQL> connect ua/ua@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on schema_b.test01 to uc with grant option;

Grant succeeded.

SQL> connect uc/uc@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on schema_b.test01 to ud with grant option;

Grant succeeded.

SQL>


OK, what a messy chain of grants that we have done starting as SCHEMA_A who was passed the right to GRANT on the privilege granted to it. Then UA can do the same and finally UC can also pass on the grant. So lets connect back as SYSDBA and check out the grants:


SQL> connect sys/oracle1@//192.168.1.95:1539/orcl.localdomain as sysdba
Connected.
SQL> set serveroutput on
SQL> @get_tab2



get_tab2: Release 1.0.0.0.0 - Production on Thu Jun 07 16:00:51 2018
Copyright (c) 2004,2009, PeteFinnigan.com Limited. All rights reserved.

OBJECT TO CHECK [XXX_XXXX]: TEST01
SCHEMA/OWNER OF THE OBJECT TO CHECK [USER]: SCHEMA_B
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:

Testing root object => [SCHEMA_B.TEST01]


GRANTOR GRANTEE S I U D A F D I R Q C E
------------- -------------- - - - - - - - - - - - -
SCHEMA_A UB X
UC UD G
UA UC G
SCHEMA_A UA G
SCHEMA_B SCHEMA_A G

PL/SQL procedure successfully completed.


For updates please visit /tools.htm

SQL>


This script shows the grants done above and shows the GRANTEE and GRANTOR and also whether the grant is normal "X" or is done WITH GRANT OPTION "G". What if you go full circle and log in as user UD and grant select back to SCHEMA_B WITH GRANT OPTION:


SQL> connect ud/ud@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on schema_b.test01 to schema_b with grant option;

Grant succeeded.

SQL> connect sys/oracle1@//192.168.1.95:1539/orcl.localdomain as sysdba
Connected.
SQL> set serveroutput on
SQL> @get_tab2



get_tab2: Release 1.0.0.0.0 - Production on Thu Jun 07 16:08:43 2018
Copyright (c) 2004,2009, PeteFinnigan.com Limited. All rights reserved.

OBJECT TO CHECK [XXX_XXXX]: TEST01
SCHEMA/OWNER OF THE OBJECT TO CHECK [USER]: SCHEMA_B
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:

Testing root object => [SCHEMA_B.TEST01]


GRANTOR GRANTEE S I U D A F D I R Q C E
------------- -------------- - - - - - - - - - - - -
SCHEMA_A UB X
UC UD G
UA UC G
SCHEMA_A UA G
SCHEMA_B SCHEMA_A G

PL/SQL procedure successfully completed.


For updates please visit /tools.htm

SQL>


Nothing changed so even though the GRANT from UD to SCHEMA_B said "Grant Succeeded" that clearly was not true. So what next, Oracle says that if you REVOKE a GRANT done WITH GRANT it should unravel all of the child grants to ensure that its all cleaned up. So log in to SCHEMA_B and revoke the original grant to SCHEMA_A with the WITH GRANT OPTION and does it unroll the other grants?


SQL> connect schema_b/schema_b@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> revoke select on test01 from schema_a;

Revoke succeeded.

SQL>


Lets check the grants that still exist; if any:


SQL> connect sys/oracle1@//192.168.1.95:1539/orcl.localdomain as sysdba
Connected.
SQL> set serveroutput on
SQL> @get_tab2



get_tab2: Release 1.0.0.0.0 - Production on Thu Jun 07 16:35:31 2018
Copyright (c) 2004,2009, PeteFinnigan.com Limited. All rights reserved.

OBJECT TO CHECK [XXX_XXXX]: TEST01
SCHEMA/OWNER OF THE OBJECT TO CHECK [USER]: SCHEMA_B
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:

Testing root object => [SCHEMA_B.TEST01]


GRANTOR GRANTEE S I U D A F D I R Q C E
------------- -------------- - - - - - - - - - - - -

PL/SQL procedure successfully completed.


For updates please visit /tools.htm

SQL>


All gone!!, very clever, Oracle even knew about all of the grants made by SCHEMA_A to other users and even the grants they then made to yet further users. As I like to know how things work; is there any clues as to how Oracle does the revokes? First lets put all of the grants WITH GRANT back again:


SQL> connect schema_b/schema_b@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on test01 to schema_a with grant option;

Grant succeeded.

SQL> connect schema_a/schema_a@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on schema_b.test01 to ua with grant option;

Grant succeeded.

SQL> grant select on schema_b.test01 to ub;

Grant succeeded.

SQL> connect ua/ua@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on schema_b.test01 to uc with grant option;

Grant succeeded.

SQL> connect uc/uc@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on schema_b.test01 to ud with grant option;

Grant succeeded.

SQL>


So if we check DBA_TAB_PRIVS is there any special marker to help it revoke recursively; no:


SQL> connect sys/oracle1@//192.168.1.95:1539/orcl.localdomain as sysdba
Connected.
SQL> set serveroutput on
SQL> @print 'select * from dba_tab_privs where table_name=''''TEST01'''''
Executing Query [select * from dba_tab_privs where table_name='TEST01']
GRANTEE : UA
OWNER : SCHEMA_B
TABLE_NAME : TEST01
GRANTOR : SCHEMA_A
PRIVILEGE : SELECT
GRANTABLE : YES
HIERARCHY : NO
COMMON : NO
TYPE : TABLE
INHERITED : NO
-------------------------------------------
GRANTEE : UB
OWNER : SCHEMA_B
TABLE_NAME : TEST01
GRANTOR : SCHEMA_A
PRIVILEGE : SELECT
GRANTABLE : NO
HIERARCHY : NO
COMMON : NO
TYPE : TABLE
INHERITED : NO
-------------------------------------------
GRANTEE : SCHEMA_A
OWNER : SCHEMA_B
TABLE_NAME : TEST01
GRANTOR : SCHEMA_B
PRIVILEGE : SELECT
GRANTABLE : YES
HIERARCHY : NO
COMMON : NO
TYPE : TABLE
INHERITED : NO
-------------------------------------------
GRANTEE : UC
OWNER : SCHEMA_B
TABLE_NAME : TEST01
GRANTOR : UA
PRIVILEGE : SELECT
GRANTABLE : YES
HIERARCHY : NO
COMMON : NO
TYPE : TABLE
INHERITED : NO
-------------------------------------------
GRANTEE : UD
OWNER : SCHEMA_B
TABLE_NAME : TEST01
GRANTOR : UC
PRIVILEGE : SELECT
GRANTABLE : YES
HIERARCHY : NO
COMMON : NO
TYPE : TABLE
INHERITED : NO
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>


Oracle doesn't need it as it could do a recursive query on the base tables of this view and look for grantor and grantee relationships and where the GRANT was WITH GRANT or GRANTABLE in DBA_TAB_PRIVS. So this option when doing granular grants on objects such as tables is a mess; it would be better to avoid this. There are two main reasons that WITH GRANT gets used; the first is to solve the view problem I showed at the start of this post and the second where a user becomes a "security user" and gets all grants WITH GRANT so that he can log in and then make grants to other users.

I would say that I don't like both of these options. Number 1 [the VIEW issue] can be solved by creating the view in the same schema as the table so that the GRANT with WITH GRANT is not needed; in fact no grant is needed as the OBJECT OWNER principal takes over and requires no grants. The security in having the data in one schema and the views in another (presumably with a limited view set on the data) is ruined by having to give WITH GRANT to that schema on the base table anyway so whilst if you block that schema and SQL injection is not possible then its OK BUT the fact still remains that this schema can now control the security of another schema; I don't like that; I prefer the risk that the view and table are in the same schema and no grants WITH GRANT are needed and the only grants made are those that expose the VIEW not the table and these are normal grants.

The Number 2 issue - the security user/schema is not a good way to manage grants. There are multiple options in this case to make grants :

  • Use a DBA %ANY% privilege to make grants

  • Use SYSDBA to make grants

  • Connect as the schema to make grants

  • Create a granting schema (number 2 above) to make grants

  • Connect to the schema using proxy and make grants


Personally I prefer the proxy approach as it allows access to the schema without needing to give out the schema password; We can also audit all actions as the proxy without affecting any schema level audit and we don't need to write schema.object in code. Using WITH GRANT simply increases the attack surface.

For extra credit; users can get the grants more than once:

SQL> connect schema_b/schema_b@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on test01 to schema_a with grant option;

Grant succeeded.

SQL> grant select on test01 to ua;

Grant succeeded.

SQL> connect schema_a/schema_a@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on schema_b.test01 to ua with grant option;

Grant succeeded.

SQL> connect sys/oracle1@//192.168.1.95:1539/orcl.localdomain as sysdba
Connected.
SQL> set serveroutput on
SQL> @get_tab2



get_tab2: Release 1.0.0.0.0 - Production on Thu Jun 07 18:38:50 2018
Copyright (c) 2004,2009, PeteFinnigan.com Limited. All rights reserved.

OBJECT TO CHECK [XXX_XXXX]: TEST01
SCHEMA/OWNER OF THE OBJECT TO CHECK [USER]: SCHEMA_B
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:

Testing root object => [SCHEMA_B.TEST01]


GRANTOR GRANTEE S I U D A F D I R Q C E
------------- -------------- - - - - - - - - - - - -
SCHEMA_B UA X
SCHEMA_A UA G
SCHEMA_B SCHEMA_A G

PL/SQL procedure successfully completed.


For updates please visit /tools.htm

SQL>

So the user UA has the SELECT twice once WITH GRANT and once not; so if we revoke the grant to SCHEMA_A it will also remove one of these grants:

SQL> connect schema_b/schema_b@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> revoke select on test01 from schema_a;

Revoke succeeded.

SQL> connect sys/oracle1@//192.168.1.95:1539/orcl.localdomain as sysdba
Connected.
SQL> set serveroutput on
SQL> @get_tab2



get_tab2: Release 1.0.0.0.0 - Production on Thu Jun 07 18:52:47 2018
Copyright (c) 2004,2009, PeteFinnigan.com Limited. All rights reserved.

OBJECT TO CHECK [XXX_XXXX]: TEST01
SCHEMA/OWNER OF THE OBJECT TO CHECK [USER]: SCHEMA_B
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:

Testing root object => [SCHEMA_B.TEST01]


GRANTOR GRANTEE S I U D A F D I R Q C E
------------- -------------- - - - - - - - - - - - -
SCHEMA_B UA X

PL/SQL procedure successfully completed.


For updates please visit /tools.htm

SQL>

If we instead try a recursive grant WITH GRANT:

SQL> connect schema_b/schema_b@//192.168.1.95:1539/orcl.localdomain
Connected.

SQL> grant select on test01 to schema_a with grant option;

Grant succeeded.

SQL> connect schema_a/schema_a@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on schema_b.test01 to ua with grant option;

Grant succeeded.

SQL> connect ua/ua@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on schema_b.test01 to schema_a with grant option;

Grant succeeded.

SQL> connect sys/oracle1@//192.168.1.95:1539/orcl.localdomain as sysdba
Connected.
SQL> set serveroutput on
SQL> @get_tab2



get_tab2: Release 1.0.0.0.0 - Production on Thu Jun 07 18:56:30 2018
Copyright (c) 2004,2009, PeteFinnigan.com Limited. All rights reserved.

OBJECT TO CHECK [XXX_XXXX]: TEST01
SCHEMA/OWNER OF THE OBJECT TO CHECK [USER]: SCHEMA_B
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:

Testing root object => [SCHEMA_B.TEST01]


GRANTOR GRANTEE S I U D A F D I R Q C E
------------- -------------- - - - - - - - - - - - -
UA SCHEMA_A G
SCHEMA_A UA G
SCHEMA_B SCHEMA_A G

PL/SQL procedure successfully completed.


For updates please visit /tools.htm

SQL>

So we granted select from SCHEMA_B to SCHEMA_A and then from SCHEMA_A to UA and then from UA to SCHEMA_A. So SCHEMA_A has the grant twice but from two users but the WITH GRANT has come full circle. What happens when we revoke?

SQL> connect schema_b/schema_b@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> revoke select on test01 from schema_a;

Revoke succeeded.

SQL> connect sys/oracle1@//192.168.1.95:1539/orcl.localdomain as sysdba
Connected.
SQL> set serveroutput on
SQL> @get_tab2



get_tab2: Release 1.0.0.0.0 - Production on Thu Jun 07 19:03:35 2018
Copyright (c) 2004,2009, PeteFinnigan.com Limited. All rights reserved.

OBJECT TO CHECK [XXX_XXXX]: TEST01
SCHEMA/OWNER OF THE OBJECT TO CHECK [USER]: SCHEMA_B
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:

Testing root object => [SCHEMA_B.TEST01]


GRANTOR GRANTEE S I U D A F D I R Q C E
------------- -------------- - - - - - - - - - - - -

PL/SQL procedure successfully completed.


For updates please visit /tools.htm

SQL>

No matter how convoluted the grants WITH GRANT get Oracle unravels them; this is not really the point; that Oracle can unravel them. The point is not to get into this mess in the first place having grants allowed from a second schema; this is then uncontrollable by the original schema and its an increased attack surface. Try not to use these.

GDPR

I posted a couple of days ago my slides from the recent UKOUG Northern Technology day in Leeds where I spoke about GPPR for the Oracle DBA. I said then that i am also preparing a service line for helping customers specifically with the problem of Oracle Security for GPDR (compliance). I am working on releasing information for a new one day class that i will be teaching called Oracle Security for GDPR. A two page pdf flyer will be added to our training page in the next couple of days. I will also announce the GPDR service lines that we will be adding soon in the next few days. We have helped customers already with work towards GDPR compliance with work specifically in the areas of Oracle security that we can cover; these include:


  • Detailed security audits performed of customers Oracle database

  • We have used PFCLScan to audit a large number of customer databases

  • Help creating a database security policy for customers

  • Breach notice policy creation

  • We have been involved with a number of incident response and forensics

  • We have helped with audit trail design, creations and implementations

  • We have of course helped with training by teaching people on our classes about Oracle security

  • We have helped with masking of data

  • Many more...



As I said I will be publishing our range of oracle security services specific for GDPR in the next few days and also talking more in details here about that and also about our new one day training class Oracle Security for GDPR that we have created.

I wanted to talk a little here about GDPR in general as we have spent a lot of efforts in PeteFinnigan.com Limited to work towards GDPR compliance. We have done data assessments to locate all personal data they we hold and documented where we hold it and why and the reason for holding it. This was an interesting exercise. A lot of people state how GDPR is a good for business - For instance this article propounds the virtues of stronger security because of GDPR.

There is a flip side though. GDPR also can cause some security weakness in my opinion if you put the need to ensure that you are compliant with GDPR above the needs of collecting what is now classified as personal data. IP addresses and similar details are now classed as personal details. There is a lot written about this; some say that including IP Addresses in your Apache web logs and error logs is collecting personal data and therefore requires you to be GDPR compliant with this; so if someone were to breach your website and steal an apache weblog with tens of thousands of IP Addresses this would be a major data breach and much be reported and you would have to notify the owners of the IP address. If we argue that we keep the access_log for needs of security then maybe its OK and a valid reason to keep it and to keep thousands of what is personal data. What about running a webalizer report to check out how many people visit your website; thats not a reason to keep this data. You could argue that you never look at the access_log but you still collect that details. You could also argue that the IP address is not personal; problem is it is for some people. If you run a whois report or you combine the ip addresses in the access_log with personal search details from the google page that brought them to the site and maybe combine with a website account (http passwd) then its a problem. Some websites I looked at say its OK to keep this data and some say no its not and you run the risk of a 20M Euro fine.

This problem basically means that whilst some articles suggest that GDPR is a good thing for businesses its could also be a bad thing if you decide the risk of a GDPR breach and a 20M Euro fine is worse than the need to keep the server logs for webalizer then this can lead to less security - a breach that occurs with no logs is an undetected breach.

You may have noticed that at the top of most pages on this website there is a "number of visitors online" field. This is generated also by collecting every unique IP Address for a short period of time (30 minutes) and then checking how many unique IP addresses are on line; this is also a GDPR problem so we decided to use still collect IP addresses BUT to anonymise them as we dont care what the IP Address is we just care to count unique visits; So this took code changes to a number of pieces of software built into the website to achieve this. The same intermediate approach can be done with Apache logs or security logs - to anonymise IP Addresses and others data.

Also we have a forum on the website and this had many hundreds of entries that people have posted over the years; these are also personal details. So instead of trying to ask each person for permission again we went through and anonymised all of the entries to change all IP addresses to dummy, all emails to me, all names to me and so on. This had to be repeated for comments in blogs for the same reason and also in a number of other places.

We also conducted Cookie audits on all of our websites using PFCLCookie our tool that can be used to locate cookies in websites as they are also considered personal data. We have eliminated all cookies that our sites created.

We also never do email marketing so this is a big area that causes you to need to comply and register with the ICO; we don't do this so its not a problem.

We also updated our privacy policy and split it into a separate privacy policy, cookie policy and legal policy. We also updated our security policies and also worked to ensure we only collect personal data for business reasons of record keeping and accounts. This is an exception to GDPR. Actually trying to understand whether you need to comply with GDPR is not simple. The ICO in the UK has a tool on its website (a questionnaire) that allows you to see if you need to register. I went through this tool and was reasonably sure that we didn't need to register. I was sure we didn't need to comply with the old data protection act. In the end i had to call the ICO to be sure; no we didn't need to register but as suggested on the ICO website you can register anyway. So even if you are convinced that you do not need to register it makes sense to do so; to show that you are taking GDPR seriously BUT you still have to treat all data with GDPR in mind.

I have been watching others attempts at GDPR; here are some examples:


We currently store some of your personal data so that we can keep you informed of any new initiatives, information or opportunities, plus invitations to our events and updates on our upcoming programmes. The data we usually hold are your contact details.

We will always tell you how we use your data, and we will make sure we collect and store your data safely and securely. We will never pass your personal information to any third party without your specific consent.

If you are happy that we keep your details to enable us to contact you with relevant information you need to do nothing further. If you would like your details to be removed from our database, please do so by clicking...


The above is an email i received two days before GDOR became live. I never registered with this security company; I didn't consent to receive their marketing emails and they don't get consent; The idea is that it has to be conscious not opt in by default. because this was sent two days before GDPR its not strictly wrong in my opinion but as soon as they send me a marketing email it is wrong. Another email i received on the 1st of June, so after GDPR:


...
We still intend on meeting â€" and exceeding â€" those goals; however, with GDPR coming into effect, we want to make sure we are abiding by the law (and ensuring you continue to see our emails in your inbox).

If you’d like to continue receiving our emails, including the latest .... webinars (and we hope you will!), please...


After GDPR, a marketing email asking me to re-subscribe to their marketing!! There have been many, many more similar ones.

GDPR compliance is an on going process and its hard work and a lot of work.

GDPR for the Oracle DBA

I did a talk at the recent UKOUG Norther Technology Summit in Leeds, UK on May 16th. This talk was an enhanced version of the one i did at the UKOUG tech conference in Birmingham in December 2017 to a packed room. This talk was covering GDPR at a reasonably high level and focusing on which elements of the recent law affects the Oracle database. There are a reasonable amount of articles in the GDPR that can affect personal data held in an Oracle database and therefore can affect or involve the DBA in doing something at your business to enable compliance with this law.

The GDPR is complex and we (at PeteFinnigan.com Limited) have spent quite a lot of time looking into and getting ready for GDPR ourselves and looking into what personal data we hold, how we got it, how we store it and what its used for. This has meant quite a lot of changes internally to comply with this law. This internal work has lead us to learn quite a lot about this law and combining with the talk I did for UKOUG and also work we have done over the last year to help customers prepare for GDPR compliance in the areas related to Oracle security of course.

I will be making a couple more posts (at least) over the next few days. I want to talk a little more about what we did as a company to prepare for GDPR as it may be useful to share for other consulting companies and I will also make a post to announce our new "Oracle Security for GDPR" page; we have a lot of services already and also trainings and products that can help with GDPR compliance where an Oracle database is involved so we will be releasing a new service page that combines all of these services, trainings and products into one place to make it easy to find and see what is useful for you.

Ok, the slides from my GDPR for the DBA talk can be downloaded from the link in this sentence.

PeteFinnigan.com Limited Printed Oracle Security Training Manuals for Sale

Over the last year or so we have offered for sale left over printed manuals from some of our training courses. Normally we only print the manuals for classes that we organise for in person training such as the classes I announced yesterday in York, UK for the 4th and 5th July and 28th and 29th August 2018. So we don't really have any left over often. I get enquiries from people more and more often to purchase the printed training manuals so we have decided to make it possible to purchase the manuals at any time.

Please visit the Oracle Security training course printed manuals for sale page for more details and to purchase a copy of any of our manuals.

Oracle Security Training In York, UK, 2018

I have just updated our public training dates page to add two new dates for Oracle Security training classes that I will be running here in York, UK. We now have 4 dates covering three available classes. These are as follows:


  • 4th to 5th July 2018 (2 Days) - How to perform a security audit of an Oracle database

  • 28th August 2018 (1 Day) - Secure coding in PL/SQL

  • 29th August 2018 (1 Day) - Oracle incident response and forensics



These are the most likely the only dates I will do as public live in person teaching events in the UK this year; I am planning other classes with partners in other countries at the moment so watch out for the details of those - I will post here and on social media when they are set up. We also have quite a number of on-line dates for public training classes on both UK/EU timezones and also USA EST timezones; please again see the public training dates page.

The 2 day class in York; How to perform a security audit of an Oracle database is a very popular class and teaches attendees how and why data can become insecure in an Oracle database and walks through the complete process of performing an audit of an Oracle database; whilst also discussing how to fix and secure issues that we see.

The 1 day class in York; Secure coding in PL/SQL is an exciting look at how and why PL/SQL can become insecure and how people can attack it and gain privileges in the database or to steal data. We cover all types of attack and also all types of best practice in secure coding; the class also covers manual and automated scanning of PL/SQL for vulnerabilities and also has a special section on how to secure the actual PL/SQL itself in terms of protecting secrets or IPR (Intellectual Property) in the code itself; as well as techniques for adding license type features to PL/SQL.

The 1 day class in York; Oracle incident response and forensics is an enthralling look at how to deal with a breach in an Oracle database; this is very opportune at the moment wth GDPR just becoming law in the last week or so. One big element of GDPR is breach notification and dealing and responding to a breach. This is a one day class that starts with a look at what is a database breach and how to recognise it and then looks at planning for an incident; creating a process and team and a toolset. We then walk through suspected breach of an Oracle database and look at how to respond, how to gather artefacts and then how to analyse and do forensic analysis. We want to answer basic questions; Did a breach occur?, When did it start?, When did it end?, How did they get in?, who did they connect as? What did they see or do? and finally what could they have done with more skills?

These are great classes and places are numbered. If you would like to book a place then please see this page.