10.1 Managing User Accounts

Managing user accounts can consume quite a bit of database administration time, especially if the DBA doesn't have the proper tools to simplify the job. Even when account management is performed infrequently,^[1] it can be resource-intensive. Any new account you create must have the proper privileges to log on to the database and be able to create database objects as necessary. And for any new account, you will need to make sure you're granting only the necessary privileges on requisite database objects — and not granting any privileges the user should not have.

^[1] It may even be that managing accounts is least organized when done infrequently; in such cases, there is less impetus to organize the tasks.

Dropping database accounts may also be a bit of a chore, but for different reasons. Dropping an account with a large number of objects can cause an extreme amount of activity in the Oracle data dictionary. This can result in contention with other processes in the data dictionary and may result in failure of the DROP USER command.

This section introduces scripts and configuration files you can use to simplify account management. We'll provide ways for you to:

• Create predefined PDBA roles.

• Create users simply from the Unix or Win32 command line.

• Duplicate existing accounts within the database.

• Drop existing accounts (first removing the account's tables and indexes to prevent data dictionary contention).

10.1.1 Creating Accounts the Old Way

Oracle DBAs often create new users by means of shell scripts such as the one shown in Example 10-1.

Example 10-1. Creating users with a shell script

#!/usr/bin/ksh
  
DBAUSER=system
DBAPASSWORD=manager
DEFTBS=users
TMPTBS=temp
ROLES="connect,resource"
  
for var in username password database
do
   print "please enter the value for $var : \c"
   read answer
   eval "$var=$answer"
done
  
sqlplus <<EOF
$DBAUSER/$DBAPASSWORD@$database
CREATE USER $username IDENTIFIED BY $password
DEFAULT TABLESPACE $DEFTBS
TEMPORARY TABLESPACE $TMPTBS;
GRANT $ROLES TO $username;
EOF

While this method is effective if you're creating a simple account, more work is often needed to tailor specific accounts. For example, if you're creating a user within a particular application suite, there are likely to be specific roles and privileges that must be granted users so they will be able to gain access to the application's data. Here is a typical scenario:

Let's just stop here, because this kind of manual DBA work can quickly spiral out of control, especially with multiple users on multiple applications coming and going across the entire company.

Fortunately, Perl provides an easier way, and we've packaged some helpful Perl account maintenance scripts in our toolkit for you to use.

10.1.2 Creating a Single Account with create_user.pl

The toolkit script create_user.pl and its associated configuration file create_user.conf give you a lot of flexibility in creating new user accounts from the command line. In comparison with the rather cumbersome way we created carla for use with the HR_CLERK role in the earlier example, we can now issue a single command. There is no need to perform the extra manual work of logging onto SQL*Plus for fine tuning. Let's look at some examples..

10.1.2.1 Scenario #1

First, we're going to create a single user account.

10.1.2.2 Scenario #2

After creating carla, we learn that a developer needs access to a production database in order to troubleshoot a newly discovered problem. The developer's access needs to be the same as it is in his development database. First let's see how you'd fix things without the toolkit. (Later, we'll show how you'd do it with the toolkit, which manages the whole operation more simply with configurable and pre-stored elements that replace manual investigative hunches with precise and reliable information.)

The toolkit comes to the rescue. Fortunately, you've predefined all of the privileges needed for the databases you administer in the toolkit's create_user.conf file. Instead, you can simply run a command line similar to what you did to create carla's account, with some changes for the user name and privileges granted. Example 10-2 shows how it's done.

Example 10-2. Create a developer account with create_user.pl

%oramon> create_user.pl -machine sherlock -database ts01 \
  -username jkstill -new_username alicia -new_password generate \
  
default tablespace  : users
temporary tablespace: temp
  
grants:  connect resource plustrace javauserpriv javadebugpriv
  
revokes:  unlimited tablespace
  
quotas: 
  indx:  5m
  users:  unlimited
  
user 'alicia' created
password: CBLD1749

With a single command you create the new alicia account and grant the following roles to it:

CONNECT

RESOURCE

PLUSTRACE

JAVAUSERPRIV

JAVADEBUGPRIV

SELECT_CATALOG_ROLE

In addition, you set the user quotas on the USERS and INDX tablespaces. Let's take a closer look now at create_user.conf and create_user.pl.

10.1.2.3 The create_user.conf configuration file

Make sure that the create_user.conf file is in your PDBA_HOME directory:

Example 10-3. create_user.conf

package cuconf;
use vars qw{ %roles %tablespaces };
  
%roles = (
   developer =>  {
      grants => [ qw{ connect
                      resource
                      plustrace
                      javauserpriv
                      javadebugpriv
                      select_catalog_role }],
  
      revokes => [ 'unlimited tablespace'],
  
      quotas => { users => 'unlimited', indx => '5m', },
   },
   app_clerk => {
      grants => ['create session', 'hr_clerk'],
      revokes => [],
      quotas => {},
   },
   app_admin => {
      grants => ['create session','hr_admin',],
      revokes => [],
      quotas => {},
   },
   backup => {
      grants => [qw{ connect exp_full_database imp_full_database }],
      revokes => [],
      quotas => {},
   },
   dba => {
      grants => [qw{connect dba}],
      revokes => [],
      tablespaces => { default => 'tools', temporary => 'temp2', },
      quotas => {},
   },
   sysdba => {
      grants => [ qw{connect dba sysdba} ],
      revokes => [],
      quotas => {},
   },
);
  
%tablespaces = ( default  => 'users', temporary => 'temp', );

create_user.conf defines a number of logical roles; we'll refer to these as PDBA roles, to differentiate them from standard Oracle database roles. Near the top of the %roles hash in Example 10-3, you'll find the role DEVELOPER. Each PDBA role is a privilege group assigned as a single entity. (Example 10-2 shows account alicia, as created using the PDBA role DEVELOPER.)

If you compare the granted privileges listed for DEVELOPER in Example 10-3, you'll see that they match the screen in Example 10-2.

Notice also the UNLIMITED TABLESPACE revoke, reversing its automatic assignment to those granted RESOURCE. The create_users.conf configuration file directed this operation without your needing to remember. You can extend this approach to any combination of grants, revokes, and quotas.

New entries can be added to %roles. For example, you might need to create lots of inventory testers on your application. You could then add the following PDBA role:

inventory_tester => {
   grants => [ qw{
      connect
      resource
      plustrace
      select_catalog_role
      inventory_user }],
  
   revokes => ['unlimited tablespace'],
  
   quotas => { users => '10m', indx => '5m', },
},

Let's see what's going on here:

To create a more limited production version of INVENTORY_TESTER, you can limit the grants to CREATE_SESSION and the database role INVENTORY_USER:

inventory_production => { grants => ['create session','inventory_user'}],
                          revokes => [],
                          quotas => {}, },

