Fair warning ... this will be a long and confusing email. However I'm about
to start sending hate mail to netscape for making debugging javascript in
ns6 sooo easy so I need a break. Hopefully this will help because I looked
for a long time for an answer to this question and got nothing sufficient
back. I'm using an example that I have on my system so you will need to
alter it according to your needs.
In this example we are calling a stored procedure in oracle to gather object
level security for the objects on a web page inside of a specific
application inside of a specific module ... sounds simple enough(ha!). It
took a looooong time to figure this out.
Basically you need to create a dummy table to store the results of your
procedure and then return the data from that table as it applies to the
instance of the stored procedure call.
In this example that temp table has the following fields:
PRV_AAL_NAME
PRV_AAL_DATA_LEVEL_SECURITY
PRV_AUS_OBJECT_PRIVS
PRV_FRU_UID
PRV_PROCESS_ID
PRV_CREATE_DATE
PRV_CREATE_USER
PRV_PROCESS_ID is the key to getting the data back. The rest of it is what
we want the stored procedure call to return. In this example we pass the
stored procedure the results of the cold fusion function CreateUUID().
The stored procedure needs to be created as a package on the Oracle side and
I'll show you the code to do this in a bit ... but first here is how to call
the procedure from CF:
<CFSTOREDPROC PROCEDURE="PACK_REFCURSOR.GET_SECURITY_PRIVS2"
DATASOURCE="#request.site.DSN#" RETURNCODE="No">
<CFPROCPARAM TYPE="In" CFSQLTYPE="CF_SQL_INTEGER" VARIABLE="p_fru_uid"
VALUE="#client.fru_uid#" NULL="No">
<CFPROCPARAM TYPE="In" CFSQLTYPE="CF_SQL_INTEGER" VARIABLE="p_amt_id"
VALUE="#get_amt_id.amt_id#" NULL="No">
<CFPROCPARAM TYPE="In" CFSQLTYPE="CF_SQL_VARCHAR"
VARIABLE="p_prv_process_id" VALUE="#CreateUUID()#" NULL="No">
<CFPROCPARAM TYPE="Out" CFSQLTYPE="CF_SQL_REFCURSOR"
VARIABLE="object_privs">
<CFPROCRESULT NAME="ObjectPrivs">
</CFSTOREDPROC>
Let me explain the variables. client.fru_uid is the user id. amt_id is the
module id the user is trying to access. CreateUUID corresponds to the
PRV_PROCESS_ID so we know what to get back and stick in the PROCRESULTS.
The package/procedure receives the variables and then executes a series of
queries that are dependent upon each other so there is a lot of querying and
then passing the variable to the next query. I'm not going to explain all
of the queries involved here, but you should be able to pick through it to
pull something together. This goes through and inserts a bunch of rows in
the prv table that consists of all the objects a user has rights to in
addition to roles that take precedence over the objects. All of this works
amazingly fast and blows away the performance hit of doing it on the cf
side. I'm not going to go into the data level security aspects at all
hehehe.
Here is the code that makes up the sql script that you run on oracle to
create the package/procedure:
CREATE OR REPLACE PACKAGE pack_refcursor AS
TYPE prv_tablerows IS REF CURSOR
RETURN prv%ROWTYPE;
PROCEDURE get_security_privs2(
p_fru_uid IN FRU.fru_uid%TYPE,
p_amt_id IN AMT.amt_id%TYPE,
p_prv_process_id IN PRV.prv_process_id%TYPE,
object_privs OUT prv_tablerows);
END pack_refcursor;
/
CREATE OR REPLACE PACKAGE BODY pack_refcursor AS
PROCEDURE get_security_privs2(
p_fru_uid IN FRU.fru_uid%TYPE,
p_amt_id IN AMT.amt_id%TYPE,
p_prv_process_id IN PRV.prv_process_id%TYPE,
object_privs OUT prv_tablerows)
IS
l_aal_id AAL.aal_id%TYPE;
l_aal_name AAL.aal_name%TYPE;
l_aal_data_level_security AAL.aal_data_level_security%TYPE;
l_aus_object_privs AUS.aus_object_privs%TYPE;
/* Cursor to get the objects for this module. */
CURSOR next_object
IS
SELECT
aal_id,
aal_name,
aal_data_level_security
FROM
aal
WHERE
aal_amt_id = p_amt_id;
/* Cursor to get the user privileges for each object. */
CURSOR next_user_privilege
IS
SELECT
aus_object_privs
FROM
aus
WHERE
aus_fru_uid = p_fru_uid AND
aus_ars_id = -1 AND
aus_aal_id = l_aal_id;
/* Cursor to get the role privileges for each object. */
CURSOR next_role_privilege
IS
SELECT
MAX(aus_object_privs)
FROM
aus,afl
WHERE
aus_fru_uid = -1 AND
aus_ars_id = afl_ars_id AND
afl_fru_uid = p_fru_uid AND
aus_aal_id = l_aal_id;
BEGIN
/* Open cursors. */
OPEN next_object;
/* Loop through each object. */
LOOP
/* Initialize variables to clear previous values. */
l_aal_id := NULL;
l_aal_name := NULL;
l_aal_data_level_security := NULL;
l_aus_object_privs := NULL;
/* Get the next object. */
FETCH
next_object
INTO
l_aal_id,
l_aal_name,
l_aal_data_level_security;
EXIT WHEN next_object%NOTFOUND;
/* Open the next_user_privilege cursor. */
OPEN next_user_privilege;
/* Get the next privilege for this user, for this object. */
FETCH
next_user_privilege
INTO
l_aus_object_privs;
IF next_user_privilege%FOUND THEN
INSERT INTO PRV(
prv_aal_name,
prv_aal_data_level_security,
prv_aus_object_privs,
prv_process_id,
prv_fru_uid)
VALUES(
l_aal_name,
l_aal_data_level_security,
l_aus_object_privs,
p_prv_process_id,
p_fru_uid);
/* If there is not a user level privilege (which takes precedence,
get the role based privileges. */
ELSE
/* Open the next_role_privilege cursor. */
OPEN next_role_privilege;
FETCH
next_role_privilege
INTO
l_aus_object_privs;
INSERT INTO PRV(
prv_aal_name,
prv_aal_data_level_security,
prv_aus_object_privs,
prv_process_id,
prv_fru_uid)
VALUES(
l_aal_name,
l_aal_data_level_security,
l_aus_object_privs,
p_prv_process_id,
p_fru_uid);
CLOSE next_role_privilege;
END IF;
CLOSE next_user_privilege;
END LOOP;
/* Close cursors. */
CLOSE next_object;
/* Now get all the object privileges from the prv table. */
OPEN object_privs FOR
SELECT
prv_aal_name,
prv_aal_data_level_security,
prv_aus_object_privs,
prv_fru_uid,
prv_process_id,
prv_create_date,
prv_create_user
FROM
prv
WHERE
prv_process_id = p_prv_process_id;
/* Commit the transactions. */
COMMIT;
EXCEPTION
WHEN OTHERS THEN
/* Close cursor. */
IF next_object%ISOPEN THEN
CLOSE next_object;
END IF;
END get_security_privs2;
END pack_refcursor;
/
-- ALTER PROCEDURE get_security_privs COMPILE
/
-----Original Message-----
From: Marsh, Jeffrey B [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 18, 2001 11:33 AM
To: Fusebox
Subject: Oracle stored procedure
I'm attempting to use Oracle stored procedures for the first time. Oracle
SQL*Plus really stinks as an IDE. There has to be a better tool out there.
I've used Microsoft SQL Server in the past.
I'm having trouble writing a simple Oracle stored procedure to select data
from a table and return a record set. I read the KB articles. Is there still
no way to return an Oracle recordset? I was wondering if some kind soul
would post a short Oracle stored procedure to select data from a table and
post the CF code as well. I'm using a simple table to exercise several data
types. The table description is below. Thanks.
Name Null? Type
------------------------------- -------- ----
RECNO NOT NULL NUMBER
LASTNAME VARCHAR2(15)
FIRSTNAME VARCHAR2(15)
MIDDLEINITIAL CHAR(1)
TEMPERATURE NUMBER(4,1)
BIRTHDATE DATE
UPDATEDBY VARCHAR2(10)
MODIFIED DATE
---
Jeffrey B. Marsh
Professionals built the Titanic.
Amateurs built the Ark.
****************************************************************************
This message is intended for the sole use of the individual and entity to
which it is addressed, and may contain information that is privileged,
confidential and exempt from disclosure under applicable law. If you are
not the intended addressee, nor authorized to receive for the intended
addressee, you are hereby notified that you may not use, copy, disclose or
distribute to anyone the message or any information contained in the
message. If you have received this message in error, please immediately
advise the sender by reply email and delete the message. Thank you very
much.
****************************************************************************
****************************************************************************
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
