DB2 10.5 for Linux, UNIX, and Windows

AUTH_GET_INSTANCE_AUTHID - Get the instance owner authorization ID

The AUTH_GET_INSTANCE_AUTHID scalar function returns the authorization ID of the instance owner

Syntax


>>-AUTH_GET_INSTANCE_AUTHID--(--)------------------------------><

The schema is SYSPROC.

Authorization

EXECUTE privilege on the AUTH_GET_INSTANCE_AUTHID scalar function.

Default PUBLIC privilege

In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the function is automatically created.

Information returned

This function returns a value of type VARCHAR(128) that specifies the authorization ID of the instance owner.

Usage notes

Common configurations have the instance owner account as a member of the SYSADM group, therefore, before DB2® Version 9.7, applications that are run under the instance owner account had unlimited authority on the database. In DB2 Version 9.7 and later, a user who holds SYSADM authority no longer has implicit DBADM authority and as a result applications that are run under the instance owner account might experience authorization errors, such as SQL1092N, SQL0551N, and SQL0552N, when performing operations that are no longer within the scope of SYSADM authority.

The UPGRADE DATABASE command and the RESTORE DATABASE command (for a previous database) grant DBADM authority to the SYSADM group, however this is not the case for any new Version 9.7 database.

To obtain a list of the authorities held by the instance owner authorization ID, follow these steps:

Use the SYSPROC.AUTH_GET_INSTANCE_AUTHID() scalar function to determine the instance owner authorization ID. For example:
```
db2 "VALUES SYSPROC.AUTH_GET_INSTANCE_AUTHID()"
```
This command returns.
```
1
--------------------------------------
BOB

  1 record(s) selected.
```

Get a list of the authorities for this authorization ID. For example:

SELECT * FROM 
   TABLE (SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID ('BOB', 'U') ) AS T 
   ORDER BY AUTHORITY

If necessary, grant any missing authorities. For example:
```
GRANT DBADM ON DATABASE TO USER BOB
```

Example

The following example shows how to use the DB2 command line processor (CLP) to obtain the authorization ID of the instance owner:

db2 "values SYSPROC.AUTH_GET_INSTANCE_AUTHID()"

The following is an example of output for this command.

1
-------------------------...-------------
ZURBIE

  1 record(s) selected.