Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Trying to trigger ALTER SESSION based on roles, but failing

    I have a simplified version of a PACKAGE that I'm calling from a LOGON trigger

    Code:
    create or replace
    package          oper_logon_trigger_pg_delete
      authid current_user
    as
      procedure MAIN;
    end oper_logon_trigger_pg_delete;
    
    
    create or replace
    package body          oper_logon_trigger_pg_delete
    as
    
      procedure MAIN 
      is
        l_str varchar2(4000);
      begin
        for row in (select granted_role from user_role_privs)
        loop;
          l_str := l_str || ' ' || row.granted_role;
        end loop;
        
        shared.shr_send_mail_pg.send_mail_show_smtp_error('me@email.com',
                                                          'me@email.com',
                                                          'test email',
                                                          l_str);
      end MAIN;
      
    end oper_logon_trigger_pg_delete;
    The ultimate goal will be to set the NLS_DATE_FORMAT for a particular set of users, if they're in a particular role.

    I tried using USER_ROLE_PRIVS and SESSION_ROLES, but neither show a full listing of the roles granted to a particular user. I thought the AUTHID CURRENT_USER would have fixed some of my problems, but it doesn't. Any advice?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >The ultimate goal will be to set the NLS_DATE_FORMAT for a particular set of users, if they're in a particular role.

    Just curious, what is downside for doing it for all ROLE?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    It's a system that's been up & running for about 4 years now. I don't want to break anything where people have (incorrectly) coded their TO_DATE( ) calls to imply the default 'DD-MON-RR' format. (We're working on this code to correct a problem when you upgrade from the 9i JDBC thin client, to the 10 or 11g client. The 9i client somehow sets the NLS_DATE_FORMAT to 'MM/DD/RR' while the newer clients assume an NLS_DATE_FORMAT from the NLS_TERRITORY setting. I know that this whole issue stems from poor coding, which we're fixing, but we're in a pickle and we're trying to find the best way forward).

    Probably I should clarify something a bit further about the code below. If I call it directly, I see all the roles I was expecting. It's only when I call the same code from the LOGON trigger, that I see a short list.

    I don't know if you can code a trigger with the AUTHID CURRENT_USER, but I am going to try that next.

    I can get the code to work if I go all heavy-handed and use code like:

    Code:
    if user in ('<user1>','<user2>') then ...
    But as we create more of these users over time, I just wanted to be able to place them into a role, and have them inherit this functionality.
    --=Chuck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •