SQL SESSION_USER()

The SQL SESSION_USER function returns the name of the currently connected database user. It is useful for tracking session-specific information, managing access controls, and auditing database activities.

In this tutorial, we will explore the SESSION_USER function in SQL, its syntax, and practical examples demonstrating its usage.

Syntax of SQL SESSION_USER

The basic syntax of the SESSION_USER function is as follows:

</>
Copy
SELECT SESSION_USER;

This function does not take any arguments and simply returns the name of the database user for the current session.

Use Cases of SESSION_USER

  • To determine the user executing the query.
  • To audit user activities and track database access.
  • To control access and apply session-based authentication rules.

Step-by-Step Examples Using SQL SESSION_USER

1. Retrieving the Current Session User

To check the user currently connected to the database:

</>
Copy
SELECT SESSION_USER AS current_user;

Example Output:

current_user
admin_user

Explanation:

  • The query retrieves the username of the database session.
  • If user Arjun is logged in, the output would be Arjun.

2. Logging User Activity with SESSION_USER

Consider a scenario where we track changes made by users in a table named orders.

In SQL Server,

</>
Copy
CREATE TABLE order_logs (
    log_id INT PRIMARY KEY IDENTITY(1,1),
    order_id INT,
    action VARCHAR(50),
    modified_by VARCHAR(50),
    modified_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Whenever a user updates an order, we log their session user:

</>
Copy
INSERT INTO order_logs (order_id, action, modified_by)
VALUES (101, 'Order Status Updated', SESSION_USER);
</>
Copy
SELECT *
FROM order_logs;

Example Output:

Explanation:

  • The order_logs table records changes made to orders.
  • The SESSION_USER function captures the user modifying the order (e.g., Ram).
  • The timestamp logs when the action took place.

Conclusion

The SESSION_USER function in SQL is used for session tracking, auditing, and security management. In this tutorial, we covered:

  1. How to retrieve the current session user.
  2. Logging user activity using SESSION_USER.
  3. Practical applications in database management.