Oracle lock tx

Oracle – O Lock TX

Falamos superficialmente sobre como os locks funcionam no Oracle, também de problemas frequentes com eles relacionados, como o lost update, block e o deadlock.

Agora é hora de olhar com um pouco mais de detalhe como as coisas funcionam internamente. Neste artigo vamos focar no lock TX e sua relação com as ITLs.

Mostraremos também uma nova feature do Oracle 12c com relação a permissão de leitura x lock.

DML Lock

Basicamente temos 3 classes de lock no Oracle que se subdividem:

– DML Lock

– DDL Lock

– Internal Lock e Latch.

DML (Data Manipulation Language) são instruções SQLs que se propõem a alterar determinado dado. Os locks deste tipo visam manter estas alterações consistentes mesmo que ocorram de forma “concorrente”. Mas note que não há alteração concorrente, então o melhor a se dizer é que os locks apenas gerenciam, de certa forma, a alteração por ordem de chegada.

Outro ponto importante é que, durante o tempo em que estamos alterando os dados, não seja permitido alterações estruturais ou em mesmo drop da tabela. O Oracle irá cuidar de tudo isso sem que você perceba ou, pelo menos, que você quase não perceba.

Lock TX

O termo TX está geralmente ligado a expressão “transaction” e é com isto que este tipo de lock está relacionado. Um lock TX é adquirido sempre que uma transação inicia e ela se inicia sempre que modificamos um dado (INSERT, UPDATE, DELETE, …) ou declaramos a intenção de alterá-lo (SELECT FOR UPDATE). Ela finalizará com um COMMIT ou um ROLLBACK.

Haverá apenas um lock TX independentemente do número de linhas, blocos ou tabelas que sua transação envolva. É importante notar que, mesmo assim, esta não é uma operação custosa. Diferente de outras implementações, o Oracle não centraliza a gestão de locks em estruturas como “lock manager”. Como dito no artigo anterior, o Oracle se baseia nas ITL (Interested Transaction List) de cada bloco para fazer esta gestão.

Desta forma, o lock estará sempre junto com o dado e ao consultá-lo (ou tentar alterá-lo) será possível descobrir se está livre. O melhor disto é que o caminho para ler ou modificar é o mesmo e direto, sem precisar passar por algum lock manager, diminuindo assim o overhead em momentos em que há muita alteração e muito locks.

Usaremos o famoso SCOTT para demonstração dos casos que veremos aqui. Então, se quiser reproduzir nosso teste, basta criá-lo em seu DB. Lembrando que estamos usando o 12c, que traz uma importante mudança com relação a permissão de leitura de dados e que citaremos ainda neste artigo.

Primeiro teste que faremos será verificar o número de linhas na v$lock versus o número de linhas alteradas. Vamos lockar uma linha da dept e em seguida todas as linhas.

SCOTT@orcl > select * from dept;

DEPTNO DNAME          LOC

———- ————– ————-

10 ACCOUNTING     NEW YORK

20 RESEARCH       DALLAS

30 SALES          CHICAGO

40 OPERATIONS     BOSTON

Ao realizar uma alteração (sem commit), podemos ver o lock.

SCOTT@orcl > update dept set loc = ‘SAO PAULO’ where DEPTNO = 40;

1 row updated.

SYS@orcl >

SELECT s.username,

l.sid,

trunc(l.id1 / power(2, 16)) undoseg,

bitand(l.id1, to_number(‘ffff’, ‘xxxx’)) + 0 slot,

l.id2 seq,

l.lmode,

l.request,

l.type,

l.block

FROM   v$lock     l

JOIN   v$session  s

ON     l.sid      = s.sid

WHERE  l.type     = ‘TX’

AND    s.username = ‘SCOTT’;

 

USERNAME      SID    UNDOSEG       SLOT        SEQ LMODE REQUEST TYPE  BLOCK

———- —— ———- ———- ———- —– ——- —– —–

SCOTT         245          5         13       2729     6       0 TX        0

 

E ao alterar todas as linhas com a mesma sessão:

SCOTT@orcl > update dept set DNAME = lower(DNAME);

4 rows updated.

 

Consultando a fila de locks:

SYS@orcl >/

USERNAME      SID    UNDOSEG       SLOT        SEQ LMODE REQUEST TYPE  BLOCK

———- —— ———- ———- ———- —– ——- —– —–

SCOTT         245          5         13       2729     6       0 TX        0

 

Ou seja, independente de alterar uma linha ou várias, o lock é feito por transação. Além disso, você deve ter notado que a query traz alguns campos “estranhos”. Eles servem exatamente para identificar a transação[1]. Veja só:

SYS@orcl >select XIDUSN, XIDSLOT, XIDSQN from v$transaction;

XIDUSN    XIDSLOT     XIDSQN

———- ———- ———-

5         13       2729

 

Não vamos entrar em muitos detalhes, mas para não deixar tão inexplicável, o Oracle possui duas colunas para registrar 3 informações: Undo segment number, slot e sequence, por isso precisamos desta manipulação.

O importante aqui é notar que o lock aponta para os mesmos valores de USN, SLOT e SQN da transação, então uma transação, um lock.

 

BLOCKs

 

Agora vamos conferir como os blocks funcionam. Alteramos (sem COMMIT) todas as linhas da tabela DEPT.

SCOTT@orcl > update dept set loc = ‘SAO PAULO’;

4 rows updated.

 

Temos todas as linhas lockadas, o que ocorre se outra transação tentar alterar alguma linha da DEPT?

Bom, como já era esperado, a sessão fica aguardando:

SCOTT@orcl > update dept set loc = ‘SAO PAULO’ where DEPTNO = 10;

Como fica a fila de locks na v$lock?

USERNAME      SID    UNDOSEG       SLOT        SEQ LMODE REQUEST TYPE  BLOCK

———- —— ———- ———- ———- —– ——- —– —–

SCOTT         421          4         24       2469     0       6 TX        0

SCOTT         244          4         24       2469     6       0 TX        1

 

Vemos uma informação interessante: A sessão com SID 244 que já possuía o lock, agora consta com BLOCK = 1 identificando que ela está fazendo alguém esperar.

A nova sessão (SID=421) fez um request de lock, mas sem sucesso. Veja que as informações a respeito da transação apontam para aquela que possui o lock.

É desta forma conseguimos identificar quem está locando quem.

SQL>

SELECT a.sid, ‘ is blocking ‘, b.sid

FROM   v$lock a

JOIN   v$lock b

ON     a.id1 = b.id1

AND    a.id2 = b.id2

WHERE  a.block = 1

AND    b.request > 0;

SID ‘ISBLOCKING’         SID

———- ————- ———-

244  is blocking         421

 

 

ITLs

 

E as ITLs, o que tem a ver com isso? Bom, agora o assunto fica um pouco mais interessante. Vamos precisar de um dump do bloco onde estão os dados da DEPT para entender. Além disso, para ilustrar o valor default do INITRANS, vamos ver como está na tabela:

SYS@orcl >

SELECT owner,

table_name,

ini_trans

FROM   dba_tables d

WHERE  d.table_name = ‘DEPT’

AND    d.owner      = ‘SCOTT’;

OWNER        TABLE_NAME       INI_TRANS

———— ————— ———-

SCOTT        DEPT                     1

 

Vamos identificar o block/datafile:

SYS@orcl >

SELECT DISTINCT dbms_rowid.rowid_block_number(ROWID) blk,

dbms_rowid.rowid_relative_fno(ROWID) file_no

FROM   scott.dept;

BLK    FILE_NO

———- ———-

181          6

 

Agora o dump do block:

SYS@orcl >alter session set tracefile_identifier=’ABONACIN’;

Session altered.

SYS@orcl >alter system dump datafile 6 block 181;

System altered.

 

O arquivo de trace será gerado no mesmo diretório do seu alert.

-rw-r—–. 1 oracle oinstall  197 Feb 26 13:30 orcl_ora_9742_ABONACIN.trm

-rw-r—–. 1 oracle oinstall 5.0K Feb 26 13:30 orcl_ora_9742_ABONACIN.trc

 

[oracle@hostlab trace]$ cat orcl_ora_9742_ABONACIN.trc

 

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0009.003.000005db  0x01000a41.00c1.21  C—    0  scn 0x0000.0018b4fb

0x02   0x0005.00d.00000aa9  0x0100073b.0254.34  —-    4  fsc 0x0000.00000000

 

Pode ser utilizado uma calculadora comum do Windows para constatar que AA9 hexa corresponde a 2729 decimal, 00d -> 13, 0005 -> 5, valores associados ao undo segment number, slot e sequence.

 

 

E o número 4 na coluna Lck, o que significa? Vamos seguir com os testes. Feito ROLLBACK, vamos alterar uma linha e fazer um dump.

SCOTT@orcl > update dept set loc = ‘Sao Paulo’ where deptno = 10;

1 row updated.

 

Locks:

USERNAME      SID    UNDOSEG       SLOT        SEQ LMODE REQUEST TYPE  BLOCK

———- —— ———- ———- ———- —– ——- —– —–

SCOTT         244          4         24       2469     6       0 TX        0

 

ITL:

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0009.003.000005db  0x01000a41.00c1.21  C—    0  scn 0x0000.0018b4fb

0x02   0x0004.018.000009a5  0x010007de.02a7.25  —-    1  fsc 0x0000.00000000

 

Vamos fazer o update na mesma transação, em outra linha.

SCOTT@orcl > update dept set loc = ‘Maringá’ where deptno = 20;

1 row updated.

 

Locks (continuamos com apenas um):

USERNAME      SID    UNDOSEG       SLOT        SEQ LMODE REQUEST TYPE  BLOCK

———- —— ———- ———- ———- —– ——- —– —–

SCOTT         244          4         24       2469     6       0 TX        0

 

ITL:

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0009.003.000005db  0x01000a41.00c1.21  C—    0  scn 0x0000.0018b4fb

0x02   0x0004.018.000009a5  0x010007de.02a7.26  —-    2  fsc 0x0000.00000000

 

E assim por diante. Veremos que esta coluna representa a quantidade de linhas deste bloco que estão lockadas por esta transação. Ao fazer um update nas 4 linhas:

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0009.003.000005db  0x01000a41.00c1.21  C—    0  scn 0x0000.0018b4fb

0x02   0x0004.018.000009a5  0x010007de.02a7.27  —-    4  fsc 0x0000.00000000

 

 

Como vimos, há duas ITLs neste bloco. O que significa que apenas duas transações podem alterá-lo simultaneamente. O que acontece se uma terceira transação tentar alterar uma linha da DEPT?

SCOTT@orcl > update dept set loc = ‘São Paulo’ where DEPTNO = 10;

1 row updated.

SCOTT@orcl > update dept set loc = ‘Maringá’ where deptno = 20;

1 row updated.

SCOTT@orcl > update dept set loc = ‘Salvador’ where deptno = 30;

1 row updated.

 

Os locks foram obtidos, como é possível?

USERNAME      SID    UNDOSEG       SLOT        SEQ LMODE REQUEST TYPE  BLOCK

———- —— ———- ———- ———- —– ——- —– —–

SCOTT         421          7         23       2490     6       0 TX        0

SCOTT         244         10         19       3574     6       0 TX        0

SCOTT         127          8         17       2844     6       0 TX        0

 

Vamos ver novamente o dump do bloco. Com isso, vemos que agora temos três ITLs.

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0007.017.000009ba  0x01000504.02bb.12  —-    1  fsc 0x0000.00000000

0x02   0x000a.013.00000df6  0x010006fc.035e.2a  —-    1  fsc 0x0000.00000000

0x03   0x0008.011.00000b1c  0x01000c33.02a1.05  —-    1  fsc 0x0000.00000000

 

Pontos importantes sobre isso são:

– As ITLs não sofrem shrink. Uma vez criadas, permanecerão no bloco.

