VMware Smart Assurance NCM: How to get list of users who have "System Administration" permissions?

Products

• VMware

Environment

VMware Smart Assurance - NCM

Resolution

On Linux Environment:
Login to NCM Combination/Database server with root privileges:
a) Switch to pgdba user with following command:
      su - pgdba
b) Login to postgres DB with below command:
     psql voyencedb voyence 
c) When prompted for password, enter the Database password.

 

On Windows Environment:
Log into the database server as a user with administrator privileges.
a) Open a command prompt (run as administrator)
b) Navigate to the PostgreSQL directory using the following command.
[Product home]\db\controldb\bin
c) Type psql -p 5435 voyencedb voyence, and press Enter.
d) When prompted for password, enter the Database password.

 

 

After login to the Database:
Run the below query:
select user_name, permissions from cm_user u left join cm_acl_entry acl on acl.user_id=u.user_id where acl.permissions like '%system-admin%';

 

Example: 
=======================
voyencedb=# select user_name, permissions from cm_user u left join cm_acl_entry acl on acl.user_id=u.user_id where acl.permissions like '%system-admin%';
user_name |
                                             permissions
-----------+---------------------------------------------------------------------
sysadmin | \254\355\000\005sr\000\023java.util.ArrayListx\201\322\035\231\307a\235\003\000\001I\000\004sizexp\000\000\000\003w\004\000\000\000\003sr\0007co
m.powerup.configmgr.server.security.PermissionDetails\323:\224s\302\365\373\334\002\000\001L\000\012permissiont\000\022Ljava/lang/String;xpt\000\021principal
-presentsq\000~\000\002t\000\024permissions-overridesq\000~\000\002t\000\014system-adminx

Test      | \254\355\000\005sr\000\023java.util.ArrayListx\201\322\035\231\307a\235\003\000\001I\000\004sizexp\000\000\000\002w\004\000\000\000\002sr\0007co
m.powerup.configmgr.server.security.PermissionDetails\323:\224s\302\365\373\334\002\000\001L\000\012permissiont\000\022Ljava/lang/String;xpt\000\014system-ad
minsq\000~\000\002t\000\021principal-presentx
(2 rows)
voyencedb=#
=======================

Here sysadmin and Test are the two users who have "System Administration" permissions in NCM.

The string "system-admin" in the encrypted "permissions" detail in above output indicates user has "System Administration" permissions.

 

Additional Information

There is no option to pull this information from Report Advisor or Automation Library.