Switching Session User in Oracle Database

In Oracle Database, session identity and schema context are important concepts for security, auditing, and application design.

Unlike some database systems, Oracle does not allow direct user switching inside the same authenticated session. However, there are secure and supported alternatives depending on your objective.

This article explains:

• The difference between user and schema
• Supported methods to simulate or perform user switching
• Security considerations
• Practical SQL examples

1. Understanding User vs Schema in Oracle

In Oracle:

• A User = Authentication identity (login account)
• A Schema = Logical container of database objects owned by a user

When you connect:

CONNECT scott/tiger

• USER = SCOTT
• CURRENT_SCHEMA = SCOTT

These can be different.

Check session details:

SELECT 
    USER,
    SYS_CONTEXT('USERENV','SESSION_USER') AS session_user,
    SYS_CONTEXT('USERENV','CURRENT_USER') AS current_user,
    SYS_CONTEXT('USERENV','CURRENT_SCHEMA') AS current_schema
FROM dual;

2. Method 1 – ALTER SESSION SET CURRENT_SCHEMA

This is the most common method when you want to access objects from another schema without reconnecting.

ALTER SESSION SET CURRENT_SCHEMA = hr;

What changes?

• Default schema for object resolution
• Does NOT change authentication user
• Does NOT grant additional privileges

Example

ALTER SESSION SET CURRENT_SCHEMA = hr;
SELECT * FROM employees;   -- instead of hr.employees

Important

Privileges are still checked against the logged-in user.

If the connected user does not have permission on hr.employees, the query fails.

3. Method 2 – CONNECT Command (Creates New Session)

Inside SQL*Plus:

CONNECT hr/hr_password;

This:

• Terminates the current session
• Creates a new authenticated session
• Changes both USER and CURRENT_SCHEMA

This is not a real “switch” — it is a reconnection.

4. Differences Between SESSION_USER, CURRENT_USER, and CURRENT_SCHEMA

Attribute	Meaning
SESSION_USER	Original authenticated user
CURRENT_USER	Effective user in definer/invoker rights
CURRENT_SCHEMA	Default schema for object resolution

5. Conclusion

In Oracle Database:

• You cannot directly change the authenticated user in a session.
• You can change the schema context.

Understanding the difference between authentication identity and schema context is critical for DBAs and architects designing secure Oracle environments.