This table holds the names of the databases to be checked


DROP TABLE DB_NAMES CASCADE CONSTRAINTS ;

CREATE TABLE DB_NAMES (
DB_NUM NUMBER (4),
HOST_NAME VARCHAR2 (64),
DBID NUMBER,
SID VARCHAR2 (16),
DB_LINK_NAME VARCHAR2 (128),
ORACLE_HOME VARCHAR2 (128),
TNS_ADMIN VARCHAR2 (128),
RMAN_BASE VARCHAR2 (128),
BDEST VARCHAR2 (128),
UDEST VARCHAR2 (128),
CDEST VARCHAR2 (128),
ARCHDEST_1 VARCHAR2 (128),
ARCHDEST_2 VARCHAR2 (128),
EXPORT_DEST VARCHAR2 (128))
TABLESPACE USERS
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 40960
NEXT 40960
PCTINCREASE 50
MINEXTENTS 1
MAXEXTENTS 505
FREELISTS 1 FREELIST GROUPS 1 )
NOCACHE;

GRANT DELETE ON DB_NAMES TO SCOTT;

GRANT INSERT ON DB_NAMES TO SCOTT;

GRANT SELECT ON DB_NAMES TO SCOTT;

GRANT UPDATE ON DB_NAMES TO SCOTT;


This table is to configure the cjhecks to be conducted


DROP TABLE CHECK_LIST CASCADE CONSTRAINTS ;

CREATE TABLE CHECK_LIST (
CHK_NUM NUMBER (3) NOT NULL,
CHK_DETAIL VARCHAR2 (4000),
COMP_CONTROL VARCHAR2 (2000),
CONSTRAINT PK_CHECK_LIST
PRIMARY KEY ( CHK_NUM )
USING INDEX
TABLESPACE INDEXES PCTFREE 10
STORAGE ( INITIAL 16384 NEXT 16384 PCTINCREASE 50 ))
TABLESPACE USERS
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 40960
NEXT 40960
PCTINCREASE 50
MINEXTENTS 1
MAXEXTENTS 505
FREELISTS 1 FREELIST GROUPS 1 )
NOCACHE;

GRANT UPDATE ON CHECK_LIST TO SCOTT;

The folowing data is to be inserted into this (CHECK_LIST) table

1 the Oracle init parameter specification UTL-FILE_DIR value does not
contain “*”. For UNIX environment UTL_FILE_DIR value does not contain “.”.
2 To ensure tsecurity on file systems that allow symbolic links, users are
not allowed WROTE permissionsto directories accessible by PLSQL file I/O
functions.
3 Oracle is not allowed to allow remote host-based authentication
4 Oracle is not configured to enable roles based on remote opearting system
user group membership.
5 Each Administrator has their valid user account in the Server where
database resed to do admin tasks.
6 If Oracle Names is used, the names control utility is password protected
7 The file names.ora contains configuration information for the Oracle Names
Database. Only valid Names server databases are included in the file.
8 The database listener is password protected and password shoukld be
encrypted in the listener file.
9 SQL*Net is configured for valid destinations only.
10 Destinations external to the company are not allowed.
11 Password are in accordance with the requirements in the general
information security standards
12 Aministrators are assigned their own unique account that is accountable
to them.
13 Default accounts are not used to perform adminitstration.
14 Authentication is not performed using the host login when the host is not
proven secure.
15 Accounts other than Oracle Default accounts are not grantedprivileges or
any roles to PUBLIC Role.
16 Object privileges are not granted with the WITH ADMIN option.
17 Accounts, other than Oracle Default, do not grant any privilege.
18 System Privileges or Roles are not granted woth WITH ADMIN oprion.
19 Accounts other than Oracle default accounts do not have any system
privileges or roles WITH ADMIN option.
20 System and Object privileges are granted via roles.
21 Roles are developed for granting each different type of account/job
function the appropraitesystem and object privileges. These accounts are
assigned only the role(s) (privileges) required.
22 The adminitrator develops roles for managing access to the database and
evelopes specific roles for each type of account.
23 System and Object privileges are not assigned directly to any account.
24 Database links are not created using CONNECT TO option.
25 If any Databse links are created using CONNECT TO option, then
initialization parameter 07_dictionary_accessibility Is set to FALSE.
26 07_DICTIONARY_ACCESSIBILITY is TRUE, and any accounts, except
adminitrators, do not have SELECT privilege onSYS.LINK$ table.


DROP TABLE SECURITY_CHECK_REPORT CASCADE CONSTRAINTS ;

CREATE TABLE SECURITY_CHECK_REPORT (
REPORT_DATE_TIME DATE,
DBID NUMBER,
SID VARCHAR2 (16),
CHK_1 CHAR (1),
CHK_2 CHAR (1),
CHK_3 CHAR (1),
CHK_4 CHAR (1),
CHK_5 CHAR (1),
CHK_6 CHAR (1),
CHK_7 CHAR (1),
CHK_8 CHAR (1),
CHK_9 CHAR (1),
CHK_10 CHAR (1),
CHK_11 CHAR (1),
CHK_12 CHAR (1),
CHK_13 CHAR (1),
CHK_14 CHAR (1),
CHK_15 CHAR (1),
CHK_16 CHAR (1),
CHK_17 CHAR (1),
CHK_18 CHAR (1),
CHK_19 CHAR (1),
CHK_20 CHAR (1),
CHK_21 CHAR (1),
CHK_22 CHAR (1),
CHK_23 CHAR (1),
CHK_24 CHAR (1),
CHK_25 CHAR (1),
CHK_26 CHAR (1),
CHK_27 CHAR (1))
TABLESPACE USERS
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 40960
NEXT 40960
PCTINCREASE 50
MINEXTENTS 1
MAXEXTENTS 505
FREELISTS 1 FREELIST GROUPS 1 )
NOCACHE;

