How to find which users can create users

Document ID : KB000028804
Last Modified Date : 14/02/2018
Show Technical Document Details

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.

Assume that the following privileges have been granted:-

GRANT DEFINE ON USER DEFINE1 TO HILIA02;
*+ Status = 0        SQLSTATE = 00000  
GRANT CREATE ON USER CREATE1 TO HILIA03;
*+ 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 HILIA01
*+       DATE LAST UPDATED 2014-09-19-04.20.07.460008 BY HILIA01
*+       TO HILIA02
*+       ;                                                      
DIS PRIV ON USER CREATE1;
*+ Status = 0        SQLSTATE = 00000
*+   GRANT CREATE ON USER CREATE1
*+       DATE CREATED 2014-09-19-04.20.07.487686 BY HILIA01
*+       DATE LAST UPDATED 2014-09-19-04.20.07.487686 BY HILIA01
*+       TO HILIA03
*+       ;                                                      

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
*+ ------              ------------
*+ HILIA02             USER
*+ HILIA03             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  HILIA01             HILIA01
*+ 016720C3CF777106  HILIA01             HILIA01
*+
*+ 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, consult the Security Administration manual entry here:

http://support.ca.com/cadocs/0/CA IDMS 18 5 User Bookshelf-ENU/Bookshelf_Files/HTML/IDMS_Security_Admin_ENU/index.htm?toc.htm?992815.html