Integration of Oracle 11g R2 with SSIM (DBA Activity)
Hi Guys, Here i have explained some simple way of integrating Oracle 11gR2 DB with SSIM in OFFBOX approach:
Following are the simple DB changes will help you in quickly configuring your DB to work with SSIM.
POA for Database changes
1 Make Sure Audit trail enable
NAME TYPE VALUE
audit_sys_operations boolean FALSE
audit_trail String DB
IF Not then run following Command to enable.
ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
SHUTDOWN;
STARTUP;
2 create user ssimtest identified by ssimtest
default tablespace users
temporary tablespace temp
quota unlimited on users;
3 Grant create session to ssimtest
4 create role read_only_role;
5 Grant select on DBA_AUDIT_TRAIL to read_only_role;
6 Grant read_only_role to ssimtest;
7 AUDIT ALL BY SYS,SYTEM BY ACCESS;
8 AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY sys by Access;
9 AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY sys,system by Access;
POA for Database changes
1 create user ssimtest identified by ssimtest
default tablespace users (if not then other like Perfstat)
temporary tablespace temp
quota unlimited on default tablespace;
2 Grant create session to ssimtest
3 create role read_only_role;
4 Grant select on DBA_AUDIT_TRAIL to read_only_role;
5 Grant read_only_role to ssimtest;
6 Create Profile for No-expiry of DB ID and configure to SSIMTEST.
Profile Configuration
1 Connect to the database
sqlplus " sys as sysdba"
password **********
2 Create Profile
create profile SSIM_Profile limit
composite_limit unlimited
connect_time unlimited
cpu_per_call unlimited
cpu_per_session unlimited
failed_login_attempts 10
idle_time unlimited
logical_reads_per_call unlimited
logical_reads_per_session unlimited
password_grace_time unlimited
password_life_time unlimited
password_lock_time unlimited
password_reuse_max unlimited
password_reuse_time unlimited
password_verify_function null
private_sga unlimited
sessions_per_user unlimited;
3 Assign to user ' SSIMTEST'
alter user SSIMTEST profile SSIM_Profile
- Check if "Oracle XML Database" is installed or not. If not installed, then install the same.
Installing XML Database:
sql > select comp_name,version,status,schema from dba_registry ;
Needs to create tablespace xdb_tbsp
CREATE TABLESPACE XDB_TBSPXDB DATAFILE
<Path\XMLDB_01.DBF> SIZE 1536M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576 DEFAULT NOCOMPRESS SEGMENT SPACEMANAGEMENT AUTO;
For Installing xdb
create tablespace xdb
datafile 'd:\oracle\databases\ora10\xmldb_01.dbf' size 500m
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576 SEGMENT SPACE MANAGEMENT AUTO
@\rdbms\admin\catqm.sql <xml_user_passwd> <xml_db_tbsp_name> <Temp_tablespace_name>
Eg. @?\rdbms\admin\catqm.sql xdb xdb_tbsp TEMP
@?\rdbms\admin\catxdbj.sql
sql > select comp_name,version,status,schema from dba_registry ;
sql>select username,account_status from dba_users;
- Also check for the ACL of Oracle XML database to grant the access to SSIMTEST
- Check for the following 3 scripts to verify the ACL’s for Oracle XML database:
3 scripts:
The user can be created in small case but when creating and assigning ACL’s – use CAPS i.e. “SSIMTEST”.
script 1
begin
dbms_network_acl_admin.create_acl (
acl => 'SSIMTEST.xml',
description => 'Allow UTL_INADDR to SSIMTEST',
principal => 'SSIMTEST',
is_grant => TRUE,
privilege => 'connect'
);
commit;
end;
/
script 2
begin
dbms_network_acl_admin.add_privilege (
acl => 'SSIMTEST.xml',
principal => 'SSIMTEST',
is_grant => TRUE,
privilege => 'resolve'
);
commit;
end;
/
script 3
begin
dbms_network_acl_admin.assign_acl(
acl => 'SSIMTEST.xml',
host => '*’
);
commit;
end;
/
To conclude,
when assigning the ACL (script 3) a “*” or “localhost” works – IP of the Oracle server or SSIM collector machine does not.
"principal => 'SSIMTEST'" – that’s the Oracle user for use with the SSIM Oracle DB collector sensor.
The user can be created in small case but when creating and assigning ACL’s – use CAPS i.e. “SSIMTEST”.
For more details on configuring ACL for XML please refer the below links:
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_networkacl_adm.htm
http://www.oracle-base.com/articles/11g/FineGrainedAccessToNetworkServices_11gR1.php
For rest of the Sesnor configuration part at SSIM, there are detailed docs available: