Monday, September 30, 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.



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 <n> hours, where <n> 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 <n> 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.
Column
Purpose
DBUSER
The database username of the visitor
STATUS
Status: A-Active, I-Inactive, X-has been extended, U-unlocked
CREATED_DT
Date/Time the visitor was created
EXPIRY_DT
Date/Time the visitor is supposed to expire
EXP_PROCESS
How the expiration will occure – LOCK/DROP
CHANGE_DT
Date/Time a change was made, e.g. extended, or unlocked
REMINDER_SENT_DT
Date/Time a reminder was sent that an expiration is approaching
EMAIL
The Email ID
LOCKED_DT
Date/Time the visitor’s DB account was locked
DROPPED_DT
Date/Time the visitor’s DB account was dropped
COMMENTS
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.
Column
Purpose
DBUSER
The database username of the visitor
STATUS
Status: A-Active, I-Inactive, X-has been extended
EXPIRY_DT
Date/Time the privilege is supposed to expire
GRANT_DT
Date/Time the privilege was granted
CHANGE_DT
Date/Time a change occurred, e.g. extended
REVOKE_DT
Date/Time the privilege was revoked (after expiration)
PRIV_NAME
The name of the privilege, e.g. “create table”
COMMENTS
Comments

DBVISITOR_PROPERTIES

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


--> -->
Column
Purpose
NAME
The name of the property, e.g. “DEFAULT_ROLE”
VALUE
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<n> where <n> 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.

Translate