– O bloco é criado com duas (ou o valor definido pela INITRANS) e utilizam o espaço livre do bloco caso mais transações concorram no mesmo bloco.

– O valor máximo é de 255, independentemente de haver espaço livre ou não.

Vamos ilustrar também este último ponto. Criamos uma tabela com linhas curtas para que ocupem apenas um bloco e uma tablespace com bloco de 32k.

SQL> alter system set db_32k_cache_size=100M;

System altered.

 

SQL> create tablespace TESTEITL datafile ‘/u01/app/oracle/oradata/orcl/testeitl01.dbf’ size 100M BLOCKSIZE 32K;

Tablespace created.

 

SCOTT@orcl >

CREATE TABLE tb_itl

(col_id  INT,

col_txt VARCHAR2(10)) TABLESPACE TESTEITL;

Table created.

 

SCOTT@orcl >

INSERT INTO tb_itl

SELECT rownum, ‘A’

FROM   dual

CONNECT BY LEVEL <= 300;

300 rows created.

 

SCOTT@orcl > COMMIT;

Commit complete.

 

SCOTT@orcl >

SELECT dbms_rowid.rowid_block_number(ROWID) blk,

dbms_rowid.rowid_relative_fno(ROWID) file_no,

count(*)

FROM   scott.tb_itl

GROUP  BY dbms_rowid.rowid_block_number(ROWID) ,

dbms_rowid.rowid_relative_fno(ROWID);

BLK    FILE_NO   COUNT(*)

———- ———- ———-

35          7        300

Ou seja, as 300 linhas estão no mesmo bloco. Um dump inicial irá mostrar apenas 2 ITLs e após nosso teste, veremos 255.

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0020.002.00000002  0x01000772.0000.04  –U-  300  fsc 0x0000.0032f0aa

0x02   0x0000.000.00000000  0x00000000.0000.00  —-    0  fsc 0x0000.00000000

 

Agora vamos fazer uma “manobra” para que sejam alteradas 300 linhas por transações diferentes. O teste consiste em abrir recursivamente uma AUTONOMOUS TRANSACTION. Esperamos que após 255 transações não seja mais possível que isto ocorra. Usaremos a procedure abaixo para isso.

CREATE OR REPLACE PROCEDURE do_update(pnLinha IN NUMBER) AS

PRAGMA        AUTONOMOUS_TRANSACTION;

vrItl         tb_itl%ROWTYPE;

veResBusy     EXCEPTION;

PRAGMA        EXCEPTION_INIT(veResBusy, -54);

BEGIN

–fazemos select for update para lockar a linha com col_id = pnLinha

SELECT *

INTO   vrItl

FROM   tb_itl

WHERE  col_id = pnLinha

FOR    UPDATE NOWAIT;

 

–chamamos recursivamente para update da linha com col_id = pnLinha + 1

do_update(pnLinha + 1);

 

COMMIT;

EXCEPTION

WHEN veResBusy THEN

dbms_output.put_line(‘Nao conseguimos lockar a linha de col_id: ‘ || pnLinha);

COMMIT;

WHEN no_data_found THEN

dbms_output.put_line(‘Todos os registros foram lockados.’);

COMMIT;

END;

/

 

Resultado:

SCOTT@orcl > exec do_update(1);

Não conseguimos lockar a linha de col_id: 256

PL/SQL procedure successfully completed.

 

E por fim, conferindo as ITLs:

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0033.002.00000002  0x010008a2.0000.03  –U-    1  fsc 0x0000.0032f7a1

0x02   0x0032.002.00000002  0x01000892.0000.03  –U-    1  fsc 0x0000.0032f7a3

0xfe   0x00fe.000.00000002  0x010017d2.0000.01  –U-    1  fsc 0x0000.0032f645

0xff   0x00ff.000.00000002  0x010017e2.0000.01  –U-    1  fsc 0x0000.0032f643

 

GRANT READ

 

Por fim, vamos falar de um privilégio novo do Oracle 12c relacionado com lock, o GRANT READ. Em versões passadas, tínhamos apenas o grant de SELECT. O problema é que o privilégio de SELECT permite o SELECT FOR UPDATE e embora alguém pudesse apenas “ler” o registro, podia declarar o interesse de alterá-lo, causando o lock.

O Oracle 12c traz esta new feature. Vejamos como funciona, de forma simples. Concedemos privilégio de SELECT para um user e de READ para outro e comparamos o resultado.

SCOTT@orcl > grant select on dept to hr;

Grant succeeded.

 

SCOTT@orcl > grant read on dept to oe;

Grant succeeded.

 

SCOTT@orcl > conn hr/hr

Connected.

 

HR@orcl > select * from scott.dept for update;

 

DEPTNO DNAME          LOC

———- ————– ————-

10 ACCOUNTING     SAO PAULO

20 RESEARCH       DALLAS

30 SALES          CHICAGO

40 OPERATIONS     BOSTON

 

HR@orcl > conn oe/oe

Connected.

 

OE@orcl > select * from scott.dept for update;

select * from scott.dept for update

*

ERROR at line 1:

ORA-01031: insufficient privileges

 

CONCLUSÃO

 

Finalizamos mais uma parte da nossa análise e ainda temos bastante assunto pela frente. Falamos de propriedade de ITL e qual a relação entre os locks TX e ITL.

 

Ficarão para os próximos artigos o lock TM e os de DDL, além de outras coisas como leitura consistente, multi versões que uma linha pode ter.

 

 

[1] https://docs.oracle.com/database/121/REFRN/GUID-FAE908F8-24B1-4B90-8FC5-86FCB532431C.htm#REFRN30291

[2] Oracle Database Transactions and Locking Revealed, Thomas Kyte, Darl Kuhn, 2014

 

 

LOCKS – Oracle

Quem trabalha com banco de dados relacionais já deve ter tido problema com Locks diversas vezes. Podem deixar apps lentas, deixá-las completamente paradas, e naturalmente vem a dúvida: por que precisamos deles?

Eles não são tão ruins assim e ao final espero que entenda que eles são, na verdade, indispensáveis. São quem permitem que sistemas multi-users possam ser escaláveis de forma consistente. Atuam para que apenas uma transação de cada vez possa alterar um determinado dado, formando uma fila com todos que tem esta intenção.

Imagine você alterando uma linha da tabela de empregados. Enquanto pretendia alterar o “salário”, outra pessoa foi e alterou o “nome” do mesmo registro. Embora não pareça algo muito preocupante, você consegue imaginar a “bagunça” quando várias sessões tentam alterar o dado ao mesmo tempo? E no caso de alguém que queira aumentar 1.000 no salário e outra aumentar 10%: a ordem de execução vai afetar o resultado final.

Enfim, para começar é importante entender que Lock é a forma que o banco de dados tem de organizar as transações e se manter consistente. Alterações nas tabelas respeitam a ordem de chegada. Chegou primeiro, atualiza primeiro. Chegou depois e tem alguém atualizando? Espere até a conclusão. E aqui nascem os problemas.

Neste artigo tentaremos mostrar um pouco de como os Locks funcionam. Não dá para se falar de maneira global porque cada banco de dados acaba implementando de forma diferente, então focaremos na implementação do Oracle. Há, obviamente, vários conceitos relacionados e vamos tratando deles no decorrer do artigo. Como tem assunto para um livro, vamos dividir em uma pequena série de artigos.

Aqui falaremos de conceitos como transações e seu papel no contexto da aplicação. Também abordaremos um problema comum de apps multi-users, os chamados lost updates. Em seguida vamos para os problemas relacionados ao banco de dados como bloqueios, uma estrutura do cabeçalho do bloco de dados chamado ITL e deadlock.

Nos próximos artigos desta série, vamos mergulhar um pouco mais para descobrir como o Oracle trabalha internamente para gestão de Locks e transações e também como ele trata as multi versões de uma linha.

 

TRANSAÇÃO

 

Primeiramente, vamos entender como um banco de dados é alterado e o que há de especial nesta mudança. Parece simples, não?

SQL> update hr.employees set FIRST_NAME = ‘Adriano’, LAST_NAME = ‘Bonacin’ where EMPLOYEE_ID = 206;

1 row updated.

Neste momento (até o commit ou rollback) o banco de dados, de maneira simplificada:

– Está protegendo esta linha para que ninguém a altere;

– Protegendo a tabela para que ninguém a drope;

– Criou uma forma de desfazer a alteração;

– Criou uma forma para refazer.

E tudo isto depois de verificar se havia espaço livre na tabela, se eu tinha permissão para fazer a alteração, entre outras coisas.

Por default, o Oracle não confirma a alteração, embora seja otimizado para isso (e não para rollbacks). Assim, é importante garantir que o commit seja efetuado para não deixar o registro bloqueado. Que fique claro, porém, que as apps também podem ter uma arquitetura que já faça a alteração e o commit automaticamente.

Quando efetuado o commit e recebido o OK do banco de dados, um DB (A C I D) garante que a partir daquele momento seu dado não será mais perdido (Durabilidade). Você ainda pode perder o banco de dados inteiro, mas aí é outra conversa.

Resumindo, chamamos de transação esta operação de iniciada com um update (ou insert, delete, …) e finalizada com commit (ou rollback) que leva o DB de um estado consistente a outro.

 

LOST UPDATES

 

Tratando de transações e Locks do ponto de vista da aplicação, um problema comum ocorre quando vários usuários podem alterar o mesmo dado, o chamado de lost update (alteração perdida). Considere o cenário abaixo como exemplo:

1 – O usuário A consulta os dados do cliente 1 para ajustar seu endereço.

2 – O usuário B consulta os dados do mesmo cliente 1 para também ajustar o endereço.

3 – O usuário A altera o endereço para “Av Brigadeiro Faria Lima”.

4 – O usuário B altera o endereço para “Av Brigadeiro F Lima”.

Qual o resultado final? O último? O primeiro? Um mix?

Para aumentar a possibilidade trabalhar em concorrência (escalabilidade) sem estes conflitos (de perder alterações), geralmente apps usam diferentes metodologias: “Lock Pessimista” e “Lock Otimista”.

Basicamente:

– Pessimista: declaramos (Lock) que vamos alterar o dado (select for update) quando o consultamos. O próximo que tiver intenção de alterar, fica aguardando.

– Otimista: lemos o dado sem o Lock e garantimos na hora da alteração que ele estava exatamente como lemos (outra sessão pode ter alterado entre ler e alterar).

 

BLOCKs

 

Este é o principal efeito negativo, quando uma sessão possui um Lock de determinado dado e outra faz o request do mesmo. A sessão que chegou depois ficará aguardando (completamente congelada) até que o dado solicitado seja liberado.

Os bloqueios podem ocorrer com SELECT FOR UPDATE, quando duas sessões consultam alguma linha em comum. Neste caso, há uma cláusula NOWAIT que pode ser adicionada e a segunda sessão tomará um erro ao invés do block.

No caso dos INSERTS, há dois cenários em que ocorrem comumente. No primeiro, a tabela deve ter PK ou algum outro índice único e ambas as sessões estão tentando inserir a mesma chave. No segundo, duas tabelas devem ter FK e o insert na filha ocorre enquanto outra sessão inseriu na tabela pai (mas ainda não comitou).

Com UPDATEs, DELETEs e MERGEs, a prevenção seria a melhor forma estar livre. Tentar lockar a linha antes da alteração com SELECT FOR UPDATE NOWAIT e só então concluir a manipulação do dado.

 

LOCK ESCALATION

 

Um outro termo que talvez você veja por aí, mas que não ocorre com o Oracle, é o Lock Escalation. Em alguns DBs a forma de gestão de lock depende de um Lock Manager. Uma estrutura de tabela em memória que registra as alinhas que estão lockadas.

Porém, quando o número de linhas lockadas de uma mesma tabela é consideravelmente grande, o DB prefere escalar o lock para o bloco/tabela inteira. Fica mais fácil para gestão, mas provavelmente teremos linhas lockadas sem a real necessidade.

