Password

[Robson Costa]

Good afternoon,

I made the migration of Oracle 10g to 11g. I realized that users who received roles identified by passwords, have begun to have problems such as the error ORA-00942 (table or view does not exist), even if it is assigned to the role. How do I access access to data from these roles to work again? I tried the SET ROLE solution, but only works if given in the user session.

[diegolenhardt]

já saw places that had a trigger after logon seting the current_schema of the session ...

[Robson Costa]

I created a trigger and used the dbms_session.set_role, but it did not work. Works only if I type the Set Role or DBMS_Session command.

follows the trigger created:

Select all

create or replace 
TRIGGER CONFIGURA_SESSAO AFTER 
LOGON ON "XXXX".SCHEMA BEGIN 
   logon_proc; 
END; 
 
create or replace 
PROCEDURE LOGON_PROC 
authid current_user is 
BEGIN 
  sys.dbms_session.set_role('teste identified by teste'); 
END LOGON_PROC;

[diegolenhardt]

Poem in the body of its procedure the alter session

Select all

 
BEGIN 
execute immediate 'alter session.....;'; 
--sys.dbms_session.set_role('teste identified by teste'); 
END LOGON_PROC; 

[Robson Costa]

Did not work.

[diegolenhardt]

And what happened, did it err when performing the trigger?

[Robson Costa]

It gives error, but only registered on ALERT.LOG

Select all

ORA-00604: ocorreu um erro no nível 1 SQL recursivo 
ORA-02248: opção inválida para ALTER SESSION 
ORA-06512: em "LOGON_PROC", line 5 
ORA-06512: em line 2 
ORA-00604: ocorreu um erro no nível 1 SQL recursivo 
ORA-06565: não é possível executar SET ROLE de dentro do procedimento armazenado 
ORA-06512: em "LOGON_PROC", line 5 
ORA-06512: em line 2

[diegolenhardt]

Select all

ORA-02248: opção inválida para ALTER SESSION 
ORA-06512: em "LOGON_PROC", line 5 
ORA-06512: em line 2 

How did you put the Alter Session within the procedure?

[Robson Costa]

Thus:

Select all

create or replace 
PROCEDURE LOGON_PROC 
authid current_user is 
BEGIN 
    execute immediate 'alter session set role teste_robson identified by teste'; 
END LOGON_PROC;