Negar usuários a usar GRANT – GPO (Grupo de Profissionais Oracle)

12 de agosto de 2017 às 2:39 am #108918

José Laurindo Chiappa

Moderador

Bom, começando pelo RAISE : *** entenda *** que como eu disse o RAISE é uma EXCEÇÃO, com ele eu mando ABORTAR processamento normal da rotina – ele Não É só “uma mensagem que vai ser exibida”, sim sim sim ??? Meio Óbvio que se vc força o RDBMS a sinalizar FALHA, a ABORTAR o último comando, os comandos SEGUINTES da transação não vão ser executados

SYS:AS SYSDBA@XE:SQL>create or replace trigger gr_trigger
2 after grant or revoke
3 on database
4 declare
5 priv dbms_standard.ora_name_list_t;
6 who dbms_standard.ora_name_list_t;
7 npriv pls_integer;
8 nwho pls_integer;
9 BEGIN
10 npriv := ora_privilege_list(priv);
11 nwho := ora_grantee(who);
12 if (ora_sysevent = ‘GRANT’) and user <> ‘SYS’ then
13 RAISE_APPLICATION_ERROR(-20001, ‘GRANTs são Proibidos!!!’);
14 end if;
15 for i in 1..npriv
16 loop
17 for j in 1..nwho
18 loop
19 insert into log values
20 (
21 systimestamp,
22 ora_sysevent,
23 priv(i),
24 ora_dict_obj_owner,
25 ora_dict_obj_name,
26 who(j),
27 ora_login_user,
28 (NVL(ora_client_ip_address, ‘N/A’))
29 );
30 end loop;
31 end loop;
32 END;
33 /

Gatilho criado.

==> Faço um GRANT pelo SYS, a EXCEÇÃO não é sinalizada, portanto o fluxo do programa CHEGA SIM até a linha mais lá embaixo onde está o INSERT :

SYS:AS SYSDBA@XE:SQL>grant select on HR.EMPLOYEES to scott;

Concessão bem-sucedida.

SYS:AS SYSDBA@XE:SQL>select * from LOG;

ACTION_DATE PVS_NAME
————————————————————————— ———————————
PRIVILEGE
———————————————————————————————————————————
OWNER OBJECT_NAME USERNAME LOGIN_USER
—————- —————————— —————- ———————————
IP_ADDRESS
———————————
11/08/17 18:30:52,180000 GRANT
SELECT
HR EMPLOYEES SCOTT SYS
N/A

SYS:AS SYSDBA@XE:SQL>

==> veja abaixo que se executo um GRANT que cai nas condições do IF, logicamente a EXCEÇÃO lá da linha 13 (no meu exemplo) é levantada, ULULANTEMENTE ÓBVIO que a rotina abortou então o processamento Não Chegou até a linha 19 que faz o INSERT :

SCOTT:@XE:SQL>grant select on TTEST to HR;
grant select on TTEST to HR
*
ERRO na linha 1:
ORA-00604: ocorreu um erro no nível 1 SQL recursivo
ORA-20001: GRANTs são Proibidos!!!
ORA-06512: em line 10

SCOTT:@XE:SQL>

==> nada foi registrado, APENAS a linha do GRANT feito pelo SYS permanece, demonstrando que o RAISE da exceção ABORTOU o processamento e portanto não chegou até a linha do INSERT na tabela de LOG :

SYS:AS SYSDBA@XE:SQL>select * from LOG;

==> Outra coisa : normalmente vc quer que as gravações na tabela de AUDITORIA seja feito INDEPENDENTE da pessoa commitar ou não a Transação que disparou a Auditoria, sim sim sim ??? Do jeito que tá programado, o INSERT na trigger fica Pendente, se o sujeito fizer um ROLLBACK (ou a sessão desconectar) o INSERT na tabela de LOG não se efetiva… O procedimento para isto é definir a rotina de Auditoria como uma AUTONOMOUS TRANSACTION , veja a Doc oracle para mais detalhes… Demonstração :

==> não foi feito nenhum grant ainda pra tabela DEPT, que vai ser o meu teste :

SYS:AS SYSDBA@XE:SQL>truncate table LOG;

Tabela truncada.

SYS:AS SYSDBA@XE:SQL>ed
Gravou file afiedt.buf

