They do not apply to sessions created by
Oracle or to recursive sessions. Sessions that connect through external
multiplexing software are not counted separately by the Oracle licensing
mechanism, although each contributes individually to the Oracle license total.
The DBA is responsible for taking these sessions into account.
Named User Licensing
In named user licensing, the license specifies a number of named users, where a named user is an individual who is authorized to use Oracle on the specified computer. No limit is set on the number of sessions each user can have
concurrently, or on the number of concurrent sessions for the database.
Named user licensing allows a DBA to set a limit on the number of users that
are defined in a database, including users connected via database links. Once
this limit is reached, no one can create a new user. This mechanism assumes
that each person accessing the database has a unique user name in the
database and that no two (or more) people share a user name.
Controlling Database Access 24-15
The DBA can set this limit in the database’s parameter file so that it takes effect when the instance starts and can change it while the instance is running (using the ALTER SYSTEM command). The latter is useful for databases that cannot
be taken offline.
If multiple instances connect to the same database with the Parallel Server, all instances connected to the same database should have the same named user
limit. See Oracle8 Parallel Server Concepts & Administration for more information.
24-16 Oracle8 Server Concepts
C H A P T E R
25 Privileges and Roles
My right and my privilege to stand here before you has been won —
won in my lifetime — by the blood and the sweat of the innocent.
Jesse Jackson: Speech at the Democratic National Convention, 1988
This chapter explains how you can control users’ ability to execute system
operations and to access schema objects by using privileges and roles. The
chapter includes:
•
Privileges
•
Roles
If you are using Trusted Oracle, see the Trusted Oracle Server Administrator’s Guide for information about roles and privileges in that environment.
Privileges and Roles 25-1
Privileges
A privilege is a right to execute a particular type of SQL statement or to access another user’s object. Some examples of privileges include the right to
•
connect to the database (create a session)
•
create a table
•
select rows from another user’s table
•
execute another user’s stored procedure
Complete listings of all system and object privileges, as well as instructions for privilege management, appear in the Oracle8 Server Administrator’s Guide.
You grant privileges to users so these users can accomplish tasks required for their job. You should grant a privilege only to a user who absolutely requires the privilege to accomplish necessary work. Excessive granting of unnecessary
privileges can compromise security. A user can receive a privilege in two
different ways:
•
You can grant privileges to users explicitly. For example, you can
explicitly grant the privilege to insert records into the EMP table to the
user SCOTT.
•
You can also grant privileges to a role (a named group of privileges), and
then grant the role to one or more users. For example, you can grant the
privileges to select, insert, update, and delete records from the EMP
table to the role named CLERK, which in turn you can grant to the users
SCOTT and BRIAN.
Because roles allow for easier and better management of privileges, you
should normally grant privileges to roles and not to specific users.
There are two distinct categories of privileges:
•
system privileges
•
schema object privileges
System Privileges
A system privilege is the right to perform a particular action, or to perform
an action on any objects of a particular type. For example, the privileges to
create tablespaces and to delete the rows of any table in a database are system privileges. There are over 60 distinct system privileges.
25-2 Oracle8 Server Concepts
Note: Usually, you should grant system privileges only to administrative personnel and application developers, because end users normally do not
require the associated capabilities.
Granting and Revoking System Privileges
You can grant or revoke system privileges to users and roles. If you grant
system privileges to roles, you can use the roles to manage system privileges
(for example, roles permit privileges to be made selectively available).
System privileges are granted to or revoked from users and roles using either
of the following:
•
the Users or the Roles folders of Server Manager
•
the SQL commands GRANT and REVOKE
Who Can Grant or Revoke System Privileges?
Only users who have been granted a specific system privilege with the
ADMIN OPTION or users with the GRANT ANY PRIVILEGE system
privilege (typically database or security administrators) can grant or revoke
system privileges to other users.
Schema Object Privileges