Examples
The global context example presented here is based on a homeland security
scenario. In this there is the concept of a threat level. The threat level is a global
value to which our applications will reference when determining access to data.
When the value is set to normal, users can access all the data. When the threat
level is elevated, the applications tighten their security. You’ll create the global
context first and then see how you can use it in a security implementation.
To begin, the syntax for creating the context differs from local application
contexts. There are several different uses of a global context, as you will see. As
the values for the global context are stored in the SGA, the database provides a
way to monitor how much SGA a user session is utilizing. Querying the
USERENV for the global_context_memory provides this information:
SELECT SYS_CONTEXT ('userenv', 'global_context_memory')
FROM DUAL;
To create a global context, add the “accessed globally” to the end of the create
context DDL statement:
CREATE CONTEXT global_threat_ctx
USING sec_mgr.global_ctx_mgr ACCESSED GLOBALLY;
Context created
The DBMS_SESSION.SET_CONTEXT procedure accepts five parameters. By
varying the values passed, you vary how the resulting context can be used. The
procedure specification is as follows:
PROCEDURE SET_CONTEXT
Argument Name Type In/Out Default?
-------------- ---------- --------- ------ --
NAMESPACE VARCHAR2 IN
ATTRIBUTE VARCHAR2 IN
VALUE VARCHAR2 IN
USERNAME VARCHAR2 IN DEFAULT
CLIENT_ID VARCHAR2 IN DEFAULT
The USERNAME and CLIENT_ID parameters are DEFAULT, which means you
don’t have to pass a value for those parameters. Values Shared for All Users
The global examples begin with the requirement of sharing a value for all
sessions and all users in the database. Start by specifying only the first three
parameters of the SET_CONTEXT procedure, which allows the least restrictive
use of the context. Any user in the database will be able to access the values.
The namespace manager will have two procedures: one for setting the attribute
and another that clears all attributes in the namespace:
sec_mgr@KNOX10g>
CREATE OR REPLACE PACKAGE global_ctx_mgr AS
PROCEDURE set_level (p_threat_level IN VARCHAR2);
PROCEDURE clear;
END;
Package created.
sec_mgr@KNOX10g>
CREATE OR REPLACE PACKAGE BODY global_ctx_mgr AS
PROCEDURE set_level(p_threat_level IN VARCHAR2) AS
BEGIN
DBMS_SESSION.set_context(namespace => 'global_threat_ctx',
ATTRIBUTE => 'threat_level',
VALUE => p_threat_level);
END;
PROCEDURE clear AS
BEGIN
DBMS_SESSION.clear_all_context('global_threat_ctx');
END;
END;
Package body created.
Execute the procedure that sets the context and then verify the value :
sec_mgr@KNOX10g> EXEC global_ctx_mgr.set_level ('normal');
PL/SQL procedure successfully completed.
sec_mgr@KNOX10g> COL threat_level format a25
sec_mgr@KNOX10g> SELECT SYS_CONTEXT('global_threat_ctx',
'threat_level') threat_level FROM DUAL;
THREAT_LEVEL
---------------------------------------------
normal
If you’re executing this example and you get NULL instead of the output above,
your Client Identifier has been set. This is a new use of the Client Identifier. With
global application contexts, the session’s Client Identifier drives what values are
returned from the SYS_CONTEXT function. Refer to the upcoming examples for
more information. To clear your Client Identifier, execute the
CLEAR_IDENTIFIER procedure in the DBMS_SESSION package:
EXEC dbms_session.clear_identifier;
Usage :
There are many uses for global application context. In this example, the global
context will be used as the global indicator for the applications. Suppose you’re
tracking assets for the U.S. Navy. (It really doesn’t matter what the data is for this
example.) This simple table lists the assets and their status:
sec_mgr@KNOX10g> CREATE TABLE asset$ (
itemid VARCHAR2(30),
item VARCHAR2(255),
status VARCHAR2(16)
);
Table created.
sec_mgr@KNOX10g> INSERT INTO asset$
(itemid, item, status)
VALUES ('AC1234516','USS John F. Kennedy (CV 67) ',
'c-1');
1 row created.
sec_mgr@KNOX10g> COMMIT ;
Commit complete.
This example requires only a single record. Our security implementation is based
on a view. The view uses the value of the global context to determine whether the
records should be returned. If the value is normal, then all records are returned;
otherwise, no records are returned. If you don’t understand the logic in this view,
don’t worry; see Chapter 10 for more details on how views can be used to
provide data security.
sec_mgr@KNOX10g> -- Create a view based on value of context.
sec_mgr@KNOX10g> -- Show all records when value is normal,
sec_mgr@KNOX10g> -- show no records when value is elevated
or undefined
sec_mgr@KNOX10g>
CREATE OR REPLACE VIEW assets AS
SELECT itemid, item, status FROM asset$
WHERE 1 = decode(SYS_CONTEXT('global_threat_ctx',
'threat_level'), 'normal',1,'elevated',0,0);
View created.
sec_mgr@KNOX10g> GRANT SELECT ON assets TO scott;
Grant succeeded.
To test this policy, first query the view and then change to the SCOTT schema
and query the view, which shows the results are consistent:
sec_mgr@KNOX10g> -- test View
sec_mgr@KNOX10g> EXEC global_ctx_mgr.set_level ('normal');
PL/SQL procedure successfully completed.
sec_mgr@KNOX10g> COL item format a30
sec_mgr@KNOX10g> COL status format a6
sec_mgr@KNOX10g> SELECT item, status FROM assets;
ITEM STATUS
-------------------------- -----------
USS John F. Kennedy (CV 67) c-1
sec_mgr@KNOX10g> -- value exists for SCOTT too
sec_mgr@KNOX10g> CONN scott/tiger Connected.
scott@KNOX10g> CREATE SYNONYM assets FOR sec_mgr.assets;
Synonym created.
scott@KNOX10g> SELECT item, status FROM assets;
ITEM STATUS
------------------------ -------
USS John F. Kennedy (CV 67) c-1
You can set the value to elevated and verify the security policy is implemented
correctly. Note, you don’t have to clear the context value first.
scott@KNOX10g> CONN sec_mgr/oracle10g Connected.
sec_mgr@KNOX10g> -- Change in value changes result set
sec_mgr@KNOX10g> EXEC global_ctx_mgr.set_level ('elevated');
PL/SQL procedure successfully completed.
sec_mgr@KNOX10g> -- Will not get any records since value is
"elevated"
sec_mgr@KNOX10g> SELECT count(*) FROM assets;
COUNT(*)
----------
0
sec_mgr@KNOX10g> CONN scott/tiger Connected.
scott@KNOX10g> SELECT count(*) FROM assets;
COUNT(*)
----------
0
Values Shared for All Sessions of Same Schema
In the next example, the username parameter is passed to the SET_CONTEXT
procedure. The desired effect is to make the attribute value sharable across all
sessions for the same schema. Note the same schema will be used for different
end users for both connection pool applications (not using proxy authentication)
and for the shared schema implementation of directory authenticated Enterprise
Users.
This is a different definition of global: it is global but only for a schema. If you are
not part of the schema, you cannot access the value or you get a different value.
sec_mgr@KNOX10g>CREATE OR REPLACE PACKAGE global_ctx_mgr
AS
PROCEDURE set_level (p_threat_level IN VARCHAR2,
p_user IN VARCHAR2);
PROCEDURE clear;
END;
Package created.
sec_mgr@KNOX10g>CREATE OR REPLACE PACKAGE BODY
global_ctx_mgr AS
PROCEDURE set_level (p_threat_level IN VARCHAR2,
p_user IN VARCHAR2) AS
BEGIN
DBMS_SESSION.set_context(namespace => 'global_threat_ctx',
ATTRIBUTE => 'threat_level',
VALUE => p_threat_level,
username => p_user);
END;
PROCEDURE clear AS
BEGIN
DBMS_SESSION.clear_all_context('global_threat_ctx');
END;
END;
Package body created.
sec_mgr@KNOX10g> EXEC global_ctx_mgr.set_level('normal',
'SEC_MGR');
PL/SQL procedure successfully completed.
sec_mgr@KNOX10g> SELECT item, status FROM assets;
ITEM STATUS
---------------------------- --------
USS John F. Kennedy (CV 67) c-1
sec_mgr@KNOX10g> -- SCOTT no longer has a value for this
sec_mgr@KNOX10g> CONN scott/tiger Connected.
scott@KNOX10g> SELECT item, status FROM assets;
no rows selected
The security is now more obvious. The global context values are only visible to a
specific schema. Because all sessions of the schema will see the value, this is an
ideal design for connection pools and shared schemas for directory authenticated
users.
Note that you could have specified any username in the SET_CONTEXT call
because there is no mechanism restricting the setting of values for users within
global contexts. If the SEC_MGR set the value for SCOTT user, then the
SEC_MGR could set it but would be unable to access it afterward.
scott@KNOX10g> CONN sec_mgr/oracle10g Connected.
sec_mgr@KNOX10g> -- Set the value for SCOTT.
sec_mgr@KNOX10g> -- This "erases" the value that was stored
for SEC_MGR
sec_mgr@KNOX10g> EXEC global_ctx_mgr.set_level ('normal',
'SCOTT');
PL/SQL procedure successfully completed.
sec_mgr@KNOX10g> SELECT item, status FROM assets;
no rows selected
sec_mgr@KNOX10g> -- SCOTT can see the value
sec_mgr@KNOX10g> CONN scott/tiger Connected.
scott@KNOX10g> SELECT item, status FROM assets;
ITEM STATUS
---------------------------- ---------
USS John F. Kennedy (CV 67) c-1
The lesson here is the more information you pass to the SET_CONTEXT
procedure, the more restrictive the access becomes.
Using the Client Identifier
As you may have noticed in the first example, the value of the Client Identifier
determines the values returned by the global context. In the case that you don’t
supply a username and do supply the Client Identifier, you have effectively
shared the values across all sessions, for all schemas (users) that have the same
value set in the Client Identifier. This is consistent with the first example because
the value for the Client Identifiers was the same: they were both NULL.
For this example, the view-based security isn’t used in efforts to better present
how the context values can be set and retrieved:
sec_mgr@KNOX10g> CREATE OR REPLACE PACKAGE global_ctx_mgr
AS
PROCEDURE set_level (p_threat_level IN VARCHAR2,
p_client_id IN VARCHAR2);
PROCEDURE clear;
END;
Package created.
sec_mgr@KNOX10g> CREATE OR REPLACE PACKAGE BODY
global_ctx_mgr AS
PROCEDURE set_level (p_threat_level IN VARCHAR2,
p_client_id IN VARCHAR2) AS
BEGIN
DBMS_SESSION.set_context(namespace => 'global_threat_ctx',
ATTRIBUTE => 'threat_level',
VALUE => p_threat_level,
client_id => p_client_id);
END;
PROCEDURE clear AS
BEGIN
DBMS_SESSION.clear_all_context('global_threat_ctx');
END;
END;
Package body created.
Notice the namespace manager does not specify a USER but does specify the
Client Identifier. To illustrate this, call the procedure twice. The first time, set one
format associated with the Client Identifier of “Application Alpha”. The second
call, change the Client Identifier to “Application Beta”. But first, reset the values in
the namespace:
sec_mgr@KNOX10g> -- Clear the namespace
sec_mgr@KNOX10g> EXEC global_ctx_mgr.clear;
PL/SQL procedure successfully completed.
Next, set a different value for two different Client Identifiers. The Identifiers
represent two separate applications:
sec_mgr@KNOX10g> -- Set global context for ID
sec_mgr@KNOX10g>
BEGIN
global_ctx_mgr.set_level ('App A Value',
'Application Alpha');
global_ctx_mgr.set_level ('App B Value',
'Application Beta');
END;
PL/SQL procedure successfully completed.
To test, set your Client Identifier to the different values and check the context. Do
this for both users to demonstrate that the values are accessible to any database
user that has the Client Identifier set appropriately.
sec_mgr@KNOX10g> -- set Client ID
sec_mgr@KNOX10g>
EXEC dbms_session.set_identifier('Application Alpha')
PL/SQL procedure successfully completed.
sec_mgr@KNOX10g> COL threat_level format a25
sec_mgr@KNOX10g> SELECT SYS_CONTEXT ('global_threat_ctx',
'threat_level') threat_level FROM DUAL;
THREAT_LEVEL
-----------------------
App A Value
sec_mgr@KNOX10g>
EXEC dbms_session.set_identifier('Application Beta');
PL/SQL procedure successfully completed.
sec_mgr@KNOX10g> SELECT SYS_CONTEXT ('global_threat_ctx',
'threat_level') threat_level FROM DUAL;
THREAT_LEVEL
--------------------------
App B Value
sec_mgr@KNOX10g> CONN scott/tiger Connected.
scott@KNOX10g> -- Client ID is not set, so no value
scott@KNOX10g> SELECT SYS_CONTEXT('global_threat_ctx',
'threat_level') threat_level FROM DUAL;
THREAT_LEVEL
-----------------
scott@KNOX10g> -- set ID allows access to value
scott@KNOX10g>
EXEC dbms_session.set_identifier('Application Alpha');
PL/SQL procedure successfully completed.
scott@KNOX10g> /
THREAT_LEVEL
---------------------
App A Value
scott@KNOX10g>
EXEC dbms_session.set_identifier('Application Beta');
PL/SQL procedure successfully completed.
scott@KNOX10g> /
THREAT_LEVEL
----------------------
App B Value
There is no inherent security in the Client Identifier by default. Any user can set it
to any value; therefore, don’t rely on this for any security-specific implementations
until you secure the Client Identifier (see Chapter 6 for an example of how to do
this).
Sharing Values While Protecting Values
The final permutation sets all the parameters in the SET_CONTEXT call. This call
has interesting effects. Just like the preceding example, the global values are
different when using different Client Identifiers within the same schema. The
difference is that the values are not shared across database schemas. The
modified SET_LEVEL procedure is shown here:
PROCEDURE set_level(p_threat_level IN VARCHAR2,
p_user IN VARCHAR2 DEFAULT NULL,
p_client_id IN VARCHAR2) AS
BEGIN
DBMS_SESSION.set_context(namespace => 'global_threat_ctx',
ATTRIBUTE => 'threat_level',
VALUE => p_threat_level,
username => p_user,
client_id => p_client_id);
END;
To illustrate this, set the username and Client Identifier. This makes the context
available only to the schema with the appropriate Client Identifier set:
sec_mgr@KNOX10g> -- Clear the namespace
sec_mgr@KNOX10g> EXEC global_ctx_mgr.clear;
PL/SQL procedure successfully completed.
sec_mgr@KNOX10g> -- Set values for the SEC_MGR user
sec_mgr@KNOX10g>
BEGIN
global_ctx_mgr.set_level(
p_threat_level => 'ClientID Alpha:SEC_MGR value',
p_user => 'SEC_MGR',
p_client_id => 'Application Alpha');
global_ctx_mgr.set_level(
p_threat_level => 'ClientID Beta:SEC_MGR value',
p_user => 'SEC_MGR',
p_client_id => 'Application Beta');
END;
PL/SQL procedure successfully completed.
sec_mgr@KNOX10g> -- show values
sec_mgr@KNOX10g>
EXEC dbms_session.set_identifier('Application Alpha')
PL/SQL procedure successfully completed.
sec_mgr@KNOX10g> COL threat_level format a30
sec_mgr@KNOX10g>
SELECT SYS_CONTEXT ('global_threat_ctx', 'threat_level')
threat_level FROM DUAL;
THREAT_LEVEL
---------------------------
ClientID Alpha:SEC_MGR value
sec_mgr@KNOX10g>
EXEC dbms_session.set_identifier('Application Beta')
PL/SQL procedure successfully completed.
sec_mgr@KNOX10g> /
THREAT_LEVEL
-----------------------------
ClientID Beta:SEC_MGR value
sec_mgr@KNOX10g> CONN scott/tiger Connected.
scott@KNOX10g> -- SCOTT cannot see values
scott@KNOX10g>
EXEC dbms_session.set_identifier('Application Alpha');
PL/SQL procedure successfully completed.
scott@KNOX10g>
SELECT SYS_CONTEXT('global_threat_ctx',
'threat_level') threat_level FROM DUAL;
THREAT_LEVEL
------------------------------------
Providing a different username with the same Client Identifier does not create an
additional (or new) entry for the schema associated with the username. Instead,
changing the username value overwrites the previous value. In the following
example, the second call, which sets the value for SCOTT, overwrites the value
just set for SEC_MGR. The SEC_MGR doesn’t get this subsequent value (it’s
secured from the SEC_MGR). Instead, the SEC_MGR value is now null. It’s
important to understand this for effective implementation, and more importantly,
for effective debugging.
scott@KNOX10g> CONN sec_mgr/oracle10g Connected.
sec_mgr@KNOX10g>
sec_mgr@KNOX10g>
BEGIN
global_ctx_mgr.set_level(
p_threat_level => 'ClientID Alpha:SEC_MGR value',
p_user => 'SEC_MGR',
p_client_id => 'Application Alpha');
-- This call erases the one just set
global_ctx_mgr.set_level(
p_threat_level => 'ClientID Alpha:SCOTT value',
p_user => 'SCOTT',
p_client_id => 'Application Alpha');
END;
/ PL/SQL procedure successfully completed.
sec_mgr@KNOX10g>
sec_mgr@KNOX10g>
EXEC dbms_session.set_identifier('Application Alpha');
PL/SQL procedure successfully completed.
sec_mgr@KNOX10g> COL threat_level format a30
sec_mgr@KNOX10g> SELECT SYS_CONTEXT('global_threat_ctx',
'threat_level') threat_level FROM DUAL;
THREAT_LEVEL
--------------------------------
sec_mgr@KNOX10g>
EXEC dbms_session.set_identifier('Application Beta');
PL/SQL procedure successfully completed.
sec_mgr@KNOX10g> /
THREAT_LEVEL
----------------------------------
ClientID Beta:SEC_MGR value
sec_mgr@KNOX10g> CONN scott/tiger Connected.
scott@KNOX10g>
EXEC dbms_session.set_identifier('Application Alpha');
PL/SQL procedure successfully completed.
scott@KNOX10g> SELECT SYS_CONTEXT('global_threat_ctx',
'threat_level') threat_level FROM DUAL;
THREAT_LEVEL
------------------------------------
ClientID Alpha:SCOTT value
scott@KNOX10g>
EXEC dbms_session.set_identifier('Application Beta');
PL/SQL procedure successfully completed.
scott@KNOX10g> /
THREAT_LEVEL
--------------------------------------
This last example illustrates some of the important aspects of understanding how
global application contexts work. If you don’t have a complete grasp of this, you
could very easily think you’re providing a secure security policy when it’s actually
an insecure solution. The global application context is a powerful and practical
tool; just ensure you have tested your SET_ CONTEXT calls to validate that the
values are appropriately shared and protected as desired.
