IDMS: Determine the users than can create other users

Products

• IDMS
• IDMS - Database

Issue/Introduction

Users with the SYSADMIN can create other users. However the privilege to create users can also be GRANTed to other users. With internal security, it is not a trivial matter to display the users to which this privilege has been granted.

Environment

Release: All supported releases.

Resolution

Assume that the following privileges have been granted:-

GRANT DEFINE ON USER DEFINE1 TO USER02;
*+ Status = 0        SQLSTATE = 00000
GRANT CREATE ON USER CREATE1 TO USER03;
*+ Status = 0        SQLSTATE = 00000

These privileges can be displayed:-

DIS PRIV ON USER DEFINE1;
*+ Status = 0        SQLSTATE = 00000
*+   GRANT DEFINE ON USER DEFINE1
*+       DATE CREATED 2014-09-19-04.20.07.460008 BY USER01
*+       DATE LAST UPDATED 2014-09-19-04.20.07.460008 BY USER01
*+       TO USER02
*+       ;
DIS PRIV ON USER CREATE1;
*+ Status = 0        SQLSTATE = 00000
*+   GRANT CREATE ON USER CREATE1
*+       DATE CREATED 2014-09-19-04.20.07.487686 BY USER01
*+       DATE LAST UPDATED 2014-09-19-04.20.07.487686 BY USER01
*+       TO USER03
*+       ;

However the nature of these privileges is such that the user on which the privilege is being granted may not exist at the time. Furthermore there is no DIS ALL PRIVILEGE command. The information can be retrieved using SQL against the security database. The following SQL schema definition must exist:-

CREATE SCHEMA IDMSSECU
    FOR NONSQL SCHEMA SYSDIRL.IDMSSECU VERSION 1
        DBNAME SYSUSER
    ;

The following SELECT can be used:-

SELECT * FROM IDMSSECU.RESOURCEAUTH WHERE RESOURCETYPE='USER';
*+
*+ AUTHID              RESOURCETYPE
*+ ------              ------------
*+ USER02              USER
*+ USER03              USER
*+
*+ RESOURCENAME                                                  RUNTIMEAUTH
*+ ------------                                                  -----------
*+ DEFINE1                                                                 0
*+ CREATE1                                                                 0
*+*+ RUNTIMEAUTHW  DEFNAUTH  DEFNAUTHW  OTHERAUTH  CTIME
*+ ------------  --------  ---------  ---------  -----
*+            0        31          0          0  016720C3CF7704E8
*+            0         1          0          0  016720C3CF777106
*+
*+ UTIME             CUSER               UUSER
*+ -----             -----               -----
*+ 016720C3CF7704E8  USER01              USER01
*+ 016720C3CF777106  USER01              USER01
*+
*+ 2 rows processed

AUTHID is the user to which the privilege has been granted.
RESOURCENAME is the user *on* which the privilege has been granted.
DEFNAUTH indicates the definition privileges that have been granted. If the '1' bit is on (i.e., the number is odd), then the entry in this result table indicates the CREATE privilege.

For more information, see Security for IDMS.