How-To: Find all users that have a privilege

[#1]

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# & =

[jonesgf]

This is really cool - the simplicity is almost elegant. Love it! Thanks, David!

[lmattie]

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.