Results 1 to 8 of 8

Thread: creatin\dropin

  1. #1
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433

    Unanswered: creatin\dropin

    db2 8.2.7
    win 2000

    I have to give creatin,dropin priv. on a schema to a user so that they can
    debug SP .
    Now by Giving these priv. they can also drop Tables in that schema which i want to avoid.
    What is the way out, so that they can drop/create the procedures but wont be able to Drop tables in the schema
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The only thing I can think of is to make them put their routines in a separate schema and grant the rights there.

    Andy

  3. #3
    Join Date
    May 2006
    Posts
    82
    First of all why you have to give privilege on Schema..?
    Cant you grant it only for that particular SP..?
    Vinay,

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by vini_srcna
    First of all why you have to give privilege on Schema..?
    Cant you grant it only for that particular SP..?
    How do you grant create rights to a SP that does not exist?

    That is why you need creatin right for the schema.

    Andy

  5. #5
    Join Date
    May 2006
    Posts
    82
    ohh OK..i was thinking if you have to alter the existing one.
    Vinay,

  6. #6
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    No , i have to keep SP and tables in the Same Schema.
    Recently a important Table was dropped by some user because of the schema priv.
    It seems to me a big security concern.
    I believe there must be a way out ,which we are missing
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Revoke the DROPIN privilege on the schemas and manage things via CONTROL privileges on the single objects or the DEFINER.

    http://publib.boulder.ibm.com/infoce...c/r0000945.htm
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    Hi Knut
    Thanks for your reply.
    1 more solution that i have got
    Revoking alterin from schema
    and then altering all tables in the schema to restrict drop.

    alter table eone.pTest124 add restrict on drop ;
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

Posting Permissions

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