The first CPU of 2009 is already out, was released on January 13, 2009. Like always, Oracle strongly recommends applying the patches as soon as possible.
To know more about the January CPU like products and components affected, please refer to the following link:
http://www.oracle.com/technology/deploy/security/critical-patch-updates/cpujan2009.html
Cheers,
Francisco Munoz Alvarez
Here is the scripts to help you to get this information:
PROMPT
PROMPT
PROMPT ******************************************** ROLES AND PRIVILEGES
PROMPT
PROMPT ******************************************** USER ROLES
SELECT grantee user, granted_role, admin_option, default_role
FROM dba_role_privs
WHERE grantee IN (SELECT username FROM dba_users)
AND grantee NOT LIKE‘%SYS%’
AND grantee NOT IN (‘DBSNMP’,‘OUTLN’)
ORDER BY grantee;
PROMPT
PROMPT ******************************************** USER PRIVILEGES
SELECT grantee user, privilege, admin_option
FROM dba_sys_privs
WHERE grantee IN (SELECT username FROM dba_users)
AND grantee NOT LIKE‘%SYS%’
AND grantee NOT IN (‘DBSNMP’,‘OUTLN’)
ORDER BY grantee;
set pages 58
column role format a19 heading‘User or Role’
column admin_option format a3 heading‘Ad?’
column owner format a7 heading‘Owner’
column table_name format a26 heading‘Table name’
column privilege format a21 heading‘Priv, Grant or Role’
column r_ord noprint
breakon role start
titel132‘ORACLE ROLES REPORT’
select 2 r_ord, b.role role, b.owner owner, b.table_name,
b.privilege privilege, b.grantable admin_option
from sys.role_tab_privs b
union
select 1 r_ord, a.role role,‘N/A’owner,‘N/A’table_name,
a.privilege privilege, a.admin_option admin_option
from sys.role_sys_privs a
union
select 3 r_ord, c.role role,‘N/A’owner,‘N/A’table_name,
c.granted_role privilege, c.admin_option admin_option
from sys.role_role_privs c
order by role,r_ord;
set flush onterm on pagesize 22 linesize 80
clear columns
clear breaks
ttitle off
pause Press enter tocontinue
Cheers,
Francisco Munoz Alvarez
For any Oracle versions prior to 9i you will need to set audit_trail parameter in the pfile ie.g.:
audit_trail = DB
For later Oracle Versions:
SQL>alter system set audit_trail = DB;
To enable auditing for a specific object run the following command:
SQL>AUDIT SELECT, INSERT, UPDATE, DELETE ON SCHEMA.TABLE;
To stop auditing use:
SQL>NOAUDIT SELECT, INSERT, UPDATE, DELETE ON SCHEMA.TABLE;
Or to audit any statement that queries, delete, insert or updates any table, issue the following statement:
AUDIT SELECT TABLE, DELETE TABLE, INSERT TABLE, UPDATE TABLE;
If you want to audit a specific user will need to run the following commands:
AUDIT ALTER TABLE, SELECT TABLE, INSERT TABLE, … ,GRANT PROCEDURE by <USERNAME>;
To stop the audit for the user you will need to run:
NOAUDIT ALTER TABLE, SELECT TABLE, INSERT TABLE, … ,GRANT PROCEDURE by <USERNAME>;
To see the results of the auditing use please run:
SQL> select * from dba_audit_trail;
This is an excellent Oracle documentation to learn how to implement it:
Also I’ll recommend you to take a look on the following links:
Cheers,Francisco Munoz Alvarez
Powered by WordPress
