With Oracle9i Database, the application context capability was augmented with the introduction of global contexts. As the name implies, these values are accessible by separate sessions and separate users. Unlike the local contexts, which are stored in the user’s private memory space (the UGA), the global contexts are stored in the shared memory area, also known as the SGA.
Global application context can be used when you want to share values across database sessions, as may be the case in connection pool applications. Note that this implies the values are not user specific or private data. With a global application context, a value can be set once, and each session of the connection pool will have access to the value. If you were to try this with local application contexts, there would have to be a context set in each session. Thus, the global context saves memory on the server machine and is easier to use because you only have to initialize it once.
Another popular application is to use the global context to pass state information about a user that is transitioning between two applications, such as from Oracle Forms to Oracle Reports. The gist is that global application contexts are a great way to pass information between database sessions. A few of the following examples also illuminate some of the possibilities available when using global contexts.
From a security perspective, you may or may not opt to use global contexts. As the name implies, the values are shared. Often the attributes that are set are session specific and sensitive. As such, local application contexts should be used. In this spirit of completeness, the various ways in which global contexts can be set and secured will be covered.
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:
sec_mgr@KNOX10g> -- Create a global context indicating current sec_mgr@KNOX10g> -- threat level sec_mgr@KNOX10g> CREATE CONTEXT global_threat_ctx 2 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.
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 2 AS 3 PROCEDURE set_level (p_threat_level IN VARCHAR2); 4 PROCEDURE clear; 5 END; 6 / Package created. sec_mgr@KNOX10g> CREATE OR REPLACE PACKAGE BODY global_ctx_mgr 2 AS 3 PROCEDURE set_level (p_threat_level IN VARCHAR2) 4 AS 5 BEGIN 6 DBMS_SESSION.set_context 7 (namespace => 'global_threat_ctx', 8 ATTRIBUTE => 'threat_level', 9 VALUE => p_threat_level); 10 END; 11 12 PROCEDURE clear 13 AS 14 BEGIN 15 DBMS_SESSION.clear_all_context 16 ('global_threat_ctx'); 17 END; 18 END; 19 / 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', 2 'threat_level') threat_level 3 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;
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$ 2 ( 3 itemid VARCHAR2(30), 4 item VARCHAR2(255), 5 status VARCHAR2(16) 6 ); Table created. sec_mgr@KNOX10g> INSERT INTO asset$ 2 (itemid, item, status) 3 VALUES ('AC1234516', 4 'USS John F. Kennedy (CV 67) ', 5 '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 2 AS 3 SELECT itemid, item, status 4 FROM asset$ 5 WHERE 1 = decode(SYS_CONTEXT ('global_threat_ctx', 6 '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
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 2 AS 3 PROCEDURE set_level ( 4 p_threat_level IN VARCHAR2, 5 p_user IN VARCHAR2); 6 7 PROCEDURE clear; 8 END; 9 / Package created. sec_mgr@KNOX10g> CREATE OR REPLACE PACKAGE BODY global_ctx_mgr 2 AS 3 PROCEDURE set_level ( 4 p_threat_level IN VARCHAR2, 5 p_user IN VARCHAR2) 6 AS 7 BEGIN 8 DBMS_SESSION.set_context 9 (namespace => 'global_threat_ctx', 10 ATTRIBUTE => 'threat_level', 11 VALUE => p_threat_level, 12 username => p_user); 13 END; 14 15 PROCEDURE clear 16 AS 17 BEGIN 18 DBMS_SESSION.clear_all_context 19 ('global_threat_ctx'); 20 END; 21 END; 22 / 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.
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 2 AS 3 PROCEDURE set_level ( 4 p_threat_level IN VARCHAR2, 5 p_client_id IN VARCHAR2); 6 PROCEDURE clear; 7 END; 8 / Package created. sec_mgr@KNOX10g> CREATE OR REPLACE PACKAGE BODY global_ctx_mgr 2 AS 3 PROCEDURE set_level ( 4 p_threat_level IN VARCHAR2, 5 p_client_id IN VARCHAR2) 6 AS 7 BEGIN 8 DBMS_SESSION.set_context 9 (namespace => 'global_threat_ctx', 10 ATTRIBUTE => 'threat_level', 11 VALUE => p_threat_level, 12 client_id => p_client_id); 13 END; 14 PROCEDURE clear 15 AS 16 BEGIN 17 DBMS_SESSION.clear_all_context 18 ('global_threat_ctx'); 19 END; 20 END; 21 / 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 2 global_ctx_mgr.set_level ('App A Value', 3 'Application Alpha'); 4 global_ctx_mgr.set_level ('App B Value', 5 'Application Beta'); 6 END; 7 / 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', 2 'threat_level') threat_level 3 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', 2 'threat_level') threat_level 3 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', 2 'threat_level') threat_level 3 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).
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 2 global_ctx_mgr.set_level 3 (p_threat_level => 'ClientID Alpha:SEC_MGR value', 4 p_user => 'SEC_MGR', 5 p_client_id => 'Application Alpha'); 6 global_ctx_mgr.set_level 7 (p_threat_level => 'ClientID Beta:SEC_MGR value', 8 p_user => 'SEC_MGR', 9 p_client_id => 'Application Beta'); 10 END; 11 / 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', 2 'threat_level') threat_level 3 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', 2 'threat_level') threat_level 3 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 2 global_ctx_mgr.set_level 3 (p_threat_level => 'ClientID Alpha:SEC_MGR value', 4 p_user => 'SEC_MGR', 5 p_client_id => 'Application Alpha'); 6 -- Thi s call erases the one just set 7 global_ctx_mgr.set_level 8 (p_threat_level => 'ClientID Alpha:SCOTT value', 9 p_user => 'SCOTT', 10 p_client_id => 'Application Alpha'); 11 END; 12 / 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', 2 'threat_level') threat_level 3 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', 2 'threat_level') threat_level 3 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 -------------------------------------- scott@KNOX10g>
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.