Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2009
    Posts
    272

    Unanswered: Replace view - privilege in DB2 9.7

    A user has a DBADM and DataAccess privilege in 9.7 but when it tries to Create or Replace a view, it fails with an error complaining that the user doesn't have the required privilege. Please let me know if the user needs to have any other privilege other than DBADM/DATAACESS in order to create/replace view.

    Any help in this regard is much appreciated !!

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    To create a view, the privileges you hold seems to be sufficient
    IBM DB2 9.7 for Linux, UNIX and Windows Information Center

    But to replace, there is another condition
    To replace an existing view, the authorization ID of the statement must be the owner of the existing view (SQLSTATE 42501).
    If the authid is not the owner, then from a SECADM user, issue TRANSFER OWNERSHIP (lookup docs for syntax) and perform the replace action.
    Of course, the other option is to use the current owner to replace the view.

    HTH

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

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Interestingly, I believe that DBADM can drop and re-create the view, even if it is not the owner.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Jun 2009
    Posts
    272
    @Sathyaram: Thanks !!

    @Marcus: You are right. I followed the same workaround to resolve the issue. Instead of replacing the view, I dropped and recreated it.

  5. #5
    Join Date
    Jun 2009
    Posts
    272
    We frequently do redirected restores of PROD data into our lower env's like QA. After the restore, some of the DDL using CREATE or REPLACE a new feature in db2 9.7 is failing saying only the owner of the object can invoke CREATE or REPLACE. The instance ID of PROD is still the owner of the objects in the lower env's after the restore, so we are not able to use the CREATE or REPLACE. As part of the redirected restore or through any other process/config can we make the instance ID of the lower lanes as owner of all objects to fix this issue? IF we cant use CREATE or REPLACE, we have to do 3 times more work for DDL with DROP and CREATE as lot of dependencies exist. Please advise.

    I understand that we can do a transfer ownership for each and every single object after the restore. But is there any other easier way to accomplish this ??

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Blazer789, AFAIK there isn't a short-cut for chaning ownership.

    BTW, changing ownership is lot easier than you might imaging.

    A view with all object owners info :

    OBJECTOWNERS

    and create a script to build and execute transfer ownerships using the view.

    DB2 Database for Linux, UNIX, and Windows

    I hope you already have the process for granting SECADM to a new user. The SECADM issuing this command cannot transfer ownership to itself.

    HTH

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

  7. #7
    Join Date
    Jun 2009
    Posts
    272
    Thanks Satya for your kind response. When I say an alternate way to accomplish this, I meant to ask if there is any way the objects in the lowerlane get the ownership of the instance id of the lower lane through an option in restore.

    For example:
    "A" is the instance user of database "d1". "A" owns all the objects in "d1".
    "B" is the instance user of database "d2". "B" owns all the objects in "d2".

    "d1" is restored on to "d2". Now "d2" is located on instance "B" but all the objects are owned by user "A". Now when I try to run "create or replace view" on the newly restored database with user B it fails complaining that "B" is not the owner of the object. So I am looking for an option in restore or someother process with which all the objects in the newly restored database would be owned by user "B" after the restore.

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    @Blazer789 , Yes I did get your point, but unfortunately(AFAIK) changing ownership during a redirected restore is not possible

    HTH

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

  9. #9
    Join Date
    Jun 2009
    Posts
    272
    Okay, Thanks !

Posting Permissions

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