Blog do Marcio Portes

pessoal eu estou no oracle 10g e queria saber se existe a possibilidade de fazer o seguinte. Tenho uma tabela de usuário onde tem login e senha.. e gostaria de como ficaria a trigger após o insert para fazer um create user xxx identify by xxxx .... e dar o grant de create session to xxxx para o usuário do oracle...

ops$marcio:LX10G> connect / as sysdba
Connected.
sys:LX10G>
sys:LX10G> grant create user to ops$marcio;

Grant succeeded.

sys:LX10G> grant create session to ops$marcio with admin option;

Grant succeeded.

sys:LX10G>
sys:LX10G>
sys:LX10G> connect /
Connected.
ops$marcio:LX10G>
ops$marcio:LX10G> create table usuarios ( username varchar2(30), senha varchar2(30));

Table created.

ops$marcio:LX10G>
ops$marcio:LX10G> create or replace
  2  procedure create_user (
  3     p_username in usuarios.username%type,
  4     p_senha    in usuarios.senha%type
  5  ) is
  6     l_create    long := 'create user '|| p_username ||' identified by '|| p_senha;
  7     l_grant     long := 'grant create session to '|| p_username;
  8  begin
  9     execute immediate( l_create );
 10     execute immediate( l_grant );
 11  end;
 12  /

Procedure created.

ops$marcio:LX10G>
ops$marcio:LX10G> create or replace procedure run_create (
  2     p_username in usuarios.username%type,
  3     p_senha    in usuarios.senha%type
  4  ) is
  5     pragma autonomous_transaction;
  6     l_job       number;
  7  begin
  8     dbms_job.submit( l_job, 'create_user( '''|| p_username ||''','''|| p_senha ||''');');
  9     commit;
 10  end;
 11  /

Procedure created.

ops$marcio:LX10G>
ops$marcio:LX10G> create or replace trigger tgr_aifer_usuarios
  2     after insert on usuarios
  3     for each row
  4  begin
  5     run_create( :new.username, :new.senha );
  6  end;
  7   /

Trigger created.

ops$marcio:LX10G>
ops$marcio:LX10G> show user
USER is "OPS$MARCIO"
ops$marcio:LX10G>
ops$marcio:LX10G> insert into usuarios values ('novo_usuario', 'senha01');

1 row created.

ops$marcio:LX10G>
ops$marcio:LX10G> exec dbms_lock.sleep(5)

PL/SQL procedure successfully completed.

ops$marcio:LX10G>
ops$marcio:LX10G> select * from usuarios;

USERNAME             SENHA
-------------------- ------------------------------
novo_usuario         senha01

1 row selected.

ops$marcio:LX10G>
ops$marcio:LX10G> select username, password
  2    from dba_users
  3   where username = 'NOVO_USUARIO'
  4  /

USERNAME             PASSWORD
-------------------- ------------------------------
NOVO_USUARIO         25249E3F24F1A585

1 row selected.

ops$marcio:LX10G>
ops$marcio:LX10G> connect novo_usuario/senha01
Connected.
novo_usuario:LX10G>

Labels: Duvidas

# posted by Marcio : 10:14 PM