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.

get_tab2.sql - Free Tool to show Privileges on an Object Updated

I have a core set of PL/SQL scripts that I use when conducting Oracle security work on customer sites. Most of these are available on this website for many years. One of these is my script get_tab2.sql which shows grants against an object horizontally across the screen and its nice as an alternative to my who_can* scripts that show a hierarchy of grants down the screen. I like get_tab2.sql because its succinct. Here is a sample run:


SQL> @get_tab2



get_tab2: Release 1.2.0.0.0 - Production on Wed Aug 30 11:10:26 2017
Copyright (c) 2007, 2017, PeteFinnigan.com Limited. All rights reserved.

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

Testing root object => [ORABLOG.CREDIT_CARD]


GRANTOR GRANTEE R S I U D A F D I R Q C E
------------- -------------- - - - - - - - - - - - - -
ORABLOG ERIC X X X X
ORABLOG RISK01 X X X X
ORABLOG BACK01 X X X X [,D][ORABLOG_READ]
ORABLOG USER03 X X X X [,D][ORABLOG_READ]
ORABLOG USER04 X X X X [,D][ORABLOG_READ]
ORABLOG SYS X X X X [A,D][ORABLOG_READ]
ORABLOG USER05 X X X X [,D][ORABLOG_CREDIT][ORABLOG_READ]
ORABLOG USER07 X X X X [,D][ORABLOG_CREDIT][ORABLOG_READ]
ORABLOG SYS X X X X [A,D][ORABLOG_CREDIT][ORABLOG_READ]
ORABLOG USER06 X X X X [,D][ORABLOG_CREDIT][ORABLOG_READ]
ORABLOG SYS X X X X [A,D][ORABLOG_SUPPORT][ORABLOG_CREDIT][ORABLOG_READ]
ORABLOG DEV02 X X X X [,D][ORABLOG_SUPPORT][ORABLOG_CREDIT][ORABLOG_READ]
ORABLOG DEV01 X X X X [,D][ORABLOG_SUPPORT][ORABLOG_CREDIT][ORABLOG_READ]
ORABLOG DEV03 X X X X [,D][ORABLOG_SUPPORT][ORABLOG_CREDIT][ORABLOG_READ]
ORABLOG FEED01 X X X X [,D][ORABLOG_CREDIT][ORABLOG_READ]
ORABLOG BATCH01 X X X X [,D][ORABLOG_CREDIT][ORABLOG_READ]
ORABLOG USER06 X X X X [,D][ORABLOG_READ]
ORABLOG USER05 X X X X [,D][ORABLOG_READ]
ORABLOG USER01 X X X X [,D][ORABLOG_READ]
ORABLOG USER07 X X X X [,D][ORABLOG_READ]
ORABLOG FEED01 X X X X [,D][ORABLOG_READ]
ORABLOG RISK01 X X X X [,D][ORABLOG_READ]
ORABLOG SYS X X X X [A,D][ORABLOG_SUPPORT][ORABLOG_READ]
ORABLOG DEV02 X X X X [,D][ORABLOG_SUPPORT][ORABLOG_READ]
ORABLOG DEV01 X X X X [,D][ORABLOG_SUPPORT][ORABLOG_READ]
ORABLOG DEV03 X X X X [,D][ORABLOG_SUPPORT][ORABLOG_READ]
ORABLOG USER02 X X X X [,D][ORABLOG_READ]
ORABLOG BATCH01 X X X X [,D][ORABLOG_READ]

PL/SQL procedure successfully completed.


For updates please visit /tools.htm

SQL>


The privileges are listed as single character entries; Read, Select, Insert, Update, Delete etc. If a grant is is made with GRANT OPTION as follows:


SQL> grant select on orablog.credit_card to eric with grant option;

Grant succeeded.

SQL>


Then this grant now shows as a 'G' instead of an 'X':


SQL> @get_tab2



get_tab2: Release 1.2.0.0.0 - Production on Wed Aug 30 11:13:44 2017
Copyright (c) 2007, 2017, PeteFinnigan.com Limited. All rights reserved.

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

Testing root object => [ORABLOG.CREDIT_CARD]


