Db2

Db2

Where DBAs and data experts come together to stop operating and start innovating. Connect, share, and shape the AI era with us.


#Data


#Data
#Databases
#Operatingsystems
#Db2
#Databasesolutions
 View Only

  • 1.  Granting DBADM without DBADM authority on database

    Posted 01/10/24 04:48 AM

    Hello,

    I need to work with a database which was created the wrong way. The only user having DBA Auth on that database was a personal account of a colleague who no longer works with the company and nobody else has a DBA Auth in the database. The instance admin is a local user and I have access, but it does not have DBA Auth in the database. The instance user is a member of the SYSADM and SYSMAINT Windows groups and I can access with this use the server via RDP.

    Is there a way how to grant a DBA Auth to the instance user in this situation?

    I found it is possible to use DB2_RESTORE_GRANT_ADMIN_AUTHORITIES=ON during the restore, but causing downtime to the database is the last option.

    Db2 11.1 LUW (standalone)
    Windows Server 2016 (in domain)

    Thank you for your advice.



    ------------------------------
    Ondrej
    ------------------------------


  • 2.  RE: Granting DBADM without DBADM authority on database

    Posted 01/10/24 11:19 AM

    1. What is the output of:

    select grantee, securityadmauth from syscat.dbauth

    2. Did you try to re-create "personal account of a colleague who no longer works with the company" on the database server?



    ------------------------------
    Jan Nelken
    ------------------------------



  • 3.  RE: Granting DBADM without DBADM authority on database

    Posted 01/11/24 12:20 AM

    Hello Jan,

    Thank you for your comment. Here is the requested output.

    C:\Program Files\IBM\SQLLIB\BIN>db2 "select grantee, securityadmauth from syscat.dbauth"

GRANTEE                                                                                                                          SECURITYADMAUTH
-------------------------------------------------------------------------------------------------------------------------------- ---------------
SKATOLICKY                                                                                                                       Y
PUBLIC                                                                                                                           N

  2 record(s) selected.


C:\Program Files\IBM\SQLLIB\BIN>

    I was also thinking about the recreation of the user account, but it is forbidden to "reuse usernames". I would need to ask for an exception, so I am looking for something that would not need any other person's intervention.

    Thank you



    ------------------------------
    Ondřej
    ------------------------------

    Original Message


  • 4.  RE: Granting DBADM without DBADM authority on database

    Posted 01/11/24 03:50 AM

    Two options:

    1 Re-create SKATOLICKY for the duration of logging in as him and issue required grants.
    2. Restore with DB2_RESTORE_GRANT_ADMIN_AUTHORITIES=YES option.



    ------------------------------
    Jan Nelken
    ------------------------------

    Original Message


  • 5.  RE: Granting DBADM without DBADM authority on database

    Posted 01/11/24 04:30 AM

    Hello Jan,

    Thank you for your comment. I will try the first one and then the second one if necessary.
    Thank you once again for your help.

    Regards



    ------------------------------
    Ondřej
    ------------------------------

    Original Message


Related Content