Introduction
What is a database user? In my opinion, there are two kinds of users:- 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.
- Human Users - these are accounts created for real human beings.
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 : DROPAnd 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:58Note 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.
-->• 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 reminderemails 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.
2 comments:
Very Useful article, Thanks For Sharing With Us
Here a related Stuff:
Digital Marketing Course Online Free
What Is SEO
Digital Marketing Online Training
Digital Marketing Training Online
What a cute blog! Thanks for sharing the link!!
Prayer Times For pc
Post a Comment