GRANT UPDATE ON SECURITY_CHECK_REPORT TO SCOTT;


java source

Necessary privileges are to be granted to enable the user to create
the java source


CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED Util AS
import java.io.*;
import java.lang.*;

public class Util extends Object
{
public static int RunThis(java.lang.String[] args)
{
Runtime rt = Runtime.getRuntime();
int rc = -1;
try
{
Process p = rt.exec(args[0]);

int bufSize = 4096;
BufferedInputStream bis =
new BufferedInputStream(p.getInputStream(),
bufSize);
int len;
byte buffer[]=new byte[bufSize];

while((len=bis.read(buffer,0,bufSize)) !=
-1)
System.out.write(buffer,0,len);
rc=p.waitFor();
}

catch (Exception e)

{
e.printStackTrace();
rc=-1;
}

finally

{
return rc;
}
}
}
/

function run_cmd

CREATE OR REPLACE function run_cmd(p_cmd in varchar2) return number
as
language java
name ‘Util.RunThis(java.lang.String[]) return integer’;
/

procedure


CREATE OR REPLACE procedure Execute_Cmd(p_cmd in varchar2)
as
x number;
begin
x:=run_cmd(p_cmd);
if x = 0 then
dbms_output.put_line(‘I am done’);
elsif x <> 0 then
dbms_output.put_line(‘I failed’);
end if;
end;
/


CREATE OR REPLACE procedure Execute_Cmd(p_cmd in varchar2)
as
x number;
begin
x:=run_cmd(p_cmd);
if x = 0 then
dbms_output.put_line(‘I am done’);
elsif x <> 0 then
dbms_output.put_line(‘I failed’);
end if;
end;
/


CREATE OR REPLACE procedure sp_chk_pwd_lock_time_setting(p_db_name in
varchar2) as


— use of ref cursor is required to avoid ORA-04054 error

TYPE RefCurType1 IS REF CURSOR;
sql_cur RefCurType1;

TYPE RefCurType2 IS REF CURSOR;
sql_cur_1 RefCurType2;

v_pwd_lock_time sys.dba_profiles.limit%type;
v_value_str varchar2(1024);
v_value_str_1 varchar2(1024);
v_sql varchar2(1024);
v_sql_1 varchar2(1024);
v_dbid fusa.db_names.dbid%type;
begin

select dbid
into v_dbid
from fusa.db_names
where db_link_name = upper(p_db_name);

v_value_str:=null;

v_sql:=’select distinct profile from dba_profiles@’|| p_db_name ||’where
resource_name=’||”’FAILED_LOGIN_ATTEMPTS”’||’and limit
<>’||”’UNLIMITED”’;

OPEN sql_cur FOR v_sql;
loop
fetch sql_cur into v_value_str;
exit when sql_cur%notfound;

v_sql_1:=’select limit from dba_profiles@’||p_db_name||’where profile =
‘||v_value_str;

OPEN sql_cur_1 FOR v_sql_1;
loop
fetch sql_cur_1 into v_value_str_1;
exit when sql_cur%notfound;

if v_value_str_1 = ‘UNLIMITED’ then

update fusa.security_check_report
set chk_27 =’Y’,
Report_date_time = sysdate
where DBID= v_dbid;

else

update fusa.security_check_report
set chk_27 =’N’,
Report_date_time = sysdate
where DBID= v_dbid;

end if;
end loop;
end loop;

commit;
end;
/

CREATE OR REPLACE procedure sp_gen_security_rep(p_db_name in varchar2)
as
— use of ref cursor is required to avoid ORA-04054 error

TYPE RefCurType1 IS REF CURSOR;
sql_cur RefCurType1;

— declaration of variables

v_dbid db_names.dbid%type;
v_value integer;
v_value_str varchar2(1024);
v_cmd_string varchar2(1024);
v_input_file_dir varchar2(1024);
v_input_file utl_file.file_type;
v_new_line varchar2(1024);
v_sql varchar2(1024);

begin

select dbid
into v_dbid
from db_names
where db_link_name=p_db_name;

— check 01. UTL_FILE_DIR is set with * or . like wild characters


v_value_str:=null;
v_sql:=’select value from v$parameter@’||p_db_name ||’ where
name=’||”’utl_file_dir”’||’ or name=’||”’UTL_FILE_DIR”’;
open sql_cur for v_sql;
fetch sql_cur into v_value_str;

if v_value_str =’*’ or v_value_str =’.’ then

update security_check_report
set chk_1=’N’,Report_date_time = sysdate
where DBID = v_dbid;

else

update security_check_report
set chk_1=’Y’,Report_date_time = sysdate
where DBID = v_dbid;

end if;

— check 02. To ensure security on file systems that allow symbloic links,
users are not allowed WRITE permissions to directories accessible by plsql
i/o functions

— to ensure (1) find the directories that plsql i/o is permitted
— they can be under two catagories.
— (a) defined under UTL_FILE_DIR in the init.ora file


v_sql:=’select value from v$parameter@’||p_db_name||’ where (name =
‘||”’UTL_FILE_DIR”’||’ or name =lower(‘||”’UTL_FILE_DIR”’||’))’;
open sql_cur for v_sql;
fetch sql_cur into v_value_str;

