-
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.
-
Hi,
Did you try to grant test_role directly to steve?
grant test_role to steve;
HTH
Tycho
-
The owner of the trigger is DAVE, so no matter who invokes the trigger at logon time, the trigger executes on behalf of its owner, DAVE. So in fact, with each login you are setting role TEST_ROLE to DAVE!
If you are on 8i, you should use "invokers right" option. I think it can't be applied to trigger, so you should wrap your call "dbms_session.set_role('TEST_ROLE IDENTIFIED BY TEST_ROLE');" into a procedure, created with invokers rights,and call that procedure from within your trigger. That way, the role will be set to STEVE when he logs in.
BTW, I hope you are avare that when you set TEST_ROLE by calling DBMS_SESSION.SET_ROLE you are at the same time disabling all other roles active for that user (RESOURCE and CONNECT in the case of STEVE).
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
THANKS JMODIC!!!!!!!!!!!!!!!!!!!
THANKS FOR THE ANWERS!!!
-
But it still does not work, check this out.
Bookmark Fixed font Go to End
Doc ID: Note:106140.1
Subject: AFTER LOGON Triggers Don't Allow DBMS_SESSION.SET_ROLE to Keep Roles Enabled
Type: BULLETIN
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 21-APR-2000
Last Revision Date: 27-NOV-2001
Problem Description
-------------------
When called from an AFTER LOGON trigger, DBMS_SESSION.SET_ROLE does
not keep roles enabled within the session after logon, although the
AFTER LOGON trigger executes correctly.
Solution Description
--------------------
This is not currently possible because the current paradigm used by both
triggers and PL/SQL does not permit the enabling of roles within logon
triggers.
Example and Explanation
-----------------------
The following example illustrates this problem:
SQL> CREATE ROLE r1;
Role created.
SQL> GRANT r1 TO scott;
Grant succeeded.
SQL> ALTER USER scott DEFAULT ROLE ALL except r1;
User altered.
SQL> select * from dba_role_privs where grantee='SCOTT';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
SCOTT CONNECT NO YES
SCOTT OWS_STANDARD_ROLE NO YES
SCOTT R1 NO NO
SCOTT RESOURCE NO YES
SCOTT WEBDB_DEVELOPER NO YES
*** Test1
SQL> CREATE OR REPLACE TRIGGER scott.logon_set_role_scott
2 AFTER LOGON ON scott.schema
3 BEGIN
4 sys.dbms_session.set_role('R1');
5 END;
6 /
Trigger created.
SQL> connect scott/tiger
Connected.
SQL> select * from session_roles;
ROLE
------------------------------
CONNECT
RESOURCE
WEBDB_DEVELOPER
OWS_STANDARD_ROLE
SQL> connect system/manager
Connected.
SQL> revoke r1 from scott;
Revoke succeeded.
SQL> connect scott/tiger
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01924: role 'R1' not granted or does not exist
ORA-06512: at "SYS.DBMS_SESSION", line 77
ORA-06512: at line 2
Warning: You are no longer connected to ORACLE.
This error shows that the trigger is fired and executed.
When a procedure is executed, it assumes the 'current' user is
switched to the procedure owner. This switch occurs, even when
the owner and invoking user are the same, to maintain consistency.
Therefore, a 'set role' issued within the procedure sets the role for
the procedure owner, not the invoker.
Hence, if the operation was allowed, it would have no effect since
procedures running under the owner run with no roles enabled. When
the procedure exits, the 'current' user is switched back to the
invoking user. Therefore, the set role would be lost anyway.
*** Test2
SQL> create or replace procedure logon_set_role_scott_proc
2 authid definer is
3 begin
4 sys.dbms_session.set_role('ALL');
5 end logon_set_role_scott_proc;
6 /
Procedure created.
SQL> CREATE OR REPLACE TRIGGER logon_set_role_scott
2 AFTER LOGON ON scott.schema
3 BEGIN
4 scott.logon_set_role_scott_proc;
5 END;
6 /
Trigger created.
$sqlplus scott/tiger
SQL*Plus: Release 8.1.5.0.0 - Production on Fri Apr 21 16:27:53 2000
(c) Copyright 1999 Oracle Corporation. All rights reserved.
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-06565: cannot execute SET ROLE from within stored procedure
ORA-06512: at "SYS.DBMS_SESSION", line 77
ORA-06512: at "SCOTT.LOGON_SET_ROLE_SCOTT_PROC", line 4
ORA-06512: at line 2
With Oracle8i, it is possible to specify whether a stored procedure
executes with the identity of the procedure owner ('definer') or the
identity of the user executing the procedure ('invoker'). However, if
a procedure is run as the invoker, set role succeeds and the role is
set after the procedure has completed.
When a procedure is run with 'invoker's rights', roles are not disabled
and role operations are permitted the same way that they are in anonymous
blocks. Thus, one might expect that this would represent a suitable
workaround. Unfortunately, this only eliminates the errors, but does not
enable the role as one might expect. Consider the following example:
*** Test3
SQL> create or replace procedure scott.logon_proc2
2 authid current_user is
3 begin
4 sys.dbms_session.set_role('R1');
5 end logon_proc2;
6 /
Procedure created.
SQL> CREATE OR REPLACE TRIGGER scott.logon2
2 AFTER LOGON ON scott.schema
3 BEGIN
4 scott.logon_proc2;
5 END;
6 /
Trigger created.
SQL> connect scott/tiger
Connected.
SQL> select * from session_roles;
ROLE
------------------------------
CONNECT
RESOURCE
WEBDB_DEVELOPER
OWS_STANDARD_ROLE
Role R1 is still not enabled for this user. Why?
Invoker's rights procedures execute with the privileges of the
invoker (not the end user). In this case, the invoker is actually
the trigger (which is definer's rights). Thus, when the top level
call to the trigger returns the same context, the switch occurs as was
the case for the standalone trigger, effectively undoing any of
the roles enabled in the procedure call.
Related Articles
----------------
[NOTE:1008453.6] Using DBMS_SQL Package to Execute DDL Statements and
Dynamic SQL from PL/SQL
[NOTE:93939.1] DBMS_SQL Package versus Native Dynamic SQL
[NOTE:69483.1] Changing Role within Stored Procedures using dbms_session.
set_role
.
--------------------------------------------------------------------------------
Copyright (c) 1995,2000 Oracle Corporation. All Rights Reserved. Legal Notices and Terms of Use.
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
|