Blog
Oracle Virtual Private Database – VPD
O Virtual Private Database, mais conhecido como VPD, é um recurso do Oracle Database Enterprise Edition (EE) existente desde a versão 8i que permite criar políticas de segurança sobre objetos, sejam eles tabelas, views ou sinônimos.
SQL> create table funcionario( nome varchar2(50), salario number(10,2) ); Table created. SQL> insert into funcionario values ('FUNC1',1000.00); 1 row created. SQL> insert into funcionario values ('FUNC2',1000.00); 1 row created. SQL> insert into funcionario values ('FUNC3',2000.00); 1 row created. SQL> insert into funcionario values ('FUNC4',5000.00); 1 row created. SQL> insert into funcionario values ('FUNC5',10000.50); 1 row created. SQL> commit; Commit complete.
Criando a tabela DENY_ACCESS_FUN onde será cadastrado os usuários que serão afetados(limitados) pela policy.
SQL> create table deny_access_fun( nome varchar2(50) ); Table created.
Criando a tabela BLOCKED_ACCESS_FUN onde será cadastrado o nome dos registros da tabela FUNCIONARIO que não podem ser visualizados pelos usuários cadastrados na tabela DENY_ACCESS_FUN.
SQL> create table blocked_access_fun( nome varchar2(50) ); Table created.
Criando a FUNÇÃO que será chamada pela policy e que irá retornar a condição (PREDICATE) para a cláusula WHERE, ou seja, quando um usuário cadastrado na tabela DENY_ACCESS_FUN realizar um select na tabela FUNCIONARIO a policy vai atribuir na condição WHERE a linha abaixo:
nome not in (select nome from blocked_access_fun);
Se o usuário não está cadastrado na DENY_ACCESS_FUN ela retorna: 1=1 o que não afetará qualquer registro.
SQL> CREATE OR REPLACE FUNCTION VPD_DENY_ACCESS_FUN (schema in varchar2, table_p in varchar2) return varchar2 as w_exists number :=0; predicate varchar2(50) default null; BEGIN select max(vlr) into w_exists from (select 0 vlr from dual union select 1 vlr from deny_access_fun where nome = UPPER(SYS_CONTEXT('USERENV', 'SESSION_USER'))); IF w_exists = 1 THEN predicate := 'nome not in (select nome from blocked_access_fun)'; ELSE predicate := '1=1'; END IF; RETURN predicate; END; / Function created.
Observe que na FUNCTION utiliza a SYS_CONTEXT para coletar informações da sessão, no caso o usuário conectado (SESSION_USER).
- Ajustando permissões:
SQL> GRANT EXECUTE ON DBMS_RLS TO PUBLIC; Grant succeeded.
- Criando a policy:
SQL> BEGIN DBMS_RLS.ADD_POLICY ( object_schema => 'ANDERSON', object_name => 'FUNCIONARIO', policy_name => 'VPD_DENY_ACCESS_FUN_RH', function_schema => 'ANDERSON', policy_function => 'VPD_DENY_ACCESS_FUN', statement_types => 'select'); END; / PL/SQL procedure successfully completed.
Na statement_types foi colocado apenas SELECT, desta forma a policy só é executada quando feito um select na tabela FUNCIONARIO.
Criando 2 usuários para testes:
SQL> create user teste1 identified by andersonteste1; User created. SQL> create user teste2 identified by andersonteste2; User created. SQL> grant create session to teste1, teste2; Grant succeeded. SQL> grant select on anderson.funcionario to teste1, teste2; Grant succeeded.
Conectando com os usuários criados todos os registros ainda são acessíveis pois nada foi cadastrado nas tabelas DENY_ACCESS_FUN e BLOCKED_ACCESS_FUN:
SQL> conn teste1/andersonteste1 Connected. SQL> select * from anderson.funcionario; NOME SALARIO -------------------------------------------------- ---------- FUNC1 1000 FUNC2 1000 FUNC3 2000 FUNC4 5000 FUNC5 10000.5 SQL> conn teste2/andersonteste2 Connected. SQL> select * from anderson.funcionario; NOME SALARIO -------------------------------------------------- ---------- FUNC1 1000 FUNC2 1000 FUNC3 2000 FUNC4 5000 FUNC5 10000.5
Em outra sessão (com permissão de insert nas tabelas de controle), será bloqueado o usuário TESTE1 para não visualizar os registros do funcionário FUNC3:
SQL> insert into deny_access_fun values ('TESTE1'); 1 row created. SQL> insert into blocked_access_fun values ('FUNC3'); 1 row created. SQL> commit; Commit complete. SQL> conn teste1/andersonteste1 Connected. SQL> select * from anderson.funcionario; NOME SALARIO -------------------------------------------------- ---------- FUNC1 1000 FUNC2 1000 FUNC4 5000 FUNC5 10000.5
Observe acima que o FUNC3 já não foi mais retornado para o usuário TESTE1 enquanto para o usuário TESTE2 ainda é apresentado:
SQL> conn teste2/andersonteste2 Connected. SQL> select * from anderson.funcionario; NOME SALARIO -------------------------------------------------- ---------- FUNC1 1000 FUNC2 1000 FUNC3 2000 FUNC4 5000 FUNC5 10000.5
Cadastrando mais uma linha(nome) da tabela FUNCIONARIO que não deve ser visualiza pelo usuário TESTE1:
SQL> conn anderson Enter password: Connected. SQL> insert into blocked_access_fun values ('FUNC5'); 1 row created. SQL> commit; Commit complete. SQL> conn teste1/andersonteste1 Connected. SQL> select * from anderson.funcionario; NOME SALARIO -------------------------------------------------- ---------- FUNC1 1000 FUNC2 1000 FUNC4 5000 SQL> conn teste2/andersonteste2 Connected. SQL> select * from anderson.funcionario; NOME SALARIO -------------------------------------------------- ---------- FUNC1 1000 FUNC2 1000 FUNC3 2000 FUNC4 5000 FUNC5 10000.5
Agora cadastrando também o usuário TESTE2 na tabela DENY_ACCESS_FUN que passa a não conseguir ver os outros registros.
SQL> conn anderson Enter password: Connected. SQL> insert into deny_access_fun values ('TESTE2'); 1 row created. SQL> commit; Commit complete. SQL> conn teste2/andersonteste2 Connected. SQL> select * from anderson.funcionario; NOME SALARIO -------------------------------------------------- ---------- FUNC1 1000 FUNC2 1000 FUNC4 5000
Como falado no inicio do artigo este foi apenas um exemplo simples de implementação com VPD limitando o SELECT a determinados USUÁRIOS X REGISTROS.
A VPD pode ser implementada de muitas outras formas e pode ser muito mais abrangente e complexa. Vários materiais sobre o tema estão disponíveis também no Oracle Help Center (Docs).
229 visualizações