O Oracle não usa este tipo de gerenciamento de lock. Para lockar uma linha, o Oracle vai até o DB Block e armazena ali a transação que alterou (ou tem a intenção de) a linha, independentemente do número de linhas. Será sempre assim, nunca haverá o Lock Escalation.

 

ITL

 

Interested Transaction List (ITL) são estruturas do cabeçalho do bloco (DB block header) onde são armazenadas as transações que desejam alterar linhas daquele bloco. É ocupada uma entrada por transação e não por linha lockada. Assim, uma transação pode lockar 300 linhas do bloco que apenas uma entrada das ITLs será utilizada.

Associado a isso, estão os eventos de espera como o “enq: TX – allocate ITL entry” que ocorre quando a requisição de alterações no mesmo bloco por transações (sessões) diferentes é alta.

As ITLs estão diretamente relacionadas com os parâmetros INITRANS e MAXTRANS dos segmentos, que representam o número mínimo e máximo de transações ocorrendo no mesmo bloco. Após escolher o número mínimo, este valor pode crescer se houver espaço livro no bloco.

INITRANS: Número de entradas iniciais do bloco, cujo default é 2 (mesmo que você solicitar 1).

MAXTRANS: Número máximo de transações, que desde o Oracle 10g é 256.

Uma conclusão imediata disto é que não será possível mais que 256 transações por bloco, embora este seja um número mais do que adequado na grande maioria das vezes.

 

 

DEADLOCKs

 

Os deadlocks são no mínimo assustadores, mas é a forma que o DB tem de evitar locks cíclicos. Veja o cenário abaixo:

– Sessão A possui lock no empregado 200

SQL> select EMPLOYEE_ID, FIRST_NAME, LAST_NAME from employees where EMPLOYEE_ID=200 for update;

EMPLOYEE_ID FIRST_NAME           LAST_NAME

———– ——————– ————————-

200 Jennifer             Whalen

– Sessão B possui lock no departamento 200

SQL> select DEPARTMENT_ID, DEPARTMENT_NAME from departments where DEPARTMENT_ID = 200 for update;

DEPARTMENT_ID DEPARTMENT_NAME

————- ——————————

200 Operations

– Sessão A tenta lockar o departamento 200 (lockado por B)

SQL> select DEPARTMENT_ID, DEPARTMENT_NAME from departments where DEPARTMENT_ID = 200 for update;

… fica congelada

– Sessão B tenta lockar o empregado 200 (lockado por A)

SQL> select EMPLOYEE_ID, FIRST_NAME, LAST_NAME from employees where EMPLOYEE_ID=200 for update;

… fica congelada

 

Veja o que ocorreu com a sessão A:

select DEPARTMENT_ID, DEPARTMENT_NAME from departments where DEPARTMENT_ID = 200 for update

*

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource

No alert do DB é registrado este evento:
ORA-00060: Deadlock detected. See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors. More info in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_32577.trc.

E a informação no trace (apenas algumas partes):

*** 2017-02-12 10:21:29.714

*** SESSION ID:(241.57407) 2017-02-12 10:21:29.714

*** MODULE NAME:(SQL*Plus) 2017-02-12 10:21:29.714

*** CLIENT DRIVER:(SQL*PLUS) 2017-02-12 10:21:29.714

DEADLOCK DETECTED ( ORA-00060 )

See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a

deadlock due to user error in the design of an application

or from issuing incorrect ad-hoc SQL. The following

information may aid in determining the deadlock:

—– Information for the OTHER waiting sessions —–

current SQL:

select EMPLOYEE_ID, FIRST_NAME, LAST_NAME from employees where EMPLOYEE_ID=200 for update

—– End of information for the OTHER waiting sessions —–

 

Information for THIS session:

—– Current SQL Statement for this session (sql_id=1fu1jghcq3ydc) —–

select DEPARTMENT_ID, DEPARTMENT_NAME from departments where DEPARTMENT_ID = 200 for update

Como se pode ver, o trace traz algumas informações importantes na investigação do que pode ter ocorrido. A principal é o SQL que a minha sessão tentou executar e não conseguiu.

Em apps de grande porte, casos comuns de deadlock são com FK não indexadas. Quando se altera/exclui dados da chave na tabela pai, o Oracle coloca lock full nas tabelas filhas que não possuem índice nos mesmo campos. Com a tabela inteira lockada, a chance de alguém precisar destes dados é maior.

O fato que talvez cause uma certa estranheza em quem nunca se aprofundou no assunto é que este lock full ocorre apenas durante a execução do DML na tabela pai e não durante toda a transação.

Para ilustrar, vamos usar o seguinte cenário:

– Table pai: hr.departments (PK: department_id)

– Table filha: hr.employees (FK: department_id)

– por default, há um índice na hr.employees.department_id. Foi dropado: DROP INDEX EMP_DEPARTMENT_IX;

Se o lock durar apenas durante o DML na pai, poderemos lockar uma linha na filha após a conclusão.

SESSAO 1> DELETE departments d WHERE  d.department_id = 170;

1 row deleted.

Elapsed: 00:00:00.00

SESSAO 2> select EMPLOYEE_ID, FIRST_NAME, LAST_NAME from employees where EMPLOYEE_ID=200 for update;

EMPLOYEE_ID FIRST_NAME           LAST_NAME

———– ——————– ————————-

200 Jennifer             Whalen

Elapsed: 00:00:00.01

Ok, conforme esperado. E como podemos então perceber este lock que dura 00:00:00.00 s? Bom, neste caso, se já houver algum registro lockado na filha, não será possível concluir o DML na pai. Faremos rollback nas duas e começaremos na ordem inversa.

SESSAO 2> select EMPLOYEE_ID, FIRST_NAME, LAST_NAME from employees where EMPLOYEE_ID=200 for update;

EMPLOYEE_ID FIRST_NAME           LAST_NAME

———– ——————– ————————-

200 Jennifer             Whalen

Elapsed: 00:00:00.00

SESSAO 1> DELETE departments d WHERE  d.department_id = 170;

… aguardando

Sempre devemos indexar as colunas das FKs? Não necessariamente. De forma simplificada, se você faz join com pai e filha e/ou pensa em alterar a chave (ou excluir) de uma tabela pai, é importante que na filha exista um índice.

 

CONCLUSÃO

 

Falamos um pouco de pontos positivos e negativos do lock. Negativos quando nos geram problemas de performance, positivos quando nos garantem a consistência dos dados. Falamos também de alguns fenômenos relacionados como o Lost Update, Blocks e Deadlocks.

Após esta breve introdução sobre o conceito, nos artigos seguintes aprofundaremos um pouco. Falaremos do tipo de lock, modo de lock tanto para DML como DDL. Revisitaremos o assunto das FKs, com uma riqueza maior de detalhes.

Até lá!

Adriano Bonacin

Oracle Database Vault: introdução a Realm

RESUMO

Objetivo deste artigo é explorar uma outra feature do Oracle Database Vault, o Realm. Veremos como é possível impedir o acesso de super users a determinado objeto, role ou mesmo a schemas inteiros.

 

INTRODUÇÃO

Este é o terceiro de uma série de artigos introdutórios a Oracle Database Vault (ODV). Vimos como configurar, as principais mudanças nas atividades do DBA e a segregação de funções em [1]. Vimos também como funcionam Factor, Rule, Ruleset e Command Rule em [2] e como podemos combiná-los para proteger um objeto, um schema ou mesmo o DB inteiro contra determinada instrução SQL.

Usaremos novamente um DB na versão 12.1.0.2 com o ODV habilitado com as configurações defaults, acrescentando apenas com as mudanças realizadas nos artigos anteriores, mas elas não necessárias. Teremos o C##DONO que possui a role DV_OWNER e o C##CONTAS com a DV_ACCTMGR.

O componente que trataremos neste artigo será o Realm (Domínio) e tentaremos ilustrar sua funcionalidade com alguns exemplos. Ele atua como um nível de proteção “mais forte” que os conhecidos privilégios de sistemas/objetos. O escopo pode ser específico a nível de determinado objeto ou amplo como tipos de objeto ou schemas.

 

CENÁRIO PARA TESTE

Para preparar nossos testes, vamos começar com a criação de users e roles que serão necessários. Contextualizando também o cenário inicial, vemos que é possível com o SYS consultar todos os objetos do schema HR mesmo com o ODV habilitado.

 

Vamos criar um user USER_CONSULTA_HR que possuirá permissão de leitura em HR.EMPLOYEES e HR.DEPARTMENTS.

 

Criaremos também uma Role que utilizaremos no último exemplo:

 

REALM

O Realm é como uma zona de proteção em que você pode colocar roles, objetos ou mesmo schemas inteiros. Com ele, é possível blindar os dados contra usuários com privilégios de sistema (como SELECT ANY TABLE, por exemplo). E isso inclui, especialmente, proteção contra o DBA [3].

Adicionar o schema HR dentro de um Realm, por exemplo, provocará erros de falta de privilégios caso seus objetos sejam acessados por um DBA ou alguém que possua SELECT ANY TABLE.

Os atributos de um Realm são:

– Name

– Description

– Enable

– Autid Option

– Type

Os dois primeiros são triviais, olhemos então para o Enable. Mas este também é trivial! Sim, só gostaríamos de chamar a atenção de como devemos utilizá-lo aplicando a API disponibilizada. Os valores aceitos para Enable são estes abaixo:

– DBMS_MACUTL.G_YES (default)

– DBMS_MACUTL.G_NO

 

Podemos também definir quando auditaremos os acessos. As opções são simplesmente não auditar, auditar falhas, auditar sucessos ou ambos.

Audit_options:

– DBMS_MACUTL.G_REALM_AUDIT_OFF (default)

– DBMS_MACUTL.G_REALM_AUDIT_FAIL

– DBMS_MACUTL.G_REALM_AUDIT_SUCCESS

– DBMS_MACUTL.G_REALM_AUDIT_FAIL + DBMS_MACUTL.G_REALM_AUDIT_SUCCESS

Quanto ao tipo, a discussão é interessante e tentaremos mostrar em detalhes através de exemplos. Há dois tipos: o REGULAR (0) e o MANDATORY (1).

A diferença entre começa no fato de que o REGULAR protege apenas os objetos contra users com “privilégios de sistema” (SELECT ANY TABLE). Porém, isso não impede de usuários que tenham privilégio de objetos de realizar a consulta (grant de SELECT/READ explícito nos objetos do HR ou via ROLE).

Já o Realm MANDATORY é um pouco mais restritivo. Nem mesmo os usuários com privilégio de objeto poderão consultá-los. Neste caso, é necessário conceder “privilégio no Realm” ao usuário que pretende fazer o acesso. Vale frisar que esta discussão inclui o owner dos objetos protegidos. Ou seja, se protegermos o HR com um Realm MANDATORY, ele perde a permissão de consultar os próprios objetos. Interessante, não?

 

Vejamos como funciona primeiramente os Realms do tipo REGULAR.

 

Aqui temos um Realm, entretanto, ele ainda não protege nada. Precisamos dizer quais são os objetos que ele deve englobar. Podemos especificar owner, nome do objeto ou mesmo seu tipo e caso precise generalizar, pode ser utilizado o “wildcard”: ‘%’. Vamos começar adicionando a tabela HR.EMPLOYEES abaixo dele.

 

A partir de agora, usuários com privilégios de sistema não conseguirão mais ver os dados desta tabela. Veja o que ocorre com o SYS:

 

Porém, aqui ainda é possível o acesso de users que possuam privilégios do objeto, como é o caso do USER_CONSULTA_HR:

 

Antes de avançar, vamos ver como estão as configurações e aproveitaremos para conhecer as views com os metadados de Realms:

 

Até aqui nenhum segredo, falamos de todos estes atributos. Vejamos como está nosso ambiente.

 

