Application Context are memory fields that contain information about the sessions of users connected to the database.  According to the information in the application context, the logged-on user is denied or allowed access to certain objects in the database.

Each Application Context has a name and value. Depending on the type of application context, each session is held in its own memory area(UGA) or shared memory area (SGA).

The Application Context has different uses in the database.  To give a few examples of these areas,

• We use the Application Context when creating detailed access rules in Virtual Private Database (VPD) database definitions.
• When multi-tier applications access databases, global application context definitions are needed to identify user identity.

It is possible to increase the samples.  Some application contexts are provided to us by the Oracle database such as USERENV.

USERENV Usage

USERENV is an application context used to represent the properties of the session to which it is linked.

Regardless of the type of application context, its value can be accessed with the SYS_CONTEXT function.

DBMS_APPLICATION_INFO

The session information defined by DBMS_APPLICATION_INFO. It is often used to divide and separate sessions into specific groups.

AUTHENTICATION_METHOD

Indicates the connection method to the database.

CURRENT_SCHEMA

Shows a connected schema.

SELECT SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA') FROM DUAL;

DB_NAME

Shows a connected database.

SELECT SYS_CONTEXT ('USERENV', 'DB_NAME') FROM DUAL;

ENTERPRISE_IDENTITY

It is a new feature that comes with Oracle 12c. Returns the DN of users connecting from Oracle Internet Directory.

INSTANCE

Returns the instance number to which the user connects.

LANGUAGE

Keeps the language value of the session.

MODULE

Returns the name of the application that links to the database.

OS_USER

Returns the operating system user name to which the connection was established.

SERVER_HOST

Returns the name of the server on which the linked database is running.

SID

Returns the session number

TERMINAL

Returns the terminal name on the operating system side of the session.

Example

In this example, let’s assume that application tables are kept in the HR user.  Our goal is to make this user connect only from the middle layer. We want to prevent this user from connecting with any query tool (SQL * Plus) even if the password is known.

This user needs to write LOGON TRIGGER.

We will check the MODULE attribute in the USERENV application context and only allow connections with module value JDBC THIN CLIENT.

CREATE OR REPLACE TRIGGER SYS.LOGON_TRIGGER_HR

AFTER LOGON

ON HR.SCHEMA

BEGIN

IF upper (SYS_CONTEXT ('USERENV', 'MODULE') ) <> 'JDBC THIN CLIENT'

THEN

raise_application_error (-20007, 'LOGON_TRIGGER_HR - You can not connect with this module');

END IF;

END;

/

connect HR;

We will continue this topic in the next article.

See you later..

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/