Search

Top 60 Oracle Blogs

Recent comments

A System for Oracle Users and Privileges with Automatic Expiry Dates

Tired of tracking down all the users in the database to deactivate them when they cease to exist, or change roles, or fulfill their temporary need to the database? Or, tracking down privileges you granted to existing users at the end of their requested period? The solution is to think out of the box - developing a system that allows you to create a database user account with an expiration date. This fire-and-forget method allows you to create users with the assurance that they will be expired (locked or dropped) at the expiration date automatically, without your intervention. Interested? Read on how I developed such a system--along with source code for you to try.

Introduction

What is a database user? In my opinion, there are two kinds of users:

  1. Permanent Residents - those who live in the database forever until there is no purpose for them. These are non-human users. Typical examples: admins accounts (sys, system) and applications schemas.
  2. Human Users - these are accounts created for real human beings.

It's the second category that is subject to a lot of scrutiny from many sources - Payment Card Industry (PCI) mandates, Health Insurance Portability and Accountability Act (HIPAA), Serbanes-Oxley (SOX), etc. All these mandates and regulations have one thing in common - the need to identify and regulate the human users. Common requirements in the mandates include database accounts should be removed when they leave the organization, they should be validated very so often (usually 90 days), they should get the privileges which they can justify a business need for, and so on.

Concept

DBVisitor is a tool to create Oracle database user accounts with an expiration date. A user in the Oracle database is permanent; there is no such thing as a temporary user. Using the DBVisitor tool the DBA can create a “visitor”, which is a regular database user but with a built-in expiration date (from as little as 5 minutes to as much as needed) after which the user is either dropped or locked (the exact action can be defined for each user specifically). This tool can also grant visitor privileges, which are regular Oracle database privileges such as create table, select on TableName, etc., with built-in expiration dates, after which the privilege is automatically revoked. The expiration time can be extended for both the visitor and the privilege. The tool keeps track of the creation, deletion, re-activation of the users. The source code as well as all the scripts used in this tool can be downloaded here.

Components

