Segurança e Auditoria

Modelo de Segurança Oracle

Gerenciamento de Usuários e Privilégios

-- Criar usuário
CREATE USER app_user IDENTIFIED BY Welcome1
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA 100M ON users;

-- Conceder privilégios de sistema
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO app_user;

-- Conceder privilégios de objeto
GRANT SELECT, INSERT, UPDATE, DELETE ON hr.employees TO app_user;
GRANT SELECT ON hr.departments TO app_user;
GRANT EXECUTE ON hr.get_salary TO app_user;

-- Conceder com WITH GRANT OPTION (permite repassar)
GRANT SELECT ON hr.employees TO app_user WITH GRANT OPTION;

-- Criar e atribuir role
CREATE ROLE app_role;
GRANT CREATE SESSION, CREATE TABLE TO app_role;
GRANT app_role TO app_user;

-- Revogar privilégios
REVOKE DELETE ON hr.employees FROM app_user;
REVOKE CREATE TABLE FROM app_user;

-- Ver privilégios de usuário
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'APP_USER';
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = 'APP_USER';
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'APP_USER';

Profiles e Políticas de Senha

-- Criar profile com política de senha forte
CREATE PROFILE secure_profile LIMIT
  -- Limites de recursos
  SESSIONS_PER_USER          2
  CPU_PER_SESSION            UNLIMITED
  CONNECT_TIME               480        -- 8 horas
  IDLE_TIME                  30         -- 30 minutos
  -- Políticas de senha
  FAILED_LOGIN_ATTEMPTS      5
  PASSWORD_LIFE_TIME         90         -- dias
  PASSWORD_REUSE_TIME        365        -- dias antes de reusar
  PASSWORD_REUSE_MAX         5          -- quantas senhas diferentes antes de reusar
  PASSWORD_LOCK_TIME         1          -- dia(s) de bloqueio
  PASSWORD_GRACE_TIME        7          -- dias de aviso antes de expirar
  PASSWORD_VERIFY_FUNCTION   ora12c_strong_verify_function;

-- Atribuir profile ao usuário
ALTER USER app_user PROFILE secure_profile;

-- Ver profiles
SELECT * FROM DBA_PROFILES WHERE PROFILE = 'SECURE_PROFILE';

-- Criar função de verificação customizada
CREATE OR REPLACE FUNCTION custom_password_verify
(username VARCHAR2, password VARCHAR2, old_password VARCHAR2)
RETURN BOOLEAN IS
BEGIN
  -- Mínimo 12 caracteres
  IF LENGTH(password) < 12 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Senha deve ter no mínimo 12 caracteres');
  END IF;
  
  -- Deve conter letra maiúscula, minúscula, número e caractere especial
  IF NOT REGEXP_LIKE(password, '[A-Z]') OR
     NOT REGEXP_LIKE(password, '[a-z]') OR
     NOT REGEXP_LIKE(password, '[0-9]') OR
     NOT REGEXP_LIKE(password, '[^A-Za-z0-9]') THEN
    RAISE_APPLICATION_ERROR(-20002, 'Senha deve conter maiúscula, minúscula, número e especial');
  END IF;
  
  -- Não pode conter username
  IF INSTR(UPPER(password), UPPER(username)) > 0 THEN
    RAISE_APPLICATION_ERROR(-20003, 'Senha não pode conter username');
  END IF;
  
  RETURN TRUE;
END;
/

TDE - Transparent Data Encryption

-- Configurar Oracle Wallet (armazena master key)
-- No sqlnet.ora adicionar:
-- ENCRYPTION_WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/wallet)))

-- Criar e abrir wallet
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/oracle/wallet' IDENTIFIED BY Welcome1;
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY Welcome1;

-- Criar master encryption key
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY Welcome1 WITH BACKUP;

-- Criptografar tablespace inteira
CREATE TABLESPACE secure_data
  DATAFILE '/u01/app/oracle/oradata/secure01.dbf' SIZE 100M
  ENCRYPTION USING 'AES256'
  DEFAULT STORAGE(ENCRYPT);

-- Criptografar tablespace existente
ALTER TABLESPACE users ENCRYPTION ONLINE USING 'AES256' ENCRYPT;

-- Criptografar colunas específicas
CREATE TABLE customers (
  customer_id   NUMBER PRIMARY KEY,
  name          VARCHAR2(100),
  ssn           VARCHAR2(11) ENCRYPT USING 'AES256',  -- Coluna criptografada
  credit_card   VARCHAR2(16) ENCRYPT USING 'AES256',
  address       VARCHAR2(200)
);

-- Verificar objetos criptografados
SELECT TABLESPACE_NAME, ENCRYPTED FROM DBA_TABLESPACES;
SELECT TABLE_NAME, COLUMN_NAME, ENCRYPTION_ALG FROM DBA_ENCRYPTED_COLUMNS;

Unified Audit

-- Verificar se Unified Audit está habilitado
SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

-- Criar política de auditoria
CREATE AUDIT POLICY critical_operations
  ACTIONS DELETE ON hr.employees,
          UPDATE ON hr.employees,
          EXECUTE ON hr.adjust_salary;

-- Habilitar política
AUDIT POLICY critical_operations BY app_user;
AUDIT POLICY critical_operations BY USERS WITH GRANTED ROLES dba;

-- Auditar todos os privilégios de sistema
CREATE AUDIT POLICY system_privs_audit
  PRIVILEGES CREATE ANY TABLE, DROP ANY TABLE, ALTER SYSTEM;
AUDIT POLICY system_privs_audit;

-- Auditar logins e logouts
AUDIT POLICY ora_logon_failures;  -- Política predefinida
AUDIT POLICY ora_account_mgmt;    -- Gestão de contas

-- Consultar trilha de auditoria
SELECT EVENT_TIMESTAMP, DBUSERNAME, ACTION_NAME, 
       OBJECT_SCHEMA, OBJECT_NAME, SQL_TEXT
FROM UNIFIED_AUDIT_TRAIL
WHERE DBUSERNAME = 'APP_USER'
ORDER BY EVENT_TIMESTAMP DESC
FETCH FIRST 20 ROWS ONLY;

-- Limpar auditoria antiga (dados acumulam rapidamente!)
BEGIN
  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    use_last_arch_timestamp => FALSE
  );
END;
/

Competências Adquiridas