Want to post your scripts here in this blog ? Email it to snojha@gmail.com. You will be rewarded for your scripts if selected by our experts.

Wednesday, January 13, 2010

Oracle Critical File Protection

-- Step 1: Collect the file locations for control, redo log, and data files:
-- Step 2: Check file permission

Select 'Control Files: '||value from v$parameter where name= 'control_files';
Select 'Control Files: '||value from v$parameter where name= 'spfile';
select 'Logfile: '||member from v$logfile;
select 'Datafile: '||name from v$datafile;

Oracle Redo log files

-- Step 1: Check how many redo log files exist.

select member from v$logfile;

-- Step 2: Check whether each group has more than 1 members.

select count(*) from V$LOG where members >1;

Auditing of Commands

SELECT name
FROM stmt_audit_option_map
WHERE name not in (select audit_option from dba_stmt_audit_opts)
AND name not like 'SELECT%'
AND name not like 'INSERT%'
AND name not like 'UPDATE%'
AND name not like 'DELETE%'
AND name not like 'EXECUTE%'
AND name not like 'LOCK%'
AND name not in
('CREATE DIRECTORY','DROP DIRECTORY','GRANT LIBRARY',
'WRITEDOWN DBLOW','READUP DBHIGH','WRITEUP DBHIGH',
'WRITEDOWN','READUP','WRITEUP','EXISTS','SUMMARY',
'NETWORK','DEBUG CONNECT USER','DEBUG CONNECT ANY',
'CREATE CLUSTER','CREATE DATABASE LINK','CREATE DIMENSION',
'CREATE LIBRARY','CREATE PROCEDURE','CREATE ROLE',
'CREATE SEQUENCE','CREATE TABLE','CREATE TRIGGER',
'CREATE TYPE','CREATE VIEW')
AND decode(name, 'TABLESPACE', 3,
'PUBLIC DATABASE LINK', 2,
'PUBLIC SYNONYM', 2,
'ROLLBACK SEGMENT', 3,
'TABLESPACE', 3,
'USER', 3, 100) >
(SELECT COUNT(*)
FROM dba_stmt_audit_opts
WHERE audit_option LIKE '%'||name||'%')
UNION ALL
select name from system_privilege_map
where name not in
(select privilege from dba_priv_audit_opts)
and name not in (select name from stmt_audit_option_map)
and name not in ('EXTENDS TYPE','ALTER OPERATOR')
and name not like '%ROLE%'
and name not like '%SECURITY%'
and name not like '%EXECUTE%'
and name not like '%RULE%';

Audit Table Permissions

select grantee from dba_tab_privs
where table_name='AUD$'
and grantee not in ('DELETE_CATALOG_ROLE')
and grantee not in
(select grantee from dba_role_privs where granted_role='DBA')
order by grantee;

Account Permissions

select grantee||': '||privilege||': '||owner||'.'||table_name
from dba_tab_privs where grantee not in (select role from dba_roles)
and grantee not in ('ORDSYS','OUTLN','PUBLIC','SYS','SYSTEM',
'CTXSYS','MDSYS','ODM','OLAPSYS','WKSYS','WMSYS','XDB','LBACSYS',
'OSE$HTTP$ADMIN','AURORA$JIS$UTILITY$','REPADMIN','PERFSTAT','SYSMAN')
and table_name<>'DBMS_REPCAT_INTERNAL_PACKAGE'
and table_name not like '%RP'
and grantee not in
(select grantee from dba_tab_privs where table_name in ('DBMS_DEFER','DEFLOB'))
and grantee not in (select grantee from dba_role_privs where granted_role='DBA');

Resource Limits Not Enabled

show parameter resource_limit;

-- OR

Select value from v$parameter where name='resource_limit';

REMOTE_LOGIN_PASSWORDFILE

Show parameter remote_login_passwordfile;

-- OR

Select value from v$parameter where name='remote_login_passwordfile';

Failed Login Attempts

select profile||': '||limit from dba_profiles,
(SELECT limit AS def_login_attmpts
FROM dba_profiles
WHERE profile = 'DEFAULT'
AND resource_name = 'FAILED_LOGIN_ATTEMPTS')
WHERE resource_name='FAILED_LOGIN_ATTEMPTS'
AND ((replace(limit,'DEFAULT',def_login_attmpts) IN ('UNLIMITED',NULL))
OR (lpad(replace(limit,'DEFAULT',def_login_attmpts),40,'0') >
lpad('3',40,'0')));

Idle Time Resource Usage Limit

Select limit from DBA_PROFILES where profile='DEFAULT' and resource_name='IDLE_TIME';
select profile||': '||limit
FROM dba_profiles,
(SELECT limit AS def_idl_tm
FROM dba_profiles
WHERE profile = 'DEFAULT'
AND resource_name = 'IDLE_TIME')
WHERE resource_name='IDLE_TIME'
AND ((replace(limit,'DEFAULT',def_idl_tm) IN ('UNLIMITED', NULL))
OR (lpad(replace(limit,'DEFAULT',def_idl_tm),40,'0') >
lpad('15',40,'0')));

users or roles granted ALL PRIVILEGES or too many privileges

-- Check for all privs:

(select count(*) from system_privilege_map) intersect
(select count(*) from dba_sys_privs group by grantee);