GRANTOR GRANTEE R S I U D A F D I R Q C E
------------- -------------- - - - - - - - - - - - - -
ORABLOG ERIC G X X X
ORABLOG RISK01 X X X X
ORABLOG BACK01 X X X X [,D][ORABLOG_READ]
ORABLOG USER03 X X X X [,D][ORABLOG_READ]
ORABLOG USER04 X X X X [,D][ORABLOG_READ]
ORABLOG SYS X X X X [A,D][ORABLOG_READ]
ORABLOG USER05 X X X X [,D][ORABLOG_CREDIT][ORABLOG_READ]
ORABLOG USER07 X X X X [,D][ORABLOG_CREDIT][ORABLOG_READ]
ORABLOG SYS X X X X [A,D][ORABLOG_CREDIT][ORABLOG_READ]
ORABLOG USER06 X X X X [,D][ORABLOG_CREDIT][ORABLOG_READ]
ORABLOG SYS X X X X [A,D][ORABLOG_SUPPORT][ORABLOG_CREDIT][ORABLOG_READ]
ORABLOG DEV02 X X X X [,D][ORABLOG_SUPPORT][ORABLOG_CREDIT][ORABLOG_READ]
ORABLOG DEV01 X X X X [,D][ORABLOG_SUPPORT][ORABLOG_CREDIT][ORABLOG_READ]
ORABLOG DEV03 X X X X [,D][ORABLOG_SUPPORT][ORABLOG_CREDIT][ORABLOG_READ]
ORABLOG FEED01 X X X X [,D][ORABLOG_CREDIT][ORABLOG_READ]
ORABLOG BATCH01 X X X X [,D][ORABLOG_CREDIT][ORABLOG_READ]
ORABLOG USER06 X X X X [,D][ORABLOG_READ]
ORABLOG USER05 X X X X [,D][ORABLOG_READ]
ORABLOG USER01 X X X X [,D][ORABLOG_READ]
ORABLOG USER07 X X X X [,D][ORABLOG_READ]
ORABLOG FEED01 X X X X [,D][ORABLOG_READ]
ORABLOG RISK01 X X X X [,D][ORABLOG_READ]
ORABLOG SYS X X X X [A,D][ORABLOG_SUPPORT][ORABLOG_READ]
ORABLOG DEV02 X X X X [,D][ORABLOG_SUPPORT][ORABLOG_READ]
ORABLOG DEV01 X X X X [,D][ORABLOG_SUPPORT][ORABLOG_READ]
ORABLOG DEV03 X X X X [,D][ORABLOG_SUPPORT][ORABLOG_READ]
ORABLOG USER02 X X X X [,D][ORABLOG_READ]
ORABLOG BATCH01 X X X X [,D][ORABLOG_READ]

PL/SQL procedure successfully completed.


For updates please visit /tools.htm

SQL>


The right hand side shows the grant path.

The changes made to this script is to cater for the READ privilege in 12c. As you can see here Oracle are now using READ grants on some views in their own dictionary rather than SELECT grants. The SELECT grant is not really just READ as it also allows the table to be locked for update. A READ grant does not. Also remember that a read only user is not really read only as any user with just CREATE SESSION has also tens of thousands of grants on PUBLIC objects most of which are EXECUTE not READ; so if you think to create a READ ONLY user remember this!!

If you have a copy of the script in the past then update your copy by downloading get_tab2.sql from here as it now supports READ as well as SELECT for 12c. The script works in non-12c databases of course.

What Are NULL pname entries in v$process?

I got a message on Linked In today from Jijo who asked why when he queries v$process are some of the PNAME column values NULL. I have a simple script vproc.sql that I use when analysing databases for many years for security issues. The script available here gives the high level details for each session. We have another script that gives everything for every session and process where we do forensic analysis of Oracle databases that may have been breached (or have in fact been breached). We get more and more work in this area (unfortunately for the customers) where we are asked to do live analysis of a database that is felt to have been attached and more often we do static analysis (after the fact) of a database that has been breached some time ago.

Forensic analysis or incident response for Oracle databases is becoming very important BUT not as important as securing your data in advance of a breach to prevent it OR ensuring that you have adequate audit trails setup in advance to capture an attack.

The output from the script vproc.sql shows:


SQL> @vproc

