Roles in Oracle manage privileges efficiently. Let’s dive into each option:
A. Roles must be password protected.
Explanation:False. Roles can be password-protected (e.g., CREATE ROLE mgr IDENTIFIED BY secret), but it’s optional. Non-protected roles (default) are enabled automatically if granted, requiring no password.
Mechanics:Password-protected roles need SET ROLE mgr IDENTIFIED BY secret, enhancing security for sensitive privileges.
B. Roles may be granted to other roles.
Explanation:True. Roles can form hierarchies (e.g., GRANT clerk TO mgr), allowing nested privilege management.
Mechanics:A user with mgr inherits clerk privileges indirectly. Revoking clerk from mgr cascades appropriately.
Practical Use:Simplifies complex privilege structures in large organizations.
C. The SET ROLE statement can enable one or more roles for a session.
Explanation:True. SET ROLE role1, role2; activates specified roles for the session, assuming they’re granted and not password-protected (or password is provided).
Mechanics:Enabled roles grant their privileges immediately within the session scope.
D. Object privileges may not be granted to roles.
Explanation:False. Object privileges (e.g., GRANT SELECT ON emp TO clerk) are a primary use of roles, making this statement incorrect.
Why Incorrect:Roles are designed for this purpose, contradicting the option.
E. All roles granted to a user are set on default when the user logs in.
Explanation:False. Only roles marked as DEFAULT ROLE (via ALTER USER ... DEFAULT ROLE role1) are enabled at login. Non-default roles require SET ROLE.
Mechanics:Check via SELECT * FROM DBA_ROLE_PRIVS WHERE DEFAULT_ROLE='YES'.
F. The SET ROLE statement can disable one or more roles for a session.
Explanation:True. SET ROLE NONE disables all roles, or SET ROLE role1 implicitly disables others not listed, providing granular control.
Practical Use:Useful for testing or restricting privileges temporarily.
