Call: +44 (0)1904 557620 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.

[Previous entry: "What Are NULL pname entries in v$process?"] [Next entry: "Oracle Security Training In York - October 30 - 31st 2017"]

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.