I have released a number of scripts to my site for creating heirarchical reports of privileges. These include find_all_privs.sql, who_has_priv.sql, who_can_access.sql, who_has_role.sql and check_parameter.sql. These are standalone sqlplus scripts. I was asked a week or so ago about whether these scripts can be installed in the database as packages or functions of procedures. So i spent ten minutes or so and made the who_has_priv script work in the database as a procedure. The converted script is available as
who_has_priv_procedure.sql and its also available on my
Oracle Security Tools page.
A simple session showing how the script is installed is shown here:
SQL> connect system/manager
Connected.
SQL> create user priv identified by priv;
User created.
SQL> grant create session, create procedure, select any dictionary to priv;
Grant succeeded.
SQL> connect priv/priv
Connected.
SQL> @who_has_priv_procedure
Procedure created.
SQL>
|
Next here are two simple executions of the procedure in the database to see how it works:
SQL> set serveroutput on size 1000000
SQL> exec who_has_priv('ALTER SESSION');
who_has_priv: Release 1.0.1.0.0 - Production on Thu Mar 19 12:15:53 2009
Copyright (c) 2009 PeteFinnigan.com Limited. All rights reserved.
Privilege => ALTER SESSION has been granted to =>
====================================================================
Role => DBA (ADM = YES) which is granted to =>
User => SYS (ADM = YES)
User => SYSMAN (ADM = NO)
User => C (ADM = NO)
User => B1 (ADM = NO)
User => SYSTEM (ADM = YES)
User => SYS (ADM = NO)
User => IX (ADM = NO)
User => SH (ADM = NO)
User => PP (ADM = NO)
Role => RECOVERY_CATALOG_OWNER (ADM = NO) which is granted to =>
User => SYS (ADM = YES)
User => OWBSYS (ADM = YES)
User => BI (ADM = NO)
User => MONITOR (ADM = NO)
User => CTXSYS (ADM = NO)
Role => OWB$CLIENT (ADM = NO) which is granted to =>
User => SYS (ADM = YES)
User => OWBSYS (ADM = YES)
User => EVILUSER (ADM = NO)
User => SCOTT (ADM = NO)
User => SYSMAN (ADM = NO)
User => FLOWS_030000 (ADM = NO)
User => HR (ADM = NO)
User => XDB (ADM = NO)
For updates please visit /tools.htm
PL/SQL procedure successfully completed.
SQL> exec who_has_priv('GRANT ANY PRIVILEGE');
who_has_priv: Release 1.0.1.0.0 - Production on Thu Mar 19 12:22:36 2009
Copyright (c) 2009 PeteFinnigan.com Limited. All rights reserved.
Privilege => GRANT ANY PRIVILEGE has been granted to =>
====================================================================
Role => IMP_FULL_DATABASE (ADM = NO) which is granted to =>
User => SYS (ADM = YES)
User => WKSYS (ADM = NO)
Role => DBA (ADM = NO) which is granted to =>
User => SYS (ADM = YES)
User => SYSMAN (ADM = NO)
User => C (ADM = NO)
User => B1 (ADM = NO)
User => SYSTEM (ADM = YES)
Role => DATAPUMP_IMP_FULL_DATABASE (ADM = NO) which is
granted t
o =>
Role => DBA (ADM = NO) which is granted to =>
User => SYS (ADM = YES)
User => SYSMAN (ADM = NO)
User => C (ADM = NO)
User => B1 (ADM = NO)
User => SYSTEM (ADM = YES)
User => SYS (ADM = YES)
Role => DATAPUMP_IMP_FULL_DATABASE (ADM = NO) which is granted to =>
Role => DBA (ADM = NO) which is granted to =>
User => SYS (ADM = YES)
User => SYSMAN (ADM = NO)
User => C (ADM = NO)
User => B1 (ADM = NO)
User => SYSTEM (ADM = YES)
User => SYS (ADM = YES)
User => SYS (ADM = NO)
Role => DBA (ADM = YES) which is granted to =>
User => SYS (ADM = YES)
User => SYSMAN (ADM = NO)
User => C (ADM = NO)
User => B1 (ADM = NO)
User => SYSTEM (ADM = YES)
User => WKSYS (ADM = NO)
User => PP (ADM = NO)
For updates please visit /tools.htm
PL/SQL procedure successfully completed.
SQL>
|
Download the script and install if its useful. Beware that the original thinking around why these scripts are sqlplus scripts and not packages is that its better to not install security tools within the database unless they are well controlled. This is because a security tool is useful not only to the DBA who wants to secure his database but also to the person who wants to find out the weaknesses.
If anyone would like the other scripts mentioned above to install into the database as procedures, let me know via a comment and I will modify them and make them available
April 2nd, 2009 at 03:35 am
Pete Finnigan says:
how about using ApEx for something like this and give it a "pretty" interface?