Online Test Banks
Score higher
See Online Test Banks
eLearning
Learning anything is easy
Browse Online Courses
Mobile Apps
Learning on the go
Explore Mobile Apps
Dummies Store
Shop for books and more
Start Shopping

How to Use Special Variables with SQL

If a user on a client machine connects to a SQL database on a server, this connection establishes a session. If the user connects to several databases, the session associated with the most recent connection is considered the current session; previous sessions are considered dormant. SQL defines several special variables that are valuable on multiuser systems. These variables keep track of the different users.

Here’s a list of the special variables:

  • SESSION_USER: The special variable SESSION_USER holds a value that’s equal to the user authorization identifier of the current SQL session. If you write a program that performs a monitoring function, you can interrogate SESSION_USER to find out who is executing SQL statements.

  • CURRENT_USER: An SQL module may have a user-specified authorization identifier associated with it. The CURRENT_USER variable stores this value. If a module has no such identifier, CURRENT_USER has the same value as SESSION_USER.

  • SYSTEM_USER: The SYSTEM_USER variable contains the operating system’s user identifier. This identifier may differ from that same user’s identifier in an SQL module. A user may log on to the system as LARRY, for example, but identify himself to a module as PLANT_MGR. The value in SESSION_USER is PLANT_MGR. If he makes no explicit specification of the module identifier, and CURRENT_USER also contains PLANT_MGR, SYSTEM_USER holds the value LARRY.

The SYSTEM_USER, SESSION_USER, and CURRENT_USER special variables track who is using the system. You can maintain a log table and periodically insert into that table the values that SYSTEM_USER, SESSION_USER, and CURRENT_USER contain. The following example shows how:

INSERT INTO USAGELOG (SNAPSHOT)
 VALUES ('User ' || SYSTEM_USER ||
  ' with ID ' || SESSION_USER ||
  ' active at ' || CURRENT_TIMESTAMP) ;

This statement produces log entries similar to the following example:

User LARRY with ID PLANT_MGR active at 2013-04-07-23.50.00
  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
Advertisement

Inside Dummies.com

Dummies.com Sweepstakes

Win $500. Easy.