Manually grant roles to users

Manually grant roles to users

  

Hi All,

I am trying to create an interface which can grant roles dynamically.

Currently we can create a role and grant it to the user, using the following function.

ex: GrantEPAdminRole

I would like to manually update the tables instead of using GrantEPAdminRole function.

1. Can I manually update only User_Role to achieve this? (Or do I need to update additional tables?)

2. Is there any function to get Role ID easily from the table? Ex: EPAdmin Role ID  = 124

3. What are User_Effective_Role and Activity_Def_Role tables and what are the uses of them?

(Is there a documentation for System tables/ERD?)

Please check the below ERD



Thanks in advance!

Sanoj



The User_effective_Role is the total roles that are assigned to the user via either the User_Role entity OR the Group_Role/Group_User entities. You should never attempt to change the User_Effective_role entity directly but it is useful if you want to display all the roles that a user has even if they are using role groups.

To add a role it is simply a matter of inserting into User_Role, the rest will take care of itself, check for duplicates first.

To get the role ID's from "Role" just do a normal aggregate on the entity, you will probably want to join to the espace entity to see where the role belongs as it is possible to have multiple roles with the same name inside different espaces.

One thing to remember, changing roles like this will not be recognised until the user logs out and back in again, don't expect it to be real-time.

User_Effective_Role is a database view, so you cannot modify it.

Just use User, Role, and User_Role entity to manually assign roles to user.


But if you want to assign Lifetime roles to Lifetime users (use the same Users entity with different tenant), then it can get really complex.

John Williams wrote:

The User_effective_Role is the total roles that are assigned to the user via either the User_Role entity OR the Group_Role/Group_User entities. You should never attempt to change the User_Effective_role entity directly but it is useful if you want to display all the roles that a user has even if they are using role groups.

To add a role it is simply a matter of inserting into User_Role, the rest will take care of itself, check for duplicates first.

To get the role ID's from "Role" just do a normal aggregate on the entity, you will probably want to join to the espace entity to see where the role belongs as it is possible to have multiple roles with the same name inside different espaces.

One thing to remember, changing roles like this will not be recognised until the user logs out and back in again, don't expect it to be real-time.

Hi John,

Thanks a lot for your reply. 

Could you please tell me how to properly filter the Role table for the correct role id (as you described "join to the espace entity to see where the role belongs as it is possible to have multiple roles")?

I tried to filter the Role table and found duplicated results.

Can I know the additional filter conditions I need to enter above to get the correct CCPORequestor ID?

Thanks a lot!

-Sanoj


Do a query on the espace table, you will see that it contains a list of every espace in your environment. You need to find the ID of the espace you are actually interested in and use that in your filter. In your screenshot see that the espace column has a different value for each record (362,382,384 etc.)

The espace being referred to here is the espace that the role actually resides in.

Hi Sanoj,

You need to join with the Espace entity, on the Role.Espace_Id attribute, and filter for the eSpace's name as well, not just the Role name.

If you want to filter only roles created by your app, you need to join to Espace entity, then filter Espace.Name = your espace name.

Then you can use output of this aggregate as source for your dropdown widget.


Hi Sanoj,

I think you need to join with the Espace entity .