10.1.2.4 Tablespaces

When you are creating an account, it is good practice to specify a default tablespace for the user's object creation needs, and a temporary tablespace for disk sorts and related operations. Specifying tablespaces in this way avoids having the generic SYSTEM tablespace being assigned for both purposes (this also avoids point deductions by the Big DBA in the sky, who generally frowns upon disk sorts in the SYSTEM tablespace's data dictionary area and the potential for SYSTEM to run out of room).

Going back to Example 10-3, you'll notice that the DBA PDBA role has the following clearly specified tablespaces:

tablespaces => { default => 'tools', temporary => 'temp2', },

Every other account makes use of another special hash, %tablespaces:

%tablespaces = ( default  => 'users', temporary => 'temp', );

This ensures that every new user created gets USERS and TEMP as its default tablespaces; this avoids having us clobber SYSTEM!

We can also override all these configured tablespaces, as we'll find out shortly.

10.1.2.5 create_user.pl

Now let's examine the create_user.pl script, which does the actual user account creation. The script's options are listed in Table 10-1.

Table 10-1. Command-line options - create_user.pl

Option	Description
-machine	Server where target database resides.
-database	Target database.
-username	DBA account that is creating the new account.
-password	DBA's account password (optional if password server used).
-new_username	New user account to be created.
-new_password	Password for new account. (Specifying a value of generate causes automatic password generation; see Example 10-2.)
-pdbarole	PDBA role to assign to the new account.
-default_tbs	Overrides the default tablespace value in create_user.conf.
-temp_tbs	Overrides the temporary tablespace value in create_user.conf.
-verbose	Outputs every user creation step to the screen.
-list_roles	Prints the list of available PDBA roles and privileges.

