Section 10.1. Managing User Accounts

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:

You're asked to create a data entry account for someone new working on a Human Resources package. For this clerical role you've already created an appropriate role, hr_clerk. Let's assume this new carla account was created in the database via the script in Example 10-1. We must now relog into SQL*Plus and execute the following:
```
 SQL> GRANT HR_CLERK TO CARLA; 
```
Because carla does not need to be able to create database objects, we revoke RESOURCE to prevent inadvertent misuse of database resource.
You may recall that granting a user RESOURCE means that Oracle auto-grants an UNLIMITED TABLESPACE privilege to carla . So now we have to issue a countermanding REVOKE UNLIMITED TABLESPACE.

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.

Here carla is created with a single script, create_user.pl , and the generated password is printed to the screen.

The -verbose option shows all the CREATE and GRANT steps taken:

 $ create_user.pl -machine sherlock -database ts01 -username jkstill \ -new_username  carla  -new_password generate -pdbarole app_clerk  -verbose  creating user '  carla  '    default tablespace  : users temporary tablespace: temp grants: create session hr_clerk quotas:    user '  carla  ' created  password BNHV815  $

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.)

Although you're opposed to developers possessing accounts on production databases, through gritted teeth you agree to create alicia on production with the same privileges she has in development.
Using the trusty old shell script method, you create the basic alicia account, then log in to the development database to determine her exact privilege set (you might use an application such as Orac or Oracletool to work this out). You then manually grant the discovered privileges to production, in a process that is both tedious and error-prone .

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:

If it's missing from PDBA_HOME (perhaps because you are logged on as a new user), copy it from the PDBA installation directory. For Unix, type:
```
 $ cp /u01/build/PDBA-1.00/routine_tasks/create_user.conf $PDBA_HOME 
```
On Win32, type:
```
 DOS> copy C:\Perl\site\lib\PDBA\conf\create_user.conf C:\PDBA 
```
Now open the file with your favorite text editor. The working contents of the file will look similar to Example 10-3. This example also gives us a good opportunity to show how Perl's qw{} quote word constructor is used. ^[2]

^[2] For much more on the qw{} , q{} , and qq{} quote constructions in Perl (these essentially allow us to use less punctuation within our code), check out perldoc perlop .

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:

In addition to the standard database roles of CONNECT, RESOURCE, PLUSTRACE, and SELECT_CATALOG_ROLE, the application-specific role of INVENTORY_USER is included.
Because RESOURCE was granted, UNLIMITED TABLESPACE is specified under revokes , ensuring its immediate removal from any new account.
Finally, any user created under inventory_tester will receive quotas of 10 and 5 megabytes on the USERS and INDX tablespaces, respectively.

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:

The script must log in to the test database as JKSTILL, and grant privileges on the LCL_1 table and TRUNCATE_TEST_NAMES procedure.
For this to work, the Password server (see the discussion in Chapter 9) must be running and configured with passwords from accounts holding necessary privileges.
JKSTILL's password on the ts01 database is also required.

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:

We'll start by getting a copy of the mucr8.conf file and placing it into your PDBA_HOME directory (you may have already done this in Chapter 9). (We also need the create_user.conf configuration file that was configured earlier in this chapter in Section 10.1.2.)

On Unix, copy configuration files from the PDBA installation directory:

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

On Win32, type:

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

Now open up mucr8.conf within a text editor, as shown in Example 10-5.

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  :  rogerw@yourdomain.com  :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: fred.flintstone@yourdomain.com                     user: brubble                       email: barney.rubble@yourdomain.com

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

Because the -dryrun option was specified, account creation failed to take place. Only a report of the future task is shown on the screen.

Next, having checked the output, we actually create the accounts:

 $ mucr8.pl -machine sherlock -database ts01 -username jkstill \    -filename myusers.txt -pdbarole  developer  -verbose \    -application 'ACCT and HR'  user: brubble                          password: KAEE7858   user: fflintstone                      password: KBPF7869

As you can see, the only output when creating accounts for real includes the username and password. You may wish to record these, even though the passwords have been mailed to the user. They're also recorded in mucr8.log , so make sure that this file is secure or simply delete it afterwards.

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.