DBA ROLES

[admala]

Hi,
Can any body please help me what is the DEFAULT_ROLE does in DBA_ROLE_PRIVS.

IF it set to YES means what?
IF it set to NO means what?

Thanks in advance,

[jmodic]

If YES, it means that this role is granted to user as a default role.
If NO, it means that this role is granted to user, but not as a default role.


I guess the above explanation does not help much, does it? So the right question would be, what is a default role and what is nondefault role.

A default reole is a role that is enabled automaticaly after you log in. On the other hand, a non-default role is a role that has been granted to you, but will not be enabled automaticaly when you log in. If you want to activate it, you have to use SQL command "SET ROLE ....".

[steeve123]

Why then i create a new user, i grant a role to that user and he has that role as default. And another user that have only that role the default_role is set to NO.

user a:
has 1 role with default_role = yes

userb:
has the same role with default_role = no

i revoke the role and regrant it and the default_role
is still to no.

I want it to be YES, cuse the user b cant see certain object because of that.

[jmodic]

GRANT the_role TO userB;

Then isue:

ALTER userB DEFAULT ROLE ALL;
-- All granted roles will be his default roles

or

ALTER userB DEFAULT ROLE the_role;
-- Only THE_ROLE will be his default role, all other roles will be non-default roles

or

ALTER userB DEFAULT ROLE the_role, another_role, ....;
-- Only the listed will be his default roles, all other roles will be non-default roles

or

ALTER userB DEFAULT ROLE ALL EXCEPT another_role;
--All roles except ANOTHER_ROLE will be his default roles; 
--ANOTHER_ROLE will be his non-default role