DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: DATABASE TRIGGER AND ROLE

  1. #1
    Join Date
    Nov 2000
    Posts
    440
    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.


  2. #2
    Join Date
    Jul 2000
    Location
    Amsterdam
    Posts
    234
    Hi,

    Did you try to grant test_role directly to steve?

    grant test_role to steve;

    HTH
    Tycho

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  4. #4
    Join Date
    Nov 2000
    Posts
    440
    THANKS JMODIC!!!!!!!!!!!!!!!!!!!
    THANKS FOR THE ANWERS!!!





  5. #5
    Join Date
    Nov 2000
    Posts
    440
    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
  •  


Click Here to Expand Forum to Full Width