How to find which users can create users
search cancel

How to find which users can create users

book

Article ID: 28804

calendar_today

Updated On:

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: IDADSO00100-18.5-ADS-for CA-IDMS
Component:

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, consult the Security Administration manual at Security for IDMS.