Menu de navegação

[Anderson de Abreu SilvaParticipante]

Boa tarde!

Pessoal,

Gostaria de apos um insert em uma tabela USER(USER, PASS) uma trigger criasse este usuario e
com outra tabela GRANTS(USER, GRANT) outra trigger desse as permissões, alguem poderia me ajudar?

Grato,

Anderson Silva

[Regis AraujoParticipante]

@anderson.silva Boa tarde..!

Bom.. não é complicado fazer isto.. mas imagina para gerenciar tantos usuários.. vc terá um baita trabalhão…

Criação de Usuário após insert:

Ex: Você vai precisar de 1 procedure e 1 trigger em cima da sua tabela de usuários… Ambos os objetos precisam ser criados com o usuário SYS, devido ao grant para criar usuários..

Vamos a Procedure:


CREATE OR REPLACE PROCEDURE CRIAR_USUARIO ( USUARIO VARCHAR, SENHA VARCHAR )
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
EXECUTE IMMEDIATE 'CREATE USER ' || USUARIO || ' IDENTIFIED BY "' || SENHA || '" TEMPORARY TABLESPACE TEMP' ;
END;


Vamos a Trigger:


CREATE OR REPLACE TRIGGER TR_CRIAR_USUARIO
AFTER INSERT ON .USERS
FOR EACH ROW
BEGIN
CRIAR_USUARIO( :new.USERS, :new.PASS);
END;


Motivo pelo qual precisa de uma procedure, é que o parametro “CREATE USER” possui commit implicito e não se pode dar commit em trigger, por este motivo foi criado em uma procedure…

Agora com relação aos Grants.. É a mesma ideia.. porém na sua procedure você irá precisar criar um CURSOR para poder ir dando os Grants automaticamente..

Mas eu seria mais didático.. criaria uma ROLE com as permissões definidas apenas daria o Grant da Role ao usuário…

Abraços..!

[Anderson de Abreu SilvaParticipante]

Thunder_Catz
Muito obrigado, agora já tenho um norte, vlw!!!

At,
Anderson Silva

[Anderson de Abreu SilvaParticipante]

Thunder Catz,

Alguem me alertou sobre o uso do AUTONOMOUS_TRANSACTION, sobre talves eu perder a integridade dos dados, mas nao entro em detalhes, pode me esclarecer mais sobre isso?

Grato,

Anderson A. Silva

[Anderson de Abreu SilvaParticipante]

Thunder Catz,

A primeira parte funcionou perfeitamente, estou criando os usuarios com um insert na tabela, mas a segunda me da um erro que não estou conseguindo reslover, olhe abaixo como fiz.
Criei uma role chamada ‘conectar’ e lhe atribui o ‘create session’, mas nao funciona na chamada.

insert into regras values (‘CONECTAR’,’FULANO’);

create or replace
TRIGGER TR_CRIAR_SESSAO
AFTER INSERT OR update ON REGRAS
FOR EACH ROW
BEGIN
CRIAR_SESSAO (:NEW.REGRA, :NEW.USUARIO);
END;

create or replace
PROCEDURE CRIAR_SESSAO (REGRA VARCHAR, USUARIO VARCHAR)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
EXECUTE IMMEDIATE 'GRANT '|| REGRA ||' TO '|| USUARIO ||' TEMPORARY TABLESPACE TEMP';
END;

[rmanParticipante]

O comando GRANT não tem TEMPORARY TABLESPACE TEMP

Tente assim:


create or replace
PROCEDURE CRIAR_SESSAO (REGRA VARCHAR, USUARIO VARCHAR)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
EXECUTE IMMEDIATE 'GRANT '|| REGRA ||' TO '|| USUARIO;
END;


[Anderson de Abreu SilvaParticipante]

rman

Funcionou perfeitamente, muito obrigado.

At,
Anderson A Silva

[rmanParticipante]

O AUTONOMOUS_TRANSACTION abre um conexão isolada, o rollback da transação original não vai desfazer a transação isolada.

Exemplo:

Connected to Oracle Database 10g Release 10.2.0.4.0
Connected as sakamoto

SQL>
SQL> CREATE TABLE PESSOAS(
2 ID NUMBER
3 ,NOME VARCHAR2(32)
4 );

Table created
SQL> ALTER TABLE PESSOAS ADD CONSTRAINT PK_PESSOAS PRIMARY KEY(ID);

Table altered
SQL> CREATE OR REPLACE PROCEDURE INSERT_PESSOA_AUTONOMOUS(ID NUMBER,NOME VARCHAR2)
2 IS
3 PRAGMA AUTONOMOUS_TRANSACTION;
4 BEGIN
5 INSERT INTO PESSOAS(ID,NOME) VALUES(ID,NOME);
6 COMMIT;
7 END;
8 /

Procedure created
SQL> CREATE OR REPLACE PROCEDURE INSERT_PESSOA(ID NUMBER,NOME VARCHAR2)
2 IS
3 BEGIN
4 INSERT INTO PESSOAS(ID,NOME) VALUES(ID,NOME);
5 END;
6 /

Procedure created
SQL> INSERT INTO PESSOAS(ID,NOME) VALUES(1,'JOÃO');

1 row inserted
SQL> EXEC INSERT_PESSOA_AUTONOMOUS(2,'PEDRO');

PL/SQL procedure successfully completed
SQL> EXEC INSERT_PESSOA(3,'MARIA');

PL/SQL procedure successfully completed
SQL> SELECT * FROM PESSOAS;

    ID NOME

---

     1 JOÃO
     2 PEDRO
     3 MARIA

SQL> ROLLBACK;

Rollback complete
SQL> SELECT * FROM PESSOAS;

    ID NOME

---

     2 PEDRO

Repare que ao fazer o ROLLBACK o INSERT de JOÃO e MARIA são desfeitos e PEDRO é mantido, isso porque o INSERT do PEDRO foi feito em outra transação que já sofreu COMMIT.

[Regis AraujoParticipante]

@anderson.silva Bom dia..!

Só consegui entrar no forum agora.. mas o @rman já explicou diretinho.. hehehe…

@rman.. vc é o cara..!!!

Abraços..!

[Autor]

Posts