000031995 - Using an SQL statement to list administrators and their roles in RSA Authentication Manager 8.1

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

Article Content

Article Number000031995
Applies ToRSA Product Set : SecurID
RSA Product/Service Type : RSA Authentication Manager
RSA Version/Condition: 8.1 Service Pack 1
Platform : SUSE Enterprise Linux
O/S Version : 11 Service Pack 3
IssueAn administrator has a requirement to use an SQL statement to list the administrator's User ID and their roles in RSA Authentication Manager 8.1.
TasksGenerating a report listing administrators and their roles for all identity sources at the command line.
  1. Use the Developer Guide (referring to the section called 'SQL Access to the RSA Authentication Manager Database') and use the procedure to create a read-only user (e.g. rouser) for the authentication manager database.
Example:
rsaadmin@am81p:/opt/rsa/am/utils> ./rsautil manage-readonly-dbusers -a create -o ocadmin -u mbell -i 192.168.41.234 -n 255.255.255.0
Enter Operations Console (OC) password: ***********
Enter password for the read-only database user: ***********
Confirm password for the read-only database user: ***********
Executing action: 'create'.
Trusted Root SSL CA certificate was copied in file '/opt/rsa/am/utils/RSAAMTrustedRootSSLCA.crt'.
'create' action complete.
rsaadmin@am81p:/opt/rsa/am/utils>


  1. Where SSH is enabled or by using the local console, logon as rsaadmin account using the operating system password specified during Quick Setup.
  2. Type cd /opt/rsa/am/utils folder, and press ENTER
  3. Use an editor (such as vi) to create a new file called admin_report.sql, then copy the SQL statement into admin_report.sql and save the file.
SQL statement (also attached):

select ipd.LOGINUID,ipd.IDENTITY_SRC_KEY,iis.NAME,iar.NAME from RSA_REP.IMS_PRINCIPAL_DATA ipd inner join RSA_REP.IMS_IDENTITY_SOURCE iis on iis.ID = ipd.IDENTITY_SRC_ID inner join RSA_REP.IMS_PRINCIPAL_ADMIN_ROLE ipar on ipar.PRINCIPAL_ID = ipd.ID inner join RSA_REP.IMS_ADMIN_ROLE iar on iar.ID = ipar.ADMIN_ROLE_ID;

  1. Use admin_report.sql to generate a report called admin_report.txt with the command below.
    /opt/rsa/am/pgsql/bin/psql -h localhost -p 7050 -d db -U rouser -A -F , -X -t -f admin_report.sql -o admin_report.txt

  2. Check the current folder for the admin_report.txt file and check the contents of the file using the following command:
    more admin_report.txt

ResolutionThe rsa-am-extras-8.1.0.0.0.zip file part of the RSA Authentication Manager 8.1 product download contains the Developer Guide which provides information on SQL access to the RSA Authentication Manager database.
In short, the administrator is required to create a read-only user to access the authentication manager database, create an SQL script file with the SQL statement at the command line and run an rsautil command at the command line to generate the report. Refer to the tasks section for the steps. 
Alternatively, an administrator could write an SQL application that runs the SQL statement on a remote workstation using the samples provided in the Developer Guide.
NotesWhere customers have a requirement to generate reports where the reporting templates in the Security Console do not meet their requirements then the customer can engage RSA Professional Services to see if writing an SQL script will help meet the requirement.
Customers can contact RSA Professional Services through RSA Sales; use URL http://www.emc.com/domains/rsa/index.htm, change the region at the top of the page and select contact us.

Outcomes