Um exemplo demonstrando o que eu disse : primeiro, crio o usuário SISTEMA,com os privilégios mínimos :
SYSTEM@O10GR2:SQL>create user SISTEMA identified by sistema; Usußrio criado. SYSTEM@O10GR2:SQL>grant create table to sistema; ConcessÒo bem-sucedida. SYSTEM@O10GR2:SQL>grant create trigger to sistema; ConcessÒo bem-sucedida. SYSTEM@O10GR2:SQL>grant create session to SISTEMA; ConcessÒo bem-sucedida. => ÓBVIO, num caso real seria uma tablespace específica, e provavelmente SEM quota ilimitada.... SYSTEM@O10GR2:SQL>alter user sistema quota unlimited on users; Usußrio alterado. SYSTEM@O10GR2:SQL>grant create role to SISTEMA; ConcessÒo bem-sucedida. ==> okdoc, vamos criar o schema de LOGs : SYSTEM@O10GR2:SQL>create user LOG_SCHEMA identified by LOG_SCHEMA; Usußrio criado. => novamente, a tablespace USERS aqui é só para o teste... SYSTEM@O10GR2:SQL>alter user LOG_SCHEMA quota unlimited on USERS; Usußrio alterado. => crio as tabelas de logs... SYSTEM@O10GR2:SQL>create table LOG_SCHEMA.LOG_INS_TAB_TESTE(username varchar2(40), data_insert date); Tabela criada. => e o usuário SISTEMA recebe o GRANT de INSERT nelas... SYSTEM@O10GR2:SQL>grant insert on LOG_SCHEMA.LOG_INS_TAB_TESTE to SISTEMA; ConcessÒo bem-sucedida. ===>> Tudo pronto, vamos criar as tabelas e os triggers de log no usuário SISTEMA : SYSTEM@O10GR2:SQL>conn sistema/sistema Conectado. SISTEMA@O10GR2:SQL>create table TAB_TESTE(c1 number, c2 varchar2(80)); Tabela criada. SISTEMA@O10GR2:SQL>create or replace trigger TRG_INS_TAB_TESTE after insert on TAB_TESTE 2 BEGIN 3 insert into LOG_SCHEMA.LOG_INS_TAB_TESTE values(user, sysdate); 4 END; 5 / Gatilho criado. => okdoc, agora o usuário SISTEMA vai dar via ROLEs os privilégios para os usuários-finais , que no meu caso vai ser o SCOTT : SISTEMA@O10GR2:SQL>create role ROLE_INSERT ; AtribuiþÒo criada. SISTEMA@O10GR2:SQL>grant INSERT on TAB_TESTE to ROLE_INSERT; ConcessÒo bem-sucedida. SISTEMA@O10GR2:SQL>grant ROLE_INSERT to scott; ConcessÒo bem-sucedida. ==> Muito bem, o usuário final faz o INSERT dele : SISTEMA@O10GR2:SQL>conn scott/tiger Conectado. SessÒo alterada. SCOTT@O10GR2:SQL>insert into SISTEMA.tab_teste values(1, 'Linha 1'); 1 linha criada. SCOTT@O10GR2:SQL>commit; Commit concluÝdo. ==> veja que o trigger DISPAROU, mesmo o usuário final Não Tendo recebido nenhum privilégio especial , como eu disse : SCOTT@O10GR2:SQL>conn system/oracle Conectado. SessÒo alterada. SYSTEM@O10GR2:SQL>select * from log_schema.log_ins_tab_teste; USERNAME DATA_INSERT ---------------- ------------------- SCOTT 06/09/2013 19:34:31 SYSTEM@O10GR2:SQL> []s Chiappa IMPORTANTE : reitero NOVAMENTE, apesar de funcionar use JUDICIOSAMENTE as triggers, dando *** TOTAL *** preferência às built-ins de Audit e LOgging, sob risco de overhead ** SEVERO ** nas transações de maior porte, okdoc ?? --- Em oracle_br@yahoogrupos.com.br, "J. Laurindo Chiappa" <jlchiappa@...> escreveu > > Fabiano, seguinte : > > 1. com certeza, TRIGGERS de DML (que respondem a INSERTs/UPDATEs/DELETEs) > podem facilmente causar Sérios problemas de performance, EM ESPECIAL se > dispararem para cada linha, como é o caso se vc quer/precisa auditar os > valores exatos que foram inseridos/atualizados/deletados - isso > principalmente devido ao fato deles, em disparando uma vez para cada linha, > Impedem na prática processamento BULK, forçando row-by-row processing.... > Assim, de cara o que eu digo é : ** ESTUDE ** as built-ins de Auditoria e > Logging (ie, comando AUDIT, DBMS_FGA, LOG MINER, views materializadas, query > monitoring se vc estiver no 11g, etc) para ver se ao menos parcialmente vc > não consegue Evitar o uso de triggers... Triggers quanto menos vc usar. > melhor... > > 2. outra opção ** EXCELENTE ** ,nem sempre possível (por exemplo, nos casos > em que os dados TEM que ser acessados por tools de reporting ou BI, que > normalmente só executam SQLs) seria vc ENCAPSULAR os SQLs todos (por > transação - NADA de TABLE APIs aqui !) em rotinas normalmente feitas em > PL/SQL) no schema SISTEMA e aí os usuários demais receberia GRANTs de EXECUTE > nos PL/SQLs apropriados ao invés de GRANTS de SELECT/INSERT/UPDATE/DELETE.... > Se vc tivesse desenvolvido assim, seria tranquilo adicionar código extra > nas APIs para fazer os logs/auditorias necessários, imagino que nâo foi esse > o caso pelo que vc descreve... > > 3. para os (raríssimos, via de regra) casos aonde nenhuma opção de > auditoria/logging te ajude e vc Realmente ter que ir para triggers, o que vc > escreveu não faz sentido : a tabela pertencendo ao schema SISTEMA, vc criaria > o trigger no próprio schema SISTEMA e NATURALMENTE, quem receber (via ROLE ou > não, tanto faz) o privilégio de INSERT, UPDATE ou DELETE na tabela *** > AUTOMATICAMENTE *** o trigger de insert/update/delete criado na tabela já vai > disparar, sim ??? > Pelo que eu entendi, vc ** PENSAVA ** que o usuário que fez o INSERT é que > teria que ter algum grant extra por causa do trigger, o que Absolutamente não > ocorre : quem recebeu um GRANT de INSERT, UPDATE ou DELETE, quando executar o > INSERT/UPDATE/DELETE os eventuais TRIGGERS de DML vão naturalmente disparar, > eles são "complementos" do comando de INSERT/UPDATE/DELETE, que o usuário já > recebeu.... > Assim, supondo que vc vá criar a trigger no schema que possui as tabelas > (o SISTEMA), para que a trigger dispare quando qquer usuário privilegiado > faça o INSERT/UPDATE/DELETE ** nada ** se precisa fazer... > > => O outro ponto a não se esquecer, CLARO, é que vc quer que a trigger que > pertence ao schema SISTEMA faça inserts nas tabelas de log do schema LOG, que > não lhe pertencem, então o LOG é que tem que dar GRANTs de INSERT nas > tabelas dele para o SISTEMA.... E aqui SIM, esses GRANTs preferencialmente > devem ser dados Diretamente, sem ROLEs.... > > []s > > Chiappa > > --- Em oracle_br@yahoogrupos.com.br, Fabiano Picolotto <fabianofpb@> escreveu > > > > Bom dia Pessoal. > > > > Tenho a seguinte situação. > > Um banco de dados com cerca de 200 usuário, mas somente um Schema tem dados > > (Schema *SISTEMA*) os outros 199 usuários tem acesso a uma ROLE e essa ROLE > > tem acesso as tabelas, view etc do Schema *SISTEMA*. > > > > > > Porem estou vendo para criar um outro Schema armazenar LOGs (Schema *LOG*) > > > > Então teria dois schemas que teriam objetos > > Schema *SISTEMA *e Schema *LOG* > > > > Para gravar os LOGs pensei em criar um trigger em cada tabela do > > Schema *SISTEMA > > *gravando em uma tabela do Schema *LOG,* mas para fazer isso não consigo > > com os acesso via ROLE, teria que conceder acesso a todos os 199 usuários > > para todos os objetos do Schema SISTEMA diretamente, não utilizando ROLE. > > > > Perguntas > > 1º Isso seria a melhor solução? Alguém tem alguma dica? > > 2º Isso pode comprometer o desempenho, já que teria muitos mais registros > > de privilégios para o banco de dados consultar? > > > > Oracle 11gR2 > > > > Obs.: se não fui claro, me avisem que tento explicar de outra maneira. > > Obrigado. > > > > -- > > Fabiano P. > > Fone: (46) 9113-6731 > > E-Mail: fabianofpb@ > > Skype: fabianofpb > > >
