Menu de navegação

[Alison Fernando Duarte FedericoParticipante]

Pessoal, tudo bom?

Sou novo aqui no forum e vim em busca de tirar uma dúvida.

Eu administro um banco de dados de alunos, onde existem vários e vários usuários, em torno de 4 mil.
E tenho problemas com provas, onde usuários dão permissões em suas tabelas para que outros usuários vejam ela, executem no caso Selects.
Eu poderia barrar os usuários de usarem GRANTs em suas tabelas, para que outros vejam?

E se possível também, idéias novas, como TRIGGERs, onde eu faria auditorias, de usuários tentando fazer algo indevido no banco.

Algo que testei e está barrando, é usuários trocar senhas de outros usuários, isso felizmente está funcionando rsrs..

Enfim, se puderem me ajudar nessa questão ficaria muito grato!! 🙂
Grande abraçoo!!

[José Laurindo ChiappaModerador]

Blz, tudo na tranquila ? Comigo sim…

Então, o primeiro ponto é que em quase TODOS os RDBMSs do mercado (mas principalmente no Oracle!!) o conceito é que quem cria uma tabela é o DONO DELA, e Naturalmente eu como o Dono tenho o direito de fazer o que BEM QUISER, sim ??/ Isso inclui ALÉM de dar privilégios a outros (natural, se eu sou o dono de um objeto porque não posso emprestar/doar/fazer o que quiser com ele ??) também alterar estruturas, manipular os dados da tabela que me pertence do JEITO QUE EU QUISER, drpar/jora fora/DESTRUIR COMPLETAMENTE a tabela no instante que me der na telha… Ela é minha pra fazer o que eu Bem Entender, certo ???
Por Causa desse Conceito, normalmente o que se faz num ambiente de Produção adequado e corretamente montado é criar para cada Aplicação um usuário DONO DA APLICAÇÃO e depois fazer com que Esse usuário (cuja senha é quase Tão Secreta e protegida quanto a senha do DBA!!) crie as tabelas necessários e dê os privilégios mínimos de SELECT, INSERT, UPDATE, o que for, para Todos os demais usuários, o que (para evitar que seja necessário repetir os comandos de GRANT uma vez pra cada um dos muitos outros usuários) se faz via ROLEs… ESSE é o cenário Justo, Correto, Simples de administrar, que faz Sentido e que é LÓGICO, certo certo ???

SE por qualquer Maluquice doida a sua Empresa é a casa da mãe Joana onde Qualquer Um cria tabelas (E portanto é o Dono : quem cria é DONO!!), vc tem QUEBRA-GALHOS e ADAPTAÇÕES sim que vc como DBA pode fazer , mas SAIBA que vc está tendo um trabalho DOIDO e SEM SENTIDO, está trabalhando CONTRA os conceitos fundamentais e best practices de utilização de RDBMS…
A principal técnica para o cenário maluco e absurdo que vc descreve seria as TRIGGERs de DDL : é um Recurso onde vc Captura o comando de DDL e toma alguma Ação… Normalmente isso é usado para algum tipo de Auditoria (ie, registrar numa tabela/num arquivo-texto quem fez um comando de DDL, seja CREATE, GRANT, REVOKE, DROP ou qual for – veja https://dba.stackexchange.com/questions/29254/is-it-possible-to-monitor-role-and-privilege-grant-revoke-using-triggers para um Exemplo) mas nada impede que vc implemente uma EXCEÇÃO abortando a operação (via comando RAISE_APPLICATION_ERROR) se o comando capturado por um GRANT….

[]s

Chiappa

OBS : nem preciso dizer que a Segurança deste remendão depende do fato de vc ter a trigger criada a nível de database ** E ** na conta de um usuário SYSDBA, o qual só vc, o DBA, tem acesso – SE a sua Empresa aí é a casa da mãe Joana ELEVADA AO QUADRADO e mais de uma pessoa tem a senha do SYSDBA, ou se outros usuários de banco afora vc que é o DBA tem contas com privilégio de SYSDBA aí BABOU, FERROU, a porca torceu o rabo, a vaca foi pro brejo, já que alguém pode ir lá e desabilitar a trigger….

[Alison Fernando Duarte FedericoParticipante]

Show de bola, melhor explicação que essa impossível kkkkkk

Resumindo a coisa aqui seria a casa da mãe joana mas por um motivo simples, não é um ambiente crítico, e sim um sistema de ensino, com isso, não existe esse controle, porem ninguém tem a senha do DBA e muito menos tem permissões administrativas.
Com isso seria bacana fazer essa trigger barrando os GRANTs…
Vou tentar esse processo, e qualquer coisa comunico aqui =]

