Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Oracle dbms_session

Oracle Database Tips by Donald Burleson


Using Oracle dbms_session


For the VPD to properly use the security policy to add the where clause to the end user's SQL, Oracle must know details about the authority of the user. This is done at sign-on time using Oracle's dbms_session package. At sign-on, a database logon trigger executes, setting the application context for the user by calling dbms_session.set_context.

The set_context procedure can be used to set any number of variables about the end user, including the application name, the user's name, and specific row restriction information. Once this data is collected, the security policy will use this information to build the run-time where clause to append to the end user's SQL statement. The set_context procedure sets several parameters that are used by the VPD, and accepts three arguments:

dbms_session.set_context(namespace, attribute, value)

For example, let's assume that we have a publication table and we want to restrict access based on the type of end user. Managers will be able to view all books for their publishing company, while authors may only view their own books. Let's assume that user JSMITH is a manager and user MAULT is an author. At login time, the Oracle database logon trigger would generate the appropriate values and execute the statements shown in Listing A:

dbms_session.set_context('publishing_application', 'role_name', 'manager');

dbms_session.set_context('publishing_application', 'user_name', 'jsmith');

dbms_session.set_context('publishing_application', 'company', 'rampant_techpress');

dbms_session.set_context('publishing_application', 'role_name', 'author');

dbms_session.set_context('publishing_application', 'user_name', 'mault');

dbms_session.set_context('publishing_application', 'company', 'rampant_techpress');

 

The dbms_session package provides a number of useful procedures and functions related to managing and/or controlling sessions. Begin by breaking down those offerings along related lines of usefulness starting with the general purpose ones for simply getting or setting session level attributes or characteristics. They are all fairly straightforward and simple, so no examples are given for them.

- Returns a unique id for a session

DBMS_SESSION.UNIQUE_SESSION_ID RETURN VARCHAR2;

- Returns whether current session is still active

DBMS_SESSION.IS_SESSION_ALIVE (uniqueid VARCHAR2) RETURN BOOLEAN;

- Returns whether named role is enabled for session

DBMS_SESSION.IS_ROLE_ENABLED (rolename VARCHAR2) RETURN BOOLEAN;

- Enables and disables named role for session

- Same as SQL command: SET ROLE

DBMS_SESSION.SET_ROLE (role_cmd VARCHAR2);

- Permits setting session's various globalization (NLS) settings

- Same as SQL command: ALTER SESSION SET nls_parameter = value

DBMS_SESSION.SET_NLS (param VARCHAR2, value VARCHAR2);

- Permits setting session trace flag on or off

- Same as SQL command: ALTER SESSION SET SQL_TRACE = boolean

DBMS_SESSION.SET_SQL_TRACE (sql_trace boolean);

- Permits closing an open database link

- Same as SQL command: ALTER SESSION CLOSE DATABSE LINK dblink_name

DBMS_SESSION.CLOSE_DATABASE_LINK (dblink VARCHAR2);

- Frees up unused memory after large operations (> 100K)

DBMS_SESSION.FREE_UNUSED_USER_MEMORY;

- Permits session to de-instantiate (i.e. unload from memory)

-- all packages and their memory, cursors, global variables, etc

DBMS_SESSION.RESET_PACKAGE;

- Permits session to change the current resource consumer group

DBMS_SESSION.switch_current_consumer_group (

new_consumer_group IN VARCHAR2,

old_consumer_group OUT VARCHAR2,

initial_group_on_error IN BOOLEAN);

 

The remaining procedures and functions primarily support row level security (RLS), also referred to as virtual private databases (VPD) and fine grained access control. Examples of using these functions are shown in the section on row level security, so identify their names, purpose, parameters and defaults.

Set_identifier, clear_set_identifier and clear_identifier procedures permit setting and clearing the client ID for the session. The client ID of a session is used to map it to some corresponding global application context, which is necessary for RLS and/or VPD.

- Permits setting the session application-specific identtifier

DBMS_SESSION.SET_IDENTIFIER (client_id VARCHAR2);

- Permits clearing the session application-specific identtifier

DBMS_SESSION.CLEAR_IDENTIFIER;

 

Now move onto procedures and functions specifically for managing contexts for a session. These are a little more complicated and the following data type needs to be worked with when listing a session's active contexts:


TYPE AppCtxRecTyp IS RECORD (

namespace VARCHAR2(30),

attribute VARCHAR2(30),

value VARCHAR2(256));

TYPE AppCtxTabTyp IS TABLE OF AppCtxRecTyp INDEX BY BINARY_INTEGER;

 

The list_context procedure is also rather simple; it returns an array of the contexts using this data type and the count of returned entries.

DBMS_SESSION.LIST_CONTEXT (list OUT AppCtxTabTyp, size OUT NUMBER);

 

Here is an example of using this procedure:


.list_context_demo.sql script

SET SERVEROUTPUT ON

DECLARE

array_size INT;

array_recs DBMS_SESSION.AppCtxTabTyp;

BEGIN

array_size := 0;

DBMS_SESSION.LIST_CONTEXT (array_recs, array_size);

for i in 1 .. array_size loop

DBMS_OUTPUT.PUT_LINE('Context Name = ' || array_recs(i).namespace);

DBMS_OUTPUT.PUT_LINE('...Atrribute = ' || array_recs(i).attribute);

DBMS_OUTPUT.PUT_LINE('...Value = ' || array_recs(i).value);

end loop;

END;

/

 

The remaining three procedures are entirely for managing the corresponding global application context, which is necessary for RLS and/or VPD and whose examples are in the section on role level security.


Set_context sets the specified context for a given namespace, of which there are four types: session local, globally initialized, externally initialized, and globally accessed.

Argument

Type

In / Out

Default Value

NAMESPACE

VARCHAR2

IN

ATTRIBUTE

VARCHAR2

IN

VALUE

VARCHAR2

IN

USERNAME

VARCHAR2

IN

NULL

CLIENT_ID

VARCHAR2

IN

NULL

Table 6.133: Set_context Parameters


Clear_context
clears the named context for a given namespace.

Argument

Type

In / Out

Default Value

NAME_SPACE

VARCHAR2

IN

CLIENT_IDENTIFIER

VARCHAR2

IN

ATTRIBUTE

VARCHAR2

IN

Table 6.134: Clear_context Parameters

Clear_all_context clears all contexts for a given namespace.

Argument

Type

In / Out

Default Value

NAME_SPACE

VARCHAR2

IN

Table 6.135: Clear_all_context Parameters

 
 
 
Get the Complete
Oracle Utility Information 

The landmark book "Advanced Oracle Utilities The Definitive Reference"  contains over 600 pages of filled with valuable information on Oracle's secret utilities. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.
 


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.