jueves, 20 de agosto de 2009

Proyecto Seguridad Bases de Datos Oracle


1. VALIDACION DEFAULT PASSWORD


EJECUTAR CON EL USUARIO SYS

SQL> set serveroutput on

SQL> set serveroutput on;
SQL> execute dba_valida_usuario_password;
UHI
UHI2
GENAP
RMAN

PL/SQL procedure successfully completed.

create or replace procedure DBA_VALIDA_USUARIO_PASSWORD as
hexpw varchar2(30);
modpw varchar2(30);
un varchar2(30);
cursor c1 is select username,password from dba_users
where length(trim(password)) = 16;
begin
--execute immediate 'TRUNCATE TABLE DBO.AUD_VAL_USUARIO_PASSWORD';
for i in c1 loop
hexpw := i.password;
un := i.username;
execute immediate 'alter user '||un||' identified by '||un;
select password into modpw from dba_users where username = un;
if modpw = hexpw then
dbms_output.put_line(un);
-- INSET INTO DBO.AUD_VAL_USUARIO_PASSWORD VALUES(un);
else
EXECUTE IMMEDIATE 'ALTER USER '||UN||' IDENTIFIED BY VALUES '''||HEXPW||'''';
end if;
commit;
end loop;
end;
/

osp_install.sql

PROMPT To install Oracle Security Probe, you need log in
PROMPT as a user with DBA or CREATE USER privileges.
PROMPT

CONNECT dbo/clave@letodb

@@osp_install_user.sql
@@osp_install_tab.sql
@@osp_install_data.sql
@@osp_install_pack.sql
@@osp_exec.sql

osp_install_user.sql

GRANT create session TO dbo;
GRANT create procedure TO dbo;
GRANT create table TO dbo;
GRANT select ON sys.dba_users TO dbo;
GRANT select_catalog_role TO dbo;

osp_install_tab.sql

DROP TABLE ORA_ACCOUNTS
/

CREATE TABLE ORA_ACCOUNTS
( product VARCHAR2(30)
, security_level NUMBER(1)
, username VARCHAR2(30)
, password VARCHAR2(30)
, hash_value VARCHAR2(30)
, commentary VARCHAR2(200))
TABLESPACE USERS
/

osp_install_data.sql

insert into ORA_ACCOUNTS
(product
, security_level
, username
, password
, hash_value
, commentary
) values (
'Oracle'
,3
,'BRIO_ADMIN'
,'BRIO_ADMIN'
,'EB50644BE27DF70B'
,'BRIO_ADMIN is an account of a 3rd party product.'
)
/

osp_install_pack.sql

CREATE OR REPLACE PACKAGE osp_pack AS
PROCEDURE default_pass_check;
END osp_pack;
/

show errors


CREATE OR REPLACE PACKAGE BODY osp_pack
AS
PROCEDURE default_pass_check
IS
CURSOR c_dba_users IS
SELECT username, password, account_status
FROM dba_users;

v_userpass_exists NUMBER;
v_default_password VARCHAR2(30);
v_security_level NUMBER;
v_tel_defaults NUMBER := 0;
v_commentary VARCHAR2(200);

BEGIN

dbms_output.put_line('Oracle accounts with default passwords');
dbms_output.put_line('======================================'||CHR(10));

FOR r_dba_users IN c_dba_users
LOOP
<>

SELECT count(*)
INTO v_userpass_exists
FROM ORA_ACCOUNTS
WHERE username=r_dba_users.username
AND hash_value=r_dba_users.password;

IF v_userpass_exists = 1 THEN

v_tel_defaults := v_tel_defaults + 1;

SELECT password, security_level, commentary
INTO v_default_password, v_security_level, v_commentary
FROM ORA_ACCOUNTS
WHERE username=r_dba_users.username
AND hash_value=r_dba_users.password;

dbms_output.put_line('Username: '||r_dba_users.username);
dbms_output.put_line('Password: '||v_default_password);
IF r_dba_users.account_status LIKE '%LOCKED%' THEN
dbms_output.put_line('Status: '||r_dba_users.account_status);
END IF;
dbms_output.put_line('-----------------------------------------------');

dbms_output.put_line('WARNING! The password of '||r_dba_users.username||' is a default '|| 'password. It is well known to hackers'||CHR(10));
dbms_output.put_line('Additional information:');
dbms_output.put_line(v_commentary||CHR(10)||CHR(10));
END IF;

END LOOP userpass_loop;

IF v_tel_defaults = 0 THEN
dbms_output.put_line('No default passwords have been detected.');
END IF;

END default_pass_check;

END osp_pack;
/

show errors

osp_exec.sql

SET PAGESIZE 1000
SET HEADING off
SET VERIFY off
SET FEEDBACK off
SET ARRAYSIZE 1
SET LINESIZE 80
TTITLE off

connect dbo/clave@conexion

SET SERVEROUTPUT on SIZE 100000

SPOOL /export/home/oracle/rman/sql/spools/default_password1.log


-- PROMPT
-- PROMPT **********************************************************************
-- PROMPT * *
-- PROMPT * D e f a u l t p a s s w o r d s *
-- PROMPT * *
-- PROMPT **********************************************************************



exec osp_pack.default_pass_check;

SPOOL off

SET LINESIZE 80
SET TIMING off
SET VERIFY off
SET NUMWIDTH 10
SET HEADING off