Call: +44 (0)7759 277220 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: "SQL Injection Exploitation techniques"] [Next entry: "Oracle Security training in Edinburgh with Pete Finnigan"]

A database installed version of who has privilege script



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 http://www.petefinnigan.com/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 http://www.petefinnigan.com/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


There has been 2 Comments posted on this article


April 2nd, 2009 at 03:35 am

chet says:

how about using ApEx for something like this and give it a "pretty" interface?



April 2nd, 2009 at 09:37 am

Pete says:

Hi Chet,

Thanks for your comment. Yes it would be nice to have a nice GUI or pretty front end to these scripts but for two reasons I dont do it. First, i focus on the database layer so do not tend to have the time to write GUI front ends, even though APEX is nice and fast to develop with but as you will have seen with my blog I have little time recently to even blog.

Second, i always say to people to at least initially stick with the command line, it then doesnt "blur" the results in anyway. GUI's are nice but people have a tendancy to click and click and drill and lose the clear focus. A full text report doesnt lose that. I am not against GUI's and indeed my company is at the moment developing a commercial Oracle security scanner tool that will be available later in the year and of course there will be "pretty" reports as part of that.

Thanks for your comment.

cheers

Pete