Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2003
    Posts
    3

    Unanswered: Db2 Trigger & User ID in Secondary Authorization ID

    How do I set up a trigger to only execute when a USER is grouped in a Secondary Authorization ID?

    For example:

    Here is my Secondary Authorization ID

    GROUP1
    --------
    USER1
    USER2
    USER3

    I have the following 2 tables:

    Job_Type:

    Job_Type_Id: char(1)
    Job_Type_desc: varchar(50)
    -----------------------------------
    Job_Type_Hstry:

    Job_Type_Id: char(1)
    Job_Type_desc: varchar(50)
    UserID: char(8)
    Tmstp: timestamp

    and this is the trigger:

    CREATE TRIGGER INSJCODE
    AFTER INSERT ON JOB_TYPE
    REFERENCING NEW AS N
    FOR EACH ROW MODE DB2SQL
    INSERT INTO JOB_TYPE_HSTRY(JOB_TYPE_ID, JOB_TYPE_DESC, USERID, TMSTP)
    VALUES (N.JOB_TYPE_ID, N.JOB_TYPE_DESC, USER, CURRENT_TIMESTAMP)
    WHEN (***what do I put here to only execute when the user is in GROUP1?***)

    Thank you very much for your help.
    SJC

  2. #2
    Join Date
    Oct 2001
    Location
    Bangalore
    Posts
    186
    Hi,

    This solution is from my point of view, i mean what i got from the question. If i am not correct, then just give clarification of the question.

    Here how you can go:

    You can create a trigger on
    before insert on Job_Type_Hstry (When Userid in (User1,User2,User3))

    and then insert the record in the table.

    Hope i have interpretted the question correctly.

    cheers,
    Prashant

  3. #3
    Join Date
    Jan 2003
    Posts
    3
    Thanks for your answer, but what if a new user is added to the Secondary Authorization ID? That means I have to alter the Trigger too?

  4. #4
    Join Date
    Oct 2001
    Location
    Bangalore
    Posts
    186
    Hi,

    Then you can use a clause where you will say that
    when (newrow.USERID in (Select USERID from GROUP1)

    cheers,
    Prashant

  5. #5
    Join Date
    Jan 2003
    Posts
    3
    The secondary auth ids are created through ACF2. I created my tigger using the when clause

    WHEN CURRENT SQLID IN ('GROUP1')

    I set my current SQL to 'GROUP1'. When I ran an INSERT query, the trigger executed as designed.

    My next test was to set my CURRENT SQLID to 'USER1'. I then ran another INSERT query, but the trigger did not execute.

    Why? USER1 is associated with 'GROUP1'. The trigger should have executed.

    I know the trigger will work if I do
    WHEN CURRENT SQLID IN ('USER1', 'USER2', 'USER3')

    However, I don't want to keep modifying my trigger everytime a new user comes aboard. Example: A new user('USER4) is added through ACF2. We would need to modify the trigger (if we remember)

    WHEN CURRENT SQLID IN ('USER1', 'USER2', 'USER3', 'USER4')

    Do you see where my dilemma is? I don't want the trigger to execute everytime a executable program updates my table. I just want to monitor a specific group (secondary auth id) when they manually change a table.

  6. #6
    Join Date
    Nov 2002
    Location
    Rio de Janeiro - Brazil
    Posts
    78
    Hey SJC,

    What O.S. is used on the Database server?

    I´m not sure about the behaviour of Db2 on other platforms, if not AIX. Although I think they´re similar.

    DB2 UDB does not control the group membership. I found in the A Complete Guide to DB2 UDB:
    "The group membership of an individual may change over time.. Groups are managed by the operating system, and UDB is not informed of changes in group membership"

    Considering that, I think that´s not possible to do what you want, unless you specify the UserId of the privileged ones. When you grant the privilege to a group, whenever a member of that group tries to access the object, DB2 certifies with the O.S. if the user is a member of the group and if the answer is true, the access is allowed. If not, the access is denied.

    So for your issue, the answer would be to specify the Users what own the privilege.

    HTH,
    Fernando.

    Originally posted by SJC
    The secondary auth ids are created through ACF2. I created my tigger using the when clause

    WHEN CURRENT SQLID IN ('GROUP1')

    I set my current SQL to 'GROUP1'. When I ran an INSERT query, the trigger executed as designed.

    My next test was to set my CURRENT SQLID to 'USER1'. I then ran another INSERT query, but the trigger did not execute.

    Why? USER1 is associated with 'GROUP1'. The trigger should have executed.

    I know the trigger will work if I do
    WHEN CURRENT SQLID IN ('USER1', 'USER2', 'USER3')

    However, I don't want to keep modifying my trigger everytime a new user comes aboard. Example: A new user('USER4) is added through ACF2. We would need to modify the trigger (if we remember)

    WHEN CURRENT SQLID IN ('USER1', 'USER2', 'USER3', 'USER4')

    Do you see where my dilemma is? I don't want the trigger to execute everytime a executable program updates my table. I just want to monitor a specific group (secondary auth id) when they manually change a table.

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    If you are very desperate, I think you can write User Defined Functions to get the members of the OS group and use it in your trigger ...

    But, you might need to use different commands for different Operating Systems ...

    HTH

    Sathyaram

Posting Permissions

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