Results 1 to 9 of 9

Thread: User privileges

  1. #1
    Join Date
    Aug 2011
    Location
    Mumbai,India
    Posts
    49

    Unanswered: User privileges

    Hi,

    I want to create an user which will be able to create tables,views and other database objects but it will not be able to drop those objects.

    Is it possible to revoke drop privilege of an object from its creator???


    DB2 V9.5

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    see : DROPIN
    Revokes the privilege to drop objects in the schema.
    REVOKE (Schema Privileges)
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    I don't think it is possible to revoke drop privilege from the user.

    Why do you want to do this ?

    You can transfer the ownership of the object to someone else and grant the original creator only selective priivileges. Look at TRANSFER OWNERSHIP command.

    -
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Guy,
    As per documentation : (http://publib.boulder.ibm.com/infoce...Fr0000988.html )




    When dropping objects that allow two-part names, the privileges held by the authorization ID of the statement must include at least one of the following:
    • DROPIN privilege on the schema for the object
    • Owner of the object, as recorded in the OWNER column of the catalog view for the object
    • CONTROL privilege on the object (applicable only to indexes, index specifications, nicknames, packages, tables, and views) <LI class=li>Owner of the user-defined type, as recorded in the OWNER column of the SYSCAT.DATATYPES catalog view (applicable only when dropping a method that is associated with a user-defined type)
    • SYSADM or DBADM authority

    So, the owner will be able to drop the object

    Sathyaram
    Last edited by sathyaram_s; 09-05-11 at 08:21. Reason: added link
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Aug 2011
    Location
    Mumbai,India
    Posts
    49
    Thanks for the info...

    but my application require such ID. Is there any alternate way to achive the same??

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    So, you don't want the application to inadvertantly drop the table ?

    Here, when you create a table, you can create 'WITH RESTRICT ON DROP'
    or ALTER the table to be RESTRICT ON DROP
    Code:
     db2 "create table t1(i int) with restrict on  drop"
    DB20000I  The SQL command completed successfully.
     
     db2 drop table t1
    DB21034E  The command was processed as an SQL statement because it was not a
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0672N  Operation DROP not allowed on table "DB2INST1.T1".  SQLSTATE=55035
     
     db2 "alter table t1   drop restrict on  drop"
    DB20000I  The SQL command completed successfully.
     
      db2 drop table t1
    DB20000I  The SQL command completed successfully.
    As you can see, unless you alter to remove restrict on drop, the table cannot be dropped

    HTH
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Aug 2011
    Location
    Mumbai,India
    Posts
    49
    Thanks Satya... This will work...

  8. #8
    Join Date
    Oct 2007
    Posts
    246
    satya

    but the user who is going to create the with this option has the privileges to alter the table to remove this option right and he can drop the table.

    regs
    Paul

  9. #9
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Yes, you are right Paul.

    If you don't want the user to have any 'automatic' drop privilege on the table, Transfer ownership( the user doesnt even have to be real)
    If you want to prevent the user from dropping the table by mistake(an application logic error,say) , then alter the table to restrict drops

    If the requirement is any different, then the solution will be too

    HTH
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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