-- Check for number of privs in case too many:

select count(*) from system_privilege_map;

-- Check how many privileges each account has:

select count(*),grantee
from dba_sys_privs
group by grantee;

PUBLIC is granted with Object Permissions

select owner||'.'||table_name||': '||privilege from dba_tab_privs
where grantee='PUBLIC'
and owner not in('SYS','CTXSYS','MDSYS','ODM','OLAPSYS','MTSSYS',
'ORDPLUGINS','ORDSYS','SYSTEM','WKSYS','WMSYS','XDB','LBACSYS',
'PERFSTAT','SYSMAN','DMSYS','EXFSYS');

Roles Granted With Admin

select grantee||': '||granted_role from dba_role_privs
where grantee not in('DBA','SYS','SYSTEM','WKSYS','LBACSYS','WMSYS')
and admin_option='YES'
and grantee not in (select distinct owner from dba_objects)
and grantee not in (select grantee from dba_role_privs where granted_role='DBA');

PUBLIC is granted with System Privileges

select privilege from dba_sys_privs where grantee='PUBLIC';

Check which user has been granted with the 'CONNECT' role

select grantee
from dba_role_privs r, dba_users u
where granted_role ='CONNECT' and
r.grantee = u.username and
u.account_status not like '%LOCKED%' and
grantee not in ('SYS','SYSTEM','DBSNMP')
union
select grantee
from dba_role_privs r, dba_users u
where r.grantee = u.username and
u.account_status not like '%LOCKED%' and
grantee not in ('SYS','SYSTEM','DBSNMP') and
granted_role in
(select grantee
from dba_role_privs
where granted_role = 'CONNECT' and
grantee <> 'DBA');

Check which user has been granted with the 'RESOURCE' role

select grantee
from dba_role_privs r, dba_users u
where granted_role ='RESOURCE' and
r.grantee = u.username and
u.account_status not like '%LOCKED%' and
grantee not in ('SYS','SYSTEM','DBSNMP')
union
select grantee
from dba_role_privs r, dba_users u
where r.grantee = u.username and
u.account_status not like '%LOCKED%' and
grantee not in ('SYS','SYSTEM','DBSNMP') and
granted_role in
( select grantee
from dba_role_privs
where granted_role = 'RESOURCE' and
grantee <> 'DBA');

Check which user has 'ALTER SESSION' and 'ALTER SYSTEM' privileges

select grantee from dba_sys_privs where privilege = 'ALTER SESSION';


select grantee from dba_sys_privs where privilege = 'ALTER SYSTEM';

Prevent granting of CREATE LIBRARY

select * from dba_sys_privs where privilege='CREATE LIBRARY';

Check for privileges with 'ANY' keywordgranted.

select grantee
from dba_sys_privs s, dba_users u
where s.grantee = u.username and
u.account_status not like '%LOCKED%' and
privilege like '%ANY%' and
grantee <> 'DBA' and
grantee not in ('SYS','SYSTEM','MDSYS','CTXSYS',
'ORDSYS','ORDPLUGINS','AURORA$JIS$UTILITY$',
'HR','ODM','ODM_MTR','OE','OLAPDBA','OLAPSYS',
'OSE$HTTP$ADMIN','OUTLN','TRACESVR','REPADMIN',
'PM','QS','QS_ADM','QS_CB','QS_CBADM', 'DBSNMP',
'QS_CS','QS_ES','QS_OS','QS_WS','RMAN','SH','WKSYS',
'WMSYS','XDB','WKPROXY','LBACSYS','OLAPSVR', 'PERFSTAT')
union
select grantee
from dba_role_privs r, dba_users u
where r.grantee = u.username and
u.account_status not like '%LOCKED%' and
granted_role in
( select grantee
from dba_sys_privs
where privilege like '%ANY%' and
grantee <> 'DBA'
) and
grantee not in ('SYS','SYSTEM','MDSYS','CTXSYS',
'ORDSYS','ORDPLUGINS','AURORA$JIS$UTILITY$',
'HR','ODM','ODM_MTR','OE','OLAPDBA','OLAPSYS',
'OSE$HTTP$ADMIN','OUTLN','TRACESVR','REPADMIN',
'PM','QS','QS_ADM','QS_CB','QS_CBADM', 'DBSNMP',
'QS_CS','QS_ES','QS_OS','QS_WS','RMAN','SH','WKSYS',
'WMSYS','XDB','WKPROXY','LBACSYS','OLAPSVR', 'PERFSTAT')
union
select grantee
from dba_role_privs r, dba_users u
where r.grantee = u.username and
u.account_status not like '%LOCKED%' and
granted_role <> 'DBA' and
granted_role in
( select grantee
from dba_role_privs
where granted_role in
( select grantee
from dba_sys_privs
where privilege like '%ANY%' and
grantee <> 'DBA'
)
) and
grantee not in ('SYS','SYSTEM','MDSYS','CTXSYS',
'ORDSYS','ORDPLUGINS','AURORA$JIS$UTILITY$',
'HR','ODM','ODM_MTR','OE','OLAPDBA','OLAPSYS',
'OSE$HTTP$ADMIN','OUTLN','TRACESVR','REPADMIN',
'PM','QS','QS_ADM','QS_CB','QS_CBADM', 'DBSNMP',
'QS_CS','QS_ES','QS_OS','QS_WS','RMAN','SH','WKSYS',
'WMSYS','XDB','WKPROXY','LBACSYS','OLAPSVR', 'PERFSTAT');

Check for Restricted PL/SQL Packages

select table_name from dba_tab_privs
where grantee='PUBLIC'
and privilege ='EXECUTE'
and table_name in
('UTL_SMTP', 'UTL_TCP', 'UTL_HTTP', 'UTL_FILE',
'DBMS_RANDOM','DBMS_LOB', 'DBMS_SQL','DBMS_JOB',
'DBMS_BACKUP_RESTORE','DBMS_OBFUSCATION_TOOLKIT');

Check for Role Permissions

select grantee,owner,table_name from dba_tab_privs
where privilege in ('ALTER','REFERENCES','INDEX')
and grantee not in ('DBA','SYSTEM','LBACSYS','XDBADMIN','XDB_ADMIN')
and table_name not in ('SDO_IDX_TAB_SEQUENCE','XDB$ACL')
and grantee not in (select grantee from dba_role_privs where granted_role='DBA')
and grantee not in (select owner from dba_objects);

Prevent granting of privileges that have WITH GRANT option

select grantee||': '||owner||'.'||table_name from dba_tab_privs
where grantable='YES'
and owner not in ('SYS','MDSYS','ORDPLUGINS',
'ORDSYS','SYSTEM','WMSYS','XDB','LBACSYS')
and grantee not in (select grantee from dba_role_privs where granted_role='DBA')
order by grantee;

Check Privileges Granted With Admin

select grantee,privilege from dba_sys_privs
where grantee not in
('SYS','SYSTEM','AQ_ADMINISTRATOR_ROLE','DBA','MDSYS','LBACSYS','SCHEDULER_ADMIN','WMSYS')
and admin_option='YES'
and grantee not in (select grantee from dba_role_privs where granted_role='DBA');

SYSDBA Privilege Assignments

SELECT USERNAME FROM V$PWFILE_USERS
WHERE USERNAME NOT IN
(SELECT GRANTEE FROM DBA_ROLE_PRIVS WHERE GRANTED_ROLE='DBA')
and USERNAME !='INTERNAL' and sysdba='TRUE';

Check for System Privilege Assignments

select grantee||': '||PRIVILEGE from dba_sys_privs
where privilege<>'CREATE SESSION' and
grantee not in ('PUBLIC','AQ_ADMINISTRATOR_ROLE',
'AQ_USER_ROLE','CTXSYS','DBA','DELETE_CATALOG_ROLE',
'EXECUTE_CATALOG_ROLE', 'EXP_FULL_DATABASE',
'GATHER_SYSTEM_STATISTICS','HS_ADMIN_ROLE', 'IMP_FULL_DATABASE',
'LOGSTDBY_ADMINISTRATOR', 'MDSYS','ODM',
'OEM_MONITOR','OLAPSYS','ORDSYS','OUTLN','MTSSYS',
'RECOVERY_CATALOG_OWNER','SELECT_CATALOG_ROLE','SNMPAGENT',
'SYSTEM','WKSYS','WKUSER','WMSYS','WM_ADMIN_ROLE','XDB',
'ANONYMOUS','CONNECT','DBSNMP','JAVADEBUGPRIV','ODM_MTR',
'OLAP_DBA','ORDPLUGINS','RESOURCE','RMAN','SYS','WKPROXY',
'AURORA$JIS$UTILITY$','AURORA$ORB$UNAUTHENTICATED',
'OSE$HTTP$ADMIN','TIMESERIES_DBA','TIMESERIES_DEVELOPER',
'OLAP_USER')
and grantee not in (select grantee from dba_role_privs where granted_role='DBA')
and grantee not in (select username from dba_users where account_status = 'LOCKED');

Check for Roles Assigned to PUBLIC

select granted_role from dba_role_privs where grantee='PUBLIC';

Check for Access to System Tables/DBA Views

select grantee,privilege,owner,table_name from dba_tab_privs
where (owner='SYS' or table_name like 'DBA_%')
and privilege <> 'EXECUTE'
and grantee not in ('PUBLIC','AQ_ADMINISTRATOR_ROLE','AQ_USER_ROLE',
'AURORA$JIS$UTILITY$','OSE$HTTP$ADMIN','TRACESVR',
'CTXSYS','DBA','DELETE_CATALOG_ROLE','EXECUTE_CATALOG_ROLE',
'EXP_FULL_DATABASE','GATHER_SYSTEM_STATISTICS','HS_ADMIN_ROLE',
'IMP_FULL_DATABASE','LOGSTDBY_ADMINISTRATOR','MDSYS','ODM',
'OEM_MONITOR','OLAPSYS','ORDSYS','OUTLN','RECOVERY_CATALOG_OWNER',
'SELECT_CATALOG_ROLE','SNMPAGENT','SYSTEM','WKSYS','WKUSER',
'WMSYS','WM_ADMIN_ROLE','XDB','LBACSYS','PERFSTAT','XDBADMIN')
and grantee not in (select grantee from dba_role_privs where granted_role='DBA')
order by grantee;

Check for Tablespace Quotas

Select username,tablespace_name
from dba_ts_quotas
where username not in (select distinct owner from dba_objects)
and username not in (select grantee from dba_role_privs where granted_role='DBA');

