000016743 - AM 7.1: custom sql query to list all users belonging to a specific group in the internal database

Document created by RSA Customer Support Employee on Jun 14, 2016Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 2Show Document
  • View in full screen mode

Article Content

Article Number000016743
Applies ToAM 7.1: custom sql query to list all users belonging to a specific group in the internal database
IssueHow to run SQL query in AM 7.1 SP4 Server or Appliance, How to run a custom sql query to list all users belonging to a specific group in the internal database
There is no report template that will list all users belonging to a user group in the internal database
Resolution

For Windows:

1) login to your AM server as the account you run AM under, and go to the c:\ prompt.

2) From the command line your first step will be to obtain the db password for your instance. Note where I reference $AMHOME, that is the path to your installation, the default installation path is c:\program files\rsa security\rsa authentication manager. Adjust to your specific environment. Go into the proper directory:

cd $AMHOME\utils

3) Run the following command to obtain your unique db password to login to sqlplus with by running the following command

rsautil.cmd manage-secrets -a get com.rsa.db.root.password

It will prompt you to enter your master password, enter it:

Enter Master password: ***********

It will produce a db password string that is unique to your instance, note this is my password and yours will be different:

com.rsa.db.root.password: YMax1EZ3yG

4) From the command line, login to sqlplus using YOUR unique password where I have noted mine

sqlplus sys/YMax1EZ3yG as sysdba

5) you will be at the sqlplus prompt

SQL>

6) Note in this example, my group name is testgroup and it contains 3 users, cindy1, cindy2, cindy3.

This is the query that will produce the list, the LOGINUID is on the top, and the group name below it.

Note: you will need to substitute your groupname where I list testgroup.

SQL> select ipd.loginuid,igd.name from rsa_rep.ims_principal_group ipg join rsa_rep.ims_principal_data ipd on ipd.id = ipg.principal_id join rsa_rep.ims_group_data igd on igd.id = ipg.group_id where igd.name='testgroup';

The resulting output will look like such:

LOGINUID
--------------------------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
cindy1
testgroup

cindy2
testgroup

cindy3
testgroup

To exit, type quit at the sqlplus prompt

SQL>quit


For Appliance: 
1) SSH to your Appliance as emcsrv then sudo su rsaadmin 
2) cd $AMHOME\utils e.g. cd /usr/local/RSASecurity/RSAAuthenticationManager/utils 
3) Run the following command to obtain your unique db password to login to sqlplus with by running the following command 
rsautil.cmd manage-secrets -a get com.rsa.db.root.password 
It will prompt you to enter your master password, enter it: 
Enter Master password: *********** 
It will produce a db password string that is unique to your instance, note this is my password and yours will be different: 
com.rsa.db.root.password: YMax1EZ3yG 
4) From the command line, run the following command exactly, <dot> <space> <dot> <slash> rsaenv 
. ./rsaenv 
Then login to sqlplus using YOUR unique password where I have noted mine 
sqlplus sys/YMax1EZ3yG as sysdba 
5) you will be at the sqlplus prompt 
SQL> 
6) Note in this example, my group name is testgroup and it contains 3 users, cindy1, cindy2, cindy3. 
This is the query that will produce the list, the LOGINUID is on the top, and the group name below it. 
Note: you will need to substitute your groupname where I list testgroup. 
SQL> select ipd.loginuid,igd.name from rsa_rep.ims_principal_group ipg join rsa_rep.ims_principal_data ipd on ipd.id = ipg.principal_id join rsa_rep.ims_group_data igd on igd.id = ipg.group_id where igd.name='testgroup'; 
The resulting output will look like such: 
LOGINUID 
-------------------------------------------------------------------------------- 
NAME 
-------------------------------------------------------------------------------- 
cindy1 
testgroup 
cindy2 
testgroup 
cindy3 
testgroup 
To exit, type quit at the sqlplus prompt 
SQL>quit
Legacy Article IDa57241

Attachments

    Outcomes