User role and permission queries

page owner: TFRS Developer

User, role and permission queries

List all users

select * from public.user;

List all roles

select * from public.role;

List all role permissions

select * from public.role_permission;

Find roles and permissions for a specific user

select u.id as user_id, u.username, u.first_name, u.last_name, ur.role_id, r.name as role_name, rp.permission_id, p.code as permission_code, p.name as permission_name from public.user u inner join user_role ur on u.id=ur.user_id inner join role r on ur.role_id=r.id inner join role_permission rp on r.id=rp.role_id inner join permission p on rp.permission_id=p.id where u.username='<user_name>';

Find role permissions for each role

select r.id as role_id, r.name as role_name , rp.id as permission_id, p.code as permission_code, p.name as permission_name from role_permission rp Inner join permission p on rp.permission_id=p.id Inner join role r on rp.role_id=r.id order by r.name;

Find users having multiple roles

select * from public.user u where u.id in (Select u.id from public.user u Inner join user_role ur on u.id=ur.user_id Group by u.id Having count(u.id)>1);

SQL Reference:

• Visual Representation of SQL Joins