Check for Default Tablespace

Select username from dba_users
where (default_tablespace= 'SYSTEM' or temporary_tablespace= 'SYSTEM')
and username not in
('AURORA$JIS$UTILITY$', 'AURORA$ORB$UNAUTHENTICATED', 'DBSNMP',
'MDSYS', 'ORDPLUGINS', 'ORDSYS', 'OSE$HTTP$ADMIN', 'OUTLN', 'REPADMIN',
'SYS', 'SYSTEM','TRACESVR','MTSSYS','DIP');

Replication Account Use

select 'The number of replication objects defined is: '||
count(*) from system.REPCAT$_REPOBJECT;

Oracle Predefined Roles

select grantee||': '||granted_role from dba_role_privs
where grantee not in
('SYS','SYSTEM','MDSYS','CTXSYS',
'ORDSYS','ORDPLUGINS','AURORA$JIS$UTILITY$',
'HR','ODM','ODM_MTR','OE','OLAPDBA','OLAPSYS',
'OSE$HTTP$ADMIN','OUTLN','TRACESVR','REPADMIN',
'PM','PUBLIC','QS','QS_ADM','QS_CB','QS_CBADM', 'DBSNMP',
'QS_CS','QS_ES','QS_OS','QS_WS','RMAN','SH','WKSYS',
'WMSYS','XDB','WKPROXY','LBACSYS','OLAPSVR', 'PERFSTAT')
and grantee not in (select role from dba_roles)
and grantee not in
(select grantee from dba_role_privs where granted_role='DBA')
and grantee not in (select distinct owner from dba_objects)
and granted_role in
('AQ_ADMINISTRATOR_ROLE',
'AQ_USER_ROLE','AUTHENTICATEDUSER','CONNECT',
'CTXAPP','DELETE_CATALOG_ROLE',
'EJBCLIENT','EXECUTE_CATALOG_ROLE',
'EXP_FULL_DATABASE','GATHER_SYSTEM_STATISTICS',
'GLOBAL_AQ_USER_ROLE','HS_ADMIN_ROLE',
'IMP_FULL_DATABASE','JAVADEBUGPRIV', 'JAVAIDPRIV','JAVASYSPRIV',
'JAVAUSERPRIV','JAVA_ADMIN','JAVA_DEPLOY',
'LOGSTDBY_ADMINISTRATOR','OEM_MONITOR','OLAP_DBA',
'RECOVERY_CATALOG_OWNER','RESOURCE','SALES_HISTORY_ROLE',
'SELECT_CATALOG_ROLE','WKUSER','WM_ADMIN_ROLE','XDBADMIN')
order by grantee;

Check for Active Schema Owner Account

select distinct owner from dba_objects, dba_users
where owner not in
('SYS','SYSTEM','MDSYS','CTXSYS',
'ORDSYS','ORDPLUGINS','AURORA$JIS$UTILITY$',
'ODM','ODM_MTR','OLAPDBA','OLAPSYS','MTSSYS',
'OSE$HTTP$ADMIN','OUTLN','LBACSYS',
'PUBLIC','DBSNMP','RMAN','WKSYS',
'WMSYS','XDB')
and owner = username
and account_status not like '%LOCKED';

Check Developer Privileges on Shared Systems

SELECT GRANTEE||': '||PRIVILEGE FROM DBA_SYS_PRIVS WHERE
(PRIVILEGE LIKE 'CREATE%' OR PRIVILEGE LIKE 'ALTER%'OR PRIVILEGE LIKE 'DROP%')
AND PRIVILEGE<>'CREATE SESSION'
AND GRANTEE NOT IN
('SYS','SYSTEM','ANONYMOUS','AQ_ADMINISTRATOR_ROLE','MTSSYS',
'CONNECT','CTXSYS','DBA','IMP_FULL_DATABASE','MDSYS','ODM',
'ODM_MTR','OEM_MONITOR','OLAPSYS','OLAP_DBA','ORDPLUGINS','ORDSYS',
'RECOVERY_CATALOG_OWNER','RESOURCE','SYS','WKPROXY','WKSYS',
'WKUSER','WMSYS','XDB','TIMESERIES_DEVELOPER','TIMESERIES_DBA',
'LBACSYS','DBSNMP','OLAP_USER') order by grantee;

Check for Unauthorized Object Owner

select distinct owner from dba_objects
where owner not in
('SYS','SYSTEM','MDSYS','CTXSYS',
'ORDSYS','ORDPLUGINS','AURORA$JIS$UTILITY$',
'HR','ODM','ODM_MTR','OE','OLAPDBA','OLAPSYS',
'OSE$HTTP$ADMIN','OUTLN','LBACSYS','MTSYS',
'PM','PUBLIC','QS','QS_ADM','QS_CB','QS_CBADM', 'DBSNMP',
'QS_CS','QS_ES','QS_OS','QS_WS','RMAN','SH','WKSYS',
'WMSYS','XDB')
and owner not in (select grantee from dba_role_privs where granted_role='DBA');

Check if DBA Includes Non-default Account

select grantee from dba_role_privs
where granted_role='DBA'
and grantee not in ('SYS','SYSTEM','CTXSYS','WKSYS','SYSMAN');

GLOBAL_NAMES Parameter

