Re: Pass a variable into procedure to use as column in select statement

On Apr 3, 12:57 pm, OutCast <Joe.r.r..._at_jpmchase.com> wrote:
> I'm new to PL/SQL and having trouble passing a variable into a
> procedure and using it as a column identifier in a select statement.
>
> I'm using this as a universal procedure to pass access information to
> applications based on user authorization.
> I have a table which includes all users with a column for all
> applications. I simple 'Y' or 'N' indicates which applications the
> user can access.
> I want to pass in the application name (This will as be the column
> name) and through a simple select statement needed access
> information.
> I know that I can not use a bind variable as an identifier (as my
> example attempts), but how or can I accomplish this another way.
> I really don't want to write 15 different select statements for each
> application, plus don't want to change the procedure every time I need
> to add a new application (column) to the user table.
>
> He is an example of what I'm trying to do:
>
> Procedure Schema_Access
> (i_User_ID varchar,i_APP_ID varchar, o_User_ID out varchar, o_Password
> out varchar)
> IS
> v_Access varchar(1);
> BEGIN
> select i_APP_ID into v_Access from User_Main where User_ID =
> UPPER(i_User_ID);
> If v_Access = 'Y' then
> o_User_ID := 'UserName';
> o_Password := 'Password';
> else
> o_User_ID := i_User_ID;
> o_Password := 'No Access';
> end if;
>
> If the are better ways to accomplish the same task, please let me
> know.
>
> Thanks in advance

You're re-writing functionality included in some editions of some versions of the Oracle database server software.

Whether or not you choose to leverage the provided code depends upon if you're licensed for it, and what the repercussions would be should users access information that they are not entitled to access.

words of interest:

"secure application roles"
"global context"
"virtual private database"
"row level security"

example:
http://youngcow.net/doc/oracle10g/network.102/b14266/apdvntro.htm

hth.

-bdbafh Received on Thu Apr 03 2008 - 12:45:37 CDT