ITS A BITE LONG TO EXPLAIN, BUT PLZ READ.
I HAVE 2 USERS.

DAVE = OWNER OF THE TABLE "DAVE_TABLE"
STEEVE= NOTHING, AND HAVE THE ROLE RESOURCE AND CONNECT


DAVE Connected.
SQL> create role test_role identified by test_role;

Role created.

SQL> grant all on dave_table to test_role;

Grant succeeded.



STEEVE CANT SEE dave_table, look...

SQL> connect steeve
Connected.
SQL> select count(*) from dave.dave_table;
select count(*) from dave.dave_table
*
ERROR at line 1:
ORA-00942: table or view does not exist


SO I DO THE FOLLOWING...

SQL> execute dbms_session.set_role('TEST_ROLE IDENTIFIED BY TEST_ROLE');

PL/SQL procedure successfully completed.

SQL> select count(*) from dave.dave_table;

COUNT(*)
---------
1308


GREAT, IT WORKS!!!




BUT I WANT TO SET THE ROLE AUTOMATICALY EVERYTIME STEEVE CONNECTS.
--
PLEASE DONT ASK WHY, IT'S A LONG STORY!!!
--


SO I CREATE A DATABASE TRIGGER...

DAVE Connected.
SQL> CREATE OR REPLACE TRIGGER test_conn AFTER LOGON ON DATABASE
2 BEGIN
3 dbms_session.set_role('TEST_ROLE IDENTIFIED BY TEST_ROLE');
4 END;
5 /

Trigger created.



LOGICALY, THE NEXT TIME STEEVE CONNECTS, HE SHOULD SEE THE TABLE OF DAVE.


SQL> connect steeve
Connected.
SQL> select count(*) from dave.dave_table;
select count(*) from dave.dave_table
*
ERROR at line 1:
ORA-00942: table or view does not exist


WHY!!! WHY!!! DOES STEEVE DONT SEE DAVE, THE DATABASE TRIGGER SHOULD HAVE GIVE HIM THE RIGHT.