search cancel

Where are the User Roles saved in DB?

book

Article ID: 244424

calendar_today

Updated On:

Products

CA Performance Management - Usage and Administration

Issue/Introduction

I just checked the NETQOSPORTAL DB and found something confusing me.

While trying to pull a List of all Users and Roles in our system, I found that some of the Roles are stored in  bind_roles Table but newer Roles are not present there.

The latest roles are listed in role_definitions while earlier Roles are listed with NULL there. Beside that there are still a few Roles that are in none of these tables.

Still missing definitions for some roles.

Why are the Roles stored in different tables?

Where Can I find the missing Roles?

 

 

Environment

Release : 21.2

Component : PM Web UI Administration/Configuration

Cause

Users and roles are stored across several tables.

 

The "role_definitions" table defines the roles. 

The "role_access_rights" contains the role rights for a role.

"bind_roles" are what NFA probably sent up during initial add to PC. 

We try and consolidate those to PC roles.

NFA sends up additional access rights that can be added to roles under NFA section in Role editor.

You can check the "role_definitions_i18n" table for the real role names if they are OOTB or synced up with localization names.

Resolution

To get all users from the mysql db on console:

 

select rd.roleid, IFNULL(rd.name,rdi.name) as 'Role Name', IFNULL(rd.description,rdi.description) as 'Role Desc', rd.tenantid, t.itemname from role_definitions rd left join role_definitions_i18n rdi on rd.roleid=rdi.roleid and rdi.cultureid='en-US' inner join t_tenant t on rd.tenantid=t.itemid order by tenantid,roleid;