The password generation code for the -new_password switch is found in the PDBA::DBA module and is shown in Example 10-4. This code simply selects several characters of the alphabet, based on the current time value of seconds as returned by SYSDATE and a MOD value of v$timer.hsecs.

Example 10-4. Generating passwords in PDBA::DBA.pm

my $Alphabet = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
my $PasswordGenSql = qq {select 
   substr('$Alphabet',MOD(TO_CHAR(SYSDATE,'SS'),25)+1,1)||
   substr('$Alphabet',MOD(substr(mod(hsecs,99999999)+?,5,2),25)+1,1)||
   substr('$Alphabet',MOD(substr(mod(hsecs,99999999)+?,6,2),25)+1,1)||
   substr('$Alphabet',MOD(substr(mod(hsecs,99999999)+?,7,2),25)+1,1)||
   mod(hsecs,9999) as password
   from v\$timer 
};
sub genPassword {
   my $newPassword;
   my $sthPasswordGen = $dbh->prepare( $PasswordGenSql );
   $sthPasswordGen->bind_columns( undef, \$newPassword );
   my $seed = (localtime(time))[0];
   use DBI qw{:sql_types};
   $sthPasswordGen->bind_param( 1, $seed, SQL_INTEGER );
   $sthPasswordGen->bind_param( 2, $seed, SQL_INTEGER );
   $sthPasswordGen->bind_param( 3, $seed, SQL_INTEGER );
   $sthPasswordGen->execute(  );
   $sthPasswordGen->fetch(  );
   return $newPassword;
}

This routine lacks true randomness but possesses sufficient uniqueness for the assignment of new account passwords. Users should, of course, be told to change these passwords upon receipt.

The list_roles switch reveals all of the roles, types, and privileges as follows:

$ create_user.pl -list_roles
ROLE: app_clerk
   TYPE: grants
      PRIV: create session
      PRIV: hr_clerk
   TYPE: quotas
   TYPE: revokes
...
ROLE: dba
   TYPE: grants
      PRIV: connect
      PRIV: dba
   TYPE: quotas
   TYPE: revokes
   TYPE: tablespaces
      PRIV: default: tools
      PRIV: temporary: temp

The -default_tbs and -temp_tbs switches assign specific tablespaces by overriding create_user.conf. The following creates a new DBA user, homer, with default and temporary tablespaces of USERS and TEMP, respectively:

$ create_user.pl -machine sherlock -database ts01 \
    -username system -password manager \
    -new_username homer -new_password doh \
    -pdbarole dba -default_tbs users -temp_tbs temp

10.1.3 Creating a Single Account With dup_user.pl

At times, you may wish to simply duplicate a user account by copying all of the characteristics of one user to another user. However, the source account may come with a large number of directly granted privileges. Duplicating accounts like this is difficult; you will need to untangle all of the source account's privileges, no matter how twisted they've become. Moreover, you must log in as the owner of original objects and re-grant these privileges. In Figure 10-1 we've illustrated a new account's receiving direct privileges from the GL, AP, and HR accounts.

Figure 10-1. Multiple direct grants to a new account

If your new account name were rowan, here's what you'd need to do to assign the correct database privileges:

CONNECT HR/password
GRANT SELECT,UPDATE,INSERT,DELETE ON EMPLOYEES TO ROWAN;
CONNECT GL/password;
GRANT SELECT,UPDATE,INSERT,DELETE ON ACCOUNT_CODES TO ROWAN;
CONNECT AP/password;
GRANT SELECT,UPDATE,INSERT,DELETE ON PURCHASE_ORDERS TO ROWAN;

This may look fairly painless. However, if the source account has many such privileges, this process can become very complex. This inspired us to create the dup_user.pl script. It fully duplicates a complete Oracle user, including all roles, directly granted privileges, system privileges, default and temporary tablespace assignments, and tablespace quotas. Its options are summarized in Table 10-2.

