Default Role issues

larrimorem1

Seems i'm not allowed to create a default role for a user that already exist in the database.

select user_name,all_roles,default_roles from userswhere default_roles ilike 'business_user_claims';

 

USER_NAME | ALL_ROLES | DEFAULT_ROLES
-----------+----------------------+----------------------
<username> | business_user_claims | business_user_claims

 

 

I'm trying to add the business_user_claims role as a default role for another user:

select user_name,all_roles,default_roles from users;

 

USER_NAME | ALL_ROLES | DEFAULT_ROLES
-----------+-------------+---------------
<username> | claims_read | claims_read

 

I tried running both the statement below:

ALTER USER <username>DEFAULT ROLE business_user_claims;

ALTER USER <username>DEFAULT ROLE business_user_claims,claims_read

 

and got the following error:

 

WARNING 4613: Role "business_user_claims" cannot be set as default
WARNING 4088: No new valid default roles specified. Retaining previous set of default roles for user <username>

 

 

What am I missing, as you can clearly see that role can be granted as a default role

K_Krutiy

 

check if user been granted access to role.

 

 

GRANT readonly_ROLE TO my_USER;
ALTER USER my_USER DEFAULT ROLE readonly_ROLE;

larrimorem1

 

Thank you that worked