Select value from v$parameter where name= 'global_names';

Oracle database log mode

Select log_mode from v$database;

'user_dump_dest' and 'background_dump_dest'

Select value from v$parameter where name='user_dump_dest';

Select value from v$parameter where name='background_dump_dest';

'user_dump_dest' and 'background_dump_dest'

Select value from v$parameter where name='user_dump_dest';

Select value from v$parameter where name='background_dump_dest';

Default Oracle System Identifier

Select value from v$parameter where name= 'instance_name';

Password Verify Function

SELECT profile, limit
FROM dba_profiles,
(SELECT limit AS def_pwd_verify_func
FROM dba_profiles
WHERE resource_name = 'PASSWORD_VERIFY_FUNCTION'
AND profile = 'DEFAULT')
WHERE resource_name='PASSWORD_VERIFY_FUNCTION'
AND REPLACE(limit,'DEFAULT',def_pwd_verify_func) in
('UNLIMITED','NULL');

Account Lock Time

SELECT profile, limit
FROM dba_profiles,
(SELECT limit AS def_pwd_lock_tm
FROM dba_profiles
WHERE profile = 'DEFAULT'
AND resource_name = ' PASSWORD_LOCK_TIME ')
WHERE resource_name = ' PASSWORD_LOCK_TIME '
AND ((replace(limit,'DEFAULT',def_pwd_lock_tm) IN ('UNLIMITED',NULL))
OR (lpad(replace(limit,'DEFAULT',def_pwd_lock_tm),40,'0') <
lpad('.0104',40,'0')));

Password Reuse

-- Check for both reuse max and reuse time not set

select profile from DBA_PROFILES
where (resource_name='PASSWORD_REUSE_MAX'
and limit in ('UNLIMITED','NULL'))
and profile in
(select profile from DBA_PROFILES where
resource_name='PASSWORD_REUSE_TIME')
and limit in ('UNLIMITED','NULL');

-- Check for reuse max with value that is less than allowed minimum

select profile from DBA_PROFILES
where resource_name='PASSWORD_REUSE_MAX'
and limit not in ('UNLIMITED','NULL')
and limit < '10';

-- Check for reuse time that is less than allowed minimum

select profile from DBA_PROFILES
where resource_name='PASSWORD_REUSE_TIME'
and limit not in ('UNLIMITED','NULL')
and limit< '365';

Password Life Time

SELECT profile, limit
FROM dba_profiles,
(SELECT limit AS def_pwd_life_tm
FROM dba_profiles
WHERE profile = 'DEFAULT'
AND resource_name = 'PASSWORD_LIFE_TIME')
WHERE resource_name = 'PASSWORD_LIFE_TIME'
AND ((replace(limit,'DEFAULT',def_pwd_life_tm) IN ('UNLIMITED',NULL))
OR (lpad(replace(limit,'DEFAULT',def_pwd_life_tm),40,'0') >
lpad('90',40,'0')));

Database Links - SQL

Show all links

Select db_link||': '||host from dba_db_links;

-- Check links
select owner, db_link, username from dba_db_links;

select owner||': '||db_link from dba_db_links
where (username is not NULL and username <> 'CURRENT_USER');

select 'Number of replication objects defined = '||count(*)
from dba_tables where table_name='REPCAT$_REPOBJECT';

--Database Link Permissions

select grantee||': '||privilege from dba_tab_privs
where grantee <> 'DELETE_CATALOG_ROLE'
and table_name='LINK$'
and grantee not in (select grantee from dba_role_privs where granted_role='DBA');

-- Check which links are defined in LINK$

select substr(name,1,20) Name, substr(host,1,20) Host, substr(password,1,20),
substr(authusr,1,20), substr(authpwd,1,20), substr(passwordx,1,5)
from sys.link$;

determine if pl/sql code is wrapped or not

select owner||'.'||name from dba_source
where
line=1
and
owner not in ('SYS', 'CTXSYS', 'MDSYS', 'ODM', 'OE', 'OLAPSYS',
'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'PM', 'QS_ADM',
'RMAN', 'SYSTEM', 'WKSYS','WMSYS','XDB')
and
owner not like 'OEM%'
and
text not like '%wrapped%'
and
type in ('PACKAGE BODY','FUNCTION','PROCEDURE');

Determine Version and options of database examined

select comp_name, version, status from dba_registry;

Check the patch level

select * from registry$history;

Get the name of the DB

select name from v$database;

Oracle Critical Patch Update Advisory - October 2009

A Critical Patch Update is a collection of patches for multiple security vulnerabilities. It also includes non-security fixes that are required (because of interdependencies) by those security patches. Critical Patch Updates are cumulative, except as noted below, but each advisory describes only the security fixes added since the previous Critical Patch Update. Thus, prior Critical Patch Update Advisories should be reviewed for information regarding earlier accumulated security fixes. Please refer to:
Critical Patch Updates and Security Alerts for information about Oracle Security Advisories.

OPatch lsinventory

Ensure that you are able to invoke the opatch lsinventory -detail command and are able to see the node information being printed out. A sample listing of the output of the command is as follows:

