Seguridad Oracle y Funcion Complejidad Clave

Revoke Unnecessary Privileges

REVOKE EXECUTE ON DBMS_RANDOM from public;
REVOKE CREATE DATABASE LINK FROM connect;
REVOKE EXECUTE ON utl_tcp FROM public;
REVOKE EXECUTE ON utl_smtp FROM public;
REVOKE EXECUTE ON utl_http FROM public;
REVOKE EXECUTE ON utl_mail FROM public;
REVOKE EXECUTE ON utl_inaddr FROM public;
REVOKE EXECUTE ON utl_file FROM public;
REVOKE EXECUTE ON dbms_java FROm public;
REVOKE SYSDBA FROM rman;

FUNCION PARA VALIDAR COMPLEJIDAD DE CONTRASEÑAS.

CREATE OR REPLACE FUNCTION dba_verify_function
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
n boolean;
m integer;
differ integer;
isdigit boolean;
ischar boolean;
ischarm boolean;
ispunct boolean;
digitarray varchar2(20);
punctarray varchar2(25);
chararray varchar2(54);


BEGIN
digitarray:= '0123456789';
chararray := 'abcdefghijklmnñopqrstuvwxyzABCDEFGHIJKLMNÑOPQRSTUVWXYZ';
punctarray:='!"#$%&()``*+,-/:;<=>?_';

-- Valida que el Password no sea Igual al Username

IF NLS_LOWER(password) = NLS_LOWER(username) THEN
raise_application_error(-20001, 'La clave es la misma o similar al usuario');
END IF;

-- Valida los caraterese Minimos de Longitud del Password

IF length(password) < isdigit =" FALSE">>

ischar:=FALSE;

FOR i IN 1..length(chararray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(chararray,i,1) THEN
ischar:=TRUE;
GOTO findpunct;
END IF;
END LOOP;
END LOOP;

IF ischar = FALSE THEN
raise_application_error(-20003, 'El password debe contener un caracter');
END IF;


-- 3. Check for the punctuation

<>

ispunct:=FALSE;

FOR i IN 1..length(punctarray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(punctarray,i,1) THEN
ispunct:=TRUE;
GOTO endsearch;
END IF;
END LOOP;
END LOOP;

IF ispunct = FALSE THEN
raise_application_error(-20003, 'La clave debe contener al menos caracter especial');
END IF;

<>

-- Compruebe si la contraseña es diferente de la anterior contraseña por lo menos en Tres caracteres

-- 4 letters

IF old_password IS NOT NULL THEN
differ := length(old_password) - length(password);

IF abs(differ) < 3 THEN
IF length(password) < length(old_password) THEN
m := length(password);
ELSE
m := length(old_password);
END IF;

differ := abs(differ);
FOR i IN 1..m LOOP
IF substr(password,i,1) != substr(old_password,i,1) THEN
differ := differ + 1;
END IF;
END LOOP;

IF differ < 3 THEN
raise_application_error(-20004, 'El password debe ser diferente al menos en (3) Tres caracteres');
END IF;
END IF;
END IF;

-- Everything is fine; return TRUE ;

RETURN(TRUE);

END;
/

-- This means that all the users on the system have Password Management
-- enabled and set to the following values unless another profile is
-- created with parameter values set to different value or UNLIMITED
-- is created and assigned to the user.

ALTER PROFILE SEGURIDAD LIMIT
PASSWORD_LIFE_TIME 60
PASSWORD_GRACE_TIME 10
PASSWORD_REUSE_TIME 1800
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1/1440
PASSWORD_VERIFY_FUNCTION dba_verify_function;

SQL> DECLARE
2 X BOOLEAN;
3 BEGIN
4 x:= dba_verify_function ('dbo','dios','pepe');
5 END;
6 /
DECLARE
*
ERROR at line 1:
ORA-20002: La clave debe ser de longitud mayor o igual a 8
ORA-06512: at "SYS.FN_COMPLEJIDAD_CLAVE", line 26
ORA-06512: at line 4

Seguridad Oracle 10G Usuarios con el Password igual que el Username

Script para determinar usuarios con el password igual al username

PARA IMPLEMENTAR EN POWER

create or replace procedure dba_valida_usuario_password as
-- Buscar Usuarios que tienen el Password Igual que el username
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);
insert into dbo.aud_val_usuario_password values (un);
else
execute immediate 'alter user 'un' identified by values '''hexpw'''';
end if;
commit;
end loop;
end;
/

SQL> CREATE TABLE AUD_VAL_USUARIO_PASSWORD (USERNAME VARCHAR2(20));/


SQL> set serveroutput on
SQL> exec dba_valida_usuario_password;

OUTLN
DBSNMP
CTXSYS
MDSYS
TEST

PL/SQL procedure successfully completed.

Oracle Mode Shutdown

Shutdown

Shutdown Mode Normal

Nuevas Conexiones: No
Espera a que las Sesiones concurrentes Terminen: Si
Espera a que la transacción termine: Si
Forza Checkpoint y cierra archivos: Si


Shutdown Mode Transactional

Nuevas Conexiones: No
Espera a que las Sesiones concurrentes Terminen: No
Espera a que la transacción termine: Si
Forza Checkpoint y cierra archivos: Si

Shutdown Mode Inmediate

Nuevas Conexiones: No
Espera a que las Sesiones concurrentes Terminen: No
Espera a que la transacción termine: No
Forza Checkpoint y cierra archivos: Si


Shutdown Mode Abort

Nuevas Conexiones: No
Espera a que las Sesiones concurrentes Terminen: No
Espera a que la transacción termine: No
Forza Checkpoint y cierra archivos: No

Oracle Flashback Query

FLASHBACK CONFIGURACION ANTES.

connect hr/hr

SQL> select * from JOB_HISTORY as of timestamp (sysdate - (1/24/60*10));

insert into job_history (select * from JOB_HISTORY as of timestamp (sysdate - (1/24/60*10)));


EMPLOYEE_ID START_DAT END_DATE JOB_ID DEPARTMENT_ID
----------- --------- --------- ---------- -------------
102 13-JAN-93 24-JUL-98 IT_PROG 60
101 21-SEP-89 27-OCT-93 AC_ACCOUNT 110
101 28-OCT-93 15-MAR-97 AC_MGR 110
201 17-FEB-96 19-DEC-99 MK_REP 20
114 24-MAR-98 31-DEC-99 ST_CLERK 50
122 01-JAN-99 31-DEC-99 ST_CLERK 50
200 17-SEP-87 17-JUN-93 AD_ASST 90
176 24-MAR-98 31-DEC-98 SA_REP 80
176 01-JAN-99 31-DEC-99 SA_MAN 80
200 01-JUL-94 31-DEC-98 AC_ACCOUNT 90

10 rows selected.


CREATE TABLE emp
(empno NUMBER PRIMARY KEY,
empname VARCHAR2(16),
salary NUMBER);
INSERT INTO emp VALUES (111, 'Mike', 555);
COMMIT;
CREATE TABLE dept
(deptno NUMBER,
deptname VARCHAR2(32));
INSERT INTO dept VALUES (10, 'Accounting');
COMMIT;
At this point, emp and dept have one row each. In terms of row versions, each table has one version of one row. Next, suppose that an erroneous transaction deletes employee id 111 from table emp:UPDATE emp SET salary = salary + 100 WHERE empno = 111;
INSERT INTO dept VALUES (20, 'Finance');
DELETE FROM emp WHERE empno = 111;
COMMIT;
Subsequently, a new transaction reinserts employee id 111 with a new employee name into the emp table.INSERT INTO emp VALUES (111, 'Tom', 777);
UPDATE emp SET salary = salary + 100 WHERE empno = 111;
UPDATE emp SET salary = salary + 50 WHERE empno = 111;
COMMIT;
At this point, the DBA detects the application error and needs to diagnose the problem. The DBA issues the following query to retrieve versions of the rows in the emp table that correspond to empno 111. The query uses Flashback Version Query pseudocolumns.connect dba_name/password
SELECT versions_xid XID, versions_startscn START_SCN,
versions_endscn END_SCN, versions_operation OPERATION,
empname, salary FROM hr.emp
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
where empno = 111;
XID START_SCN END_SCN OPERATION EMPNAME SALARY
---------------- ---------- --------- ---------- ---------- ----------
0004000700000058 113855 I Tom 927
000200030000002D 113564 D Mike 555
000200030000002E 112670 113564 I Mike 555
3 rows selected
The results table reads chronologically, from bottom to top. The third row corresponds to the version of the row in emp that was originally inserted in the table when the table was created. The second row corresponds to the row in emp that was deleted by the erroneous transaction. The first row corresponds to the version of the row in emp that was reinserted with a new employee name.
The DBA identifies transaction 000200030000002D as the erroneous transaction and issues the following Flashback Transaction Query to audit all changes made by this transaction:SELECT xid, start_scn START, commit_scn COMMIT,
operation OP, logon_user USER,
undo_sql FROM flashback_transaction_query
WHERE xid = HEXTORAW('000200030000002D');
XID START COMMIT OP USER UNDO_SQL
---------------- ----- ------ -- ---- ---------------------------
000200030000002D 195243 195244 DELETE HR insert into "HR"."EMP"
("EMPNO","EMPNAME","SALARY") values ('111','Mike','655');
000200030000002D 195243 195244 INSERT HR delete from "HR"."DEPT"
where ROWID = 'AAAKD4AABAAAJ3BAAB';
000200030000002D 195243 195244 UPDATE HR update "HR"."EMP"
set "SALARY" = '555' where ROWID = 'AAAKD2AABAAAJ29AAA';
000200030000002D 195243 113565 BEGIN HR
4 rows selected

Oracle Papelera Reciclaje

PAPELERA RECICLAJE ORACLE

SELECT * FROM RECYCLEBIN;

ALTER SESSION SET recyclebin = OFF;
ALTER SYSTEM SET recyclebin = OFF;

VISTAS

USER_RECYCLEBIN
This view can be used by users to see their own dropped objects in the recycle bin. It has a synonym RECYCLEBIN, for ease of use.

DBA_RECYCLEBIN
This view gives administrators visibility to all dropped objects in the recycle bin

PURGE TABLE nombre_tabla;
Elimina la tabla indicada de la papelera. El nombre de la tabla puede ser el nombre original o el renombrado.

PURGE INDEX nombre_índice;
Elimina el índice indicado de la papelera. El nombre del índice es el nombre original y no el renombrado.

PURGE RECYCLEBIN;
Elimina todos los objetos (del usuario que lanza la orden) de la papelera.

PURGE DBA_RECYCLEBIN;
Elimina todos los objetos (de todos los usuarios) de la papelera. Solo un SYSDBA puede lanzar este comando.

PURGE TABLESPACE nombre_tablespace;
Elimina todos los objetos (del usuario) de la papelera que residan en el tablespace indicado.

PURGE TABLESPACE nombre_tablespace USERS nombre_usuario;
Elimina todos los objetos de la papelera que residan en el tablespace indicado y pertenezcan el usuario indicado.

SQL> show recyclebin;ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME---------------- ------------------------------ ------------ -------------------PENDIENTES BIN$WJ4MAU6RU7XgRAAUT2gJUg==$0 TABLE 2008-10-06:17:17:21PRUEBA BIN$WMDKHy9/HiHgRAAUT2gJUg==$0 TABLE 2008-10-08:10:44:19PRUEBA BIN$WL+UR7qDG8/gRAAUT2gJUg==$0 TABLE 2008-10-08:09:17:41PRUEBA BIN$WL83vpCuGmTgRAAUT2gJUg==$0 TABLE 2008-10-08:08:51:49

SQL> flashback table pendientes to before drop;
Flashback complete.
SQL>


SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMPLOYEES BIN$yrMKlZaVMhfgNAgAIMenRA==$0 TABLE 2003-10-27:14:00:19

Tmabien se puede realizar un query con el nombre de la tabla de esta forma.

example: SELECT * FROM "BIN$yrMKlZaVMhfgNAgAIMenRA==$0";

PURGE TABLE BIN$jsleilx392mk2=293$0;

PURGE TABLESPACE example;

PURGE TABLESPACE example USER oe;

Limpia toda la papelera reciclaje

PURGE RECYCLEBIN;

PURGE DBA_RECYCLEBIN;

Oracle Flashback Database

LABORATORIO DE FLASHBACK DATABASE

SQL>select * from v$flash_recovery_area_usage;

VER EL ESPACIO ESTIMADO DE OCUPACION DEL FLASHBACK

SQL> select estimated_flashback_size, flashback_size from v$flashback_database_log;


DETERMINAR PRIMER FLASHBACK PUNTO EN EL PASADO

SQL> SELECT OLDEST_FLASHBACK_SCN, to_char(OLDEST_FLASHBACK_TIME,'YYYY-MM-DD:HH24:MI:SS'),"OLDEST_FLASHBACK_TIME" FROM V$FLASHBACK_DATABASE_LOG;

SQL> create table prueba as (select * from per_sexo);

Tabla creada.

SQL> select current_scn,flashback_on from v$database;

CURRENT_SCN FLASHBACK_ON
----------- ------------------
853235 YES


SQL> update prueba set estado = 50
2 ;

2 filas actualizadas.

SQL> commit;

Confirmacion terminada.

SQL> select current_scn,flashback_on from v$database;

CURRENT_SCN FLASHBACK_ON
----------- ------------------
853288 YES


SQL> connect sys/sys as sysdba
Conectado.
SQL> flashback database to scn 853235;
flashback database to scn 853235
*
ERROR en linea 1:
ORA-38757: La base de datos debe estar montada y no estar abierta para FLASHBACK.


SQL> shutdown immediate;
Base de datos cerrada.

Base de datos desmontada.
Instancia ORACLE cerrada.
SQL>
SQL> startup mount;
Instancia ORACLE iniciada.

Total System Global Area 104857600 bytes
Fixed Size 1217932 bytes
Variable Size 71305844 bytes
Database Buffers 25165824 bytes
Redo Buffers 7168000 bytes
Base de datos montada.
SQL> flashback database to scn 853235;

Flashback terminado.

SQL> alter database open resetlogs;

EN CASO DE QUE CUANDO ME RECUPERE NO SEA EL PUNTO EXACTO

shutdown immediate;
startup mount;
flashback database to before resetlogs;

FLASHBACK DATABASE FECHA;

flashback database TO TIMESTAMP TO_TIMESTAMP('2020-04-29 19:44:00','YYYY-MM-DD HH24:MI:SS');