SID SERIAL# USERNAME OSUSER PNAME TERMINAL MACHINE PORT PROCPROG SESSPROG B
----- ------- ---------- ---------- ---------- ---------- ------------------------- ------ ----------------------------------- --------------------------------------------- -
1 41159 oracle oracle PMON UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (PMON) oracle@oel7.localdomain (PMON) B
238 61512 oracle oracle CLMN UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (CLMN) oracle@oel7.localdomain (CLMN) B
2 30618 oracle oracle PSP0 UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (PSP0) oracle@oel7.localdomain (PSP0) B
239 54869 oracle oracle VKTM UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (VKTM) oracle@oel7.localdomain (VKTM) B
3 4943 oracle oracle GEN0 UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (GEN0) oracle@oel7.localdomain (GEN0) B
240 44338 oracle oracle MMAN UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (MMAN) oracle@oel7.localdomain (MMAN) B
11 41222 oracle oracle SMON UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (SMON) oracle@oel7.localdomain (SMON) B
241 48205 oracle oracle GEN1 UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (GEN1) oracle@oel7.localdomain (GEN1) B
5 28558 oracle oracle SCMN UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (SCMN) oracle@oel7.localdomain (SCMN) B
242 36406 oracle oracle DIAG UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (DIAG) oracle@oel7.localdomain (DIAG) B
6 51574 oracle oracle OFSD UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (OFSD) oracle@oel7.localdomain (OFSD) B
243 48466 oracle oracle SCMN UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (SCMN) oracle@oel7.localdomain (SCMN) B
7 16054 oracle oracle DBRM UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (DBRM) oracle@oel7.localdomain (DBRM) B
244 13590 oracle oracle VKRM UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (VKRM) oracle@oel7.localdomain (VKRM) B
8 58188 oracle oracle SVCB UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (SVCB) oracle@oel7.localdomain (SVCB) B
245 790 oracle oracle PMAN UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (PMAN) oracle@oel7.localdomain (PMAN) B
9 16947 oracle oracle DIA0 UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (DIA0) oracle@oel7.localdomain (DIA0) B
246 22744 oracle oracle DBW0 UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (DBW0) oracle@oel7.localdomain (DBW0) B
10 27816 oracle oracle LGWR UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (LGWR) oracle@oel7.localdomain (LGWR) B
247 49432 oracle oracle CKPT UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (CKPT) oracle@oel7.localdomain (CKPT) B
4 21723 oracle oracle LG00 UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (LG00) oracle@oel7.localdomain (LG00) B
248 18011 oracle oracle LG01 UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (LG01) oracle@oel7.localdomain (LG01) B
12 26869 oracle oracle SMCO UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (SMCO) oracle@oel7.localdomain (SMCO) B
249 32114 oracle oracle RECO UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (RECO) oracle@oel7.localdomain (RECO) B
34 9377 oracle pxf UNKNOWN Peters-MacBook-Pro.local 55071 oracle@oel7.localdomain sqlplus@Peters-MacBook-Pro.local (TNS V1-V3) F
250 2896 oracle oracle LREG UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (LREG) oracle@oel7.localdomain (LREG) B
35 1177 oracle oracle UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (TNS V1-V3) sqlplus@oel7.localdomain (TNS V1-V3) F
251 18917 oracle oracle PXMN UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (PXMN) oracle@oel7.localdomain (PXMN) B
17 31463 oracle oracle MMNL UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (MMNL) oracle@oel7.localdomain (MMNL) B
252 16226 oracle oracle MMON UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (MMON) oracle@oel7.localdomain (MMON) B
18 26484 oracle oracle TMON UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (TMON) oracle@oel7.localdomain (TMON) B
262 58041 oracle oracle QM02 UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (QM02) oracle@oel7.localdomain (QM02) B
19 37134 oracle oracle TT00 UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (TT00) oracle@oel7.localdomain (TT00) B
255 63272 oracle oracle TT01 UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (TT01) oracle@oel7.localdomain (TT01) B
20 28933 oracle oracle TT02 UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (TT02) oracle@oel7.localdomain (TT02) B
256 3862 oracle oracle AQPC UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (AQPC) oracle@oel7.localdomain (AQPC) B
22 53389 oracle oracle CJQ0 UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (CJQ0) oracle@oel7.localdomain (CJQ0) B
259 18082 oracle oracle Q001 UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (Q001) oracle@oel7.localdomain (Q001) B
13 44347 oracle oracle W003 UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (W003) oracle@oel7.localdomain (W003) B
260 61261 oracle oracle W002 UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (W002) oracle@oel7.localdomain (W002) B
27 39265 oracle oracle Q003 UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (Q003) oracle@oel7.localdomain (Q003) B
26 6270 oracle oracle W006 UNKNOWN oel7.localdomain 0 oracle@oel7.localdomain (W006) oracle@oel7.localdomain (W006) B

42 rows selected.

SQL>


As you (and the questioner) can see the PNAME column from v$process for two of the processes is NULL. These lines are SID/SERIAL# 34/9377 and 35/1177. As you can see these are the only lines that are for FOREGROUND processes and are also are the only lines where the PROGRAM is sqlplus. One is a connection to the 12.2.0.1 database as SYSDBA from the server and one is a connection as SYSDBA from a client PC (MacBook). So the answer of why PNAME is NULL is that these are the processes that are not BACKGROUND processes.

If anyone would like to connect to me on linked in, Facebook, twitter and youtube then please see the links in the footer of this and every page of this blog and please follow, connect, like, etc.

Pete Finnigan is now an Oracle ACE

I just got an email from the Oracle ACE program to tell me that I had been accepted onto the ACE program and was awarded the Oracle ACE status by Oracle.

I have been active on the internet around Oracle and security since 2001 when I wrote my first big paper Exploiting and protecting Oracle in July that year. I also started to write posts and contribute to forums (comp.database.oracle.* at the time) and then in 2003 I started PeteFinnigan.com Limited and created my pre-blog posts (these were shorts like a blog but before I set up Greymatter - see the ramblings section of my website for some examples. I then created the first Oracle Security blog on the 20th September 2004 - almost 13 years ago. Also at the end of 2002 and start of 2003 I published the https://www.amazon.co.uk/Oracle-Security-Step-Step-Finnigan/dp/0972427341/ref=sr_1_7?ie=UTF8&qid=1503685632&sr=8-7&keywords=pete+finnigan - (broken link) SANS Oracle Security step-by-step guide book. This was then used by CIS as the basis for the first CIS Oracle Security benchmark.

Since those beginnings I have written multiple books/chapters and forwards for books, almost 1,500 blog posts; written many papers; written and presented much more than 100 presentations and also created four software products all in the area of Oracle security - new ones in the making now also!! I have also written the original SANS 509 6 day Oracle course on Oracle security and also written 6 days of Oracle security training for my own company which I updated regularly and teach regularly on subjects such as How to audit a database, secure coding, hardening and lock down and much more.

That's a very brief potted history of my Oracle security. I was one of the first people to do only Oracle Security and still doing it now and still just as interested in it now. I hope I can continue to help customers secure their Oracle databases and data!!

Well that's two blog posts in one day!!, this is the first time I have done that for a long time; getting three presentation slots at the UKOUG and becoming an Oracle ACE in one day has warranted two blog posts !!

Oracle Security at UKOUG December 2017

I have just had an email from the UKOUG to say that three of my presentations have been accepted for the upcoming conference on December 4th to 6th at the ICC in Birmingham. I will have one talk on the 4th December at 12:35 for a 50 minute session on "GDPR for the Oracle DBA". Here is the submission description:

GDPR is a new law amalgamating each of the 27(8) EU states data protection acts but it goes further than the current laws. There are now huge fines possible for loss of data and new rules that mean most companies will need to be compliant. What does GDPR mean for the DBA or Oracle person; what tasks do you need to help with as a DBA? - Finding data, classify data, protect data and more. Come along and see what GDPR will mean for the Oracle DBA and how it can affect your database

My second slot is on the 5th December at 09:00 for a 50 minute session. This session is "Auditing the Oracle Database". Here is the submission description:

Pete has extensive experience of visiting customer sites to review their Oracle database for security issues or because they have been breached or attacked. One common theme is seen time after time. No audit trails, or very little audit trails in evidence and of those who have an audit trail even fewer use the audit interactively. Pete has developed a toolkit of PL/SQL and SQL*plus scripts to allow a simple deployment of an audit trail to any single database and also to a central monitoring database. The only pre-configuration needed is to decide what policies you want, which alerts you need and to add some basic background information. the toolkit is extensive and allows automated centralisation of audit trails. The idea is to audit the core database engine and to be able to react to alerts in real time - maybe an attack? Pete will demo the deployment of the toolkit and show how attacks and misuse of the database can be detected easily.

This talk will include quite a bit of demo'ing on some Oracle XE databases and some simple hacking of the database and applications to see what audit trails are captured - should be fun!!

My final session on the 5th December at 12:25 is a 50 minute session - titled "Oracle security round table". This is an open free discussion format which will be all about Oracle Security so please come along and bring a question or discussion point. I have done this Oracle security round table for many years and its always been well attended and also contains some great questions and discussions.

I hope to see you all at the UKOUG in December in Birmingham!!

New Video of Oracle Security Vulnerability Scanning



I have just made a new video of a sample session using PFCLScan our vulnerability / security scanner for the Oracle database. In the video I show how easy it is to get started with PFCLScan and scan an Oracle 12.1.0.2 database for security issues and produce a report. The tool is super easy to use and its as simple as creating a new project, adding database connection details and scanning the database and then generating a report that can be used by the security team and DBA's to review the issues and plan fixes.

Please have a look at the video and like and share. If anyone would like more details or would like to purchase a license then please contact us.

The engagement license is great value at just £110 GBP (+ VAT if applicable) and is a great way to get started and scan databases in your organisation. At this license fee level there is no risk in purchasing and having a look at your own Oracle security.

More Oracle Security Training Manuals for Sale

I advertised here some months ago a small number of printed manuals that I found in our company storage for some of my Oracle security classes. We had these printed over the years for various classes that I taught and normally just stored them away as we cannot re-use them on a new class as I change the slides quite often and the printed versions were out of date.

Having a set of printed manuals that were not used is a pity as they cost (quite a bit) to print. At that time I decided to offer those that I found for sale. This proved to be a success as they all sold quickly on a first come first served basis.

I now have 3 more manuals for my class "How to Perform a Security Audit of an Oracle Database" that are *almost* current. They were printed recently for a private class here in the UK but three people less than was planned arrived for the class so the manuals were not used and are now not the exact same content as the classes I will teach shortly online and as we don't provide printed manuals for the online classes (only downloads) then these also cannot be re-used.

So again I am offering these three manuals for sale on a first come first served basis. The picture below is from last time but its the same printer and style so its a similar book to what you will get. I am not with the manuals now to re-photograph them but the picture would be the same anyway:

How to perform a security audit of an Oracle database


Above is a picture of the class manual for the - How to perform a security audit of an Oracle database - class. This is an A4 bound manual, Black and White and there are approximately 610 MS PPT slides. These were printed in mid 2017 and I have three copies. I will accept £75 GBP + Postage + VAT for each copy.

Please email me on info@petefinnigan.com if you are interested to purchase one of these course books that we have for sale. Please also let me know your postal address and I will get an accurate postage price from the post office and let you know in advance of billing you. Also as we are registered for UK VAT then we may need to also add VAT dependant on where you are. Please get in touch if you would like to purchase one of these course manuals but hurry we don't have many and we will not be repeating this. We are happy to post to anywhere that the UK Royal Mail can offer fully tracked delivery to. Last time we sold manuals to South America, North America, Canada, UK and Europe.

New Oracle Security On-Line Training Dates Added

We have finally added new on-line training dates for some of our classes; the very popular two days "How to perform a security audit of an Oracle Database" is first followed by the one day class "Hardening and Securing Oracle" and then the one day class "Secure Coding in PL/SQL" and finally "Designing Practical Audit Trails for an Oracle Database".

Each class is going to be taught twice on USA Easter time zone - New York (USA) time and twice on UK time zone - York (UK) time - between now and November.

Please have a look at the register and details page for details of how to book your place. Each two day class is £750 (+VAT if applicable) per person and each one day class is £395 (+VAT if applicable) per person. Details and how to pay and register are in the link above. The prices are in GBP.

Each class is detailed and fast paced and includes a download of all of the class slides and also free tools and scripts that are used throughout the class for you to take away and use in your own Oracle Security endeavours. Some of the classes include complete toolkits;

Please register your places now or email at the address in the details page for more details.