-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|