[José Laurindo ChiappaModerador]

Então : um sistema de ensino que seja, SE os dados são importantes a ponto de merecerem serem controlados num RDBMS Oracle, ENTÃO vale a pena usar da maneira Certa, não é não ?? Pense nisso : sei que é uma batalha complexa vc mudar uma política interna que deixa qualquer um fazer o que quiser a hora que quiser como quiser (entre outras coisas entra o conceito de DEVOPS, que é uma idéia tipo “os desenvs/usuários fazem o que quiserem e depois o DBA se vira pra limpar o lixo”) mas é algo que vale DEMAIS a pena, se Estabilidade, Segurança e Controle na Administração desse database é Importante…

Nesse meio tempo, a programação exigida para fazer o Remendão sugerido é bem básica – segue um exemplo adaptado DIRETAMENTE do link que te passei, cortando fora o que não precisa (as partes de Auditoria) e dando permissão só pro usuário SYS (que é quem tem perms de SYSDBA aqui no meu banco) de fazer GRANTs :

SYS:AS SYSDBA@XE:SQL>create or replace trigger trigger1 after grant or revoke on database
2 BEGIN
3 if (ora_sysevent = ‘GRANT’) and user ‘SYS’ then
4 RAISE_APPLICATION_ERROR(-20001, ‘GRANTs são Proibidos!!!’);
5 end if;
6 END;
7 /

Gatilho criado.

SYS:AS SYSDBA@XE:SQL>

==> Agora um outro usuário (o HR no meu exemplo) quer fazer um GRANT, vai ser barrado :

HR:@XE:SQL>grant select on employees to scott;
grant select on employees to scott
*
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 3

HR:@XE:SQL>

==> agora o usuário SYS vai tentar fazer um GRANT – no caso o mesmo GRANT que o HR foi proibido de fazer, mas não importa :

SYS:AS SYSDBA@XE:SQL>grant SELECT on HR.employees to scott;

Concessão bem-sucedida.

SYS:AS SYSDBA@XE:SQL>

Blz ??

[]s

Chiappa

[Alison Fernando Duarte FedericoParticipante]

Poxa meu amigo agradeço muito pela ajuda.

Estou tentando criar dessa maneira:

create table log (
Action_DaTe date
, Pvs_name varchar2(15)
, PRIVILEGE varchar2(30)
, OWNER varchar(20)
, OBJECT_NAME varchar2(30)
, Username varchar2(30)
, login_user varchar2(20)
, IP_address varchar2(15)
);

create or replace trigger gr_trigger after grant or revoke on database
declare
priv dbms_standard.ora_name_list_t;
who dbms_standard.ora_name_list_t;
npriv pls_integer;
nwho pls_integer;
begin
npriv := ora_privilege_list(priv);
if (ora_sysevent = ‘GRANT’) and user ‘SYS’ then
nwho := ora_grantee(who);
else
nwho := ora_revokee(who);
end if;
for i in 1..npriv
loop
for j in 1..nwho
loop
insert into log values
(
systimestamp,
ora_sysevent,
priv(i),
ora_dict_obj_owner,
ora_dict_obj_name,
who(j),
ora_login_user,
(NVL(ora_client_ip_address, ‘N/A’))
);
end loop;
end loop;
end;
/

Ela funciona, porem eu não consigo criar usuários usando a conta SYS rsrsrs
Ela me apresenta esse erro quando tento criar um usuário:
ERROR at line 1:
ORA-00604: ocorreu um erro no nφvel 2 SQL recursivo
ORA-06502: PL/SQL: erro numΘrico ou de valor
ORA-06512: em line 13

Quando dropo a trigger eu consigo criar o usuário normalmente.

Teria ideia de qual poderia ser o problema? rsrs

[José Laurindo ChiappaModerador]

A msg de erro é clara :

ORA-06502: PL/SQL: erro numΘrico ou de valor