There are 7 major stored procedures in the tool. (Please Note: I plan to have all these in a single package in a later release)

    ADD_VISITOR To add a visitor. The expiration, password, default role, etc. can be given here
    ADD_PRIVILEGE To add a privilege, e.g. “create session” to a visitor with expiration date
    EXTEND_VISIT_TIME To extend the expiration date for a visitor
    EXTEND_PRIV_TIME To extend the expiration date for a privilege granted to a visitor
    EXPIRE_VISITORS To inactivate the visitors at the end of expiration (called via a job)
    EXPIRE_VISITOR_PRIVS To revoke the privileges at expiration (via a job)
    SEND_REMINDER_EMAIL To send email reminders just before the expiration date of visitors
    UNLOCK_VISITOR To unlock the visitor whose account is locked at expiration

    The actions are recoded in a table called DBVISITOR_EXPIRATION (for visitors) and DBVISITOR_PRIVS (for the privileges granted). This table is never deleted. When the expiration date is extended, a new record is inserted and the old record updated, to leave an audit trail which can be examined later.

    How it Works

    When a visitor is created by this tool, a record goes into the DBVISITOR_EXPIRATION table with the expiry date. A job searches that table and when it finds some visitor whose expiration date is past, inactivates that visitor. The exact actions of inactivation could be “DROP”, i.e. the user is completely dropped; or “LOCK”, i.e. it is not dropped but its account is locked so it can’t log in any more. The latter action preserves any tables or other objects created by the user; but prevents the login. The record is marked “I” (for Inactive). The active visitors are marked with “A”. The same mechanism applies to privileges too, except that those records are located in the table DBVISITOR_PRIVS.

    When the expiration time is extended, DBVisitor creates a new record with the new expiration date and status as “A”. The status of the old record is updated with the flag “X”, for Extended. Similarly, when the account is unlocked, the status is shown as “U” in the old record.

    Not all parameters to the stored procedures are mandatory. If not specified, they assume default values, which are stored in a table called DBVISITOR_PROPERTIES. If you want to reduce the expiration date (not extend it), you can use the same extend_* stored procedure; but use a negative number. If you want to expire the visitor right now without waiting, just update the table DBVISITOR_EXPIRATION or DBVISITOR_PRIVS to set the EXPIRY_DT to something less than the sysdate. The job will see the expiration date as past and will inactivate the account.

    Usage

    When asked to create a visitor, execute the stored procedure ADD_VISITOR. You can see the details of the stored procedure later in the blog. For your convenience the downloaded file contains an SQL*Plus script for each activity. Here are the scripts:

    addv.sql – to add visitors
    addp.sql – to add privileges
    extv.sql – to extend time for visitors
    extp.sql – to extend time for privileges
    unlock.sql – to unlock the account
    selv.sql – to list the visitors
    selp.sql – to list the privileges
    selxv.sql – visitors expiring in next hours

    Here is an example of how to create a visitor named JSMITH with an expiration of 3 hours. The script will prompt you for the values. If you press ENTER, the default values will be taken.

    SQL> @addv
    Enter value for username: jsmith
    Enter value for duration: 3
    Enter value for dur_unit: hour
    Enter value for role:
    Enter value for password:
    Enter value for expiration_process:
    Enter value for email: john.smith@proligence.com
    Enter value for comments:

    There is a very important things you should note here:we omitted entering some fields, e.g. password, role, etc. These values are picked up from the default settings. The default values are defined in the table DBVISITOR_EXPIRATION. At the end, an email will go out to the visitor and you will see a small confirmation for the user created:

     * UserID         : JSMITH
    * Email          : JOHN.SMITH@PROLIGENCE.COM
    * Password       : changem3
    * Expires in     : 3 HOUR
    * Expiry Date    : 10/07/13 18:28:10
    * Role           : VISITOR
    * Expiry Process : DROP

    And here is how you will grant a privilege – create table – to the visitor.

     SQL> @addp
    Enter value for usrname: jsmith
    Enter value for privilege: create table
    Enter value for duration: 2
    Enter value for duration_unit: hours

    * CREATE TABLE
    * granted to JSMITH
    * until 03/07/13 17:30:58

    Note a very important point: we created the visitor for 3 hours but the privilege for only 2 hours. This is allowed. If you need to add more privileges, just execute addp.sql for each privilege. Do not give multiple privileges in the script.

    Extension

    When you need to extend the visit time or the privilege time, use extv.sql and extp.sql respectively. You can extend the time only if the visitor or the privilege being extended is active. Here is an example where you extend the visit time of JSMITH by 2 more hours:

    SQL> @extv
    Enter value for username: jsmith
    Enter value for extend_time: 2
    Enter value for extend_dur: hours
    Enter value for comments: to continue from earlier

    *********************************************
    *
    * Expiration Date Change for JSMITH
    * Old: 10/07/13 18:28:10
    * New: 10/07/13 20:28:10
    *
    *********************************************
    Updated.

    Similarly, to extend the CREATE TABLE privilege to this user by 2 more hours, you will need to execute the extp.sql script.



    SQL> @extp

    Enter value for username: jsmith
    Enter value for priv_name: create table
    Enter value for extend_time: 2
    Enter value for extend_unit: hours
    Enter value for comments:

    *********************************************
    *
    * Expiration Date Change for JSMITH
    * for CREATE TABLE
    * Old 10/11/13 14:52:37
    * New 10/11/13 16:52:37
    *
    *********************************************
    Updated.

    Reporting

    To find out the visitors and their privileges, you can select from the tables DBVISITORS_EXPIRATION and DBVISITORS_PRIVS. To make it easier, three scripts have been provided:

    • selv.sql – this shows the visitors you have created earlier, along with the expiration dates. The expired visitors are also shown. Status column shows Active (A) or Inactive (I). If it shows X, then the visitor’s time was extended. Here is a sample report:
     SQL> @selv                            

    Expiry
    DB User Status Process Created on Expires on Locked on Dropped on changed on Misc
    -------------------- ------ -------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------------------------------
    JSMITH A DROP 09/30/13 09:50:30 09/30/13 12:50:30 Change Ticket 3456789
    JOHN.SMITH@PROLIGENCE.COM
    JOHNSMITH I DROP 09/29/13 21:59:24 09/29/13 23:59:24 09/29/13 23:59:48 ARUP@PROLIGENCE.COM
    • selp.sql – this shows the privileges granted to the visitors, active or not. Here is a sample report:
    DB User Privilege Name Status Granted on Expires on Revoked on Changed on Comments
    ------- ---------------------- ------ ----------------- ----------------- ----------------- ----------------- --------------------
    CHRIS SELECT ANY TABLE X 02/02/13 12:41:59 02/02/13 14:41:59 02/02/13 12:44:50
    CHRIS SELECT ANY TABLE I 02/02/13 12:44:50 02/02/13 16:41:59 02/02/13 16:42:22 change ticket 123
    MARK SELECT ANY TABLE X 02/02/13 13:00:55 02/02/13 15:00:55 02/02/13 13:01:36
    MARK SELECT ANY TABLE I 02/02/13 13:01:36 02/02/13 17:00:55 02/02/13 17:01:22 change ticket 234
    PAT SELECT ON ARUP.ITLTEST I 02/07/13 14:32:41 02/07/13 14:33:41 02/07/13 14:34:23
    ARUP2 CREATE TABLE X 03/06/13 13:32:11 03/27/13 13:32:11 03/06/13 13:32:54
    ARUP2 CREATE TABLE I 03/06/13 13:32:54 04/19/13 13:32:11 04/19/13 13:32:22
    VIS3 CREATE TABLE I 03/07/13 15:30:58 03/07/13 17:30:58 03/07/13 17:31:22
    JSMITH CREATE TABLE X 03/11/13 12:52:37 03/11/13 14:52:37 03/11/13 12:53:17
    • selxv.sql – this shows the visitors who are expiring in the next hours, where is something you supply.


    Quick Reference


    -->•    To add a visitor: addv.sql•    Default expiration: 2 hours
    •    To add a privilege: addp.sql
    •    Default expiration: 2 hours
    •    To deactivate a visitor now: update dbvisitor_expiration set expiry_dt = sysdate -1/24/60; commit;
    •    To delete a privilege now, issue the above update against dbvisitor_privs
    •    Never delete records from these tables.
    •    To extend the visit time: extv.sql
    •    To extend the privilege time: extp.sql
    •    To reduce the expiration (make it expire earlier), extv.sql and extp.sql but use a -ve num in duration
    •    To get reports on visitors: selv.sql
    •    To get reports on privileges: selp.sql
    •    To get the list of visitors expiring in next hours: selxv.sql
    •    The column STATUS: A – active and I – Inactive. X – the visitor or privilege was initially granted and then extended.
    •    To unlock a visitor after locked: unlock.sql

    Important

    You can extend the expiry only if the visitor or the privilege is active (status = ‘A’). If the visitor is already expired, you can’t extend it. You must re-add it (in the same name).

    -->

    Specification

    Here are the descriptions of each of the procedures and tables.

    Tables

    DBVISITOR_EXPIRATION

    It’s there to hold the visitor information, as a part of the tool. The term visitor is a user in the database which has a built-in expiration date after which the user is either dropped or locked.
    #4F81BD 1.0pt; mso-border-bottom-themecolor: accent1; mso-border-top-alt: solid #4F81BD 1.0pt; mso-border-top-themecolor: accent1; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;">
    #4F81BD 1.0pt; border-left: none; border-right: none; border-top: solid #4F81BD 1.0pt; mso-border-bottom-themecolor: accent1; mso-border-top-themecolor: accent1; padding: 0in 5.4pt 0in 5.4pt; width: 128.9pt;" valign="top" width="172">
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">Column
    #4F81BD 1.0pt; border-left: none; border-right: none; border-top: solid #4F81BD 1.0pt; mso-border-bottom-themecolor: accent1; mso-border-top-themecolor: accent1; padding: 0in 5.4pt 0in 5.4pt; width: 421.9pt;" valign="top" width="563">
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">Purpose
    #D3DFEE; border: none; mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0in 5.4pt 0in 5.4pt; width: 128.9pt;" valign="top" width="172">
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">DBUSER
    #D3DFEE; border: none; mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0in 5.4pt 0in 5.4pt; width: 421.9pt;" valign="top" width="563">
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">The database username of the visitor
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">STATUS
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">Status: A-Active, I-Inactive, X-has been extended, U-unlocked
    #D3DFEE; border: none; mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0in 5.4pt 0in 5.4pt; width: 128.9pt;" valign="top" width="172">
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">CREATED_DT
    #D3DFEE; border: none; mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0in 5.4pt 0in 5.4pt; width: 421.9pt;" valign="top" width="563">
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">Date/Time the visitor was created
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">EXPIRY_DT
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">Date/Time the visitor is supposed to expire
    #D3DFEE; border: none; mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0in 5.4pt 0in 5.4pt; width: 128.9pt;" valign="top" width="172">
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">EXP_PROCESS
    #D3DFEE; border: none; mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0in 5.4pt 0in 5.4pt; width: 421.9pt;" valign="top" width="563">
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">How the expiration will occure – LOCK/DROP
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">CHANGE_DT
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">Date/Time a change was made, e.g. extended, or unlocked
    #D3DFEE; border: none; mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0in 5.4pt 0in 5.4pt; width: 128.9pt;" valign="top" width="172">
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">REMINDER_SENT_DT
    #D3DFEE; border: none; mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0in 5.4pt 0in 5.4pt; width: 421.9pt;" valign="top" width="563">
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">Date/Time a reminder was sent that an expiration is approaching
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">EMAIL
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">The Email ID
    #D3DFEE; border: none; mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0in 5.4pt 0in 5.4pt; width: 128.9pt;" valign="top" width="172">
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">LOCKED_DT
    #D3DFEE; border: none; mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0in 5.4pt 0in 5.4pt; width: 421.9pt;" valign="top" width="563">
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">Date/Time the visitor’s DB account was locked
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">DROPPED_DT
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">Date/Time the visitor’s DB account was dropped
    #D3DFEE; border-bottom: solid #4F81BD 1.0pt; border: none; mso-background-themecolor: accent1; mso-background-themetint: 63; mso-border-bottom-themecolor: accent1; padding: 0in 5.4pt 0in 5.4pt; width: 128.9pt;" valign="top" width="172">
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">COMMENTS
    #D3DFEE; border-bottom: solid #4F81BD 1.0pt; border: none; mso-background-themecolor: accent1; mso-background-themetint: 63; mso-border-bottom-themecolor: accent1; padding: 0in 5.4pt 0in 5.4pt; width: 421.9pt;" valign="top" width="563">
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">Any comments, e.g. Change ticket

    -->

    DBVISITOR_PRIVS

    This holds temporary privileges granted to the visitor users. The privileges have a built-in expiration date after which they are revoked automatically.
    #4F81BD 1.0pt; mso-border-bottom-themecolor: accent1; mso-border-top-alt: solid #4F81BD 1.0pt; mso-border-top-themecolor: accent1; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;">
    #4F81BD 1.0pt; border-left: none; border-right: none; border-top: solid #4F81BD 1.0pt; mso-border-bottom-themecolor: accent1; mso-border-top-themecolor: accent1; padding: 0in 5.4pt 0in 5.4pt; width: 108.9pt;" valign="top" width="145">
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">Column
    #4F81BD 1.0pt; border-left: none; border-right: none; border-top: solid #4F81BD 1.0pt; mso-border-bottom-themecolor: accent1; mso-border-top-themecolor: accent1; padding: 0in 5.4pt 0in 5.4pt; width: 441.9pt;" valign="top" width="589">
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">Purpose
    #D3DFEE; border: none; mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0in 5.4pt 0in 5.4pt; width: 108.9pt;" valign="top" width="145">
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">DBUSER
    #D3DFEE; border: none; mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0in 5.4pt 0in 5.4pt; width: 441.9pt;" valign="top" width="589">
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">The database username of the visitor
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">STATUS
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">Status: A-Active, I-Inactive, X-has been extended
    #D3DFEE; border: none; mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0in 5.4pt 0in 5.4pt; width: 108.9pt;" valign="top" width="145">
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">EXPIRY_DT
    #D3DFEE; border: none; mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0in 5.4pt 0in 5.4pt; width: 441.9pt;" valign="top" width="589">
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">Date/Time the privilege is supposed to expire
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">GRANT_DT
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">Date/Time the privilege was granted
    #D3DFEE; border: none; mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0in 5.4pt 0in 5.4pt; width: 108.9pt;" valign="top" width="145">
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">CHANGE_DT
    #D3DFEE; border: none; mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0in 5.4pt 0in 5.4pt; width: 441.9pt;" valign="top" width="589">
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">Date/Time a change occurred, e.g. extended
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">REVOKE_DT
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">Date/Time the privilege was revoked (after expiration)
    #D3DFEE; border: none; mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0in 5.4pt 0in 5.4pt; width: 108.9pt;" valign="top" width="145">
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">PRIV_NAME
    #D3DFEE; border: none; mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0in 5.4pt 0in 5.4pt; width: 441.9pt;" valign="top" width="589">
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">The name of the privilege, e.g. “create table”
    #4F81BD 1.0pt; border: none; mso-border-bottom-themecolor: accent1; padding: 0in 5.4pt 0in 5.4pt; width: 108.9pt;" valign="top" width="145">
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">COMMENTS
    #4F81BD 1.0pt; border: none; mso-border-bottom-themecolor: accent1; padding: 0in 5.4pt 0in 5.4pt; width: 441.9pt;" valign="top" width="589">
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">Comments

    DBVISITOR_PROPERTIES

    It’s part of the tool, this stores the default values of various parameters used in the DBVisitor tool.


    --> --> #4F81BD 1.0pt; mso-border-bottom-themecolor: accent1; mso-border-top-alt: solid #4F81BD 1.0pt; mso-border-top-themecolor: accent1; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;">
    #4F81BD 1.0pt; border-left: none; border-right: none; border-top: solid #4F81BD 1.0pt; mso-border-bottom-themecolor: accent1; mso-border-top-themecolor: accent1; padding: 0in 5.4pt 0in 5.4pt; width: 108.9pt;" valign="top" width="145">
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">Column
    #4F81BD 1.0pt; border-left: none; border-right: none; border-top: solid #4F81BD 1.0pt; mso-border-bottom-themecolor: accent1; mso-border-top-themecolor: accent1; padding: 0in 5.4pt 0in 5.4pt; width: 441.9pt;" valign="top" width="589">
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">Purpose
    #D3DFEE; border: none; mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0in 5.4pt 0in 5.4pt; width: 108.9pt;" valign="top" width="145">
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">NAME
    #D3DFEE; border: none; mso-background-themecolor: accent1; mso-background-themetint: 63; padding: 0in 5.4pt 0in 5.4pt; width: 441.9pt;" valign="top" width="589">
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">The name of the property, e.g. “DEFAULT_ROLE”
    #4F81BD 1.0pt; border: none; mso-border-bottom-themecolor: accent1; padding: 0in 5.4pt 0in 5.4pt; width: 108.9pt;" valign="top" width="145">
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">VALUE
    #4F81BD 1.0pt; border: none; mso-border-bottom-themecolor: accent1; padding: 0in 5.4pt 0in 5.4pt; width: 441.9pt;" valign="top" width="589">
    #365f91; mso-themecolor: accent1; mso-themeshade: 191;">The value of the property, e.g. “VISITOR”

    -->

    Procedures

    ADD_VISITOR

    Purpose : Adding a visitor user to the database which has a built-in expiration date after  which the database user account is either locked or dropped, based on settings.

    Usage  : This accepts 8 parameters:
                    p_username  = the username to be created. This is prefixed by a predefined
                                 prefix when the user is created. If omitted, the default is
                                 VISITOR where is a unique number.
                    p_duration  = the duration after which the user is expired
                    p_dur_unit  = the unit in which the above parameter is mentioned. Valid values
                                 are DAY(S), HOUR(S) and MINUTE(S). Can't exceed 90 days.
                    p_role      = the role granted to the visitor automatically
                    p_password  = the password to be used for the user. This password is used only
                                  for initial login. the user must change the password immediately.
                    p_exp_proc  = how the account is to be expired, i.e. LOCK or DROP
                    p_email     = the email ID of the user. For convenience you can specify SW
                                  for starwoodhotels.com. starwood, sw.com, star will work too. acn,
                                  acc, accenture will work for accenture.com.
                    p_comments  = any free format comments (up to 2000) chars can be used.

                    All these parameters are optional. If omitted, the default values are picked up
                    from a table called DBVISITOR_PROPERTIES.

    ADD_PRIVILEGE

    Purpose     : Adding a database privilege (create session, select on tableName, etc.) to a visitor user in the database which has a built-in expiration date after which the privilege is revoked automatically.

    Usage       : This accepts 5 parameters:
                    p_username  = the username to which the privilege is to be granted.
                    p_privilege = the privilege to be granted (e.g. 'create session')
                    p_duration  = the duration after which the privilege is expired
                    p_dur_unit  = the unit in which the above parameter is mentioned. Valid values
                                 are DAY(S), HOUR(S) and MINUTE(S). Can't exceed 90 days.
                    p_comments  = any free format comments (up to 2000) chars can be used.

                    All these parameters, except user and privilege, are optional. If omitted,
                    the default values are picked up from a table called DBVISITOR_PROPERTIES.
                    There is no default for the p_comments parameter.

    EXTEND_VISIT_TIME

    Purpose     : DBVisitor is tool to create a user in the DB with a built-in expiration after which the database user account is either locked or dropped, based on settings.  This procedure is used to extend that expiration date.

    Usage       : This accepts 4 parameters:
                    p_username      = the username to for which the time should be extended.
                    p_extend_time   = the duration by which the time is to be extended
                    p_extend_unit   = the unit in which the above parameter is mentioned. Valid values
                                 are DAY(S), HOUR(S) and MINUTE(S). Can't exceed 90 days.
                    p_comments  = any free format comments (up to 2000) chars can be used.
                                 
                    All these parameters are optional. If omitted, the default values are picked up
                    from a table called DBVISITOR_PROPERTIES.

    EXTEND_PRIV_TIME

    Purpose     : To extend the expiration time for a database privilege (e.g. create session) of a visitor user in the database which has a built-in expiration date afterwhich the privilege is revoked automatically.

    Usage       : This accepts 5 parameters:
                    p_username  = the username to which the privilege is to be granted.
                    p_priv_name = the privilege to be extended (e.g. 'create session'). This 
                                  must already exists for the user. Use add_privilege if not.
                    p_duration  = the time extension after the original expiration
                    p_dur_unit  = the unit in which the above parameter is mentioned. Valid values
                                 are DAY(S), HOUR(S) and MINUTE(S). Can't exceed 90 days.
                    p_comments  = any free format comments (up to 2000) chars can be used.
        
                    All these parameters, except user and privilege, are optional. If omitted,
                    the default values are picked up from a table called DBVISITOR_PROPERTIES.
                    There is no default for the p_comments parameter.

    UNLOCK_VISITOR

    Purpose     :
                  This procedure is used to unlock the account that was locked earlier by the
                  tool after it expired. You can only unlock an account; it will not work if
                  the visitor was dropped. You can set the expiration time (from now) for this 
                  newly unlocked accounts.

    Usage       : This accepts 4 parameters:
                    p_username      = the username which is to be unlocked.
                    p_extend_time   = the duration by which the time is to be extended (from now)
                    p_extend_unit   = the unit in which the above parameter is mentioned. Valid values
                                      are DAY(S), HOUR(S) and MINUTE(S). Can't exceed 90 days.
                    p_comments      = any free format comments (up to 2000) chars can be used.

                    All these parameters are optional. If omitted, the default values are picked up
                    from a table called DBVISITOR_PROPERTIES.

    SEND_REMINDER_EMAILS

    Purpose     : This stored procedure reads through the dbvisitor_expiration and sends reminder
                  emails to the visitors whose account is expiring in stated number of days. The
                  reminder is sent only once. The column reminder_sent_dt is populated; and
                  reminder for that user is not sent again.

    Usage       : This has only one parameter
                  p_before_days = the number of days after which the visits accounts will be inactivated. If you want  
                                  hours, simply pass on that many  days, e.g. for 9 hours, enter 9/24. Default is 7 days.

    EXPIRE_VISITORS

    Purpose     : This stored procedure reads through the dbvisitor_expiration and inactivates the users for which the expiration date has been past. The user is either dropped or locked depending on the setting for each visitor.

    Usage       : This has no parameter. It is called from a scheduler job.

    EXPIRE_VISITOR_PRIVS

    Purpose     : This stored procedure reads through the dbvisitor_privs and revokes the privileges for which the expiration date has been past.

    Usage       : This has no parameter. It is called from a scheduler job.