Tài liệu ORACLE8i- P25 doc

958 CHAPTER 21 • ORACLE8i DATABASE SECURITY

context package; user sign-on context package seems like a more appropriate name.)

The context area is an area of private memory that is unique to every session con￾nected to the database. You will create placeholders in this memory area to store some

values that are set at logon. Later in the process, you will read the context memory

area and retrieve these stored memory areas for use. This is an optional step. You

could define the context area every time the user accesses the object, but this

approach is faster, because you do these checks only once.

You place values into the context area by creating a package or function that is

called each time users log on to the database. For this project, you will store the user’s

assigned security level into a context variable whenever the user logs in. This way,

you can quickly retrieve this security value from the context area when you need it.

This saves multiple trips to the security table to look up the security ID each time you

need that value.

Before you can use a context namespace, you must first create the context name￾space, which needs to be done only once for each specific context area. The context

namespace is created using the CREATE CONTEXT command (or you can use CREATE

OR REPLACE CONTEXT, if you prefer). When you create the context namespace, you

define the package that will be used to set the values to be stored in the context

namespace. Typically, when you create the context, the associated package will not

have been created yet, and that’s okay. You will create that package next.

NOTE Creating the context namespace is similar to programming with a pointer. You

first create the pointer, and then you allocate memory to it. The same is true with a con￾text. You first create the context, and then the context setting package “allocates” what is

to be stored to the context. (Okay, so all comparisons have limitations!)

After you have created the context area, you will use the DBMS_SESSION.SET_

CONTEXT package to store a value in a context area. This package allows you to cre￾ate what amounts to variables and assign values to these variables. These variables

and their assigned values are then poked into memory for quick retrieval later. The

DBMS_SESSION.SET_CONTEXT procedure takes three parameters. Here is the proce￾dure definition:

PROCEDURE DBMS_SESSION.SET_CONTEXT

( NAMESPACE VARCHAR2,

ATTRIBUTE VARCHAR2,

VALUE VARCHAR2);

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com

959

When you call the SET_CONTEXT procedure from your context setting package or

function, you simply pass it the name of the context you want to use, the name of

the context variable you wish to create, and the value you wish to store associated

with that variable.

When you create the application context package, this package will take one param￾eter, which is the username of the user who is signing onto the database. You use the

username to cross-reference with the TBL_USER_ID table columns USER_NAME and

SECURITY_LEVEL to determine what the proper security level setting for the user

should be. You then set a context variable called security_level to the correct security

level for the user. You will use a database logon trigger to call the package.

Listing 21.7 shows the creation of a context and then the creation of the context￾checking package.

Listing 21.7: Creating the Context and Context-Checking Package

-- Grant the required privilege to create the context.

CONNECT system/robert

GRANT CREATE ANY CONTEXT TO spy_owner;

GRANT DROP ANY CONTEXT TO spy_owner;

GRANT CREATE ANY TRIGGER TO spy_owner;

GRANT ADMINISTER DATABASE TRIGGER TO spy_owner;

-- Now, connect and create the context. We will reference the

-- currently nonexisting package pkg_spy_owner_context_01 as the

-- context-checking package. We will write this package next.

CONNECT spy_owner/spy_owner

-- Drop any preexisting context.

DROP CONTEXT spy_owner.cont_spy_owner_01;

-- Create the context. Note that we could have used CREATE OR

-- REPLACE instead.

CREATE CONTEXT cont_spy_owner_01

USING spy_owner.pkg_spy_owner_context_01;

-- Now, create the pkg_spy_owner_context_01 package header.

CREATE OR REPLACE package spy_owner.pkg_spy_owner_context_01

AUTHID DEFINER AS

PROCEDURE proc_get_spy_context(p_usern IN VARCHAR2);

END;

ENFORCING ROW-LEVEL SECURITY

Beyond Simple

Database Managment

PART

III

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com

960 CHAPTER 21 • ORACLE8i DATABASE SECURITY

/

-- Now, create the package body.

CREATE OR REPLACE PACKAGE BODY pkg_spy_owner_context_01

AS

PROCEDURE proc_get_spy_context(p_usern IN VARCHAR2)

IS

V_Spy_clearance NUMBER;

BEGIN

-- Now, take the p_usern, look it up in our

-- tbl_userid table and get the correct clearance

-- for this spy.

SELECT security_level

INTO v_spy_clearance

FROM spy_owner.tbl_user_id

WHERE user_name=p_usern;

-- These next two lines will store the username and the

-- user clearance level in the context for later use.

SYS.DBMS_SESSION.SET_CONTEXT(’cont_spy_owner_01’,’v_spy_clearance’,

v_spy_clearance);

SYS.DBMS_SESSION.SET_CONTEXT(’cont_spy_owner_01’,’spy_id’,p_usern);

END proc_get_spy_context;

END pkg_spy_owner_context_01;

/

NOTE Notice the use of the DROP CONTEXT command in the example. This command

will drop any existing context. To create a context, you must have the CREATE ANY CON￾TEXT privilege. To drop a context requires the DROP ANY CONTEXT privilege.

Keep in mind that setting the context, as done in Listing 21.7, and the actual

implementation of FGAC are two different things. You are only setting the context

area with the username and the clearance code because it will make the performance

of your security policy (which truly implements FGAC) better.

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com

961

Executing the Context Package at User Logon

Once the context-checking package is in place, you need to create a database logon

event trigger to call the application context package. It is the job of this trigger to fire

when the user logs in to the database. The user’s context area will be created, and

then the application context package will be executed, determining the security level

of the user.

To create this logon trigger, you use the built-in SQL function SYS_CONTEXT. This

function returns the value of a specific context variable to the calling program or SQL

statement. Oracle provides a built-in context namespace for each user session called

USERENV. You will use this built-in context namespace to get the username of the

user who is logging in to the database. You will then pass that username to the appli￾cation context-checking package. You will use the values stored in the context areas

shortly. Listing 21.8 shows the logon trigger.

Listing 21.8: Setting the Application Context with a Logon Trigger

CONNECT spy_owner/spy_owner

-- The logon trigger

CREATE OR REPLACE TRIGGER tr_db_logon_01

AFTER LOGON ON DATABASE

DECLARE

spy_id VARCHAR2(30);

BEGIN

spy_id:=sys_context(’USERENV’,’SESSION_USER’);

pkg_spy_owner_context_01.proc_get_spy_context(spy_id);

EXCEPTION

WHEN others THEN

NULL;

END;

/

Creating the Security Enforcement Package

The next step is to create the security enforcement package. The security enforcement

package will be executed when a user attempts to access the table that the security

enforcement package will be assigned to in the next step. To enforce the row-level

security, the security enforcement package adds a dynamic predicate to the SQL state￾ment that is being executed. The dynamic predicate becomes an additional restriction

to be appended to the SQL statement to be executed (basically, extending the WHERE

clause to include restrictions for the implementation of row security).

ENFORCING ROW-LEVEL SECURITY

Beyond Simple

Database Managment

PART

III

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com