Month: September 2013

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.


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.


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.


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.


    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:
    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

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


    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

    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


    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                            

    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
    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 <n> hours, where <n> is something you supply.

    Quick Reference

    Transform Data Center Performance with the Oracle SPARC Runner Video Game

    Disaster has struck the Acme Co. data center! I’m Ora and I need your help! Aging, inefficient servers have left  the data center unable to respond to changing business needs. Tasked with increasing application performance, accelerating deployment speed, and consolidating multiple systems, Acme’s IT Manager called the best in the business—me, Ora, the SPARC Runner—to help him out.

    But I can’t do this all by myself. I need your help navigating through five exciting, but treacherous data center levels. Help me jump, dash, and slide through complex and inefficient IT environments so I can:

    • Accelerate performance with the world’s fastest microprocessor
    • Increase speed with servers and OS that are co-engineered to do more together
    • Consolidate multiple legacy “IBS” servers onto a single SPARC architecture

    See how many inferior “IBS” servers you can consolidate and challenge your friends to beat your score in this awesome new data center adventure video game, debuting at Oracle OpenWorld. Think you have what it takes? Play the new Oracle SPARC Runner and test your grit!

    What do you think of the game and what was your high score?

    Virtual Integrity

    A recent question on the Oracle-L list server described a problem with data coming in from SQL Server and an oddity with referential integrity failing on Oracle because (for example) a child row was in lower case while the parent was in upper. This raised a few comments on how you might handle referential integrity […]

    UK OUG TECH 2013

    Well ladies and gentlemen, it seems that I’ll be sharing my thoughts on different ways of streaming data from the Oracle database.  The material I’ll be presenting will include and extend on my posts Polling for new data and Polling for new data: Part 2.  Come and see me here and we can have a good […]

    UKOUG New Feature(s)

    The UKOUG Tech13 conference will be held in Manchester this year and a new feature for the conference launch is that of the Featured Speaker of the week, in the weeks leading up to the event. I am pleased to have been chosen as the first Featured Speaker last week and that my Oracle colleague […]

    Openworld is Over – Now We’re Coming to India..!

    Oracle Openworld 2013 is now over, but no sooner have we unpacked from that trip, we’re packing again for our next one – our BI Masterclass Tour for India, starting in a few week’s time in Bangalore. Running in partnership with ODTUG and with myself, Venkat Janakiraman and Stewart Bryson leading the sessions, we’re looking […]

    TEL/電話+86 13764045638
    QQ 47079569