Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    earth
    Posts
    96

    Unanswered: Oracle Create Context

    Hello,

    Does DB2 has the similar create context which is similar to Oracle?

    DB2 10.5 FP3 has Oracle Compatibility Vector=ORA, does it support Oracle Create Context?

    Examples from Oracle site
    ===================

    Creating an Application Context: Example This example uses the PL/SQL package emp_mgmt, created in "Creating a Package: Example", which validates and secures the hr application. The following statement creates the context namespace hr_context and associates it with the package emp_mgmt:

    CREATE CONTEXT hr_context USING emp_mgmt;

    You can control data access based on this context using the SYS_CONTEXT function. For example, suppose your emp_mgmt package has defined an attribute new_empno as a particular employee identifier. You can secure the base table employees by creating a view that restricts access based on the value of new_empno, as follows:

    CREATE VIEW hr_org_secure_view AS
    SELECT * FROM employees
    WHERE employee_id = SYS_CONTEXT('hr_context', 'new_empno');

    Thanks in advance!

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    DB2 does not support Oracle Context, obviously, but you can easily mimic the behaviour by using either global or package variables.

    If you are after security, consider using row and column access control instead.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hello,

    You can use global variables with some naming convention, for example:
    ORAENV.CTX$namespace$parameter
    Using this you can create your own SYS_CONTEXT function like this:
    Code:
    --#SET TERMINATOR @
    
    SET PATH = SYSTEM PATH, ORAENV@
    
    CREATE OR REPLACE FUNCTION ORAENV.SYS_CONTEXT (
      p_namespace  VARCHAR(255)
    , p_parameter  VARCHAR(255)
    )
    RETURNS VARCHAR(255)
    SPECIFIC SYS_CONTEXT 
    READS SQL
    DETERMINISTIC 
    NO EXTERNAL ACTION
    BEGIN
      DECLARE l_res VARCHAR(255);
      
      IF (UPPER(p_namespace)='USERENV') THEN 
        RETURN  
      (
      SELECT
      CASE UPPER(p_parameter)
        WHEN 'CURRENT_SCHEMA'    THEN CURRENT SCHEMA
        WHEN 'SID'               THEN CHAR(C.APPLICATION_HANDLE)  
        WHEN 'INSTANCE_NAME'     THEN E.INST_NAME 
        WHEN 'MODULE'            THEN C.APPLICATION_NAME
        WHEN 'CURRENT_USER'      THEN CURRENT USER 
        WHEN 'SESSION_USER'      THEN CURRENT USER
        WHEN 'OS_USER'           THEN C.CLIENT_USERID
        WHEN 'SERVER_HOST'       THEN S.HOST_NAME
        WHEN 'DB_NAME'           THEN CURRENT SERVER
        WHEN 'IP_ADDRESS'        THEN CLIENT_IPADDR 
        WHEN 'HOST'              THEN C.CLIENT_WRKSTNNAME 
        WHEN 'TERMINAL'          THEN C.CLIENT_WRKSTNNAME 
        WHEN 'SESSIONID'         THEN C.APPLICATION_ID
        WHEN 'CLIENT_IDENTIFIER' THEN C.SYSTEM_AUTH_ID||':'||CLIENT_IPADDR
        ELSE RAISE_ERROR('75001', 'parameter "'||p_parameter||'" for namespace "'||p_namespace||'" is not supported')
      END
      FROM 
        TABLE(MON_GET_CONNECTION (mon_get_application_handle(), -1)) C
      , SYSIBMADM.ENV_INST_INFO E, SYSIBMADM.ENV_SYS_INFO S
      );
      ELSE 
        -- user contexts
        PREPARE S1 FROM 'SET ? = (VALUES ORAENV.CTX$'||p_namespace||'$'||p_parameter||')';
        EXECUTE S1 INTO l_res;
        RETURN l_res;
      END IF;
    END
    @
    and create your own contexts and their attributes like this:
    Code:
    CREATE VARIABLE ORAENV.CTX$HR_CONTEXT$NEW_EMPNO ANCHOR employees.employee_id@
    SET ORAENV.CTX$HR_CONTEXT$NEW_EMPNO='000010'@
    VALUES SYS_CONTEXT('hr_context', 'new_empno')@
    You can set these attributes by a connect procedure which works like an Oracle "logon trigger".
    Regards,
    Mark.

  4. #4
    Join Date
    Feb 2004
    Location
    earth
    Posts
    96
    Thank you Ni and Mark.

    Hi Ni,

    I have used RCAC and DB2 security roles and privileges to handle each session users access to data in each table, now I am using session variables as add-on on top of RCAC protected tables, that will add another layer of filtering on protected table.

    Hi Mark,
    Thanks for the guide, the session_context works.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •