Pete Finnigan's Oracle Security Weblog

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.