I wrote this script some time ago to help someone who asked for such a script in a newsgroup or mailing list - sorry I cannot remember the original posting to reference here. The person at the time wanted to know all the privileges (system privileges, object privileges and roles) that had been granted to any particular user. They also wanted to see the privileges held hierarchically. That is if a user has been allocated a role then also what privileges that role also gave the user.
I want to present this useful script here and also over the next week or so I will also introduce four more scripts that i have written that:
- Lists out which users or roles have been allocated a specific role
- Lists out which users or roles can access a specific object in the database and with which privilege
- Lists out which users or roles have been allocated a specific system privilege
- Lists out the detailed settings for a specific parameter passed in. This includes hidden parameters
I hope that these scripts will be a useful addition to any DBA or security auditor’s toolkit.
Let's start with a simple example. Let's list out the privileges allocated to a default role called OLAP_DBA which is installed on 9iR2. Here is the output snipped for brevity:
find_all_privs: Release 1.0.6.0.0 - Production on Sat Oct 02 20:29:25 2004
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.
NAME OF USER TO CHECK [ORCL]: olap_dba
OUTPUT METHOD Screen/File [S]: s
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
User => OLAP_DBA has been granted the following privileges
====================================================================
ROLE => SELECT_CATALOG_ROLE which contains =>
ROLE => HS_ADMIN_ROLE which contains =>
TABLE PRIV => EXECUTE object => SYS.DBMS_HS grantable => NO
TABLE PRIV => SELECT object => SYS.HS_ALL_CAPS grantable => NO
TABLE PRIV => SELECT object => SYS.HS_ALL_DD grantable => NO
TABLE PRIV => SELECT object => SYS.HS_ALL_INITS grantable => NO
TABLE PRIV => SELECT object => SYS.HS_BASE_CAPS grantable => NO
TABLE PRIV => SELECT object => SYS.HS_BASE_DD grantable => NO
TABLE PRIV => SELECT object => SYS.HS_CLASS_CAPS grantable => NO
TABLE PRIV => SELECT object => SYS.HS_CLASS_DD grantable => NO
TABLE PRIV => SELECT object => SYS.HS_CLASS_INIT grantable => NO
TABLE PRIV => SELECT object => SYS.HS_FDS_CLASS grantable => NO
TABLE PRIV => SELECT object => SYS.HS_FDS_CLASS_DATE grantable => NO
TABLE PRIV => SELECT object => SYS.HS_FDS_INST grantable => NO
TABLE PRIV => SELECT object => SYS.HS_INST_CAPS grantable => NO
TABLE PRIV => SELECT object => SYS.HS_INST_DD grantable => NO
TABLE PRIV => SELECT object => SYS.HS_INST_INIT grantable => NO
TABLE PRIV => DELETE object => SYS.DBA_AWS grantable => NO
TABLE PRIV => DELETE object => SYS.DBA_AW_PS grantable => NO
TABLE PRIV => EXECUTE object => SYS.DBMS_CDC_UTILITY grantable => NO
TABLE PRIV => INSERT object => SYS.DBA_AWS grantable => NO
TABLE PRIV => INSERT object => SYS.DBA_AW_PS grantable => NO
TABLE PRIV => SELECT object => SYS.ALL_AUDIT_POLICIES grantable => NO
TABLE PRIV => SELECT object => SYS.ALL_INDEXTYPE_COMMENTS grantable => NO
TABLE PRIV => SELECT object => SYS.ALL_OPERATOR_COMMENTS grantable => NO
TABLE PRIV => SELECT object => SYS.ALL_SECONDARY_OBJECTS grantable => NO
TABLE PRIV => SELECT object => SYS.CHANGE_SETS grantable => NO
TABLE PRIV => SELECT object => SYS.CHANGE_SOURCES grantable => NO
TABLE PRIV => SELECT object => SYS.CHANGE_TABLES grantable => NO
TABLE PRIV => SELECT object => SYS.CODE_PIECES grantable => NO
{output snipped}
TABLE PRIV => SELECT object => SYS.V_$TRANSACTION grantable => NO
TABLE PRIV => SELECT object => SYS.V_$TRANSACTION_ENQUEUE grantable => NO
TABLE PRIV => SELECT object => SYS.V_$TYPE_SIZE grantable => NO
TABLE PRIV => SELECT object => SYS.V_$UNDOSTAT grantable => NO
TABLE PRIV => SELECT object => SYS.V_$VPD_POLICY grantable => NO
TABLE PRIV => SELECT object => SYS.V_$WAITSTAT grantable => NO
TABLE PRIV => SELECT object => SYS.V_$_LOCK grantable => NO
TABLE PRIV => UPDATE object => SYS.DBA_AWS grantable => NO
TABLE PRIV => UPDATE object => SYS.DBA_AW_PS grantable => NO
SYS PRIV => ALTER ANY DIMENSION grantable => NO
SYS PRIV => ALTER ANY TABLE grantable => NO
SYS PRIV => ANALYZE ANY grantable => NO
SYS PRIV => CREATE ANY DIMENSION grantable => NO
SYS PRIV => CREATE ANY INDEX grantable => NO
SYS PRIV => CREATE ANY TABLE grantable => NO
SYS PRIV => CREATE ANY VIEW grantable => NO
SYS PRIV => DROP ANY DIMENSION grantable => NO
SYS PRIV => DROP ANY TABLE grantable => NO
SYS PRIV => DROP ANY VIEW grantable => NO
SYS PRIV => LOCK ANY TABLE grantable => NO
SYS PRIV => SELECT ANY DICTIONARY grantable => NO
SYS PRIV => SELECT ANY TABLE grantable => NO
PL/SQL procedure successfully completed.
For updates please visit /tools.htm
As you can see the script first prints out a header notice and then asks for some input. The name of a user or role is required. In this case OLAP_DBA is used. We can then select whether the output is written to the screen (and spooled to a default file called find_all_privs.lis) or is written to a server side operating system file. This functionality uses the UTL_FILE built in package. This in turn needs an operating system directory to be specified in the utl_file_dir parameter or after 9iR2 can be specified by using a DIRECTORY object. Be aware of the security risks of setting either this parameter or creating DIRECTORY objects in a production database.
The spooled output from this example is then shown. The output has been snipped for brevity. The first thing of interest is the hierarchical nature of the report. You can see that the user OLAP_DBA has been granted the role SELECT_CATALOG_ROLE which in turn has been granted the role HS_ADMIN_ROLE which then in turn has SELECT and EXECUTE privileges on various SYS owned objects. The SELECT_CATALOG_ROLE itself also has been granted various SELECT, INSERT, DELETE and EXECUTE privileges on various SYS owned objects. Interestingly the SELECT_CATALOG_ROLE does not do what it says on the tin!. It allows other access besides SELECTing. Finally we can also see that the OLAP_DBA user itself has also been granted various SYSTEM PRIVILEGES directly.
This script is very useful in investigating exactly what privileges a user has.
The script can also be used to investigate the privileges allocated to a USER. This can be demonstrated with the default user OUTLN. Here is the output:
find_all_privs: Release 1.0.6.0.0 - Production on Sat Oct 02 20:59:46 2004
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.
NAME OF USER TO CHECK [ORCL]: OUTLN
OUTPUT METHOD Screen/File [S]: S
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
User => OUTLN has been granted the following privileges
====================================================================
ROLE => CONNECT which contains =>
SYS PRIV => ALTER SESSION grantable => NO
SYS PRIV => CREATE CLUSTER grantable => NO
SYS PRIV => CREATE DATABASE LINK grantable => NO
SYS PRIV => CREATE SEQUENCE grantable => NO
SYS PRIV => CREATE SESSION grantable => NO
SYS PRIV => CREATE SYNONYM grantable => NO
SYS PRIV => CREATE TABLE grantable => NO
SYS PRIV => CREATE VIEW 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
SYS PRIV => EXECUTE ANY PROCEDURE grantable => NO
SYS PRIV => UNLIMITED TABLESPACE grantable => NO
TABLE PRIV => DELETE object => SCOTT.EMP grantable => NO
TABLE PRIV => EXECUTE object => SYS.OUTLN_PKG grantable => NO
PL/SQL procedure successfully completed.
For updates please visit /tools.htm
SQL>
The above example output is self explanatory. As you can see i could have sent the output to a file instead. This done by also specifying the name of the output file and also the operating system directory (see utl_file_dir setting or and existing DIRECTORY object that can b accessed by the user running the script).
The script has only a few small limitations. It doesn't include all the privileges available to the PUBLIC group. That is if you query as above the user OUTLN for instance, the script will not list all of the PUBLIC objects that can also be accessed by OUTLN. This is done for clarity and brevity. It also does not include proxy privileges (I plan to enhance it to do this). It also doesn't include objects accessible by the user being queried via the SYSTEM PRIVILEGES SELECT ANY TABLE and SELECT ANY DICTIONARY. This is again for brevity and clarity. If the user has been allocated either of these two system privileges then this is of course listed.
Watch out for the other four scripts in the toolkit coming soon.