if v_value_str <> NULL then
v_input_file_dir:=v_value_str;
v_cmd_string:=’ls -ltra ‘||v_input_file_dir ||’>
/tmp/public_access/UTL_FILE_DIR_permissions.txt’;
Execute_cmd(v_cmd_string);
v_input_file_dir:=’/tmp/public_access/’;
v_input_file:=utl_file.fopen(v_input_file_dir,’UTL_FILE_DIR_permissions.txt’,’r’);
loop
begin
UTL_FILE.GET_LINE(v_input_file,v_new_line);
exception
when no_data_found then
exit;
end;

if (substr(v_new_line,5,3)=’r–‘) or
(substr(v_new_line,8,3)=’r–‘)then
dbms_output.put_line(‘The others have read permissions only’);
update security_check_report
set chk_2=’Y’,Report_date_time = sysdate
where dbid = v_dbid;
elsif (substr(v_new_line,5,3)=’rw-‘) or
(substr(v_new_line,5,3)=’rwx’)or (substr(v_new_line,8,3)=’rw-‘) or
(substr(v_new_line,8,3)=’rwx’) then
update security_check_report
set chk_2=’N’,Report_date_time = sysdate
where dbid = v_dbid;
end if;
commit;
end loop;
utl_file.fclose(v_input_file);
end if;


— (b) defined any directories craeted using PLSQL
— the data dictionary view that stores all the info about the
directories that are used by plsql is dba_directories

v_value_str:= null;
v_sql:=’select directory_path from dba_directories’;

OPEN sql_cur FOR v_sql;
loop
fetch sql_cur into v_value_str;
exit when sql_cur%notfound;

if v_value_str <> NULL then
v_value_str:=v_input_file_dir;
v_cmd_string:=’ls -ltra ‘||v_input_file_dir ||’>
/tmp/plsql_dir/DIR_permissions.txt’;
Execute_cmd(v_cmd_string);
dbms_output.put_line(‘Generated temporary file to store plsql
directory/ies info.’);

v_input_file:=utl_file.fopen(v_input_file_dir,’/tmp/plsql_dir/DIR_permissions.txt’,’r’);
loop
begin
UTL_FILE.GET_LINE(v_input_file,v_new_line);
exception
when no_data_found then
exit;
end;

if (substr(v_new_line,5,3)=’r–‘) or
(substr(v_new_line,8,3)=’r–‘)then
dbms_output.put_line(‘The others have read permissions only’);
update security_check_report
set chk_2=’Y’,Report_date_time = sysdate
where dbid = v_dbid;
elsif (substr(v_new_line,5,3)=’rw-‘) or
(substr(v_new_line,5,3)=’rwx’)or (substr(v_new_line,8,3)=’rw-‘) or
(substr(v_new_line,8,3)=’rwx’) then
update security_check_report
set chk_2=’N’,Report_date_time = sysdate
where dbid = v_dbid;
end if;
commit;
end loop;
utl_file.fclose(v_input_file);
end if;
v_cmd_string:=’rm /tmp/plsql_dir/*.*’;
Execute_cmd(v_cmd_string);
dbms_output.put_line(‘Deleted all temporary file that stored the plsql
directory.’);
end loop;

— check 03.oracle is not configured to allow remote host-based
authentication.

v_value_str:= null;
v_sql:=’select value from v$parameter@’||p_db_name ||’ where
name=’||”’remote_os_authent”’||’ or
name=upper(‘||”’remote_os_authent”’||’)’;

open sql_cur for v_sql;
fetch sql_cur into v_value_str;
if v_value_str=’TRUE’ then

update security_check_report
set chk_3=’N’,Report_date_time = sysdate
where DBID = v_dbid;

else

update security_check_report
set chk_3=’Y’,Report_date_time = sysdate
where DBID = v_dbid;

end if;

— check 04. oracle is not configured to enable roles based on remote
operating system user group membership

v_value_str:= null;
v_sql:=’select value from v$parameter@’||p_db_name ||’ where
name=’||”’remote_os_roles”’||’or
name=upper(‘||”’remote_os_roles”’||’)’;
open sql_cur for v_sql;
fetch sql_cur into v_value_str;

if v_value_str =’TRUE’ then

update security_check_report
set chk_4=’N’,Report_date_time = sysdate
where DBID= v_dbid;

else

update security_check_report
set chk_4=’Y’,Report_date_time = sysdate
where DBID = v_dbid;

end if;


— check 05. each adminitsrator has their valid user account in the server
where database resides

v_value:= null;
v_sql:=’select count(*) from ( select username,osuser from
v$session@’||p_db_name||’ where type <> ‘||”’BACKGROUND”’||’and username
is not null and osuser is not null)’;
open sql_cur for v_sql;
fetch sql_cur into v_value_str;

if v_value <> 0 then

update security_check_report
set chk_5=’N’,Report_date_time = sysdate
where DBID = v_dbid;

else

update security_check_report
set chk_5=’Y’,Report_date_time = sysdate
where DBID = v_dbid;

end if;

— check 06. if Oracle Names utility is used, then it should be password
protected. this is a practice to be implemented and not a procedue that can
be verified

— check 07. the file names.ora contains configuration information for the
oracle names database. only valid names should be included in this.

— check 08. the database listener is password protected. If $TNS_ADMIN is
not set then look in the $ORACLE_HOME/network/admin directory. Alternatly
the tnsnames may exist in */etc or /var/opt/oracle as well.
— Generallly $TNS_ADMIN is set to /var/opt/oracle or /etc.

v_cmd_string:=’cat $TNS_ADMIN/listener.ora >
/tmp/read_listener/listener.txt’;
Execute_cmd(v_cmd_string);
dbms_output.put_line(‘Generated temporary file to store read_listener
directory/ies info.’);

