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

Defining a Database User

The concept of the
USER, while being plain and simple in an
intuitive layman way, is one of the most confusing across the RDBMS
implementations. SQL99 does not specify any special syntax (or even a way) to
create a user in the database. Left to their own devices, the database vendors
have managed to create some ingenious solutions. For example, Oracle
9i makes little distinction between a user and the
database schema, IDB DB2 UDB only uses operating system-defined users (or those
defined by some external framework), and the Microsoft SQL Server combines both
approaches, using Windows accounts and special system procedures for adding
users to a database.

By definition, a database user is someone
who makes use of the services provided by the RDBMS server. It could be an
application, a database administrator, or just anyone who happens to access the
database at any given moment. User authentication, as we've mentioned before,
is the first line of defense when it comes to security issues.

In Oracle
9i, the syntax for creating a user is as follows:

CREATE USER <user_name>
IDENTIFIED BY [<password> | EXTERNALLY] [DEFAULT TABLESPACE
<def_tablespace_name>] [TEMPORARY TABLESPACE <tmp_tablespace_name>]
[QUOTA (<integer> (K | M) ON <tablespace_name>],... [PROFILE
<profile_name>] [PASSWORD EXPIRE] [ACCOUNT [LOCK |
UNLOCK]];

In addition to the
CREATE
USER privilege (explained later in the
chapter), to create a user, you need, at the very least, to specify user name
and password, since most of the clauses in the statement are optional. To
create a user identified by the operating system (OS), use the keyword
EXTERNALLY. For more information on OS
integration, see the sidebar "OS Security Integration" later in the
chapter.

The other options are Oracle-specific, and
not found in other RDBMS implementations. For example, by specifying the
PASSWORD
EXPIRE clause, you are essentially telling
Oracle that the password for this user expires immediately, forcing the user to
select a different password on the next connection to the database.
ACCOUNT
LOCK allows you to specify, for example,
that access is to be blocked after so many unsuccessful attempts (precluding
password guessing), or disable user's account when, say, employee leaves the
company. This is a security measure that prevents all "inactive" users from
logging onto the database. A detailed discussion of all the Oracle-specific
options is well beyond the scope of this book.

Here is a very simple version of the
CREATE
USER statement:

CREATE USER new_user IDENTIFIED
BY it_is_me; User created.

By omitting all the optional clauses, this
statement creates a user that has all the system defaults assigned: it uses
system tablespace, his/her password never expires, and so on. Needless to say,
this practice has no place in a real, live production environment. In addition
to being less secure, it is also very inefficient.

The
PROFILE clause assigns an environmental
user profile that governs the user's resource access by setting limits the user
cannot exceed. If no profile is specified, then a default profile is used. To
create a profile, you must assign the
CREATE
PROFILE privilege — either directly or
through a role (explained later in the chapter).

A profile is created with the
CREATE
PROFILE statement. The syntax is
relatively simple:

CREATE PROFILE <profile
name> LIMIT [RESOURCE PARAMETERS | PASSWORD PARAMETERS ];

Resource parameters could be set to
integer
UNLIMITED, or
DEFAULT. The first explicitly specifies
number of days, retries, and so on; the second removes the limit; and
DEFAULT tells Oracle to use its default
value, which is set in the system as part of the default profile (assigned to
any user for whom no explicit profile was specified).

There are a number of parameters used
here, most of which require some Oracle knowledge. Many of these are directly
related to security. For example, the resource parameter
SESSIONS_PER_USER limits the number of
concurrent sessions allowed for this user ID; when the number is exceeded, the
logon is denied. Another resource parameter —
CONNECT_TIME — sets a limit for the
connection time; yet another,
IDLE_TIME, tells Oracle exactly how long
a particular connection is allowed to remain idle, terminating the connection
once the limit is reached. Of course, there are more resource parameters used
for allocating system resources to a user, which you will find in the Oracle
documentation.

Among password parameters, virtually
every one falls into the security domain. Parameters like
FAILED_LOGIN_ATTEMPTS,
PASSWORD_LIFE_TIME,
PASSWORD_REUSE_TIME,
PASSWORD_REUSE_MAX,
PASSWORD_LOCK_TIME, and
PASSWORD_GRACE_TIME all define how
passwords are created, used, and expired. The names of the parameters describe
their functions. The first,
FAILED_LOGIN_ATTEMPTS, guards against
guessing the password and locks an account once the allowed number is reached.
The
PASSWORD_REUSE_MAX parameter specifies
number of password changes before the current password is reused, and so on.
One of the parameters (PASSWORD_VERIFY_FUNCTION)
allows for using custom logic, usually implemented as PL/SQL script, for
additional password verification, logging, and so on.

When a user is created, a schema
corresponding to that user is also created. The newly created user has no
rights or privileges within RDBMS, but must at least be granted the
CONNECT privilege to be able to connect to
the Oracle database and the set of privileges assigned on "as needed" basis
(privileges and the
GRANT statement syntax are discussed later
in this chapter). After these privileges have been granted, the user
ACME might access database
ACME using password
ACME, and perform actions based on the
privileges granted.

Of course, as with almost any Oracle
database object, a
USER could be either dropped or altered
later on. Here is the
DROP syntax:

DROP USER <user name>
[CASCADE];

CASCADE
specifies that all objects in the user's schema should be dropped before
dropping the user; Oracle does not allow dropping a user whose schema contains
any objects, unless this clause is specified.

The
ALTER
USER statement specifies a number of
changes for a user. It assigns a different profile, changes the resources
limit, changes the default tablespace, and much more. It also provides a
convenient way to modify a user's properties without the need to drop the user
and then recreate it from scratch. For complete syntax of the
ALTER
USER statement, refer to Oracle
documentation.

Information about users in the Oracle
database is accessible through the system catalogs, discussed in detail in
Chapter
13.

IBM DB2 UDB
uses a combination of external security services and internal access controls.
The first step in RDBMS security is authentication. IBM DB2 UDB does not
provide authentication services, relying instead on external services, which
could be implemented within the operating system, or as a third-party software
product (especially in the case where the database is installed as part of a
distributed environment).

This means that a user cannot be created
unless s/he also has an operating system account, be it Windows, Unix, or any
other OS. As such, all user authentication is managed outside of the DB2 UDB
database. For more information, refer to the OS Security Integration section
later in this chapter, or to IBM DB2 UDB documentation.

Microsoft SQL Server
2000 can be configured for user authentication either through the
Windows NT/2000 operating system, or through Mixed Mode authentication.

In the first case, the user must belong to
some predefined Windows account that allows logon to the SQL Server database
with his/her system login, which must be mapped to the internal SQL Server
account. Therefore, the OS login must be mapped to an SQL Server login in each
and every database within it that this login is allowed to access.

The second case — Mixed Mode — requires the
user to (1) have a valid Windows account to establish connection to the SQL
Server and (2) supply a user ID/password to be authenticated by the SQL
Server.

The Microsoft SQL Server does not have the
CREATE
USER statement. Instead it uses several
system-stored procedures, which are special precompiled
routines stored within the database server, usually written in Transact-SQL.
The stored procedures can be executed from ISQL/OSQL command-line utilities, or
from the visual interface of the Microsoft SQL Server Query Analyzer (see
Appendix
E for more information on accessing RDBMS).

Table 12-1 lists stored procedures utilized by
user management within the SQL Server. The arguments needed to run these stored
procedures are by and large self-describing. If you are new to the MS SQL
Server, consult SQL Server Books Online (included with every installation of
the RDBMS), as there are some restrictions applicable to the use of these
procedures.

To execute any of the above stored
procedures, either from ISQL/OSQL or the Query Analyzer, type in

EXEC <stored procedure
name> (arg1,arg2,...,argN)

The information about users in the
Microsoft SQL Server is accessible in the system catalogs (INFORMATION_SCHEMA), discussed in detail in
Chapter
13, as well as in the system-stored procedures.