Database Reference
In-Depth Information
The Create-Session Role
We will also create a simple role,
create_session_role
, which will only have one privilege:
CREATE
SESSION
. The
CREATE SESSION
privilege is required for a user to connect to an Oracle database.
Traditionally, this has been accomplished through a predefined role (it exists when you install Oracle)
named
CONNECT
. In current releases of Oracle database,
CONNECT
only has the one privilege. But in this
case, as with the
DBA
role, Oracle recommends that administrators create their own roles and not rely on
the predefined roles (like
CONNECT
). Do this as
SYS
:
CREATE ROLE create_session_role NOT IDENTIFIED;
GRANT CREATE SESSION TO create_session_role;
We will be granting this role to all users in the database. It would take the exact amount of effort to
grant the
CREATE SESSION
privilege to each user as it takes to grant
create_session_role
. However, there
is a benefit to roles as a centralization of privileges. For example, if we wanted to quickly give another
privilege to all users, we could grant it to the
create_session_role
; although, I don't recommend that.
More likely there might be a time when you need to keep the database running, but want to keep all
users from connecting to it. That can be done by a single command, revoking
CREATE_SESSION
from
create_session_role
.
NOT IDENTIFIED
I hope the
CREATE ROLE
syntax is fairly obvious. The only odd feature is the
NOT IDENTIFIED
keyword.
That merely indicates that when a user is acquiring this role, we do not have any password or encoded
process to verify his access to it. A role of this type has to be granted to a user (or to another role) by an
administrative command. Generally, roles that are designated as
NOT IDENTIFIED
are also default roles,
which a user acquires automatically when he connects to the Oracle database. This is the most usual
configuration of a role.
Using Roles
Most roles get created as default roles. When a user connects to an Oracle database, he acquires all his
default roles, and all the privileges associated with those roles. Any regular
NOT IDENTIFIED
role can be
set to be default or non-default.
Note
In Oracle Database 11g, default roles that are identified by a password are no longer acquired when a
user first connects—the user must enter the password. More on that later.
At any time, the Oracle user can acquire a role that has been granted by executing a SET ROLE
command. This is true for both default and non-default roles.
When we SET ROLE to a new role, it becomes the only role in use, even if other roles were in use
before. Fortunately, the session has already been created (we are already connected when the new role is
set), so we can stand to lose the
create_session_role
(which will happen). I can imagine a scenario
