Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2012
    Posts
    9

    Unanswered: How to revoke user's drop permission

    Hi,

    To validate one business scenario i wanted to revoke the DROP privileges of a DB2 user so that any drop query does not work.

    How can that be done?

    Thanks in advance.
    Ashryar

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    But a user who has any of the following can still drop a table:

    PHP Code:
    When dropping objects that allow two-part namesthe 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 indexesindex specificationsnicknamespackagestables, and views)
    DBADM authority 

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You can revoke DBADM authority as well as CONTROL authority. But if the person created the table, then they can drop it as well. You cannot change the OWNER.

    You could create an object that would restrict the DROP from happening. Look at Table 1 on this page: IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

    Andy

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    The object owner can drop the table. But, as Andy says, you can restrict drop, so that the object is not dropped by mistake.

    If you want to change the owner, you can use TRANSFER OWNERSHIP statement

    TRANSFER Ownership

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

  6. #6
    Join Date
    Mar 2012
    Posts
    9
    Thanks Guys

Posting Permissions

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