Table 10-2. Command-line options — dup_user.pl

Option	Description
-machine	Server where the target database resides.
-database	Target database.
-username	DBA account.
-password	DBA account password (optional if password server in use.)
-source_username	Account to duplicate.
-new_username	User account to create.
-nosystemprivs	Avoids assigning source system privileges to target.
-systemprivs	Assigns source system privileges to target (the default).
-noobjectprivs	Avoids assigning source object privileges to target.
-objectprivs	Assigns source object privileges to target (the default).
-noroles	Avoids assigning source roles to target.
-roles	Assigns source roles to target (the default).

We'll demonstrate the use of dup_user.pl on our test database by creating a duplicate of scott who has been granted a few extra privileges:

SELECT GRANTEE, 'ROLE' PRIVTYPE, GRANTED_ROLE PRIVNAME, 
       NULL OWNER, NULL TABLE_NAME
  FROM DBA_ROLE_PRIVS
 WHERE GRANTEE =  'SCOTT'
 UNION
SELECT GRANTEE, 'SYSPRIV' PRIVTYPE, PRIVILEGE PRIVNAME, 
       NULL OWNER, NULL TABLE_NAME
  FROM DBA_SYS_PRIVS
 WHERE GRANTEE = 'SCOTT'
 UNION
SELECT GRANTEE, 'TABPRIV' PRIVTYPE, PRIVILEGE PRIVNAME, 
       OWNER, TABLE_NAME
  FROM DBA_TAB_PRIVS
 WHERE GRANTEE = 'SCOTT'
 ORDER BY 1, 2, 3, 4, 5;
  
           PRIV
GRANTEE    TYPE    PRIV NAME              OWNER      TABLE NAME
---------- ------- ---------------------- ---------- -------------------
SCOTT      ROLE    CONNECT
                   RESOURCE
                   SELECT_CATALOG_ROLE
           SYSPRIV CREATE SESSION
                   CREATE TRIGGER
           TABPRIV DELETE                 JKSTILL    LCL_1
                   EXECUTE                JKSTILL    TRUNCATE_TEST_NAMES
                   SELECT                 JKSTILL    LCL_1
                   UPDATE                 JKSTILL    LCL_1

We'll use dup_user.pl to create SCOTT_DUP, a duplicated clone of SCOTT. Note the following:

Here's the command line needed to create the duplicate account:

$ dup_user.pl -machine sherlock -database ts01 -username jkstill \
     -source_username scott -new_username scott_dup

The data dictionary confirms SCOTT_DUP's creation, with SCOTT's privileges:

PRIV
GRANTEE    TYPE    PRIV NAME              OWNER      TABLE NAME
---------- ------- ---------------------- ---------- -------------------
SCOTT_DUP  ROLE    CONNECT
                   RESOURCE
                   SELECT_CATALOG_ROLE
           SYSPRIV CREATE SESSION
                   CREATE TRIGGER
           TABPRIV DELETE                 JKSTILL    LCL_1
                   EXECUTE                JKSTILL    TRUNCATE_TEST_NAMES
                   SELECT                 JKSTILL    LCL_1
                   UPDATE                 JKSTILL    LCL_1

10.1.4 Creating Multiple Accounts with mucr8.pl

Most requests for new accounts come one at a time. However, you may occasionally have to deal with the need to create a large number of new accounts all at once. For example, you may be asked to migrate an existing application to Oracle or to install a new company-wide application. Whatever the reason, creating hundreds of new users can be a heavy piece of work, and it's essential to create an accurate list of all the new account names. This will be our starting point in this section. But entering many account names by hand is a time-consuming and potentially error-prone process. So let's first try to obtain the list, with permission, from such places as:

• The project manager of the company-wide application.

