SQL Bible [Electronic resources] نسخه متنی

Managing Security with Roles

ROLE is an
abstract concept introduced in the relational databases to facilitate user
management tasks by grouping users'' privileges according to some criteria,
usually a job function. If, for example, your accounting staff of 100 people
needs privileges for the dozens of objects they access daily — in addition to
some system-level privileges — you have two choices: go through each and every
user and individually grant him/her all the privileges required; or create a
group (role), such as
ACCOUNTANTS, grant all the privileges to
the role, and thus grant this role to all the users in the group. Revoking the
privileges would pose the same choices. It seems fairly obvious which choice is
better.

Some RDBMS provide roles-creating
capabilities, in addition to having a number of predefined system roles that
could be granted to a user. Oracle 9i and the Microsoft
SQL Server 2000 have this feature, while DB2 UDB employs only fixed, predefined
roles (authorities).

The
Oracle 9i roles are collections of privileges that
could be granted to (or revoked from) a user or another role, thus providing a
hierarchy of privileges. A role must be enabled (with a
SET
ROLE statement or by the database
administrator) before it can pass on all the privileges granted to it.

Oracle 9i has a
number of predefined roles through which privileges are granted to users.
Table
12-8 shows these with short descriptions.

You neither can add new privileges to a
predefined role, nor can you revoke any from the role.

The following Oracle syntax, which creates
a custom role, is straightforward:

CREATE ROLE <role name>
[IDENTIFIED {BY <password> | EXTERNALLY | GLOBALLY | USING
[<schema>].[<package>] } ];

Only the role name is a required argument
for this statement, the rest is optional. If the role is to have its own
password, then the
IDENTIFIED clause must be used. The
NOT
IDENTIFIED clause indicates that no
password is required to enable it.

The Microsoft-specific
DENY statement adds an additional level
of granularity to the system of privileges not found in Oracle or DB2 UDB. The
SQL92/99 standard does not define such a statement.

If you are using roles to assign a set
of privileges to a user, you might find yourself in a situation when this set
should be modified for one and only one user, while preserving it for all other
users to whom this role was granted. Attempting to revoke a privilege from the
user that was granted through a role will result in an error. You could create
another role and grant a subset of privileges from the first role and then
grant it to a user; or you could grant these privileges to the user directly,
bypassing the role. Any of these, while workable, would lead to redundancy and
possible confusion. The
DENY statement provides an elegant
solution to the problem. It allows you to grant a role with a full set of
privileges and then deny some of these privileges to a user.

The following
DENY statement works both for
system-level and object-level privileges, with the syntax very similar to that
of
GRANT and
REVOKE statements:

DENY ALL [PRIVILEGES] |
<permission>,... [(<column>)] ON [<table> | <view>] |
ON [<table> | <view>] [(<column>,...)] | ON
[<stored_procedure> | <extended_procedure>] | ON
[<user_defined_function>] TO <security_account>,...
[CASCADE]

The
CASCADE clause means that a privilege
(permission) denied to the
security_account will also be denied to
every other user/role/group to that has this
security_account permission.

For example, you have a role
SALES that has all the privileges in the
database, and a user
NEW_USER needs all of these, except for
CREATE
TABLE. The following statements take care
of granting the appropriate privileges to the user:

GRANT sales TO new_user GO
DENY CREATE TABLE TO new_user GO

The
DENY statement always takes precedence,
so while having the inherited privileges of the
SALES role, the user
NEW_USER will get an error if s/he ever
attempts to create a table, though other members granted
SALES role will have no problems doing
so.

For identified users, the
IDENTIFIED
BY <password> clause actually creates a local user with this
password if none previously existed; a password needs to be specified when
enabling the role. The
USING [<schema>].[<package>] clause creates an application role (compared
to that in the MS SQL Server), which means that the role can be enabled by that
specific application using authorized package.

Using the
EXTERNALLY clause creates an external user
(see OS Security integration sidebar later in the chapter), and the
GLOBALLY clause creates a global user,
authorized by the enterprise directory service.

Here is an example of a role created for
the ACME database with a minimal set of default options:

CREATE ROLE sales_force; Role
created.

Now you can grant privileges to this role
(see
GRANT statement paragraph earlier in this
chapter for more information), and later grant the privileges to everyone who
needs them by assigning those people to the
sales_force role.

A custom role can be altered or dropped by
using, respectively, the
ALTER
ROLE or
DROP
ROLE statements.

Any user that accesses the
IBM DB2 UDB database must have a valid OS
account. Once authenticated, the user''s access to the database''s objects is
governed by a system of authorities (roles) and privileges inside the RDBMS.
DB2 does not support user-defined roles. Instead it relies on the system''s
predefined authorities (roles), which a user can be a member of, and on
GROUP, which behave almost the same as
roles that are employed in Oracle or the MS SQL Server 2000.

System authorities (roles) include system
administration (SYSADM), system control (SYSCTRL), system maintenance (SYSMAINT), and database administration (DBADM), listed in
Table
12-9. Each of these roles implies certain privileges; certain types of
privileges are automatically granted to every user authenticated by the
OS.

A privilege in DB2 UDB is defined as
permission for the authenticated user to access and use database resources such
as tables, views, and stored procedures, which will be discussed in greater
detail later in the chapter.

Most of IBM DB2 UDB privileges — both on
system and object levels — granted to the authorities (Table 12-9) are listed
in
Table
12-6.

The highest authority level belongs to
SYSADM, which has full control over all
database objects, as well as the DB2 UDB installation that contains this
database. It defaults to the Administrators Group on Windows NT/2000/XP; on
Unix the initial value is
NULL and defaults to the primary group of
the database instance owner.

