User audit script how?

[webfuture]

I would like a script to run under SQLplus to audit a user.

I am on oracle 9i.

The information I would like is:
1. default and temp table space
2. Roles
3. granted system privs
4. granted objects privs

Thanks,

Simon

 

[SantaMufasa]

Simon,

Here is a script that I wrote for you:

set pagesize 500
set linesize 200
set verify off
set feedback off
set heading on
accept usr prompt "Enter the Oracle User to Audit: "
prompt
col a heading 'Default Tablespace for "&usr":' format a30
select default_tablespace a from dba_users where username = upper('&usr');
col a heading '"&usr" is a member of|these Roles:' format a30
select granted_role a from dba_role_privs where grantee = upper('&usr');
col a heading '"&usr" has|these system privileges:' format a30
select privilege a from dba_sys_privs where grantee = upper('&usr');
prompt
prompt "&usr" has these object privileges:
prompt ====================================
col obj format a50
col priv format a15
break on obj
select privilege priv, owner||'.'||table_name obj
from dba_tab_privs where grantee = upper('&usr');

You will need to save the above code to a script (I called mine "Audit_User.sql"). Here is a sample invocation of the script:

SQL> @audit_user
Enter the Oracle User to Audit: dhunt


Default Tablespace for "dhunt"
------------------------------
DATA2

"dhunt" is a member of
these Roles:
------------------------------
DBA
CONNECT
RESOURCE
WHATEVER
DEV_USERS

"dhunt" has
these system privileges:
------------------------------
ALTER ANY TABLE
CREATE ANY TABLE
SELECT ANY TABLE
CREATE DATABASE LINK
UNLIMITED TABLESPACE

"dhunt" has these object privileges:
====================================

PRIV            OBJ
--------------- ---------------------
READ            SYS.FLATFILE_IO
WRITE
READ            SYS.MYFLATFILES
WRITE

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com:

Providing remote Database Administration and support services

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[webfuture]

Mufasa,

Thanks... Works like a charm

Simon

 

[dbtoo2001]

Man, every day is christmas with SantaMuffas around!

 

[SantaMufasa]

Ho, Ho, Ho...Merry Kwanza/Christmas/Chanukah/Festivus in June.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com:

Providing remote Database Administration and support services

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.