Big news! The community will be moving to a new platform April 21. Read more.
Big news! The community will be moving to a new platform April 21. Read more.

How-To: Find all users that have a privilege

How-To: Find all users that have a privilege

In SBM, it is sometimes necessary to find out all users that have a specific privilege. This can be done easily by directly querying the database.  I referenced the SBM Schema guide for this information.

Privileges are stored in the TS_PRIVILEGES table.  A user will have multiple records depending privilege type.  To see what all the privilege types mean, please reference the SBM Schema guide.  Use the steps below to determine how to identify users that have a given privilege.

WARNING: If you are using seat licenses, make sure that you have an available license before using this method.

  1. Create a new user and assign neither groups nor privileges.  
  2. Run the following query.  There should be no records returned for the user.

    select u.ts_id, u.TS_LOGINID, p.*
    from TS_PRIVILEGES p
    join TS_USERS u on p.TS_USERID=u.TS_ID
    where u.TS_LOGINID='userLoginID'
     
  3. Next, use the SBM Application Administrator (web admin) to add the privilege that you are trying to identify.
  4. Run the following query.  Note the TS_TYPE and the TS_MASK#.  This represents the privilege just added.

    select u.ts_id, u.TS_LOGINID,  p.*
    from TS_PRIVILEGES p
    join TS_USERS u on p.TS_USERID=u.TS_ID
    where u.TS_LOGINID='userLoginID'

    You will see the following values for a TS_TYPE and one of the MASK fields (TS_MASK1, TS_MASK2, TS_MASK3, TS_MASK4, TS_MASK5)
     
  5. Create the below query using the values from above.  This will display all users that have the privilege.  Replace the TS_MASK# field reference below with name of the field that has a value in your system.

    select u.ts_id, u.TS_LOGINID, p.*
    from TS_PRIVILEGES p
    join TS_USERS u on p.TS_USERID=u.TS_ID
    where TS_TYPE= and TS_MASK# & =
Labels (1)

DISCLAIMER:

Some content on Community Tips & Information pages is not officially supported by Micro Focus. Please refer to our Terms of Use for more detail.
Comments
This is really cool - the simplicity is almost elegant. Love it! Thanks, David!
It should be noted that this will only find privileges directly granted to that user and does not include any privileges they might have been granted from groups or roles.
 
 
Top Contributors
Version history
Revision #:
2 of 2
Last update:
‎2020-01-30 18:16
Updated by:
 
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.