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.

Note

In fact, that is the only way to create a "real schema"; the CREATE SCHEMA statement serves a somewhat different purpose. See Chapter 4 for more information on creating database objects.

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.

Table 12-1: Selected Microsoft SQL Server 2000 User Management
System-Stored Procedures: Stored Procedure	Description
sp_grantdbaccess <windows account name>, [<name inside database>]	Maps security account to one inside SQL server database, adding a user to the users list of the current database. The Windows account must be a valid account (<DOMAIN NAME>\<User Name>). When a second argument is not supplied, the Windows account name is used. If called from within an application, it returns 0 on success or 1 on failure. Only members of the SQL Server fixed role SYSADMIN have permission to execute this procedure.
sp_revokedbaccess <name inside database>	Removes the account mapping from the current database.
sp_adduser <windows account name>, [<name inside database>].[<group name>] sp_grantdbaccess	Adds user (security account) to the current database; Microsoft supplies this procedure for backward compatibility only and recommends using instead If called from within an application, returns 0 on success or 1 on failure.
sp_dropuser <name inside database>	Removes user (security account) from the current database. Microsoft supplies this procedure for backward compatibility only and recommend using sp_revokedbaccess instead.
sp_addlogin <login name>,[<password>], [<default database>], [<default language>], [<security identification number>], [<encryption option>]	Creates a new SQL Server login that allows a user to connect to the SQL Server using SQL Server authentication. Normally used for users connecting to the SQL Server over the network (i.e., they do not have a local account). All arguments, except the login name are optional. If called from within an application, returns 0 on success or 1 on failure.
sp_grantlogin <windows account name>	Grants SQL Windows account. If called from within an application, returns 0 on success or 1 on failure.Server access privileges to a valid
sp_revokelogin <windows account name>	Removes access privileges for a Windows account (either user or group). If called from within an application, returns 0 on success or 1 on failure. Note: This procedure revokes individual privileges; if a user whose login privileges were revoked is a member of a group that has these privileges, the user will still be able to connect.
sp_denylogin <windows account name>	Adds login to 'deny list'; the login for this user will be denied. If called from within an application, returns 0 on success or 1 on failure. Only members of sysadmin and securityadmin fixed SQL Server roles can execute this procedure.
sp_droplogin <windows account name>	Removes a login from the current database. If a login is mapped to a user, the user must first be removed using the sp_dropuser stored procedure; other restrictions also apply. If called from within an application, returns 0 on success or 1 on failure.

Note

The main difference between system-stored procedures and user-created procedures is their scope. The first are global, while the latter are usually local to the database in which they are created. Virtually all system-stored procedures are prefixed with sp_, and they all are located in the Master database.

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.