Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2008
    Posts
    26

    Unanswered: Automatically granting lost privileges

    Hello ppls,

    We are using DB2 V8.2 for ERP(peoplesoft application)

    Developers most of the time drop and recreate the tables via application side (not from DB side)

    As everyone know, dropping the table removes all the privileges pertaining to that table.

    The moment that table got dropped, i would like to grant select & update privileges to 3 - 4 users. Also i would like to automate this process.

    Is there anyway we can do that? Trigger is out of scope, as there is no option of 'after create/drop'.

    Will be great if anyone can shed some lights over here. Thanks.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    If the programmers are dropping and recreating tables via the application, then they should also recreate the privileges.

    Andy

  3. #3
    Join Date
    Aug 2008
    Posts
    26
    No Andy,

    (Peoplesoft) tool gives you the possibility of dropping and recreating only.

    It is not having the flexibility of adding a extra DDL statement to it.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Unless you can have a window from when the table gets created to when the privileges get granted, you have a problem. The best I can offer is to set up a monitoring script on the server that detects a new table and then grants the privileges. Then schedule the script to run every few minutes (whatever your window is).

    Andy

  5. #5
    Join Date
    Aug 2008
    Posts
    26
    My window is very small. As mentioned by you, i will have scripts scheduled as the last option.

    Thanks andy for your inputs.

Posting Permissions

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