Note que tivemos um outro efeito com esta proteção: não é mais possível conceder permissão de leitura nesta tabela. Resolveremos este ponto adiante.

 

Explorando um pouco mais o conceito de Realm, vamos falar do tipo que é mais restritivo, o MANDATORY. Como citado acima, ele impede quem tenha privilégios de sistema, privilégios de objetos e até mesmo o owner de fazer consultas em objetos protegidos.

Para ilustrar, vamos alterar o tipo do Realm PROTECT_HR para MANDATORY e, com isso, esperamos que o USER_CONSULTA_HR perca o acesso de leitura, visto que será necessário um privilégio a mais para conseguir “chegar” aos dados [4].

 

Consultando novamente, vemos que temos um Realm MANDATORY:

 

Vamos testar o acesso de USER_CONSULTA_HR aos dados protegidos (EMPLOYEES) e não protegidos (DEPARTMENTS).

 

Veja o que ocorreu com o HR:

 

O próprio owner perdeu acesso a seus dados. É nesse sentido que afirmamos que o MANDATORY é mais restritivo. Segundo a documentação, com sua utilização é necessária autorização explícita ao Realm. Ou seja, para acessar os dados, além dos devidos privilégios de objeto, será necessária autorização no Realm.

O que seria esta autorização no Realm? São dois os tipos de permissão: Participante (DBMS_MACUTL.G_REALM_AUTH_PARTICIPANT) ou Owner (DBMS_MACUTL.G_REALM_AUTH_OWNER). A principal diferença no tipo de autorização é que o tipo PARTICIPANT não permite a concessão de privilégios sobre os objetos protegidos. Para isso, é necessário a autorização do tipo OWNER.

Autorizando o USER_CONSULTA_HR como PARTICIPANT, resolverá o problema da falta de privilégio. Veja:

 

Precisamos também aumentar nossa query (removemos alguns campos para simplificar) que verifica a configuração atual, adicionando outra view:

 

E agora podemos ver que USER_CONSULTA_HR é PARTICIPANT do Realm PROTECT_HR. Será que isso resolve o problema de falta privilégio? Resolvido.

 

Para ilustrar a diferença, vamos conceder o mesmo nível de privilégio para o HR e em seguida vamos resolver um outro ponto citado acima.

 

O problema citado foi o user HR não conseguir conceder privilégio de leitura em uma tabela protegida, embora consiga agora acessar a tabela.

 

Para que seja possível, precisamos alterar o tipo de privilégio do HR para OWNER do Realm.

 

Agora temos:

 

Isto resolve nosso problema, confira:

 

Faltou ainda falar de um outro atributo da “autorização” a nível de Realm, os Rulesets. Deseja permitir o acesso apenas se determinadas condições forem verificadas? A partir de determinado host? Apenas em certos horários? Este é o ponto chave para atingir este objetivo. Falamos de Rulesets em [http://www.oracle.com/technetwork/pt/articles/idm/rule-ruleset-command-rule-vault-3038750-ptb.html] e citamos que elas poderiam ser utilizadas aqui.

Aumentando a especificidade do nosso exemplo, aproveitarei para incluir um questionamento que nosso time recebeu da equipe de Sec. Como fazer para que a concessão de privilégios tenha prazo de validade? Com os pontos que já discutimos nos artigos anteriores, temos condições de resolver este ponto.

Faremos agora com que o Realm só permita acesso do USER_CONSULTA_HR aos dados até um horário determinado: 10h 35min do dia 18/06/2016. É uma forma não depender de uma ação manual para removê-lo no futuro. Vale enfatizar que o Ruleset está vinculado com a autorização e, portanto, pode afetar apenas determinado user.

Vamos criar uma Rule e um Ruleset e associá-los.

 

Precisamos também alterar a autorização dada ao USER_CONSULTA_HR para que seja também validado o Ruleset:

 

Verificando a configuração, temos:

 

E finalmente, só admirar o resultado:

 

Último ponto que discutiremos nesta introdução a Realms, será como proteger também as Roles. No começo do artigo criamos uma role ROLE_READ_SCOTT. Vamos conceder privilégio de leitura em uma tabela do SCOTT e em seguida criar um Realm para proteger a Role. Depois, tentaremos conceder outro privilégio.

 

Grant concedido com sucesso. Agora a criação do Realm e incluindo a Role na proteção.

 

Analisando o resultado:

 

E agora que a Role está protegida, podemos conceder privilégio de leitura na SCOTT.DEPT?

 

A forma de “resolver” a violação é fazer com que o SCOTT tenha autorização de OWNER no Realm.

 

E o resultado:

Desta forma, vimos que podemos evitar que privilégios sejam adicionados ou removidos de determinada Role se a protegermos com Realm. Somente quem for autorizado como OWNER do Realm poderá fazer o Grant/Revoke.

 

CONCLUSÃO

Neste artigo exploramos uma outra feature poderosa do Oracle Database Vault. Este é o componente responsável por impedir o acesso de usuários privilegiados aos dados sensíveis. Vimos os diferentes tipos de Realms e que podemos escolher quando auditaremos os acessos (ou tentativas). Falamos também o quão específico ou genéricos podemos ser quando o assunto é proteger os dados.

Há ainda pontos a serem explorados nós próximos artigos como autorização para datapump e patches, auditoria, roles, schedulers, etc. Faremos na medida do possível.

 

REFERÊNCIA

[1] http://www.oracle.com/technetwork/pt/articles/idm/seguranca-oracle-database-vault-2999070-ptb.html

[2] http://www.oracle.com/technetwork/pt/articles/idm/rule-ruleset-command-rule-vault-3038750-ptb.html

[3] https://docs.oracle.com/database/121/DVADM/cfrealms.htm#DVADM70144

[4] https://docs.oracle.com/database/121/DVADM/cfrealms.htm#DVADM71156

 

Oracle Database Vault: introdução a Rule, Ruleset e Command Rule

Vimos anteriormente como configurar o Oracle Database Vault (ODV), procedimentos para habilitar e desabilitar, além do cuidado extra que precisamos ter com a senha de seus users (a perda destas senhas pode ser trágica). Falamos também de algumas atividades do dia a dia de um DBA que sofreram alterações[1].

O objetivo agora é mostrar que nem tudo é um pesadelo e também quais são os pontos positivos desta “mudança”. Vamos continuar a utilizar um DB 12.1.0.2 com ODV habilitado com as configurações default e assim podemos explorar algumas outras features focando no ganho de segurança que elas nos proporcionam.

O ODV como um todo pode ser gerenciado a partir do Cloud Control, mas aqui nos restringiremos ao SQL*Plus e na rica API disponibilizada para gestão dos diversos componentes.

Nosso primeiro passo será passar por alguns exemplos tentando entender quais são as configurações default. Vale lembrar que grande parte das propriedades do Vault só podem ser consultadas por usuários devidamente privilegiados, como nosso C##DONO que possui privilégios da role DV_OWNER. Ao mesmo tempo, veremos também como aumentar a proteção através das Rulesets e Comand Rules que criaremos.

Há uma infinidade de combinações e aqui escolhemos usar o seguinte exemplo para nos guiar nos conceitos que discutiremos. Vamos impor que, com exceção do SCOTT, SYS e SYSTEM, qualquer user que possua o privilégio de UNLIMITED TABLESPACE terá de se conectar exclusivamente via SQL*Plus. Tenha em mente que, fazendo isso, alguns processos automáticos podem ser afetados, é sempre indicado testar em ambientes de teste/lab.

 

CENÁRIO PARA O EXEMPLO

Vamos preparar nosso cenário de testes concedendo privilégios ao SCOTT e criando dois novos users, um com privilégio de UNLIMITED TABLESPACE (USER_UNLIM_TS) e outro sem (USER_LIM_TS). Chamamos a atenção que aqui temos um DB cujo ODV foi habilitado e (como falamos no artigo anterior) temos um user C##CONTAS que gerencia contas e profiles.

SYS@cdb1> SELECT VALUE FROM V$OPTION WHERE PARAMETER = ‘Oracle Database Vault’;

VALUE

———

TRUE

C##CONTAS@pdb1> create user USER_UNLIM_TS identified by oracle;

User created.

C##CONTAS@pdb1> create user USER_LIM_TS identified by oracle;

User created.

SYS@pdb1> grant create session, unlimited tablespace to USER_UNLIM_TS;

Grant succeeded.

SYS@pdb1> grant create session to USER_LIM_TS;

Grant succeeded.

SYS@pdb1> grant unlimited tablespace to SCOTT;

Grant succeeded.

E agora vamos explorar um pouco mais o Database Vault.

 

FACTOR

É uma forma de o Oracle obter os atributos de uma conexão com o DB, seja do lado Server ou Client, como IP do host onde roda o database ou IP do cliente. Podemos utilizá-lo para restringir acesso ao DB ou aos dados, a partir de determinado host ou alguma outra propriedade específica como ferramentas utilizadas e métodos de autenticação [2]. Também é possível combiná-los com Rules nas RuleSets aumentando ainda mais a especificidade das políticas de segurança.

O conjunto de Factors já existentes pode ser consultado através da view dvsys.dba_dv_factor:

C##DONO@pdb1> SELECT name, get_expr FROM   dvsys.dba_dv_factor;

Lang                           UPPER(SYS_CONTEXT(‘USERENV’,’LANG’))

Machine                        DVSYS.DBMS_MACADM.GET_SESSION_INFO(‘MACHINE’)

Authentication_Method          UPPER(SYS_CONTEXT(‘USERENV’,’AUTHENTICATION_METHOD’))

Client_IP                      UPPER(SYS_CONTEXT(‘USERENV’,’IP_ADDRESS’))

Database_IP                    UPPER(SYS_CONTEXT(‘USERENV’,’SERVER_HOST_IP’))

Database_Hostname              UPPER(DVSYS.DBMS_MACADM.GET_INSTANCE_INFO(‘HOST_NAME’))

 

Podemos testar os Factors usando uma função do DVSYS chamada GET_FACTOR. Ela faz a avaliação do Factor solicitado que, obviamente, pode variar de sessão para sessão. Vejamos dois exemplos acessando o mesmo DB a partir de origens diferentes:

oracle-factors

C##DONO@pdb1> select DVSYS.GET_FACTOR(‘Machine’) from dual;

DVSYS.GET_FACTOR(‘MACHINE’)

———————-

prod.localdomain

 

Agora que conhecemos os Factor defaults, iremos construir uma forma de apontar o uso do SQL*Plus (ou qualquer outra ferramenta), que será necessário como um dos steps do nosso exemplo. Para enriquecer um pouco mais, também criaremos um novo tipo de Factor (factor_type) e o chamaremos de App (por default existe um Factor Type chamado Application e muitos outros, que podem ser consultados em dvsys.dba_dv_factor_type). Vamos lá, com um user que possua privilégio DV_OWNER:

C##DONO@pdb1>

BEGIN

dbms_macadm.create_factor_type(name        => ‘App’,

description => ‘Factor_type relacionado a aplicacao utilizada para conexao ao DB.’);

END;

COMMIT;

/

PL/SQL procedure successfully completed.

 

Agora que temos um novo tipo, vamos criar o Factor que nos devolverá o nome da ferramenta em uso. Depois basta validarmos se é ou não o que desejamos.

C##DONO@pdb1>

BEGIN

DBMS_MACADM.CREATE_FACTOR(factor_name      => ‘App_Name’,

factor_type_name => ‘App’,

description      => ‘Factor que devolvera o nome da app.’,

rule_set_name    => NULL,

get_expr         => ‘UPPER(SYS_CONTEXT(”USERENV”,”MODULE”))’,

validate_expr    => NULL,

identify_by      => DBMS_MACUTL.G_IDENTIFY_BY_METHOD,

labeled_by       => DBMS_MACUTL.G_LABELED_BY_SELF,

eval_options     => DBMS_MACUTL.G_EVAL_ON_SESSION,

audit_options    => DBMS_MACUTL.G_AUDIT_ON_GET_ERROR,

fail_options     => DBMS_MACUTL.G_FAIL_WITH_MESSAGE);

COMMIT;

END;

/

PL/SQL procedure successfully completed.

 

Detalhes das opções válidas para cada parâmetro você pode encontrar em [3], não vamos entrar em tantos detalhes de Factor aqui. Agora testamos o resultado do nosso novo factor.

C##DONO@pdb1> select DVSYS.GET_FACTOR(‘App_Name’) from dual;

DVSYS.GET_FACTOR(‘APP_NAME’)

——————————————

SQL*PLUS

 

Para ilustrar a diferença, vamos fazer a mesma query através do SQL Developer:

sql-developer

Demos o primeiro passo para proteção do cenário que propomos como exemplo. Vamos continuar essa caminhada.

 

RULE

Próximo passo é obter uma forma de fazer nossas asserções a respeito de propriedades/características do DB, como permissões de um determinado user, e para isso usamos as Rules (regras). Elas nada mais são que objetos no DB que possuem uma expressão lógica associada e que devem, portanto, retornar TRUE ou FALSE, levando em conta os parâmetros informados, quando for o caso. Você pode usar até 90 caracteres para atribuir um nome e a expressão que será avaliada deve possuir até 1024 caracteres.

Você pode, basicamente, fazer o mesmo que faria na cláusula WHERE de um SQL, inclusive utilizar funções. Neste caso será necessário conceder privilégio de EXECUTE para o DVSYS e utilizar o “Full Qualified Name” (schema.nome_da_funcao). Caso opte por functions/procedures, você provavelmente deverá usar “definer’s rights” [4]ou a Rule poderá falhar (por padrão o DVSYS não possui muitos privilégios). Há mais detalhes em [5].

As Rules defaults, encontradas em dvsys.dba_dv_rule, incluem duas bastante convenientes para o caso em que não se deseja “testar” condições, mas sim impor. Elas podem permitir ou impedir determinada ação. São elas:

NAME      RULE_EXPR

——— ———————

True      1=1

False     1=0

E, como esperado, a True sempre retornará TRUE e a False, FALSE. Outras interessantes são as que verificam se o user conectado possui determinado privilégio.

Is User Manager

DVSYS.DBMS_MACUTL.USER_HAS_ROLE_VARCHAR(‘DV_ACCTMGR’, ‘”‘||dvsys.dv_login_user||'”‘) = ‘Y’

Is Database Administrator

DVSYS.DBMS_MACUTL.USER_HAS_ROLE_VARCHAR(‘DBA’,'”‘||dvsys.dv_login_user||'”‘) = ‘Y’

“DVSYS.DV_LOGIN_USER” é uma function dentro do schema DVSYS que retorna o user que está logado e a DVSYS.DBMS_MACUTL.USER_HAS_ROLE_VARCHAR retornará “Y” caso o user possua a Role informada, mesmo que indiretamente através de outra.

Existem algumas funções que a API disponibiliza no schema DVSYS e podem ser utilizadas para a criação de regras mais especializadas [6,7], como a DVSYS.DV_SYSEVENT que retorna o evento que disparou a avaliação das regras, DVSYS.DV_INSTANCE_NUM que retorna o número da instância e DVSYS. DBMS_MACUTL. USER_HAS_SYSTEM_PRIV_VARCHAR que verifica se o user possui determinado privilégio de sistema.

Prosseguindo com nosso exemplo, vamos criar uma Rule que utilizaremos para verificar se o user que tentará se conectar é um dos que permitiremos, outra para verificar se o user possui ou não o privilégio de sistema UNLIMITED TABLESPACE e, finalmente, uma que analisa o Factor criado anteriormente para identificar a aplicação utilizada.

Tenha em mente que o resultado TRUE na avaliação da regra permitirá prosseguir, e se deseja proibir alguma ação, ela deve retornar FALSE. Uma forma de facilitar a compreensão das Rules é utilizar uma pergunta como nome, algo como “É o usuário X?” ou “Não possui privilégio Y?”, e a resposta será o retorno. Perguntas negativas nem sempre são tão intuitivas quando o retorno é falso, mas nada que alguns testes de mesa não resolvam.

A primeira para verificar se o user é o SCOTT, SYS ou SYSTEM:

C##DONO@pdb1>

BEGIN

DBMS_MACADM.CREATE_RULE(rule_name => ‘Eh scott, sys ou system’,

rule_expr => ‘USER IN (”SCOTT”,”SYS”,”SYSTEM”)’);

END;

/

PL/SQL procedure successfully completed.

 

Precisaremos também criar uma Rule que verifique se o usuário “não” possui o privilégio de UNLIMITED TABLESPACE.

C##DONO@pdb1>

BEGIN

DBMS_MACADM.CREATE_RULE(rule_name => ‘Nao possui UNLIMITED TS’,

rule_expr => ‘(DVSYS.DBMS_MACUTL.USER_HAS_SYSTEM_PRIV_VARCHAR(”UNLIMITED TABLESPACE”,”””||dvsys.dv_login_user||”””) = ”N”)’);

END;

/

PL/SQL procedure successfully completed.

O Factor que criamos acima pode também ser transformado em uma Rule. Ele, na verdade, virou uma função (podemos encontrá-la na dba_objects) e a utilizamos como base para a Rule:

SYS@pdb1>

select owner, object_name, object_type

from   dba_objects

where  object_name like ‘%APP_NAME%’;

OWNER        OBJECT_NAME  OBJECT_TYPE

———— ———— ————

DVF          F$APP_NAME   FUNCTION

C##DONO@pdb1>

BEGIN

DBMS_MACADM.CREATE_RULE(rule_name => ‘Eh SQL*Plus’,

rule_expr => ‘UPPER(DVF.F$APP_NAME) like ”%SQL%PLUS%”’);

END;

/

PL/SQL procedure successfully completed.

 

RULESET

Falaremos agora de um componente que nos permite agrupar Rules em uma única verificação. Rulesets são um conjunto de Rules (uma ou mais) que são avaliadas em tempo de execução e vão retornar TRUE ou FALSE baseada nas regras que a compõe. O ponto interessante é que elas atuam como acessório para outros componentes do ODV. Assim, Command Rules ou Realms (que falaremos em um próximo artigo), por exemplo, podem utilizá-las para permitir ou não a execução de determinada instrução.

Dentre as opções de avaliação de uma Ruleset, temos:

– ALL TRUE: Todas as regras deverão ser avaliadas como TRUE para que a Ruleset retorne TRUE.

– ANY TRUE: Pelo menos uma das regras deve retornar TRUE para que a Ruleset retorne TRUE.

A avaliação pode ocorrer apenas uma vez e ter o resultado reaproveitado para todas as chamadas subsequentes da mesma sessão (Static) ou ocorrer a cada nova chamada (non-Static).

Também há outras opções, como executar um PL/SQL em caso de sucesso ou falha baseado na forma escolhida para “Hander_Options” [8]. Pode ser utilizado uma procedure de envio de e-mail, uma que apenas registre em alguma tabela específica ou qualquer outra que o DV_OWNER tenha privilégio de execução. Para enriquecer um pouco nosso exemplo, vamos criar uma tabela no schema SYSTEM para registro das falhas de conexão provocadas pela Ruleset e uma procedure para populá-la (lembre-se que este é apenas um exemplo sem muito critério para ilustrar features do ODV, você deve usar uma procedure que atenda a seus requisitos e em um schema apropriado).

SYSTEM@pdb1> CREATE TABLE ruleset_fail

2  (username     VARCHAR2(128),

3  data_hora    DATE);

Table created.

 

SYSTEM@pdb1> CREATE OR REPLACE PROCEDURE prc_ruseset_fail

2  AS

3  PRAGMA AUTONOMOUS_TRANSACTION ;

4  BEGIN

5    INSERT INTO ruleset_fail

6     (username,

7      data_hora)

8    VALUES

9     (USER,

10      SYSDATE);

11     COMMIT;

12  EXCEPTION

13    WHEN OTHERS THEN

14      dbms_output.put_line(‘Algo errado:’||Sqlerrm||dbms_utility.format_error_backtrace);

15  END;

16  /

Procedure created.

 

SYSTEM@pdb1> grant execute on prc_ruseset_fail to dvsys;

Grant succeeded.

 

Agora temos pronto o que precisamos para seguir em diante:

– Verificar se o user é SCOTT, SYS ou SYSTEM;

– Se o user NÃO possui privilégio de UNLIMITED TABLESPACE;

– Se a ferramenta utilizada ser SQL*Plus;

Os passos que seguiremos agora serão: a criação da Ruleset e em seguida associá-la com as Rules criadas anteriormente.

C##DONO@pdb1> BEGIN

DBMS_MACADM.CREATE_RULE_SET(rule_set_name   => ‘RS_SCOTT_SYS_SYSTEM_SQLPLUS_UNLIMTS’,

description     => ‘Ruleset que verifica se o user eh o scott_sys_system, se possui UNLIM TS or se a conexao eh via SQLPLUS’,

enabled         => DBMS_MACUTL.G_YES,

eval_options    => DBMS_MACUTL.G_RULESET_EVAL_ANY,

audit_options   => DBMS_MACUTL.G_RULESET_AUDIT_FAIL,

fail_options    => DBMS_MACUTL.G_RULESET_FAIL_SHOW,

fail_message    => ‘Conexoes com UNLIM TS devem ser feitas pelo SQL*PLUS’,

fail_code       => -20001,

handler_options => DBMS_MACUTL.g_ruleset_handler_fail,

handler         => ‘system.prc_ruseset_fail’,

is_static       => FALSE);

END;

/

PL/SQL procedure successfully completed.

 

C##DONO@pdb1> BEGIN

DBMS_MACADM.ADD_RULE_TO_RULE_SET(rule_set_name => ‘RS_SCOTT_SYS_SYSTEM_SQLPLUS_UNLIMTS’,

rule_name     => ‘Eh SQL*Plus’,

rule_order    => 1,

enabled       => DBMS_MACUTL.G_YES);

END;

/

PL/SQL procedure successfully completed.

 

C##DONO@pdb1> BEGIN

DBMS_MACADM.ADD_RULE_TO_RULE_SET(rule_set_name => ‘RS_SCOTT_SYS_SYSTEM_SQLPLUS_UNLIMTS’,

rule_name     => ‘Eh scott, sys ou system’,

rule_order    => 2,

enabled       => DBMS_MACUTL.G_YES);

END;

/

PL/SQL procedure successfully completed.

 

C##DONO@pdb1> BEGIN

DBMS_MACADM.ADD_RULE_TO_RULE_SET(rule_set_name => ‘RS_SCOTT_SYS_SYSTEM_SQLPLUS_UNLIMTS’,

rule_name     => ‘Nao possui UNLIMITED TS’,

rule_order    => 3,

enabled       => DBMS_MACUTL.G_YES);

END;

/

PL/SQL procedure successfully completed.

 

De posse da nossa Ruleset, precisamos agora associá-la a uma Command Rule. Vejamos então o que é este outro componente.

 

COMMAND RULE

Basicamente é uma forma de se proteger de determinada instrução impondo uma variedade de condições. As condições que podem ser impostas são muitas, como IP de origem, horário, programa utilizado, username, usuário do SO e tantas outras. O único ponto importante é criar Rules/Ruleset que faça todas as verificações que deseja.

Há também uma grande coleção de instruções que podem ser protegidas, como por exemplo UPDATE, CREATE VIEW, ALTER FUNCTION, ALTER SEQUENCE. A lista completa pode ser encontrada em [9].

Assim, apenas quando as condições forem satisfeitas a instrução será executada com sucesso. São sempre verificadas em tempo de execução e afetam todas as instruções que ela “protege”. Por exemplo, no caso de alterar a senha de um usuário, como discutimos em [1], mesmo que o SYS tenha privilégios de ALTER USER, há uma Command Rule que protege esta instrução.

C##DONO@cdb1>

SELECT command,

rule_set_name,

object_owner,

object_name

FROM   dvsys.dba_dv_command_rule

WHERE  command = ‘ALTER USER’;

COMMAND       RULE_SET_NAME                OBJECT_OWNER   OBJECT_NAME

————- —————————- ————– ————

ALTER USER    Can Maintain Own Account     %              %

 

As Command Rules podem ter efeito em todo o sistema, algum schema específico ou mesmo apenas um determinado objeto. Desta forma, é possível que um objeto esteja protegido por mais de uma Command Rule. Você poderia ter uma protegendo um schema e outra em um objeto específico. Todas elas serão validadas, para cada uma será verificada a condição imposta pela Ruleset.

Command Rules são “mais fortes” que privilégios de sistema e, desta forma, é possível evitar que DBAs possam realizar determinada operação.

Vamos ver um exemplo de como isso funciona? Nossa primeira Command Rule impedirá que tabelas sejam dropadas do schema SCOTT.

Vamos criar, dropar e recriar uma tabela TB_EXEMPLO no schema SCOTT para comparar com o resultado que esperamos.

SCOTT@pdb1> create table TB_EXEMPLO as select * from all_tables;

Table created.

 

SCOTT@pdb1> drop table TB_EXEMPLO;

Table dropped.

 

SCOTT@pdb1> create table TB_EXEMPLO as select * from all_tables;

Table created.

 

Agora, com um user que possua DV_OWNER ou DV_ADMIN, basta criar a Command Rule. Usaremos a RuleSet “Disabled” para impedir todas as requisições. É uma daquelas Ruleset predefinida (coringa) e bastante útil sempre que precisamos avaliar como “FALSE” todas as instruções.

C##DONO@pdb1>

BEGIN

DBMS_MACADM.CREATE_COMMAND_RULE(command       => ‘DROP TABLE’,

rule_set_name => ‘Disabled’,

object_owner  => ‘SCOTT’,

object_name   => ‘%’,

enabled       => DBMS_MACUTL.G_YES);

END;

/

PL/SQL procedure successfully completed.

 

Em seguida, vamos apenas verificar se a criação foi realizada com sucesso.

C##DONO@pdb1>

SELECT command,

rule_set_name,

object_owner,

object_name,

enabled

FROM   dvsys.dba_dv_command_rule

WHERE  command = ‘DROP TABLE’;

COMMAND      RULE_SET_NAME   OBJECT_OWNER    OBJECT_NAME     ENABLED

———— ————– ————— ————— ———-

DROP TABLE   Disabled       SCOTT           %               Y

 

Agora basta testar nossa Command Rule tentando dropar a tabela do SCOTT. E lá está:

SCOTT@pdb1> drop table TB_EXEMPLO;

drop table TB_EXEMPLO

*

ERROR at line 1:

ORA-47400: Command Rule violation for DROP TABLE on SCOTT.TB_EXEMPLO

 

Note que apenas o schema SCOTT foi afetado, continuamos dropando tabelas do HR:

HR@pdb1> create table TB_EXEMPLO as select * from all_tables;

Table created.

HR@pdb1> drop table TB_EXEMPLO;

Table dropped.

 

Agora que vimos como funciona uma Command Rule simples, vamos aproveitar para explorar um pouco a API e apagá-la (sempre com um user apropriado):

C##DONO@pdb1> BEGIN

DBMS_MACADM.DELETE_COMMAND_RULE(command      => ‘DROP TABLE’,

object_owner => ‘SCOTT’,

object_name  => ‘%’);

END;

/

PL/SQL procedure successfully completed.

 

C##DONO@pdb1>

SELECT command,

rule_set_name,

object_owner,

object_name,

enabled

FROM   dvsys.dba_dv_command_rule

WHERE  command = ‘DROP TABLE’;

no rows selected

 

E agora é possível dropar a tabela novamente:

SCOTT@pdb1> drop table TB_EXEMPLO;

Table dropped.

Ilustrado o uso de Command Rules, iremos agora finalizar nosso exemplo inicial, pois já dispomos de toda ferramenta necessária. Então, mãos à obra.

Vamos criar a Command Rule associada a Ruleset RS_SCOTT_SYS_SYSTEM_SQLPLUS_UNLIMTS impedindo conexões (comando SQL: CONNECT).

C##DONO@pdb1> BEGIN

DBMS_MACADM.CREATE_COMMAND_RULE(command       => ‘CONNECT’,

rule_set_name => ‘RS_SCOTT_SYS_SYSTEM_SQLPLUS_UNLIMTS’,

object_owner  => ‘%’,

object_name   => ‘%’,

enabled       => DBMS_MACUTL.G_YES);

END;

/

PL/SQL procedure successfully completed.

 

Agora basta testar (as falhas ficarão registradas na tabela system.ruleset_fail):

1 – Scott, Sys e System, devem se conectar em qualquer aplicação.

2 – USER_UNLIM_TS, deve se conectar apenas no SQL*Plus, já que possui UNLIMITED TABLESPACE.

3- USER_LIM_TS, deve se conectar em qualquer aplicação, visto que não possui UNLIMITED TABLESPACE.

Testando no SQL Developer

sql-developer2

sql-developer3

sql-developer4

Conferindo os registros na nossa tabela:

SYSTEM@pdb1> select * from ruleset_fail;

 

USERNAME        DATA_HORA

————— ——————-

USER_UNLIM_TS   22/05/2016 20:26:28

 

E finalizando, vamos checar se os users USER_LIM_TS e USER_UNLIM_TS conseguem se conectar via SQL*Plus:

SYSTEM@pdb1> connect USER_UNLIM_TS/oracle@pdb1

Connected.

USER_UNLIM_TS@pdb1> connect USER_LIM_TS/oracle@pdb1

Connected.

USER_LIM_TS@pdb1> conn scott/tiger@pdb1

Connected.

 

CONCLUSÃO

Chegamos ao final do nosso exemplo passando por Factor, Rule, Ruleset e Command Rule. Vimos que as Command Rule são peças poderosas que visa impedir que um comando seja executado com sucesso. Elas se baseiam nas Ruleset que verificam as diversas Rules impostas e podem nos alertar ou registrar sucessos ou falhas.

Assim, conseguimos conhecer mais algumas características do ODV e no próximo artigo exploraremos um pouco dos Realms, visando impedir que super users consigam consultar/alterar determinados schemas/objetos.

 

REFERÊNCIA

[1] http://www.oracle.com/technetwork/pt/articles/idm/seguranca-oracle-database-vault-2999070-ptb.html

[2] https://docs.oracle.com/database/121/DVADM/cfgfact.htm

[3] https://docs.oracle.com/database/121/DVADM/apis_factors.htm#DVADM70570

[4] http://docs.oracle.com/database/121/DBSEG/dr_ir.htm#DBSEG99925

[5] https://docs.oracle.com/database/121/DVADM/cfrulset.htm

[6] https://docs.oracle.com/database/121/DVADM/apis_rule_sets.htm#CHDDDEII

[7] https://docs.oracle.com/database/121/DVADM/apis_dbms_macutl.htm#DVADM71518

[8] https://docs.oracle.com/database/121/DVADM/cfrulset.htm#DVADM70172

[9] https://docs.oracle.com/database/121/DVADM/cfcmdaut.htm#DVADM70208

Implementação da feature de segurança Oracle Database Vault em Oracle 12c

Vamos falar um pouco sobre um componente de segurança do Oracle chamado Oracle Database Vault (ODV) e como ele influencia as tarefas rotineiras de um DBA.

O Database Vault é uma feature que visa principalmente retirar “todo o poder” de um DBA e demais usuários “super” privilegiados e entregar para outras mãos as questões relacionadas a segurança do banco de dados. Obviamente que isso não nos cheira bem, a princípio, mas com o tempo vamos entendendo e concordando que realmente tem uma grande utilidade e eficácia. Imagine o quão trágico pode ser o vazamento de uma conta do usuário SYS ou SYSTEM.

É difícil encontrar, atualmente, quem nunca tenha feito uma compra pela internet (pelo menos entre seus amigos). Em algum lugar no meio de um storage possivelmente estarão todos seus dados pessoais, bancários, de cartão de crédito e as vezes muito mais do que imaginamos. Também não é raro encontrar pessoas que tiveram o cartão de crédito extraviado. E, infelizmente, com apenas uma porção de números qualquer pessoa pode fazer compras por aí e deixar conosco a conta.

Uma dúvida já deve ter vindo a mente de muita gente: o quão seguro estão estes dados? Quantos desenvolvedores/analistas/DBAs podem consultá-los diretamente no banco de dados?

Perceba que o ponto onde quero chegar é que nem sempre as coisas “erradas” são feitas de má fé. O PC de um usuário com muitos privilégios pode ser invadido e, sem seu dono saber, lá se vão um punhado destas informações para destino não muito agradável.

Há uma preocupação internacional especificamente com as informações de cartão de crédito e alguns padrões são sugeridos (às vezes exigidos) pelo PCI Security Standards Council (https://www.pcisecuritystandards.org). Entre os pontos em destaque, estão a criptografia dos dados, máscara, permissão de acesso, entre outras.

Com o aumento das normatizações, a tecnologia avança e ajuda a nos proteger. Neste artigo busca-se mostrar, no bom e velho SQL*Plus, como implementar algumas políticas de segurança relacionadas ao ODV. Abordaremos também, algumas das principais mudanças no desempenhar das atividades do DBA.

A bagunça entre português e o inglês será um pouco comum aqui, o objetivo é não traduzir alguns termos técnicos bastante difundidos. Eu prefiro assim desde a primeira vez que me perguntei o que é um “Espaco de Tabela”.

Em [1] há uma boa contextualização (em português) para o caso de interesse em um pouco mais além de instruções de como proceder com a instalação no Oracle 11g.

Neste artigo abordaremos superficialmente algumas features que o Database Vault nos disponibiliza e para isso, usaremos uma instalação default do Oracle 12c Enterprise Edition. Nos próximos artigos detalharemos features como Realm, Ruleset, entre outras.

 

 

INSTALAÇÃO E CONFIGUÇÃO

No Oracle 12c, o Oracle Label Security (OLS) (pre-req para o Oracle Database Vault) e o Oracle Database Vault vem instalado por default. Instalados, mas não configurados.

Começaremos por aí e, para ilustrar, utilizaremos o procedimento para habilitar o Vault e posteriormente como é possível desabilitá-lo.

Utilizamos:

SYS@cdb1> select version from v$instance;

VERSION

—————–

12.1.0.2.0

Após a instalação default, entre outras coisas teremos:

SYS@cdb1> col version for a12

SYS@cdb1> col comp_name for a30

SYS@cdb1> select COMP_NAME, VERSION, STATUS from dba_registry where COMP_NAME in (‘Oracle Database Vault’,’Oracle Label Security’);

COMP_NAME                      VERSION      STATUS

—————————— ———— ———–

Oracle Database Vault          12.1.0.2.0   VALID

Oracle Label Security          12.1.0.2.0   VALID

 

E também podemos notar que já existem diversas roles que falaremos a seguir[2].

SYS@cdb1> col role for a25

SYS@cdb1> select ROLE, COMMON from dba_roles where role like ‘DV%’;

ROLE                       COM

————————– —

DV_ACCTMGR                 YES

DV_ADMIN                   YES

DV_OWNER                   YES

DV_SECANALYST              YES

… e várias outras

 

Para verificar se o DB Vault está habilitado, basta consultar se a option está com valor TRUE. Porém, em um ambiente multitenant, você deve estar atento também com o escopo em que o ODV e o OLS serão habilitados/configurados, já que podem ser utilizados a nível de CDB ou PDB. Aqui criaremos no contêiner root, mas geralmente aplicar tais regras aqui não fazem muito sentido (quando permitidas). Posteriormente habilitaremos em um PDB e analisaremos algumas mudanças nos procedimentos que os DBAs estão acostumados.

 

SYS@cdb1> SELECT VALUE FROM V$OPTION WHERE PARAMETER = ‘Oracle Database Vault’;

VALUE

—————

FALSE

Assim, comecemos pelo procedimento para habilitá-lo e, a partir dos erros, seguiremos pelos passos necessários para a configuração. Criamos um usuário e concedemos o maior privilégio do Vault, DV_OWNER [2], que é necessário para Enable/Disable.

 

SYS@cdb1> create user C##DBV_OWNER identified by oracle;

User created.

 

SYS@cdb1> grant create session, dv_owner to C##DBV_OWNER;

Grant succeeded.

 

SYS@cdb1> conn C##DBV_OWNER/oracle

Connected.

C##DBV_OWNER@cdb1> EXEC DBMS_MACADM.ENABLE_DV;

BEGIN DBMS_MACADM.ENABLE_DV; END;

*

ERROR at line 1:

ORA-12459: Oracle Label Security not configured

ORA-06512: at “LBACSYS.OLS_ENFORCEMENT”, line 3

ORA-06512: at “LBACSYS.OLS_ENFORCEMENT”, line 25

ORA-06512: at “DVSYS.DBMS_MACADM”, line 2068

ORA-06512: at line 1

 

Primeiro problema encontrado: o Oracle Label Security não está configurado. Também podemos consultar a view v$option para ver que o VALUE está FALSE.

SYS@cdb1> SELECT VALUE FROM V$OPTION WHERE PARAMETER = ‘Oracle Label Security’;

VALUE

———–

FALSE

Vale chamar a atenção que há uma outra view que trata especificamente dos componentes do OLS:

SYS@cdb1> select NAME, STATUS, DESCRIPTION from dba_ols_status;

NAME                 STATU DESCRIPTION

——————– —– ————————————-

OLS_CONFIGURE_STATUS FALSE Determines if OLS is configured

OLS_DIRECTORY_STATUS FALSE Determines if OID is enabled with OLS

OLS_ENABLE_STATUS    FALSE Determines if OLS is enabled

 

Nela podemos notar que o OLS não está configurado, muito menos habilitado. Para configurar e habilitar o OLS é necessário restart da base e precisamos, com o SYS, executar os seguintes procedimentos [3]:

SYS@cdb1> EXEC LBACSYS.CONFIGURE_OLS;

PL/SQL procedure successfully completed.

SYS@cdb1> EXEC LBACSYS.OLS_ENFORCEMENT.ENABLE_OLS;

PL/SQL procedure successfully completed.

SYS@cdb1> shutdown immediate;

SYS@cdb1> startup;

 

Checando novamente os status, podemos ver que agora o OLS está configurado e habilitado.

 

SYS@cdb1> set lines 200

SYS@cdb1> set pages 200

SYS@cdb1> select NAME, STATUS, DESCRIPTION from dba_ols_status;

 

NAME                 STATU DESCRIPTION

——————– —– ————————————-

OLS_CONFIGURE_STATUS TRUE  Determines if OLS is configured

OLS_DIRECTORY_STATUS FALSE Determines if OID is enabled with OLS

OLS_ENABLE_STATUS    TRUE  Determines if OLS is enabled

 

 

O LBACSYS é o master user do OLS e uma boa prática é deixá-lo como backup (com a senha em local seguro) e apenas conceder a role LBAC_DBA para os usuários DBAs que atuarão neste componente. Ele vem lockado e expirado.

 

SYS@cdb1> create user C##LBAC identified by oracle;

User created.

SYS@cdb1> grant LBAC_DBA to C##LBAC;

Grant succeeded.

 

Agora podemos verificar que o status do OLS mudou. Porém, o ODV segue na mesma.

 

SYS@cdb1> SELECT VALUE FROM V$OPTION WHERE PARAMETER = ‘Oracle Label Security’;

VALUE

———-

TRUE

SYS@cdb1> SELECT VALUE FROM V$OPTION WHERE PARAMETER = ‘Oracle Database Vault’;

VALUE

———-

FALSE

 

Ao tentar habilitar o ODV, temos novamente um problema. Agora nos informa que o ODV ainda não está configurado.

SYS@cdb1> EXEC DBMS_MACADM.ENABLE_DV;

BEGIN DBMS_MACADM.ENABLE_DV; END;

*

ERROR at line 1:

ORA-47502: Database Vault is not yet configured.

ORA-06512: at “DVSYS.DBMS_MACADM”, line 2059

ORA-06512: at “DVSYS.DBMS_MACADM”, line 2069

ORA-06512: at line 1

Para configurá-lo precisamos de um master user (C##DBV_OWNER, já criado) – que possuirá a role DV_OWNER e, opcionalmente, um que atue como account manager (C##DBV_MANAGER) – que possuirá a role DV_ACCTMGR. Eles devem ser criados antes da operação de configuração. Como SYS, crie o usuário e execute o procedimento informando-os.

 

SYS@cdb1> create user C##DBV_MANAGER identified by oracle;

User created.

 

SYS@cdb1> BEGIN

2  DVSYS.CONFIGURE_DV (

3    dvowner_uname         => ‘C##DBV_OWNER’,

4    dvacctmgr_uname       => ‘C##DBV_MANAGER’);

5  END;

6  /

 

PL/SQL procedure successfully completed.

 

Neste ponto, é possível notar no alert algumas mudanças relativas à segurança:

Tue Mar 22 23:19:47 2016

ALTER SYSTEM SET audit_sys_operations=TRUE SCOPE=SPFILE;

Tue Mar 22 23:19:47 2016

ALTER SYSTEM SET os_roles=FALSE SCOPE=SPFILE;

Tue Mar 22 23:19:47 2016

ALTER SYSTEM SET recyclebin=’OFF’ SCOPE=SPFILE;

Tue Mar 22 23:19:47 2016

ALTER SYSTEM SET remote_login_passwordfile=’EXCLUSIVE’ SCOPE=SPFILE;

Tue Mar 22 23:19:47 2016

ALTER SYSTEM SET sql92_security=TRUE SCOPE=SPFILE;

Agora basta executar utlrp.sql para recompilar os objetos que eventualmente tenham ficados inválidos.

 

SYS@cdb1> @?/rdbms/admin/utlrp.sql

 

Aqui o ODV está configurado, mas não habilitado. Habilitar ou desabilitar, precisa ser feito com algum usuário com privilégio de DV_OWNER. Vamos aqui deixar os users C##DBV_OWNER e C##DBV_MANAGER como backup e vamos criarmos usuários para administração, concedendo apenas os privilégios DV_OWNER e DV_ACCTMGR.

SYS@cdb1> create user C##DONO identified by oracle;

User created.

SYS@cdb1> create user C##CONTAS identified by oracle;

User created.

SYS@cdb1> grant create session, DV_OWNER to C##DONO container=ALL;

Grant succeeded.

SYS@cdb1> grant create session, DV_ACCTMGR to C##CONTAS container=ALL;

Grant succeeded.

 

 

E com o C##DONO, vamos habilitar o ODV.

 

SYS@cdb1> conn C##DONO/oracle

Connected.

C##DONO@cdb1> EXEC DBMS_MACADM.ENABLE_DV;

 

PL/SQL procedure successfully completed.

No alert vemos:

Tue Mar 22 23:37:56 2016

Database Vault is enabled.

 

Agora, para que as alterações tenham efeito, vamos restartar a base. Após aberta, basta consultar novamente a option.

 

SYS@cdb1> shutdown immediate;

SYS@cdb1> startup;

Database opened.

SYS@cdb1> SELECT VALUE FROM V$OPTION WHERE PARAMETER = ‘Oracle Database Vault’;

 

VALUE

———-

TRUE

 

Voltando a discussão a respeito do escopo em que o Vault é aplicado, note que fizemos a alteração no CBD e com isso, o PDB segue sem o ODV desabilitado. Veja:

SYS@pdb1>  SELECT VALUE FROM V$OPTION WHERE PARAMETER = ‘Oracle Database Vault’;

VALUE

—————

FALSE

SYS@cdb1>  SELECT VALUE FROM V$OPTION WHERE PARAMETER = ‘Oracle Database Vault’;

VALUE

—————

TRUE

Ou seja, todos os passos anteriores devem ser refeitos a nível do PDB (ou mesmo ser feito exclusivamente nele) que deseja proteger. Basta repetir os passos anteriores, restartar o PDB e o ODV estará habilitado.

SYS@pdb1> EXEC LBACSYS.CONFIGURE_OLS;

PL/SQL procedure successfully completed.

Se for feito diretamente no PDB, será necessário restart da base. Basta sempre consultar o VALUE da option.

SYS@pdb1> EXEC LBACSYS.OLS_ENFORCEMENT.ENABLE_OLS;

PL/SQL procedure successfully completed.

SYS@pdb1> BEGIN

DVSYS.CONFIGURE_DV (

dvowner_uname         => ‘C##DBV_OWNER’,

dvacctmgr_uname       => ‘C##DBV_MANAGER’);

END;

/

PL/SQL procedure successfully completed.

C##DBV_OWNER@pdb1> EXEC DBMS_MACADM.ENABLE_DV;

PL/SQL procedure successfully completed.

SYS@pdb1> shutdown immediate;

Pluggable Database closed.

SYS@pdb1> startup

Pluggable Database opened.

SYS@pdb1> SELECT VALUE FROM V$OPTION WHERE PARAMETER = ‘Oracle Database Vault’;

VALUE

———

TRUE

 

 

O QUE MUDA?

Após checar os status, basta descobrir o que “parou de funcionar” (e olha que a coleção é grande). Um dos pontos principais, como já discutido, é a segregação de tarefas entre os users. Agora, alguns pontos relativos à segurança saem da alçada do DBA e vão para baixo dos users recém criados para o Database Vault. Se preferir, eles podem ficar sob custódia do time de segurança.

Antes, em um procedimento amplamente conhecido e que viola boas práticas de segurança, um DBA poderia alterar a senha de qualquer usuário do DB, fazer uso desta conta e depois voltar para senha original desde que ele tenha o valor antigo na sys.user$.spare4[4]. E agora, é possível?

Vamos tentar alterar a senha de um usuário:

SYS@pdb1> select USERNAME, ACCOUNT_STATUS, COMMON from dba_users where username = ‘SCOTT’;

 

USERNAME     ACCOUNT_STATUS                   COM

———— ——————————– —

SCOTT        EXPIRED & LOCKED                 NO

 

SYS@pdb1> alter user scott identified by tiger;

alter user scott identified by tiger

*

ERROR at line 1:

ORA-01031: insufficient privileges

Como é possível o SYS não poder alterar a senha de um usuário? Pois é, o SYS teve alguns privilégios “removidos”. Destaco o “removidos” devido ao fato de ele ainda possuir o privilégio em todos os containers:

SYS@cdb1> select * from dba_sys_privs where grantee = ‘SYS’ and privilege like ‘%USER%’;

GRANTEE      PRIVILEGE       ADM COM

———— ————— — —

SYS          DROP USER       NO  YES

SYS          CREATE USER     NO  YES

SYS          ALTER USER      NO  YES

SYS          BECOME USER     NO  YES

E o privilégio de criação, também foi removido? Também!

SYS@cdb1> create user C##USUARIO identified by oracle;

create user C##USUARIO identified by oracle

*

ERROR at line 1:

ORA-01031: insufficient privileges

A partir de agora, os users que gerenciam contas (users, profiles) são aqueles que possuem a role DV_ACCTMGR. Apenas eles poderão criar novos users, alterar senha, profiles.

Veja só:

C##CONTAS@pdb1> alter user scott identified by tiger account unlock;

User altered.

C##CONTAS@cdb1> create user C##USUARIO identified by oracle;

User created.

 

Mas as coisas novas não param por aí. Power users do ODV não podem ter suas senhas alteradas a menos que sejam por eles mesmos. Por exemplo, C##DBV_OWNER não pode alterar a senha do C##DBV_MANAGER e vice-versa e o SYS não altera de ninguém. Neste ponto é importante um pouco mais de atenção e a explicação será dada no momento de desabilitar o ODV.

SYS@cdb1> alter user C##DBV_OWNER identified by oracle;

alter user C##DBV_OWNER identified by oracle

*

ERROR at line 1:

ORA-01031: insufficient privileges

 

SYS@cdb1> alter user C##DBV_MANAGER identified by oracle;

alter user C##DBV_MANAGER identified by oracle

*

ERROR at line 1:

ORA-01031: insufficient privileges

 

SYS@cdb1> conn C##DBV_MANAGER/oracle

Connected.

C##DBV_MANAGER@cdb1> alter user C##DBV_OWNER identified by oracle2;

alter user C##DBV_OWNER identified by oracle

*

ERROR at line 1:

ORA-01031: insufficient privileges

 

C##DBV_MANAGER@cdb1> alter user C##DBV_MANAGER identified by oracle;

User altered.

 

C##DBV_MANAGER@cdb1> conn C##DBV_OWNER/oracle

Connected.

C##DBV_OWNER@cdb1> alter user C##DBV_MANAGER identified by oracle;

alter user C##DBV_MANAGER identified by oracle

*

ERROR at line 1:

ORA-01031: insufficient privileges

 

 

C##DBV_OWNER@cdb1> alter user C##DBV_OWNER identified by oracle;

User altered.

 

Incomodado com isso, você pode pensar: vou fazer um export, levar os dados para outro DB e lá faço o que eu quiser. Ok, vamos tentar.

[oracle@host ~]$ expdp system@pdb1 directory=MYDIR full=y DUMPFILE=full.dmp LOGFILE=expdp_full.log

 

Export: Release 12.1.0.2.0 – Production on Wed Mar 23 12:14:34 2016

 

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Password:

 

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, Automatic Storage Management, Oracle Label Security, OLAP,

Advanced Analytics, Oracle Database Vault and Real Application Testing options

Starting “SYSTEM”.”SYS_EXPORT_FULL_01″:  system/********@pdb1 directory=MYDIR full=y DUMPFILE=full.dmp LOGFILE=expdp_full.log

ORA-39327: Oracle Database Vault data is being stored unencrypted in dump file set.

Estimate in progress using BLOCKS method…

Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 305.2 MB

Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER

Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER

ORA-39126: Worker unexpected fatal error in KUPW$WORKER.FETCH_XML_OBJECTS [MARKER]

ORA-01031: insufficient privileges

 

ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95

ORA-06512: at “SYS.KUPW$WORKER”, line 11259

 

O ODV também impede expdp full do banco de dados, mas você pode fazer export de apenas um schema caso ele não esteja protegido por Realm (falaremos detalhadamente em um próximo artigo):

[oracle@host ~]$ expdp system@pdb1 directory=MYDIR schemas=SCOTT dumpfile=scott.dmp logfile=expdp_scott_01.log

 

Export: Release 12.1.0.2.0 – Production on Mon Mar 28 12:04:40 2016

 

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Password:

 

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, Automatic Storage Management, Oracle Label Security, OLAP,

Advanced Analytics, Oracle Database Vault and Real Application Testing options

Starting “SYSTEM”.”SYS_EXPORT_SCHEMA_01″:  system/********@pdb1 directory=MYDIR schemas=SCOTT DUMPFILE=scott.dmp LOGFILE=expdp_scott_01.log

ORA-39327: Oracle Database Vault data is being stored unencrypted in dump file set.

Estimate in progress using BLOCKS method…

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 192 KB

Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA

. . exported “SCOTT”.”DEPT”                              6.031 KB       4 rows

. . exported “SCOTT”.”EMP”                               8.781 KB      14 rows

. . exported “SCOTT”.”SALGRADE”                          5.960 KB       5 rows

. . exported “SCOTT”.”BONUS”                                 0 KB       0 rows

Master table “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:

/u01/dpump/scott.dmp

Job “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ completed with 1 error(s) at Mon Mar 28 12:05:11 2016 elapsed 0 00:00:29

Embora ele alerte que o dumpfile não está criptografado, o procedimento é concluído com sucesso. Basta realizar o impdp e temos o user em outro DB.

[oracle@prod ~]$ impdp system@prod1  directory=MYDIR schemas=SCOTT remap_schema=SCOTT:SCOTT_VAULT DUMPFILE=scott.dmp LOGFILE=impdp_scott_01.log

 

Import: Release 12.1.0.2.0 – Production on Mon Mar 28 12:12:30 2016

 

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Password:

 

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics

and Real Application Testing options

Master table “SYSTEM”.”SYS_IMPORT_SCHEMA_01″ successfully loaded/unloaded

Starting “SYSTEM”.”SYS_IMPORT_SCHEMA_01″:  system/******** directory=MYDIR schemas=SCOTT remap_schema=SCOTT:SCOTT_VAULT DUMPFILE=scott.dmp LOGFILE=impdp_scott_01.log

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported “SCOTT_VAULT”.”DEPT”                        6.031 KB       4 rows

. . imported “SCOTT_VAULT”.”EMP”                         8.781 KB      14 rows

. . imported “SCOTT_VAULT”.”SALGRADE”                    5.960 KB       5 rows

. . imported “SCOTT_VAULT”.”BONUS”                           0 KB       0 rows

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA

Job “SYSTEM”.”SYS_IMPORT_SCHEMA_01″ successfully completed at Mon Mar 28 12:12:59 2016 elapsed 0 00:00:22

Por sorte, nada mudou nos backups com RMAN. Você continua fazendo backup e restore/recover como sempre fez. A ideia é que a administração do banco de dados, do ponto de vista físico (datafiles, controlfile… ), continue exatamente igual e apenas tarefas que comprometam a segurança dos dados sejam protegidas.

Porém, nem tudo são flores. Há mudanças que interferem no dia a dia do DBA. Por exemplo:

– Alterar o db_recovery_file_dest,

SYS@cdb1> alter system set db_recovery_file_dest=’+DATA’;

alter system set db_recovery_file_dest=’+DATA’

*

ERROR at line 1:

ORA-01031: insufficient privileges

 

– Alterar o destino de criação de redo onlines,

SYS@cdb1> alter system set db_create_online_log_dest_1=’+DATA’;

alter system set db_create_online_log_dest_1=’+DATA’

*

ERROR at line 1:

ORA-01031: insufficient privileges

 

– Alterar o destino dos audit files já precisa de disposição,

SYS@cdb1> alter system set audit_file_dest=’/u01′ scope=spfile;

alter system set audit_file_dest=’/u01′ scope=spfile

*

ERROR at line 1:

ORA-01031: insufficient privileges

 

– Recyclebin então, nem pensar:

SYS@cdb1> alter system set recyclebin=on scope=spfile;

alter system set recyclebin=on scope=spfile

*

ERROR at line 1:

ORA-01031: insufficient privileges

 

Em [5] há detalhadamente as operações que sofreram alteração. A tabela abaixo foi retirada de [6] e é um resumo:

Administration Task Oracle Database Vault operational controls required?
GENERAL DATABASE ADMINISTRATION TASKS
Starting up and shutting down the database NO
Creating databases NO
Configuring database network connectivity NO
Database cloning NO
Managing database initialization parameters YES
Scheduling database jobs YES
ADMINISTERING DATABASE USERS
Managing users and roles YES
Creating and modifying database objects YES
DATABASE BACKUP AND RECOVERY
Oracle Data Pump YES
Oracle RMAN NO
Oracle SQL*Loader NO
Flashback YES
Managing database storage structures YES
DATABASE REPLICATION
Oracle Data Guard YES
Oracle Streams YES
DATABASE TUNING
DBMS_STATS PL/SQL Package NO
Modifying database instance memory NO
Automatic database diagnostic monitor (ADDM) NO
Active session history (ASH) NO
Automatic workload repository (AWR) NO
SQL Tuning Advisor NO
EXPLAIN PLAN YES
ANALYZE TABLE YES
Maintaining indexes YES
DATABASE PATCHING AND UPGRADE
Performing database patching YES
Performing software upgrade NO
Performing database upgrade YES
ORACLE ENTERPRISE MANAGER
Configuring Oracle Enterprise Manager settings NO
Adding administrators in Oracle Enterprise Manager YES

 

 

 

 

HABILITAR E DESABILITAR

Caso seja necessário desabilitar o ODV por algum motivo, ele só pode ser feito com users que possuam privilégios DV_OWNER e o DB precisa necessariamente ser reiniciado [7]. É de extrema importância esta observação, pois a perda da senha da conta C##DBV_ONWER (no caso deste artigo) e todos os outros que possuam DV_OWNER impede que o ODV seja desabilitado. Portando, considere guardar esta senha em local seguro e utilizar outros users com tais privilégios.

Os passos são os seguintes, com algum master user:

C##DONO@pdb1> SELECT VALUE FROM V$OPTION WHERE PARAMETER = ‘Oracle Database Vault’;

VALUE

————-

TRUE

C##DONO@pdb1> EXEC DBMS_MACADM.DISABLE_DV;

PL/SQL procedure successfully completed.

 

E em seguida, com o SYS, restart do DB.

SQL> conn sys/oracle@pdb1 as sysdba

Connected.

SYS@pdb1> shutdown immediate;

Pluggable Database closed.

SYS@pdb1> startup;

Pluggable Database opened.

SYS@pdb1> SELECT VALUE FROM V$OPTION WHERE PARAMETER = ‘Oracle Database Vault’;

VALUE

————

FALSE

 

O procedimento para habilitar, como já discutido, é análogo, porém utilizando o DBMS_MACADM.ENABLE_DV e também exige o restart.

SQL> conn C##DONO/oracle@pdb1

Connected.

C##DONO@pdb1> SELECT VALUE FROM V$OPTION WHERE PARAMETER = ‘Oracle Database Vault’;

VALUE

—————

FALSE

 

C##DONO@pdb1> EXEC DBMS_MACADM.ENABLE_DV;

PL/SQL procedure successfully completed.

 

SQL> conn sys/oracle@pdb1 as sysdba

Connected.

SYS@pdb1> shutdown immediate;

Pluggable Database closed.

SYS@pdb1> startup;

Pluggable Database opened.

SYS@pdb1> SELECT VALUE FROM V$OPTION WHERE PARAMETER = ‘Oracle Database Vault’;

VALUE

————-

TRUE

 

 

 

CONCLUSÃO

Vimos como realizar os primeiros passos com o ODV, como habilitá-lo e desabilitá-lo e algumas das principais mudanças provocadas no dia a dia do DBA. O próximo passo será descrever como aproveitaremos os pontos fortes que ele disponibiliza e então poderemos entender as configurações default e como podemos barrar privilégios mesmo tendo privilégios (caso do SYS que possui o privilégio de sistema ALTER ANY USER).

 

 

REFERENCIAS

[1] http://www.oracle.com/technetwork/pt/articles/idm/proteger-database-com-oracle-vault-2390952-ptb.html

[2] https://docs.oracle.com/database/121/DVADM/db_objects.htm

[3] https://docs.oracle.com/database/121/OLSAG/getting_started.htm

[4] http://www.dba-oracle.com/t_spare4.htm

[5] https://docs.oracle.com/database/121/DVADM/dba.htm

[6] http://www.oracle.com/technetwork/database/security/twp-databasevault-dba-bestpractices-199882.pdf

[7] https://docs.oracle.com/database/121/DVADM/dvdisabl.htm