This post is prompted by a message I received from a member of a recent customers staff. He thanked me for the free tools he had been using to review permissions in his database and also my articles on our website.
So, I know I have posted about these tools in the past but I thought it worth while having a new post so that people can be aware of these free tools and use them in their own database security analysis.
First there is find_all_privs.sql - This is a simple PL/SQL based tool that allows you to analyse the privileges granted to a user or a role. This includes object grants, system grants and also role grants. The output is hierarchical so that you can see how a user was granted a right.
Lets use this script on my 21c database to see what rights are granted to my sample user ORABLOG:
SQL> @find_all_privs
find_all_privs: Release 1.0.7.0.0 - Production on Thu Mar 30 13:19:19 2023
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.
NAME OF USER TO CHECK [ORCL]: ORABLOG
OUTPUT METHOD Screen/File [S]: S
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
User => ORABLOG has been granted the following privileges
====================================================================
ROLE => CONNECT which contains =>
SYS PRIV => CREATE SESSION grantable => NO
SYS PRIV => SET CONTAINER grantable => NO
ROLE => RESOURCE which contains =>
ROLE => SODA_APP which contains =>
TABLE PRIV => EXECUTE object => XDB.DBMS_SODA_ADMIN grantable => NO
TABLE PRIV => EXECUTE object => XDB.DBMS_SODA_USER_ADMIN grantable => NO
TABLE PRIV => READ object => XDB.JSON$USER_COLLECTION_METADATA grantable => NO
SYS PRIV => CREATE CLUSTER grantable => NO
SYS PRIV => CREATE INDEXTYPE grantable => NO
SYS PRIV => CREATE OPERATOR grantable => NO
SYS PRIV => CREATE PROCEDURE grantable => NO
SYS PRIV => CREATE SEQUENCE grantable => NO
SYS PRIV => CREATE TABLE grantable => NO
SYS PRIV => CREATE TRIGGER grantable => NO
SYS PRIV => CREATE TYPE grantable => NO
SYS PRIV => CREATE ANY CONTEXT grantable => NO
SYS PRIV => CREATE PROCEDURE grantable => NO
SYS PRIV => CREATE VIEW grantable => NO
SYS PRIV => UNLIMITED TABLESPACE grantable => NO
TABLE PRIV => EXECUTE object => SYS.DBMS_CRYPTO grantable => NO
TABLE PRIV => EXECUTE object => FACADM.USER_ADMIN grantable => NO
TABLE PRIV => EXECUTE object => SYS.UTL_FILE grantable => NO
TABLE PRIV => EXECUTE object => SYS.UTL_HTTP grantable => NO
TABLE PRIV => READ object => SYS.ORABLOG grantable => NO
TABLE PRIV => SELECT object => IMPORTER.C34 grantable => NO
TABLE PRIV => WRITE object => SYS.ORABLOG grantable => NO
PL/SQL procedure successfully completed.
For updates please visit http://www.petefinnigan.com/tools.htm
SQL>
The report gives you a nice tree of rights granted. We also include WITH ADMIN and WITH GRANT flags. It is also possible to use UTL_FILE and write the output to the server but you need to then set up a DIRECTORY object so the 'S'creen choice for output works well. We can see above rights granted to a role , then to a role then to the user. For instance the EXECUTE grant on XDB.DBMS_SODA_ADMIN has been granted to the SODA_APP role that has been granted to the RESOURCE role and then eventually to the ORABLOG user. We can easily see duplicate grants such as CREATE PROCEDURE granted directly and also via the RESOURCE role.
The CONNECT role is interesting as it includes SET CONTAINER but as this is a local user in the PDB that doesn't really make sense. We should not be using Oracle designed roles as they were not designed by us or for our application specifically.
The goal with this report is to identify privileges that should be removed and reduced i.e. to least privilege.
There is a second version of the script called - find_all_privs2.sql - this has an extra parameter that allows you to not include object grants or not. This is a simple switch when running the script. Here is an example running the script with table (object) privileges not included for the same user ORABLOG:
SQL> @find_all_privs2.sql
find_all_privs: Release 1.9.0.0.0 - Production on Thu Mar 30 13:30:30 2023
Copyright (c) 2004 - 2014 PeteFinnigan.com Limited. All rights reserved.
NAME OF USER TO CHECK [ORCL]: ORABLOG
OUTPUT METHOD Screen/File [S]: S
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
EXAMINE TABLE PRIVILEGES? [N/Y]: N
User => ORABLOG has been granted the following privileges
====================================================================
SYS PRIV => CREATE ANY CONTEXT grantable => NO
SYS PRIV => CREATE PROCEDURE grantable => NO
SYS PRIV => CREATE VIEW grantable => NO
SYS PRIV => UNLIMITED TABLESPACE grantable => NO
ROLE => CONNECT which contains =>
SYS PRIV => CREATE SESSION grantable => NO
SYS PRIV => SET CONTAINER grantable => NO
ROLE => RESOURCE which contains =>
SYS PRIV => CREATE CLUSTER grantable => NO
SYS PRIV => CREATE INDEXTYPE grantable => NO
SYS PRIV => CREATE OPERATOR grantable => NO
SYS PRIV => CREATE PROCEDURE grantable => NO
SYS PRIV => CREATE SEQUENCE grantable => NO
SYS PRIV => CREATE TABLE grantable => NO
SYS PRIV => CREATE TRIGGER grantable => NO
SYS PRIV => CREATE TYPE grantable => NO
ROLE => SODA_APP which contains =>
PL/SQL procedure successfully completed.
for updates please visit http://www.petefinnigan.com/tools.htm
SQL>
The script (not included here) also shows whether a ROLE has a password or not. This version of the script is useful to show reduced output and show only the roles and privileges granted.
There is a third version of this privilege analysis script - find_all_privs_sum.sql - that shows the object granted summarised. Here is an example of running this script for my ORABLOG user in 21c again:
SQL> @find_all_privs_sum.sql
find_all_privs: Release 1.0.7.0.0 - Production on Thu Mar 30 13:40:53 2023
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.
NAME OF USER TO CHECK [ORCL]: ORABLOG
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
User => ORABLOG has been granted the following privileges
====================================================================
ROLE => CONNECT which contains =>
SYS PRIV => CREATE SESSION grantable => NO
SYS PRIV => SET CONTAINER grantable => NO
ROLE => RESOURCE which contains =>
ROLE => SODA_APP which contains =>
TABLE PRIV => EXECUTE object => XDB.Count[2] grantable => NO
TABLE PRIV => READ object => XDB.Count[1] grantable => NO
SYS PRIV => CREATE CLUSTER grantable => NO
SYS PRIV => CREATE INDEXTYPE grantable => NO
SYS PRIV => CREATE OPERATOR grantable => NO
SYS PRIV => CREATE PROCEDURE grantable => NO
SYS PRIV => CREATE SEQUENCE grantable => NO
SYS PRIV => CREATE TABLE grantable => NO
SYS PRIV => CREATE TRIGGER grantable => NO
SYS PRIV => CREATE TYPE grantable => NO
SYS PRIV => CREATE ANY CONTEXT grantable => NO
SYS PRIV => CREATE PROCEDURE grantable => NO
SYS PRIV => CREATE VIEW grantable => NO
SYS PRIV => UNLIMITED TABLESPACE grantable => NO
TABLE PRIV => EXECUTE object => FACADM.Count[1] grantable => NO
TABLE PRIV => EXECUTE object => SYS.Count[3] grantable => NO
TABLE PRIV => READ object => SYS.Count[1] grantable => NO
TABLE PRIV => SELECT object => IMPORTER.Count[1] grantable => NO
TABLE PRIV => WRITE object => SYS.Count[1] grantable => NO
PL/SQL procedure successfully completed.
For updates please visit http://www.petefinnigan.com/tools.htm
SQL>
As you can see from the output above for the SODA_APP role, the grants on objects are summarised at the owner of the object level - i.e. XDB.Count[2]. This version is useful to summarise the grants if the output would be too big to list as individual grants.
These tools are great to analyse an Oracle database users or roles permissions (remember you can also use the script on roles as well as users) but remember these are working tools, not demos for training classes. We use them every day in real work. BUT, they do not include any fancy HTML reports or detailed analysis text. They are tools.
You can run this script with a user who has CREATE SESSION and SELECT ANY DICTIONARY or if you wish select on the individual tables/views used instead of SELECT ANY DICTIONARY.
Please remember to follow us on out social media and like out posts
