Database Reference
In-Depth Information
wherein we would require every role to be granted CREATE SESSION privilege so that when you SET ROLE ,
you still have CREATE SESSION in the new role.
There is a way to add to existing roles, keeping those that exist. By issuing the command:
SET ROLE ALL;
This command cannot be used to set a role that is IDENTIFIED by a password or a procedure.
However, after you acquire a password-protected role or secure application role (we will discuss those in
a minute), you can SET ROLE ALL and restore your default roles as well.
I should mention that roles can be granted to roles, which we will do. Setting your role replaces
whatever you had before, but will include not only the role you set, but all the roles granted to that role,
and so on, recursively. There is a constraint to these cascading grants so that no endless loops can be
formed.
Password-Protected Roles
Let me preface this discussion with a note regarding Oracle 11g: starting with this version of Oracle, in
order to set password-protected roles from an active connection, you have to supply the password, even
if it's a default role. This may catch you off guard. Previously, if a role was protected by a password but it
was a default role, the user got that role by default when she connected.
I agree with this change to Oracle; and for the same reasons, we will not have any password-
protected roles in this topic. The only reason to have a password-protected role is in a case where the
role is granted to a user, but you don't want the user to use the role. You protect the role by setting a
password, and don't let the user know. But, we have to ask, “Why did we grant him the role in the first
place?”
A better approach is to only grant the role to the users who need it. Perhaps this is a bit muddied by
the possibility for many people to connect as a specific Oracle user, where some of those people need
access to the role and some don't. In that case, I recommend you either make the people who need the
role enter the role password, or, better yet, revisit your users and roles and sort them out.
One possibility is to have a secure application role, validated by a procedure. That role can be
granted based on user id or some group membership. Then no role password is needed. You just need to
maintain a list of acceptable users or a group membership list.
I have observed a corporate policy of securing every application role with a password. The idea is
that people sitting at a general SQL client, like SQL*Plus, will not have access to the role, because they
don't know the password. But from the application where we want them to have the role, a procedure is
called to set the role by looking up the role password in a database table. It may be that this
configuration provides a small impediment to casual Oracle users getting the application roles; however,
the bare facts to observe here are that the person who has been granted the role will have access to the
role either by that same procedure that the application calls, or by the same code that's in that
procedure. From SQL*Plus, the user may simply call the procedure, get the password, and set the role.
Perhaps the role could be protected from bald access by assuring that only a certain client
application is being used; again, this is a job for a secure application role. But the real problem here is
that a role has been granted to an Oracle user, and the administrator does not want the user to access
the role. Password-protected roles provide only the illusion of enhanced security, unless you make
specific users memorize and manually enter the password to set the role. That is the thinking behind the
new policy in Oracle 11g.
 
Search WWH ::




Custom Search