v_input_file:=utl_file.fopen(v_input_file_dir,’/tmp/read_listener/listener.txt’,’r’);
loop
begin
UTL_FILE.GET_LINE(v_input_file,v_new_line);
exception
when no_data_found then
exit;
end;

if (substr(v_new_line,1,8)=’PASSWORD’) then
dbms_output.put_line(‘The Password is set’);
update security_check_report
set chk_8=’Y’,Report_date_time = sysdate
where dbid = v_dbid;
elsif (substr(v_new_line,1,8)<>’PASSWORD’) then
dbms_output.put_line(‘The Password is NOT set’);
update security_check_report
set chk_8=’N’,Report_date_time = sysdate
where dbid = v_dbid;
end if;
commit;
end loop;
utl_file.fclose(v_input_file);

v_cmd_string:=’rm /tmp/read_listener/*.*’;
Execute_cmd(v_cmd_string);
dbms_output.put_line(‘Deleted all temporary file that stored the
read_listener directory.’);


— check 09. SQL*Net is configured for valid destinations only.TNSNames.ora
file shall have the host defined for every alias
— in this context the following files are to be checked.

— (1)(a) TNSNames.ora
— hosts, host=, listed in the TNSNames.ora file must be valid company
network addresses and the system Identifiers.


v_cmd_string:=’cat $TNS_ADMIN/tnsnames.ora >
/tmp/read_tnsnames/tnsnames.txt’;
Execute_cmd(v_cmd_string);
dbms_output.put_line(‘Generated temporary file to store
read_listener directory/ies info.’);

v_input_file:=utl_file.fopen(v_input_file_dir,’/tmp/read_tnsnames/tnsnames.txt’,’r’);
loop
begin
UTL_FILE.GET_LINE(v_input_file,v_new_line);
exception
when no_data_found then
exit;
end;


v_value_str:= null;
v_sql:=’select host_name from dbnames’;
open sql_cur for v_sql;
fetch sql_cur into v_value_str;
exit when sql_cur%notfound;

if (substr(rtrim(ltrim(v_new_line,’ ‘),’ ‘),1,4))= initcap(‘Host’)
or (substr(rtrim(ltrim(v_new_line,’ ‘),’ ‘),1,4))= Upper(‘Host’)
or (substr(rtrim(ltrim(v_new_line,’ ‘),’ ‘),1,4))= lower(‘Host’ )
then
dbms_output.put_line(‘TNSNAMES alias is set to a right database’);

if v_value_str=
(substr(v_new_line,instr(ltrim(rtrim(v_new_line, ‘ ‘),’=’),+1))) or
v_value_str= (substr(v_new_line,instr(ltrim(rtrim(v_new_line, ‘
‘),’=’),+2)))
Then
dbms_output.put_line(‘The host name is valid ‘);
update security_check_report
set chk_9=’Y’,Report_date_time = sysdate
where dbid = v_dbid;
elsif
v_value_str<>substr(v_new_line,instr(ltrim(rtrim(v_new_line, ‘ ‘),’=’),+1))
or
v_value_str<>substr(v_new_line,instr(ltrim(rtrim(v_new_line, ‘
‘),’=’),+2)) Then
dbms_output.put_line(‘The host name is not valid ‘);
update security_check_report
set chk_9=’N’,Report_date_time = sysdate
where dbid = v_dbid;
end if;
commit;
end if;
end loop;
utl_file.fclose(v_input_file);

v_cmd_string:=’rm /tmp/read_tnsnames/*.*’;
Execute_cmd(v_cmd_string);
dbms_output.put_line(‘Deleted all temporary file/s that are stored
read_tnsnames directory.’);

— (1)(b) TNSNames.ora
— SID and SID_NAME should be a valid database name


v_cmd_string:=’cat $TNS_ADMIN/tnsnames.ora >
/tmp/read_tnsnames/tnsnames.txt’;
Execute_cmd(v_cmd_string);
dbms_output.put_line(‘Generated temporary file to store read_listener
directory/ies info.’);

v_input_file:=utl_file.fopen(v_input_file_dir,’/tmp/read_tnsnames/tnsnames.txt’,’r’);
loop
begin
UTL_FILE.GET_LINE(v_input_file,v_new_line);
exception
when no_data_found then
exit;
end;


v_value_str:= null;
v_sql:=’select db_name from dbnames’;
open sql_cur for v_sql;
fetch sql_cur into v_value_str;
exit when sql_cur%notfound;

if (substr(rtrim(ltrim(v_new_line,’ ‘),’ ‘),1,4))=
initcap(‘SID’) or
(substr(rtrim(ltrim(v_new_line,’ ‘),’ ‘),1,4))= Upper(‘SID’)
or
(substr(rtrim(ltrim(v_new_line,’ ‘),’ ‘),1,4))= lower(‘SID’) then

dbms_output.put_line(‘TNSNAMES alias is set to a right database’);

if v_value_str= substr(v_new_line,instr(ltrim(rtrim(v_new_line, ‘
‘),’=’),+1)) or
v_value_str= substr(v_new_line,instr(ltrim(rtrim(v_new_line, ‘
‘),’=’),+2))
Then
dbms_output.put_line(‘The host name is valid ‘);
update security_check_report
set chk_9=’Y’,Report_date_time = sysdate
where dbid = v_dbid;
elsif
v_value_str<>substr(v_new_line,instr(ltrim(rtrim(v_new_line, ‘ ‘),’=’),+1))
or
v_value_str<>substr(v_new_line,instr(ltrim(rtrim(v_new_line, ‘
‘),’=’),+2)) Then
dbms_output.put_line(‘The host name is not valid ‘);
update security_check_report
set chk_9=’N’,Report_date_time = sysdate
where dbid = v_dbid;
end if;
commit;
end if;
end loop;
utl_file.fclose(v_input_file);

v_cmd_string:=’rm /tmp/read_tnsnames/*.*’;
Execute_cmd(v_cmd_string);
dbms_output.put_line(‘Deleted all temporary file/s that are stored
read_tnsnames directory.’);

— (2)(a) listener.ora
— hosts, host=, listed in the listener.ora file must be valid company
network addresses and the system Identifiers.

v_cmd_string:=’cat $TNS_ADMIN/listener.ora >
/tmp/read_tnsnames/tnsnames.txt’;
Execute_cmd(v_cmd_string);
dbms_output.put_line(‘Generated temporary file to store
read_listener directory/ies info.’);

v_input_file:=utl_file.fopen(v_input_file_dir,’/tmp/read_tnsnames/tnsnames.txt’,’r’);
loop
begin
UTL_FILE.GET_LINE(v_input_file,v_new_line);
exception
when no_data_found then
exit;
end;


v_value_str:= null;
v_sql:=’select host_name from dbnames’;
open sql_cur for v_sql;
fetch sql_cur into v_value_str;
exit when sql_cur%notfound;

if (substr(rtrim(ltrim(v_new_line,’ ‘),’ ‘),1,4))=
initcap(‘Host’) or
(substr(rtrim(ltrim(v_new_line,’ ‘),’ ‘),1,4))= Upper(‘host’)
or
(substr(rtrim(ltrim(v_new_line,’ ‘),’ ‘),1,4))= lower(‘host’) then

dbms_output.put_line(‘Host is an accepted server/system’);

if (v_value_str= substr(v_new_line,instr(ltrim(rtrim(v_new_line, ‘
‘),’=’),+1))) or
(v_value_str= substr(v_new_line,instr(ltrim(rtrim(v_new_line, ‘
‘),’=’),+2))) Then
dbms_output.put_line(‘The host name is valid ‘);
update security_check_report
set chk_9=’Y’,Report_date_time = sysdate
where dbid = v_dbid;
elsif
(v_value_str<>substr(v_new_line,instr(ltrim(rtrim(v_new_line, ‘
‘),’=’),+1))) or
(v_value_str<>substr(v_new_line,instr(ltrim(rtrim(v_new_line, ‘
‘),’=’),+2) )) Then
dbms_output.put_line(‘The host name is not valid ‘);
update security_check_report
set chk_9=’N’,Report_date_time = sysdate
where dbid = v_dbid;
end if;
commit;
end if;
end loop;
utl_file.fclose(v_input_file);

v_cmd_string:=’rm /tmp/read_tnsnames/*.*’;
Execute_cmd(v_cmd_string);
dbms_output.put_line(‘Deleted all temporary file/s that are stored
read_tnsnames directory.’);


— (2)(b) listener.ora
— sid_name listed in the listener.ora file must be valid SID_NAME from the
init.ora file reflecting instance_name
— or the value for the column instance_name from v$instance Data
Dictionary.


v_cmd_string:=’cat $TNS_ADMIN/listener.ora >
/tmp/read_tnsnames/tnsnames.txt’;
Execute_cmd(v_cmd_string);
dbms_output.put_line(‘Generated temporary file to store read_listener
directory/ies info.’);

v_input_file:=utl_file.fopen(v_input_file_dir,’/tmp/read_tnsnames/tnsnames.txt’,’r’);
loop
begin
UTL_FILE.GET_LINE(v_input_file,v_new_line);
exception
when no_data_found then
exit;
end;


v_value_str:= null;
v_sql:=’select sid from dbnames’;
open sql_cur for v_sql;
fetch sql_cur into v_value_str;
exit when sql_cur%notfound;

if (substr(rtrim(ltrim(v_new_line,’ ‘),’ ‘),1,4))=
initcap(‘sid_name’) or
(substr(rtrim(ltrim(v_new_line,’ ‘),’ ‘),1,4))=
Upper(‘sid_name’) or
(substr(rtrim(ltrim(v_new_line,’ ‘),’ ‘),1,4))= lower(‘sid_name’)
then

dbms_output.put_line(‘Host is an accepted server/system’);

if (v_value_str= substr(v_new_line,instr(ltrim(rtrim(v_new_line, ‘
‘),’=’),+1))) or
(v_value_str= substr(v_new_line,instr(ltrim(rtrim(v_new_line, ‘
‘),’=’),+2))) Then
dbms_output.put_line(‘The host name is valid ‘);
update security_check_report
set chk_9=’Y’,Report_date_time = sysdate
where dbid = v_dbid;
elsif
(v_value_str<>substr(v_new_line,instr(ltrim(rtrim(v_new_line, ‘ ‘),’=’),+1))
) or
(v_value_str<>substr(v_new_line,instr(ltrim(rtrim(v_new_line, ‘
‘),’=’),+2))) Then
dbms_output.put_line(‘The host name is not valid ‘);
update security_check_report
set chk_9=’N’,Report_date_time = sysdate
where dbid = v_dbid;
end if;
commit;
end if;
end loop;
utl_file.fclose(v_input_file);

v_cmd_string:=’rm /tmp/read_tnsnames/*.*’;
Execute_cmd(v_cmd_string);
dbms_output.put_line(‘Deleted all temporary file/s that are stored
read_tnsnames directory.’);

— (3) tnsnav.ora
— in case of multi protocol interchange

v_cmd_string:=’cat $TNS_ADMIN/tnsnav.ora >
/tmp/read_tnsnames/tnsnames.txt’;
Execute_cmd(v_cmd_string);
dbms_output.put_line(‘Generated temporary file to store read_listener
directory/ies info.’);

v_input_file:=utl_file.fopen(v_input_file_dir,’/tmp/read_tnsnames/tnsnames.txt’,’r’);
loop
begin
UTL_FILE.GET_LINE(v_input_file,v_new_line);
exception
when no_data_found then
exit;
end;


v_value_str:= null;
v_sql:=’select host_name from dbnames’;
open sql_cur for v_sql;
fetch sql_cur into v_value_str;
exit when sql_cur%notfound;

if (substr(rtrim(ltrim(v_new_line,’ ‘),’ ‘),1,4))=
initcap(‘COMMUNITY=’) or
(substr(rtrim(ltrim(v_new_line,’ ‘),’ ‘),1,4))=
Upper(‘COMMUNITY=’) or
(substr(rtrim(ltrim(v_new_line,’ ‘),’ ‘),1,4))= lower(‘COMMUNITY=’)
then

dbms_output.put_line(‘TNSNAV community is set to a right database’);

if (v_value_str= substr(v_new_line,instr(ltrim(rtrim(v_new_line, ‘
‘),’=’),+1))) or
(v_value_str= substr(v_new_line,instr(ltrim(rtrim(v_new_line, ‘
‘),’=’),+2) )) Then
dbms_output.put_line(‘The host name is valid ‘);
update security_check_report
set chk_9=’Y’,Report_date_time = sysdate
where dbid = v_dbid;
elsif
(v_value_str<>substr(v_new_line,instr(ltrim(rtrim(v_new_line, ‘
‘),’=’),+1))) or
(v_value_str<>substr(v_new_line,instr(ltrim(rtrim(v_new_line, ‘
‘),’=’),+2) )) Then
dbms_output.put_line(‘The host name is not valid ‘);
update security_check_report
set chk_9=’N’,Report_date_time = sysdate
where dbid = v_dbid;
end if;
commit;
end if;
end loop;
utl_file.fclose(v_input_file);

v_cmd_string:=’rm /tmp/read_tnsnames/*.*’;
Execute_cmd(v_cmd_string);
dbms_output.put_line(‘Deleted all temporary file/s that are stored
read_tnsnames directory.’);


— (4)sqlnet.ora

— (5)protocol.ora


— check 10. Destinations external to the company are not allowed. This data
is to be from host file ???

— check 11. Passwords are in accordance with the requirements in the
general information security stanadards

v_value :=null;
v_sql:=(‘select count(*) from (select username from
dba_users@’||p_db_name ||’ where profile not in (select profile from
dba_profiles@’||p_db_name ||’ where
resource_name=’||”’PASSWORD_VERIFY_FUNCTION”’||’))’);
open sql_cur for v_sql;
fetch sql_cur into v_value;

if v_value <> 0 then

update security_check_report
set chk_11=’N’,Report_date_time = sysdate
where DBID = v_dbid;

else

update security_check_report
set chk_11=’Y’,Report_date_time = sysdate
where DBID = v_dbid;

end if;

— check 12.Administrators are assigned their own unique unique account that
is accountable to them.


v_value :=null;
v_sql:=(‘select count(*) from (select username from V$SESSION@’||p_db_name
||’ where USERNAME IN
(‘||”’SYS”’||’,’||”’SYSTEM”’||’,’||”’ORACLE”’||’,)’);
open sql_cur for v_sql;
fetch sql_cur into v_value;


if v_value <> 0 then

update security_check_report
set chk_12=’N’,Report_date_time = sysdate
where DBID = v_dbid;

else

update security_check_report
set chk_12=’Y’,Report_date_time = sysdate
where DBID = v_dbid;

end if;

— check 13.default accounts are not used to perform administration.

v_value :=null;
v_sql:=(‘select count(*) from (select username from V$SESSION@’||p_db_name
||’ where USERNAME IN
(‘||”’SYS”’||’,’||”’SYSTEM”’||’,’||”’ORACLE”’||’,)’);
open sql_cur for v_sql;
fetch sql_cur into v_value;

if v_value <> 0 then

update security_check_report
set chk_12=’N’,Report_date_time = sysdate
where DBID = v_dbid;

else

update security_check_report
set chk_12=’Y’,Report_date_time = sysdate
where DBID = v_dbid;

end if;

— check 14.authentication is not performed when using host logon when the
host has not been proven secure.


v_value_str:= null;
v_sql:=’select value from v$parameter@’||p_db_name ||’ where
name=’||”’remote_os_authent”’||’ or
name=upper(‘||”’remote_os_authent”’||’)’;

open sql_cur for v_sql;
fetch sql_cur into v_value_str;

if v_value_str=’TRUE’ then

update security_check_report
set chk_14=’N’,Report_date_time = sysdate
where DBID = v_dbid;

else

update security_check_report
set chk_14=’Y’,Report_date_time = sysdate
where DBID = v_dbid;

end if;


— check 15. accounts other than default oracle accounts are not granted
privileges or any roles to public role. If it returns values

v_value := null;
v_sql:=’select count(*) from (select * from dba_tab_privs@’||p_db_name
||’ where grantee=’||”’PUBLIC”’||’ and owner not in
(‘||”’SYS”’||’,’||”’SYSTEM”’||’,’||”’DBSNMP”’||’,’||”’ORACLE”’||’,’||”’OUTLN”’||’))’;
open sql_cur for v_sql;
fetch sql_cur into v_value;

if v_value <> 0 then

update security_check_report
set chk_15=’N’,Report_date_time = sysdate
where DBID = v_dbid;

else
update security_check_report
set chk_15=’Y’,Report_date_time = sysdate
where DBID = v_dbid;

end if;


— check 16. object privileges are not granted with admin option


v_value := null;
v_sql:=’select count(*) from (select grantee from
dba_tab_privs@’||p_db_name||’ where grantable = ‘||”’YES”’||’ and
grantee in ( select username from dba_users@’||p_db_name||’ where
username not in
(‘||”’SYS”’||’,’||”’SYSTEM”’||’,’||”’ORACLE”’||’,’||”’DBSNMP”’||’,’||”’OUTLN”’||’)))’;
open sql_cur for v_sql;
fetch sql_cur into v_value;
if v_value <> 0 then

update security_check_report
set chk_16=’N’,Report_date_time = sysdate
where DBID = v_dbid;

else
update security_check_report
set chk_16=’Y’,Report_date_time = sysdate
where DBID = v_dbid;

end if;


— check 17. Accounts other than Oracle default accounts do not grant
privileges


v_value := null;

v_sql:=’select count (*) from (select * from dba_tab_privs@’||p_db_name
||’ where grantor not
in(‘||”’SYSTEM”’||’,’||”’SYS”’||’,’||”’ORACLE”’||’,’||”’DBSNMP”’||’,’||”’OUTLN”’||’))’;
open sql_cur for v_sql;
fetch sql_cur into v_value;

if v_value <> 0 then

update security_check_report
set chk_17=’N’,Report_date_time = sysdate
where DBID = v_dbid;

else
update security_check_report
set chk_17=’Y’,Report_date_time = sysdate
where DBID = v_dbid;

end if;

— check 18. system privileges or roles are not granted with admin option

v_value := null;
v_sql:=’select count(*) from (select grantee from
dba_sys_privs@’||p_db_name||’ where admin_option = ‘||”’YES”’||’ and
grantee in ( select username from dba_users@’||p_db_name||’ where
username not in
(‘||”’SYS”’||’,’||”’SYSTEM”’||’,’||”’ORACLE”’||’,’||”’DBSNMP”’||’,’||”’OUTLN”’||’)))’;
open sql_cur for v_sql;
fetch sql_cur into v_value;

— here is another sql to meet the same purpose select count(*) from
(select *from dba_sys_privs where grantee not in
(‘SYS’,’SYSTEM’,’DBSNMP’,’OUTLN’,’ORACLE’)and grantee not in (select role
from dba_roles) AND ADMIN_OPTION = ‘YES’);

if v_value <> 0 then

update security_check_report
set chk_18=’N’,Report_date_time = sysdate
where DBID = v_dbid;

else
update security_check_report
set chk_18=’Y’,Report_date_time = sysdate
where DBID = v_dbid;

end if;

— check 19 accounts other than Oracle Default Accounts do not have any
system privileges or roles with admin option


v_value := null;
v_sql:=’select count(*) from (select * from dba_sys_privs@’||p_db_name||’
where grantee not in
(‘||”’SYS”’||’,’||”’SYSTEM”’||’,’||”’DBSNMP”’||’,’||”’OUTLN”’||’,’||”’ORACLE”’||’,’||”’DBA”’||’)and admin_option
= ‘||”’YES”’||’)’;
open sql_cur for v_sql;
fetch sql_cur into v_value;

if v_value <> 0 then

update security_check_report
set chk_19=’N’,Report_date_time = sysdate
where DBID = v_dbid;

else

update security_check_report
set chk_19=’Y’,Report_date_time = sysdate
where DBID= v_dbid;

end if;


–check 20 System and Object privileges are granted only via roles

v_value := null;

v_sql:=’select count(*) from (select grantee,privilege from
dba_sys_privs@’||p_db_name||’ where grantee not in
(‘||”’SYS”’||’,’||”’SYSTEM”’||’,’||”’DBSNMP”’||’,’||”’OUTLN”’||’,’||”’ORACLE”’||’,’||”’PUBLIC”’||’)
and grantee not in (select role from dba_roles@’||p_db_name||’) and grantee
in (select username from dba_users@’||p_db_name||’) union select
grantee,privilege from dba_tab_privs@’||p_db_name ||’ where owner not in
(‘||”’SYS”’||’,’||”’SYSTEM”’||’,’||”’DBSNMP”’||’,’||”’OUTLN”’||’,’||”’ORACLE”’||’)and grantee
not in (select role from dba_roles@’||p_db_name||’)and grantee <>
‘||”’PUBLIC”’||’and grantee in (select username from
dba_users@’||p_db_name||’))’;
open sql_cur for v_sql;
fetch sql_cur into v_value;

if v_value <> 0 then

update security_check_report
set chk_20=’N’,Report_date_time = sysdate
where DBID = v_dbid;

else
update security_check_report
set chk_20=’Y’,Report_date_time = sysdate
where DBID = v_dbid;

end if;

— check 21. Roles are developed for granting each different type of
account/job function the appropriate system and object orivileges.
— these accounts are assigned only the roles for each type of account.

— This can be certified by database administrators. It is a practice.

— check 22. The administrator develops roles for managing access to the
database, and develops specific roles for each account.
— This can be certified by database administrators. It is a practice.

— check 23. SYSTEM AND OBJECT PRIVILEGES ARE NOT ASSIGNED TO AN ACCOUNT
DIRECTLY.

v_value := null;
v_sql:=’ select COUNT(*) from ( ‘||
‘SELECT DECODE(SA1.GRANTEE#, 1,’||”’PUBLIC”’||’, U1.NAME) UserName, ‘||
‘SUBSTR(U2.NAME,1,20) Role, ‘||
‘SUBSTR(SPM.NAME,1,27) Privilege, ‘||
‘Users.profile ‘||
‘FROM SYS.SYSAUTH$ SA1, ‘||
‘SYS.SYSAUTH$ SA2,’||
‘SYS.USER$ U1, ‘||
‘SYS.USER$ U2, ‘||
‘SYS.SYSTEM_PRIVILEGE_MAP SPM,’||
‘SYS.DBA_USERS USERS’||
‘WHERE SA1.GRANTEE# = U1.USER# ‘||
‘AND SA1.PRIVILEGE# = U2.USER# ‘||
‘AND U2.USER# = SA2.GRANTEE# ‘||
‘AND SA2.PRIVILEGE# = SPM.PRIVILEGE ‘||
‘AND U1.NAME=USERS.USERNAME ‘||
‘UNION’||
‘SELECT U.NAME UserName,’||
‘NULL Role,’||
‘SUBSTR(SPM.NAME,1,27) Privilege,’||
‘Users.profile ‘||
‘FROM SYS.SYSTEM_PRIVILEGE_MAP SPM, ‘||
‘SYS.SYSAUTH$ SA, ‘||
‘SYS.USER$ U,’||
‘SYS.DBA_USERS USERS’||
‘WHERE SA.GRANTEE#=U.USER# ‘||
‘AND SA.PRIVILEGE#=SPM.PRIVILEGE ‘||
‘AND U.NAME=USERS.USERNAME ‘||
‘) where role is null ‘||
‘and username not in
(‘||”’SYS”’||’,’||”’SYSTEM”’||’,’||”’ORACLE”’||’,’||”’DBSNMP”’||’,’||”’OUTLN”’||’)’;

open sql_cur for v_sql;
fetch sql_cur into v_value;

if v_value <> 0 then

update security_check_report
set chk_23=’N’,Report_date_time = sysdate
where DBID = v_dbid;

else
update security_check_report
set chk_23=’Y’,Report_date_time = sysdate
where DBID= v_dbid;

end if;

— check 24. Database links are not created using CONNECT TO option

v_value := null;

v_sql:=’select count(*) from (select * from sys.link$@’||p_db_name ||’
where userid is not null)’;
open sql_cur for v_sql;
fetch sql_cur into v_value;

if v_value <> 0 then

update security_check_report
set chk_24=’N’,Report_date_time = sysdate
where DBID = v_dbid;

else
update security_check_report
set chk_24=’Y’,Report_date_time = sysdate
where DBID= v_dbid;

end if;


— check 25. Parameter 07_DICTIONARY_ACCESSABILITY is set to FALSE

v_value := null;

v_sql:=’select count(*)from (select * from v$parameter@’||p_db_name ||’
where (name like upper(‘||”’%dictionary%”’||’)or name like
‘||”’%dictionary%”’||’)and value = ‘||”’TRUE”’||’)’;
open sql_cur for v_sql;
fetch sql_cur into v_value;

if v_value <> 0 then

update security_check_report
set chk_25=’N’,Report_date_time = sysdate
where DBID= v_dbid;

else
update security_check_report
set chk_25=’Y’,Report_date_time = sysdate
where DBID= v_dbid;

end if;

— check 26 if Parameter 07_DICTIONARY_ACCESSABILITY is set to TRUE no user
is granted select privilege on LINK$ table

v_value := null;

v_sql:=’select count(*) from (select grantee from
dba_tab_privs@’||p_db_name ||’ where table_name=’||”’LINK$”’||’ and
privilege=’||”’SELECT”’||’)’;
open sql_cur for v_sql;
fetch sql_cur into v_value;

if v_value <> 0 then

update security_check_report
set chk_26=’N’,Report_date_time = sysdate
where DBID= v_dbid;
else
update security_check_report
set chk_26 =’Y’,Report_date_time = sysdate
where DBID= v_dbid;

update check_list
set comp_control=’Check 24, 25 are compensated’
where chk_num = 26;
end if;

sp_chk_pwd_lock_time_setting(p_db_name);

commit;
exception
when utl_file.invalid_operation then
utl_file.FCLOSE(v_input_file);
raise_application_error(-20051, ‘invalid operation’);
dbms_output.put_line(‘invalid operation’);
when utl_file.invalid_filehandle then
utl_file.FCLOSE(v_input_file);
raise_application_error(-20052, ‘invalid file
handle’);
dbms_output.put_line(‘invalid file handle’);
when utl_file.read_error then
utl_file.FCLOSE(v_input_file);
raise_application_error(-20053, ‘read error’);
dbms_output.put_line(‘read error’);
when utl_file.invalid_path then
raise_application_error(-20054, ‘invalid path’);
dbms_output.put_line(‘invalid path’);
when utl_file.invalid_mode then
raise_application_error(-20055, ‘invalid mode’);
dbms_output.put_line(‘invalid mode’);
when utl_file.internal_error then
raise_application_error(-20056, ‘internal error’);
dbms_output.put_line(‘internal error’);
when value_error then
dbms_output.put_line(‘VALUE_ERROR’);
when others then
utl_file.FCLOSE(v_input_file);
dbms_output.put_line(sqlcode||’ ‘||sqlerrm);

end;
/


CREATE OR REPLACE procedure sp_run_security_check
as
cursor cur_db_names is select db_num,db_link_name from db_names;
begin
for db_name_rec in cur_db_names
loop
sp_gen_security_rep(db_name_rec.db_link_name);
end loop;
end ;
/