How to Implement a Database in MySQL for HTML5 and CSS3 Programming
How to Run a Script with phpMyAdmin in SQL for HTML5and CSS3 Programming
How to Calculate Date Values in SQL Data for HTML5and CSS3 Programming

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
How to Use SQL with Microsoft Access
How to Update SQL Views
SQL First, Second and Third Normal Forms
How to Build a PHP Connection to SQL Databases for HTML5and CSS3 Programming
How to Use Embedded SQL
Advertisement

Inside Dummies.com