Oracle interim Patch Installer version 10.2.0.4.6
Copyright (c) 2009, Oracle Corporation. All rights reserved..
Oracle Home : /home/racqa/102_twork/toii/toiir/toiir571/HOMEtoiir571
Central Inventory : /home/racqa/102_twork/toii/toiir/toiir571/inventory
from : /home/racqa/102_
twork/toii/toiir/toiir571/HOMEtoiir571/oraInst.loc
OPatch version : 10.2.0.4.6
OUI version : 10.2.0.4.6
OUI location : /home/racqa/102_twork/toii/toiir/toiir571/HOMEtoiir571/oui
Log file location : /home/racqa/102_
twork/toii/toiir/toiir571/HOMEtoiir571/cfgtoollogs/opatch/opatch-2008_May_30_
01-04-52-PDT_Mon.log
Lsinventory Output file location : /home1/racqa/102_
twork/toii/toiir/toiir571/HOMEtoiir571/cfgtoollogs/opatch/lsinv/lsinventory-2008_
May_30_01-04-52-PDT_Mon.txt
--------------------------------------------------------------------------------
Installed Top-level Products (1):
Oracle Database 10g 10.2.0.4.6
There are 1 products installed in this Oracle Home.
Installed Products (151):
Advanced Queueing (AQ) API 10.2.0.4.6
Advanced Replication 10.2.0.4.6
Agent Required Support Files 10.2.0.4.6
Assistant Common Files 10.2.0.4.6
Authentication and Encryption 10.2.0.4.6
Bali Share 1.1.18.0.0
Character Set Migration Utility 10.2.0.4.6
CSS Single-instance Common Files 10.2.0.4.6
Data Management Services Common Files 10.2.0.4.6
Database Configuration Assistant 10.2.0.4.6
Database SQL Scripts 10.2.0.4.6
Database Upgrade Assistant 10.2.0.4.6
Database Verify Utility 10.2.0.4.6
Database Workspace Manager 10.2.0.4.6
DBJAVA Required Support Files 10.2.0.4.6
Documentation Required Support Files 10.2.0.4.6
Enterprise Edition Options 10.2.0.4.6
Enterprise Manager Agent 10.2.0.4.6
Enterprise Manager Common Files 10.2.0.4.6
Enterprise Manager Minimal Integration 10.2.0.4.6
Enterprise Manager plugin Common Files 10.2.0.4.6
Enterprise Manager Repository 10.2.0.4.6
Export/Import 10.2.0.4.6
Extended Windowing Toolkit 3.3.18.0.0
Generic Connectivity Common Files 10.2.0.4.6
Generic Connectivity Using ODBC 10.2.0.4.6
Installation Common Files 10.2.0.4.6
Installer SDK Component 10.2.0.1.0
iSQL*Plus 10.2.0.4.6
Java Naming and Directory Interface Libraries 10.2.0.4.6
Java Runtime Environment 1.4.2.0.0
JDBC Common Files 10.2.0.4.6
JDBC/OCI Common Files 10.2.0.4.6
JDBC/OCI Common Files for Instant Client 10.2.0.4.6
LDAP Required Support Files 10.2.0.4.6
New Database ID 10.2.0.4.6
Object Type Translator 10.2.0.4.6
Oracle Advanced Security 10.2.0.4.6
Oracle C++ Call Interface 10.2.0.4.6
Oracle C++ Call Interface for Instant Client 10.2.0.4.6
Oracle Call Interface (OCI) 10.2.0.4.6
Oracle Client Required Support Files 10.2.0.4.6
Oracle Code Editor 1.2.1.0.0I
Oracle Containers for Java 10.2.0.4.6
Oracle Core Required Support Files 10.2.0.4.6
Oracle Data Mining 10.2.0.4.6
Oracle Database 10g 10.2.0.4.6
Oracle Database 10g 10.2.0.4.6
Oracle Database User Interface 2.2.13.0.0
Oracle Database Utilities 10.2.0.4.6
Oracle Development Kit 10.2.0.4.6
Oracle Display Fonts 9.0.2.0.0
Oracle Enterprise Manager 10g Database Control 10.2.0.4.6
Oracle Enterprise Manager Console DB 10.2.0.4.6
Oracle Extended Windowing Toolkit 3.4.28.0.0
Oracle Globalization Support 10.2.0.4.6
Oracle Help For Java 4.2.5.0.0a
Oracle Help for the Web 1.1.7.0.0a
Oracle Ice Browser 5.2.3.3.0
Oracle interMedia 10.2.0.4.6
Oracle interMedia Annotator 10.2.0.4.6
Oracle interMedia Audio 10.2.0.4.6
Oracle interMedia Client Compatibility Files 10.2.0.4.6
Oracle interMedia Client Demos 10.2.0.4.6
Oracle interMedia Client Option 10.2.0.4.6
Oracle interMedia Common Files 10.2.0.4.6
Oracle interMedia Image 10.2.0.4.6
Oracle interMedia Java Advanced Imaging 10.2.0.4.6
Oracle interMedia Java Client 10.2.0.4.6
Oracle interMedia Locator 10.2.0.4.6
Oracle interMedia Video 10.2.0.4.6
Oracle interMedia Web Client 10.2.0.4.6
Oracle Internet Directory Client 10.2.0.4.6
Oracle Internet Directory Client Common Files 10.2.0.4.6
Oracle Internet Directory Tools 10.2.0.4.6
Oracle Java Tools 10.2.0.4.6
Oracle JDBC Thin Driver for JDK 1.2 10.2.0.4.6
Oracle JDBC Thin Driver for JDK 1.4 10.2.0.4.6
Oracle JDBC Thin Driver for JDK 1.4 for Instant Client 10.2.0.4.6
Oracle JDBC/OCI Driver for JDK 1.4 10.2.0.4.6
Oracle JFC Extended Windowing Toolkit 4.2.18.0.0
Oracle JVM 10.2.0.4.6
Oracle Locale Builder 10.2.0.4.6
Oracle Message Gateway Common Files 10.2.0.4.6
Oracle Net 10.2.0.4.6
Oracle Net Configuration Assistant 10.2.0.4.6
Oracle Net Listener 10.2.0.4.6
Oracle Net Manager 10.2.0.4.6
Oracle Net Required Support Files 10.2.0.4.6
Oracle Net Services 10.2.0.4.6
Oracle Notification Service 9.0.4.0.0
Oracle OLAP 10.2.0.4.6
Oracle OLAP API 10.2.0.4.6
Oracle OLAP Catalog 10.2.0.4.6
Oracle One-Off Patch Installer 10.2.0.1.0
Oracle Partitioning 10.2.0.4.6
Oracle Programmer 10.2.0.4.6
Oracle RAC Required Support Files 10.2.0.4.6
Oracle RAC Required Support Files-HAS 10.2.0.4.6
Oracle Real Application Clusters 10.2.0.4.6
Oracle Spatial 10.2.0.4.6
Oracle Starter Database 10.2.0.4.6
Oracle Text 10.2.0.4.6
Oracle UIX 2.1.21.0.0a
Oracle Ultra Search Common Files 10.2.0.4.6
Oracle Ultra Search Middle-Tier 10.2.0.4.6
Oracle Ultra Search Server 10.2.0.4.6
Oracle Universal Installer 10.2.0.1.0
Oracle Wallet Manager 10.2.0.4.6
Oracle XML Developer's Kit 10.2.0.4.6
Oracle XML Runtime Components 10.2.0.4.6
Oracle XML SQL Utility 10.2.0.4.6
Oracle10g Real Application Clusters Common Files 10.2.0.4.6
Parser Generator Required Support Files 10.2.0.4.6
Perl Interpreter 5.6.1.0.2d
PL/SQL 10.2.0.4.6
PL/SQL Embedded Gateway 10.2.0.4.6
PL/SQL Required Support Files 10.2.0.4.6
Platform Required Support Files 10.2.0.4.6
Precompiler Common Files 10.2.0.4.6
Precompiler Required Support Files 10.2.0.4.6
Pro*C/C++ 10.2.0.4.6
RDBMS Required Support Files 10.2.0.4.6
Recovery Manager 10.2.0.4.6
regexp 2.1.9.0.0
Required Support Files 10.2.0.4.6
Sample Schema 10.2.0.4.6
Secure Socket Layer 10.2.0.4.6
Secure Socket Layer 10.2.0.4.6
SQL*Loader 10.2.0.4.6
SQL*Plus 10.2.0.4.6
SQL*Plus Required Support Files 10.2.0.4.6
SQLJ Runtime 10.2.0.4.6
SSL Required Support Files 10.2.0.4.6
SSL Required Support Files for InstantClient 10.2.0.4.6
Sun JDK 1.4.2.0.0
Sun JDK extensions 9.0.4.0.0
Utilities Common Files 10.2.0.4.6
Visigenics ORB 3.4.0.0.0
XDK Required Support Files 10.2.0.4.6
XML 10.2.0.4.6
XML Class Generator for C++ 10.2.0.4.6
XML Class Generator for Java 10.2.0.4.6
XML Parser for C 10.2.0.4.6
XML Parser for C++ 10.2.0.4.6
XML Parser for Java 10.2.0.4.6
XML Parser for Oracle JVM 10.2.0.4.6
XML Parser for PL/SQL 10.2.0.4.6
XML Transviewer Beans 10.2.0.4.6
XML Transx 10.2.0.4.6
XSQL Servlet 10.2.0.4.6
There are 151 products installed in this Oracle Home.
Intermin patches (1) :
Patch 3811942 : applied on Mon May 30 00:59:33 PDT 2008
Created on 31 Aug 2007, 12:06:28 hrs US/Pacific
Bugs fixed:
3811942
Files Touched:
/hosp.o --> ORACLE_HOME/lib/libagent10.a
/pesblt.o --> ORACLE_HOME/lib/libpls10.a
/kgl.o --> ORACLE_HOME/lib/libgeneric10.a
/qcpi6.o --> ORACLE_HOME/lib/libgeneric10.a
ins_rdbms.mk --> ORACLE_HOME/rdbms/lib/iextjob
ins_rdbms.mk --> ORACLE_HOME/rdbms/lib/iextjobo
ins_rdbms.mk --> ORACLE_HOME/rdbms/lib/client_sharedlib
/hosp.o --> ORACLE_HOME/lib/libserver10.a
/prse.o --> ORACLE_HOME/lib/libserver10.a
/prsa.o --> ORACLE_HOME/lib/libserver10.a
/prsf.o --> ORACLE_HOME/lib/libserver10.a
/prssz.o --> ORACLE_HOME/lib/libserver10.a
/kprc.o --> ORACLE_HOME/lib/libserver10.a
/qmhdr.o --> ORACLE_HOME/lib/libserver10.a
/pesblt.o --> ORACLE_HOME/lib/libpls10.a
/qcpi6.o --> ORACLE_HOME/lib/libgeneric10.a
prvtpexp.plb --> ORACLE_HOME/rdbms/admin/prvtpexp.plb
sjsex.o --> ORACLE_HOME/rdbms/lib/sjsex.o
ins_rdbms.mk --> ORACLE_HOME/rdbms/lib/iextjob
ins_rdbms.mk --> ORACLE_HOME/rdbms/lib/iextjobo
ins_rdbms.mk --> ORACLE_HOME/rdbms/lib/ioracle
ins_rdbms.mk --> ORACLE_HOME/rdbms/lib/client_sharedlib
driload.pkh --> ORACLE_HOME/ctx/admin/driload.pkh
/oracle/sysman/assistants/util/em/EMConfigStep.class --> ORACLE_
HOME/assistants/jlib/assistantsCommon.jar
/oracle/sysman/assistants/util/sqlEngine/SQLEngine.class --> ORACLE_
HOME/assistants/jlib/assistantsCommon.jar
/oracle/sysman/assistants/dbca/backend/DBEntryStep.class --> ORACLE_
HOME/assistants/dbca/jlib/dbca.jar
/oracle/sysman/assistants/dbca/backend/EMConfigStep.class --> ORACLE_
HOME/assistants/dbca/jlib/dbca.jar
/oracle/sysman/assistants/dbca/backend/PostDBCreationStep.class --> ORACLE_
HOME/assistants/dbca/jlib/dbca.jar
/oracle/sysman/emcp/EMConfig.class --> ORACLE_HOME/jlib/emca.jar
/oracle/sysman/emcp/IEMCAConstants.class --> ORACLE_HOME/jlib/emca.jar
/oracle/sysman/emcp/EMConfig.class --> ORACLE_HOME/sysman/jlib/emCORE.jar
/oracle/sysman/emcp/IEMCAConstants.class --> ORACLE_
HOME/sysman/jlib/emCORE.jar
/oracle/sysman/emSDK/conf/ConfigManager.class --> ORACLE_
HOME/sysman/jlib/emCORE.jar
/oracle/sysman/emSDK/eml/EmlConstants.class --> ORACLE_
HOME/sysman/jlib/emCORE.jar
/oracle/sysman/util/pref/PrefUtil.class --> ORACLE_
HOME/sysman/jlib/emCORE.jar
emctl.pl --> ORACLE_HOME/emdw/bin/emctl.pl
/ncrfipm.o --> ORACLE_HOME/lib/libnro10.a
libsqlplus.a --> ORACLE_HOME/lib/libsqlplus.a
libsqlplus.so --> ORACLE_HOME/lib/libsqlplus.so
libisqlplus.a --> ORACLE_HOME/lib/libisqlplus.a
libisqlplus.so --> ORACLE_HOME/lib/libisqlplus.so
mdprivs.sql --> ORACLE_HOME/md/admin/mdprivs.sql
prvtgmd.plb --> ORACLE_HOME/md/admin/prvtgmd.plb
sdolrsmd.sql --> ORACLE_HOME/md/admin/sdolrsmd.sql
prvtccbk.plb --> ORACLE_HOME/md/admin/prvtccbk.plb
wk0acl.pkh --> ORACLE_HOME/ultrasearch/admin/wk0acl.pkh
wk0acl.plb --> ORACLE_HOME/ultrasearch/admin/wk0acl.plb
wk0adm.pkh --> ORACLE_HOME/ultrasearch/admin/wk0adm.pkh
wk0adm.plb --> ORACLE_HOME/ultrasearch/admin/wk0adm.plb
wk0snapshot.plb --> ORACLE_HOME/ultrasearch/admin/wk0snapshot.plb
wk0util.pkh --> ORACLE_HOME/ultrasearch/admin/wk0util.pkh
wk0util.plb --> ORACLE_HOME/ultrasearch/admin/wk0util.plb
Patch Location in Inventory:
/home1/racqa/102_
twork/toii/toiir/toiir571/HOMEtoiir571/inventory/oneoffs/3811942
Patch Location in Storage area:
/home1/racqa/102_twork/toii/toiir/toiir571/HOMEtoiir571/.patch_
storage/3811942_Aug_31_2007_12_06_28
Rac system comprising of multiple nodes
Local node = stadu56
Remote node = stacg16
--------------------------------------------------------------------------------
OPatch succeeded.

If you do not find the node information correctly printed out, you need to update the node list. For more information on updating the node list, see the Oracle Universal Installer User's Guide.

How to use wrap utility to encode Oracle database source code Object

The wrap program provided by Oracle encodes the PL/SQL source code but does not encrypt it.

select owner||'.'||name from dba_source
where
line=1
and
owner not in ('SYS', 'CTXSYS', 'MDSYS', 'ODM', 'OE', 'OLAPSYS',
'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'PM', 'QS_ADM',
'RMAN', 'SYSTEM', 'WKSYS','WMSYS','XDB')
and
owner not like 'OEM%'
and
text not like '%wrapped%'
and
type in ('PACKAGE BODY','FUNCTION','PROCEDURE');