The
SYSCTRL and
SYSMAINT roles represent a lower level of
hierarchy, followed by
DBADM. Users that do not belong to any of
these roles are granted privileges on an object-by-object basis. A new group
can be created by
DBADM, and privileges could then be granted
to this group and to users in this group. This is a handy way to administer
privileges for a number of users with similar responsibilities within the
database, and is similar in functionality to the roles in Oracle and the
Microsoft SQL Server.

For the syntax of granting system
authorities, refer to the
GRANT statement section of this
chapter.

On the authentication level, the
MS SQL Server 2000 offers two choices:
Windows OS authentication and mixed authentication modes. The first is usually
rated as the better security provider, because it relies on the more robust
security mechanisms of the operating system, and — for accessing the database
over the network — it does not require sending login information unprotected.
The second offers a more "personal approach" that allows many users to connect
to the same SQL Server without needing to be added to the Windows users group.
Microsoft defines a special system administrator user — usually sa,
which is the default — who is responsible for all administrative
tasks within the MS SQL Server 2000.

Authorization is performed by the SQL
Server itself. All information about the user''s granted permissions is recorded
in the server''s system tables (partially accessible through
INFORMATION_SCHEMA views). Like IBM DB2 UDB
and Oracle, the Microsoft SQL Server enforces security through a hierarchical
system of users implemented via fixed roles and
application roles (the concept of a role is explained at the
beginning of this chapter).

It is important to remember that the
operating system (OS) was there before the first computer database was
invented, and no RDBMS could operate without some kind of OS. Each operating
system comes with its own security mechanisms. All the RDBMS discussed in this
book — to a certain extent — provide security integration with the OS they are
running on. Essentially, it boils down to using operating system accounts and
privileges to access the database, instead of relying on the RDBMS
itself.

The Microsoft SQL Server 2000, for
example, has tight OS-integrated security, which allows users with a valid
Windows account to be authenticated based on their Windows NT/2000/XP
credentials. Instead of supplying user ID and password, the user is able to
access the SQL Server 2000 automatically as soon as s/he logs onto the machine
that runs RDBMS.

Both Oracle 9i and
IBM DB2 UDB extend this functionality to multiple operating systems, and the
details of implementation and usage are just as different as the systems they
integrate with.

DB2 UDB uses an external facility for
user authentication — either the operating system, or a so-called
distributed computing environment (DCE) facility. A user
must have a valid OS account — login and user ID — in order to access the
database.

For regular users to be authenticated
externally, an Oracle database administrator must create a user account
prefixed with a character value from the Oracle initialization parameter
OS_AUTHENT_PREFIX and marked as
AUTHENTICATED
EXTERNALLY. Oracle uses the value of the
parameter
OS_AUTHENT_PREFIX to check whether any of
the users'' names created within the database are prefixed with this value if a
user name does not have this prefix, it will not be allowed to be authenticated
externally. This is only a brief description of the basic mechanism implemented
by Oracle, refer to the vendor''s documentation for more information on this
issue.

For
EXTERNALLY authenticated users that
intend to administer a database, Oracle 9i requires that
two predefined
OSDBA and
OSOPER groups be created on the machine
that is running the operating system, and make these users members of one of
these groups. In addition, the Oracle start-up parameter
REMOTE_LOGIN_PASSWORDFILE has to be set
to one of its valid values.

RDBMS running on some operating systems
(notably, Windows 9x) do not have OS security integration, as the OS itself
does not provide facilities for this.

Fixed server roles provide a server-wide
scope hierarchy where each role is allowed to perform certain activities,
SYSADMIN being on the top, and having
privileges to perform any activity. These roles are listed in
Table
12-10.

A fixed role cannot be altered, and new
fixed server roles cannot be created. You may add new members to a role, or
remove members from the role using SQL Server system-stored procedures, or
through graphical user interface (GUI).

The next level in the SQL Server 2000''s
security hierarchy is fixed database roles, shown in
Table
12-11. Each database defined within an SQL Server instance has a set of
predefined (fixed) database roles to which any of the database users (logins)
can be added. The scope of these roles is much more limited — they are confined
to the database within which they are declared. As with the fixed server roles,
no permissions can be altered for these roles, but new database roles can be
created (unlike fixed server roles).

Application roles are unique to the SQL
Server 2000 (and Oracle 9i with some specifics). They are
activated only by the application that accesses RDBMS; there are no predefined
application roles. In a way, they just provide another method to manage group
permissions — if users always connect to the database server through some
accounting program, the SQL Server DBA can create a role for that accounting
program and assign all the privileges it needs for normal functioning. When the
SQL Server receives a request from the accounting program, it activates the
role for this application — no sooner, no later. If the application is phased
out and replaced by a new one, all the DBA must do to prevent access from the
previous application is to drop an associated application role.

Application roles contain no members, and
there are no predefined application roles. There is much more to application
roles than described here. If you need to use them, refer to SQL Server (and
Oracle) documentation.

As with other RDBMS, permissions
(privileges) can be assigned at the object level, for example, in a table or
stored procedure — all the way down to a column. A column is the smallest
object for which a user may have privilege. In addition to object granularity,
privileges can be differentiated by type —
EXECUTE privilege,
SELECT privilege,
DELETE privilege, and so on, in any
combination. The privileges — both on system and object levels — are discussed
earlier in this chapter.

The SQL Server does not have the
CREATE
ROLE statement; it employs system-stored
procedures instead. The list of some relevant procedures is given in
Table
12-12.