1 create or replace trigger gr_trigger
2 after grant or revoke
3 on database
4 declare
5 priv dbms_standard.ora_name_list_t;
6 who dbms_standard.ora_name_list_t;
7 npriv pls_integer;
8 nwho pls_integer;
9 pragma autonomous_transaction;
10 BEGIN
11 npriv := ora_privilege_list(priv);
12 nwho := ora_grantee(who);
13 for i in 1..npriv
14 loop
15 for j in 1..nwho
16 loop
17 insert into log values
18 (
19 systimestamp,
20 ora_sysevent,
21 priv(i),
22 ora_dict_obj_owner,
23 ora_dict_obj_name,
24 who(j),
25 ora_login_user,
26 (NVL(ora_client_ip_address, ‘N/A’))
27 );
28 commit;
29 end loop;
30 end loop;
31 —
32 if (ora_sysevent = ‘GRANT’) and user <> ‘SYS’ then
33 RAISE_APPLICATION_ERROR(-20001, ‘GRANTs são Proibidos!!!’);
34 end if;
35* END;
SYS:AS SYSDBA@XE:SQL>/

Gatilho criado.

==> tento fazer um GRANT fora do schema SYS :

SCOTT:@XE:SQL>grant select on DEPT to HR;
grant select on DEPT to HR
*
ERRO na linha 1:
ORA-00604: ocorreu um erro no nível 1 SQL recursivo
ORA-20001: GRANTs são Proibidos!!!
ORA-06512: em line 30

SCOTT:@XE:SQL>

==> Olha a gravação :

SYS:AS SYSDBA@XE:SQL>select * from log;

ACTION_DATE PVS_NAME PRIVILEGE
————————————————————————— ——————————— ——————–
OWNER OBJECT_NAME USERNAME LOGIN_USER IP_ADDRESS
—————- —————————— —————- ——————————— ———————————
11/08/17 19:10:05,917000 GRANT SELECT
SCOTT DEPT HR SCOTT N/A

SYS:AS SYSDBA@XE:SQL>

==> E O MAIS IMPORTANTE, veja que a exceção levantada ** NÃO DEIXOU O GRANT acontecer ** (repito, ela Não É Só uma “mensagem”!!!) :

SYS:AS SYSDBA@XE:SQL>select * from DBA_TAB_PRIVS where table_name=’DEPT’;

não há linhas selecionadas

SYS:AS SYSDBA@XE:SQL>

==> Isto é crucial : não só vc quer um INSERT na tabela de Audit mas QUER que o DDL seja Abortado…

==> Ainda que TODAS as sessões envolvidas peçam ROLLBACK, o AUTONOMOUS TRANSACTION me Garantiu que o INSERT na tabela de Audit foi tratado INDEPENDENTEMENTE :

SCOTT:@XE:SQL>rollback;

Rollback concluído.

SCOTT:@XE:SQL>
SYS:AS SYSDBA@XE:SQL>rollback;

Rollback concluído.

==> Olha aí o resultado persistido :

SYS:AS SYSDBA@XE:SQL>select * from log;

SYS:AS SYSDBA@XE:SQL>

==> Sim sim ??? E se eu fizer o GRANT diretamente pelo SYS , veja que é Logado também E o GRANT em si é SIM efetuado :

SYS:AS SYSDBA@XE:SQL>grant select on SCOTT.DEPT to HR;

Concessão bem-sucedida.

SYS:AS SYSDBA@XE:SQL>select * from log;

11/08/17 19:11:59,801000 GRANT SELECT
SCOTT DEPT HR SYS N/A

SYS:AS SYSDBA@XE:SQL>

SYS:AS SYSDBA@XE:SQL>rollback;

Rollback concluído.

==> A gravação PERSISTIU graças ao Autonomous Transaction :

SYS:AS SYSDBA@XE:SQL>select * from log;

11/08/17 19:11:59,801000 GRANT SELECT
SCOTT DEPT HR SYS N/A

==> E FINALMENTE : veja que o GRANT foi sim feito :

SYS:AS SYSDBA@XE:SQL>select * from DBA_TAB_PRIVS where table_name=’DEPT’;

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
—————- —————- —————————— —————- ——————– — —
HR SCOTT DEPT SCOTT SELECT NO NO

SYS:AS SYSDBA@XE:SQL>

==> okdoc ???

Já sobre a questão de erro quando vc está “criando usuario”, pra mim não ocorreu :

SYS:AS SYSDBA@XE:SQL>create user ZE identified by mane;

Usuário criado.

SYS:AS SYSDBA@XE:SQL>

==> então é a sua Lição de Casa : fazer o DEBUG dessa rotina, ok ? Provavelmente meter uns DBMS_OUTPUT dando mensagens pra vc ver onde o fluxo do programa tá indo, talvez…

Essa é UMA das Alegrias que vc obtém quando teu banco é casa da mãe joana e vc tem que ficar GAMBIARRANDO com triggers : a trigger TEM que prever trocentas situações possíveis…. Tá por sua conta aí, yes ???

[]s

Chiappa