vc tem alguma variável e/ou coluna de tabela ** menor ** do que os dados que estão sendo introduzidos nelas… De cara eu questiono essa tua tabela LOG, pois OWNER (pelo que entendi sem estudar muito a lógica do exemplo) é o nome do Dono do Objeto e isso tá documentado (veja os manuais Oracle referentes à DBA_TABLES e DBA_OBJECTS) ser de 30 caracteres, não faz sentido essa tua definição “,OWNER varchar(20)” … De forma similar, PVS_NAME pelo que entendi vai receber o privilégio que estava válido pra pessoa fazer o DDL, e isso pode ser (entre OUTRAS coisas!!) o nome de uma role, que é MAIOR que esses 15 caracteres que vc definiu em “,Pvs_name varchar2(15)”….
Ou seja : primeira coisa, AUMENTA SIGNIFICATIVAMENTE as colunas aí da tua tabelinha, blz ???

[]s

Chiappa

[Alison Fernando Duarte FedericoParticipante]

Coloquei 2 mil já no varchar2 e nada kkkkk
Estranho ele dar esse erro, estou criando usuario rsrs, so se internamente ele da algum GRANT na hora de criar o usuario….
E outra coisa que estou tentando é fazer ele gravar na tabela LOG e também mostrar um RAISE:

create or replace trigger gr_trigger
after grant or revoke
on database
declare
priv dbms_standard.ora_name_list_t;
who dbms_standard.ora_name_list_t;
npriv pls_integer;
nwho pls_integer;
BEGIN
npriv := ora_privilege_list(priv);
nwho := ora_grantee(who);
if (ora_sysevent = ‘GRANT’) and user ‘SYS’ then
RAISE_APPLICATION_ERROR(-20001, ‘GRANTs são Proibidos!!!’);
end if;
for i in 1..npriv
loop
for j in 1..nwho
loop
insert into log values
(
systimestamp,
ora_sysevent,
priv(i),
ora_dict_obj_owner,
ora_dict_obj_name,
who(j),
ora_login_user,
(NVL(ora_client_ip_address, ‘N/A’))
);
end loop;
end loop;
END;
/

Infelizmente não está funcionando, ele da a mensagem de erro mas não grava na tabela LOG:

ORA-00604: ocorreu um erro no nφvel 1 SQL recursivo
ORA-20001: GRANTs sao Proibidos!!!
ORA-06512: em line 10

[José Laurindo ChiappaModerador]

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;

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

==> 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;

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>

==> 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;

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

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;

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

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

[José Laurindo ChiappaModerador]

Dica Adicional : quando vc é obrigado a ficar Customizando, a aplicar ** remendões ** no seu database porque neguim não está seguindo as práticas de uso Recomendadas e Recomendáveis, vc está usando coisas que nem sempre são tão testadas quanto as funcionalidades primárias, são coisas “secundárias”, digamos assim – aí é Óbvio que vc está sujeito a encontrar bugs e/ou comportamentos inesperados….
Para o seu caso de trigger de DDL que usa as funções de auditoria interna reportando arrays de privilégios via ora_grantee, ora_revokee e etc, se vc tiver acesso (de consulta que seja) ao Suporte Oracle (mínimo que se pede para poder Administrar um banco corretamente/seguramente) dê uma olhada no documento de Suporte Oracle que registra Bug 25852917 : ORA_PRIVILEGE_LIST MUST RETURN 1 INSTEAD OF NULL IN CREATE COMMAND : ele mostra um caso onde o REVOKE (por não preencher todas as colunas do array, já que ao contrário do GRANT não é só o dono ou o SYSDBA que o pode fazer) retorna NULL pra algumas posições, aí as variáveis de limite dos laços FOR da trigger (como a nwho e a npriv das linhas “for i in 1..npriv” e “for j in 1..nwho” ) ficam com NULL, e um loop de 1 a NULL dá erro de valor, já que NULL não é igual nem maior nem menor que um valor numérico….
Veja lá se teu erro na verdade não é algo nesse sentido, manda um debug logo depois das linhas que vc passa valor pras variáveis, tenta meter uns NVLs ou IFs adicionais… Quando eu disse que a programação em si é bem básica MAS que vc tem que prever muitas situações adversas tava pensando em coisas do tipo.. É por isso que eu pessoalmente ABOMINO e considero remendos horrorosos coisas do tipo triggers de DDL altamente customizadas, mas enfim….

[]s

Chiappa

[Autor]

Posts