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 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.
The session information defined by DBMS_APPLICATION_INFO. It is often used to divide and separate sessions into specific groups.
Indicates the connection method to the database.
Shows a connected schema.
SELECT SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA') FROM DUAL;
Shows a connected database.
SELECT SYS_CONTEXT ('USERENV', 'DB_NAME') FROM DUAL;
It is a new feature that comes with Oracle 12c. Returns the DN of users connecting from Oracle Internet Directory.
Returns the instance number to which the user connects.
Keeps the language value of the session.
Returns the name of the application that links to the database.
Returns the operating system user name to which the connection was established.
Returns the name of the server on which the linked database is running.
Returns the session number
Returns the terminal name on the operating system side of the session.
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; /
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.
1,115 views last month, 1 views today