• The company personnel records (although it's often rightly difficult to obtain this sensitive information).

The information we'll need is the following:

The account name

The user's email address

The user's first name (optional)

The user's last name (optional)

The essential elements are the account name and the email address, though the first and last names are useful for constructing account names if specific account names remain unavailable. Once we have the list, we could employ single-user creation tools executed inside a Unix for loop.

However, our cross-platform mucr8.pl (Multi User Create) toolkit script provides functionality well beyond this. Here's what we do:

Example 10-5. mucr8.conf

package mucr8;
  
use PDBA;
use vars qw( %conf %tags ) ;
  
%conf = (
   messageFile => PDBA->pdbaHome . '/mucr8.msg',
   fieldSeparator => ':',
   usernamePosition => 0,
   emailAddressPosition => 1
);
  
%tags = (
   '<<APPLICATION>>' => '$optctl{application}', # Used later, 
   '<<DATABASE>>' => '$optctl{database}',       # in messages!  :-)
   '<<USERNAME>>' => '$newUsername',
   '<<PASSWORD>>' => '$newUser->{PASSWORD}'
);
1;

The %conf hash sets up script controls, and %tags personalizes the email messages sent to each new account. There are four keys in %conf:

messageFile

Points to the message file emailed to users. This file contains tags used as placeholders for runtime data, which we'll discuss shortly.

fieldSeparator

Separates fields in the list file data used by mucr8.pl.

usernamePosition, emailAddressPosition

Numeric positions of data within the text record; for example:

rogerwil:[email protected]:Wilco:Roger

The rogerwil username is held in field 0, the email address in field 1.

10.1.4.1 mucr8.msg

The mucr7.msg file contains the text that will be automatically emailed to the owners of new Oracle accounts. It makes use of << >> tags to customize the message, as we'll explain shortly.

On Unix, copy the mucr8.msg file from the directory from which PDBA was installed:

$ cp /u01/build/PDBA-1.00/routine_tasks/mucr8.msg $PDBA_HOME

On Win32, the copy operation is very similar:

DOS> copy C:\Perl\site\lib\PDBA\conf\mucr8.msg C:\PDBA

Take a look at mucr8.msg in Example 10-6.

Example 10-6. mucr8.msg

An account has been created for you on one of the company Oracle databases in support of 
the following application:
  
Application: <<APPLICATION>>
  
The information you need to logon to this database is as follows:
  
Username   : <<USERNAME>>
Password   : <<PASSWORD>>
Database   : <<DATABASE>>
  
If you are unsure why you received this email or are having difficulty, please contact the 
Help Desk at 555-346-2852.
  
Thank You,
Your DBA Team

At runtime, the mucr8.pl script replaces the << * >> tags with the attributes assigned in mucr8.conf within the %tags hash (shown in Example 10-5). These replace the corresponding tag values found in mucr8.msg. The following cut-down code from mucr8.pl accomplishes this:

open(MSG,"< mucr8.msg")  # Open the email message file.
  
my @mailMsg = <MSG>;     # Slurp the message file into @mailMsg array.
close MSG;
  
# Create a scalar variable, $msg, made up of all elements from the 
# @mailMsg array, slurped in earlier.
  
my $msg = join('',@mailMsg);
# Loop through all tags defined in the %tags hash, found in mucr8.conf.
foreach my $tag ( keys %mucr8::tags ) {
  
   # For each tag from %tags, replace the tag found in the message 
   # file with the value specified from %tags.
  
   eval '$msg =~ ' . "s/$tag/" . (eval $mucr8::tags{$tag}) . "/gm" ;
}

If the tag found in mucr8.msg is <<USERNAME>>, it's replaced by the variable $newUsername from the mucr8.pl script, and so on. Any of the attributes associated with a new user object may also be used as replacement text message values. Here are some you may find useful:

Scalars

OBJECT

Name of the created user.

PASSWORD

Scalar containing the password assigned.

DEFAULT_TABLESPACE

Default tablespace.

TEMPORARY_TABLESPACE

Corresponding temporary tablespace.

PROFILE

Assigned profile, if any.

Array references

PRIVS

Reference to an array of privileges granted.

REVOKES

Reference to an array of privileges revoked.

Hash references

QUOTAS

Hash reference to the account quotas.

You add the scalars to the mucr8.conf like this:

'<<DEFAULT_TBS>>' => '$newUser->{DEFAULT_TABLESPACE}',

The following array reference lists privileges in the mucr8.conf file:

'<<PRIVS>' => q{join(',' @{$newUser->{PRIVS}})},

The following hash reference fills the <<QUOTAS>> key in any message:

'<<QUOTAS>>' => 
   q{join(',',map { $_ . ' => ' . $newUser->{QUOTAS}{$_}}
   keys %{$newUser->{QUOTAS}})},

The variable information from an example email using <<PRIVS>> and <<QUOTAS>> might look like this:

...
Application: ACCT and HR
  
The information you need to log on to this database is as follows:
  
Username   : brubble
Password   : KAEE7858
Database   : ts01
  
Grants     : connect,resource,plustrace,javauserpriv,javadebugpriv,select_catalog_role
Quotas     : indx => 5m,users => unlimited
...

10.1.4.2 Running mucr8.pl

Creating actual database accounts in a test database may make our discussion easier to understand, so let's try out the mucr8.pl script. Create a file called myusers.txt with the following lines:

brubble,<your email address here>
fflintstone, <your email address here>

(Change the email addresses to some valid and observable test values.) Table 10-3 summarizes the muc8.pl command-line options.

Table 10-3. Command-line options — mucr8.pl

Option	Description
-machine	Server where the target database resides.
-database	Target database.
-username	DBA account.
-password	DBA password (optional if Password server in use).
-filename	File name containing the new account information.
-application	Informational only; allows the use of this value within the email message file to specify the application.
-pdbarole	Which PDBA role to assign to the new account.
-default_tbs	Overrides default tablespace.
-temp_tbs	Overrides temporary tablespace.
-verbose	Outputs all of the user creation steps to the screen.
-message_file	Name of the email message file sent to new account owners. This overrides the file name in mucr8.conf.
-logfile	Log of operations. Defaults to mucr8.log.
-field_separator	Field separator for list file. Overrides mucr8.conf value.
-mail_password	Causes mucr8.pl to email account information to users.
-dryrun	Prints an operational dry run. Logging is turned off, email is unsent, and the new accounts remain untouched.

10.1.4.3 Account creation dry run

We'll try the new -dryrun option in our first example. Example 10-7 shows a dry run for our friends Barney Rubble and Fred Flintstone.

Example 10-7. A mucr8.pl dry run

mucr8.pl -machine sherlock -database ts01 -username jkstill \
  -filename myusers.txt -pdbarole developer -verbose \
  -application 'ACCT and HR' \
  -dryrun
  
dry run only 
  
default tablespace: users
temp    tablespace: temp
grants:  connect resource plustrace javauserpriv javadebugpriv
  indx:  5m
  users:  unlimited
  
user: fflintstone                   email: [email protected]                    
user: brubble                       email: [email protected]

Here's what's going on in Example 10-7:

If you run a test with PDBA role DEVELOPER, you may encounter errors against databases with some Java components missing. If so, use the CONNECT PDBA role, which has minimal privileges; it should work on most databases.

10.1.5 Dropping Oracle Accounts

You can drop most user accounts easily using Oracle's SQL*Plus, as in the following example:

SQL> DROP USER username CASCADE;

However, account removal can become complex. When an Oracle account owns a large number of objects, removing that account with DROP USER can cause a great deal of recursive data dictionary SQL. This can be a major resource drain, and take excessive time to complete. To avoid this situation, some DBAs drop all account tables before executing DROP USER username CASCADE. In the toolkit, we've provided a Perl script that allows you to do this automatically — drop_user.pl. Table 10-4 summarizes the command-line options for this script.

Table 10-4. Command-line options — drop_user.pl

Option	Description
-machine	Server where the target database resides.
-database	Target database.
-username	DBA account.
-password	DBA password (optional if password server in use).
-drop_username	Name of the user to drop.
-force	Drops user without verification (the default is to ask).

The drop_user.pl script allows you to change your mind; before actually dropping the user, it will ask you to verify that you really do want to drop that user. In the following example we use drop_user.pl to erase the newly created account for Barney Rubble:

$ drop_user.pl -machine sherlock -database ts01 -username system \
     -drop_username brubble
  
dropping user 'brubble'
  
Really drop user brubble?: Y/N: y
user brubble successfully dropped

Because the -force option was not specified, drop_user.pl required verification. Any response starting without Y (or y) results in drop_user.pl exiting without dropping the account.