Oracle Syntax
Users and authentication rules are defined in the database using the CREATE USER and ALTER USER SQL statements. There is some difference in the available syntax for these two commands, but we will
attempt to cover all of the syntax options. Following is a demonstration of some of the basic capabilities
for defining users to the database:
create user joe identified by newpass1;
create user cam identified by origpass password expire;
create user ops$mary identified externally;
create user chris identified globally as ‘CN=chris,O=xyzcorp’;
create user app_proxy_user identified by app_serv_pw;
alter user joe password expire;
alter user cam identified by newpass3 replace origpass;
alter user ops$mary account lock;
alter user chris grant connect through app_proxy_user
authentication required;
In this series of commands, you see the creation of five new database user accounts. The first, “joe,” is cre-
ated with a password that will be used for authentication. The second, “cam,” is given a password also,but it is set to expire so that it will have to be reset by the administrator or “cam” before the account can
be used to connect to the database. The “ops$mary” account will be authenticated using an external
mechanism (the operating system) rather than by supplying a password; the O/S user “mary” will be
able to connect directly to the “ops$mary” database account. User “chris” will be authenticated through
the use of an Lightweight Directory Access Protocol (LDAP) directory, the topic of another section later in
this chapter. The final account being created is going to be a proxy account used by an application server
to be able to maintain a connection and share it among various application users. This is different than
simply having the application server use a single account for multiple end user transactions in that the
database will be able to track the individual users who are proxied. This is useful for maintaining different
authorizations for individual users, for applying virtual private database policies, or for proxy auditing,
discussed later in this chapter. The fundamental purpose of using proxy users is the ability to preserve the
actual user identity even though the actual connection is made through a “generic” user connection.
(The ops$ prefix is a default used for external authorization through the operating system but it can be
modified by setting the OS_AUTHENT_PREFIX initialization parameter.)
The code example continues by showing five examples of altering existing user accounts to change
authentication rules. First the password for the “joe” account is explicitly expired by the DBA (although
you’ll see later how to force periodic password expiration automatically.) Next, the “cam” password is
explicitly changed. This can be done by the DBA or by cam directly if cam has the alter user privilege.
(The extra clause shown here of REPLACE current_password is only needed when a user is changing
his or her own password and a password complexity routine has been specified by the administrator.)
The third change shown locks the “ops$mary” account so no logins will be allowed.
The final example alters the “chris” account to allow connections for this account to be made using the
shared “app_proxy_user” account that was created for this purpose. (This example shows the syntax
from Oracle 10g; slightly different syntax for proxy authentication was available in release 9i.) You will
see how such proxy accounts are used later in the chapter.
We mentioned that password complexity routines can be defined by the DBAto ensure that users specify
passwords that meet certain minimum standards for complexity to reduce the chance of someone guessing
their password or discovering it through repeated trial and error. A password complexity routine is
assigned as part of a profile that is assigned to a user. Typically, an organization will define one or more
standard profiles, and every user account will be assigned one of these when the user is created. Profile
assignments can also be changed:
alter user joe profile corp_security_profile;
Profiles must first be created by the DBA using the CREATE PROFILE command. Profiles are used for two
purposes, as a means of controlling resources used by an account and for enforcing password and other
authentication rules. Here is an example of password and login control:
CREATE PROFILE corp_security_profile
LIMIT
failed_login_attempts 5
password_lock_time 1
password_life_time 90
password_grace_time 3
password_reuse_max 5
password_reuse_time 365
password_verify_function f_corp_pwd_vfy;
Most organizations will not necessarily implement all of the available password rules as shown in this
exhaustive example. This command says that any user who has been assigned the corp_security_profile will have his or her account locked after five successive failed attempts to log
in and that the account will remain locked for 24 hours (one day) unless explicitly unlocked by the DBA.
Any password can be used for no more than 90 days, but the user will receive advance warnings for
three days before the actual password expiration. A user’s password cannot be reused until five other
password changes have been made, and a password can’t be reused within 365 days.
Finally, the profile specifies that all passwords will be validated using a verification function, named
f_corp_pwd_vfy. This function can perform any tests on the actual proposed password string and will
either accept or reject the password. If rejected, the user will have to select another password. Before this
profile can be created this function will have to be written using PL/SQL using techniques discussed
later in Chapter 13, “Functions.” Since many readers don’t have extensive PL/SQL experience yet, we’ll
show an overly simple example of how this function might be coded just to ensure that a user doesn’t
use his or her username as a password. (This function must be created by the privileged user SYS in
order for it to be used in a profile.)
CREATE OR REPLACE FUNCTION f_corp_pwd_vfy
(in_username IN VARCHAR,
in_new_password IN VARCHAR,
in_old_password IN VARCHAR)
RETURN BOOLEAN
AS
pwd_okay BOOLEAN;
BEGIN
IF in_new_password = in_username THEN
raise_application_error(-20001, ‘Password may not be username’);
END IF;
RETURN TRUE;
END f_corp_pwd_vfy;
Remember this simple example only makes one test of password validity. Oracle provides a script
(utlpwdmg.sql, found within the rdbms/admin subdirectory under the ORACLE_HOME directory) to
create a more useful password verification routine or to use as a template for your own routine. Figure
5.1 shows what happens when a user who has been assigned this profile attempts to change his
password in violation of the rule.
