Re: One set of stored procedure for multiple users

On 3 Jan 1997 15:00:05 GMT, hgu_at_honshu.tbu.att.com (Hongtao Gu) wrote:

>On 02 Jan 1997 22:46:28 GMT, rickfor_at_scsn.net wrote:
>
>>this is just one of the problems we as application developers
>>encounter when we try to use database users as application
>>users.
 

>>It's really very unwieldy. Here's our solution:
 

>>Basically, have the user log on thru a dialog box that you code
>>that has nothing to do with the database user.
 

>>Then, behind the scenes, have the application log onto the
>>database with the same user, no matter who is logged on.
 

>>Handle all the security yourself.
 

>>Now, as to your set of stored procedures:
 

>>alter a column onto each of your tables - USERNAME VARCHAR2(20)
>>or whatever. Make it part of the key.
 

>>Pass the username into the stored procedure from the application.
>>have the stored procedure read/write the table based on the additional
>>AND clause of username=passed_username.
 

>>what do ya think?
 

>>rick
>
>Thank you for the suggestion.
>
>It is a good solution for a new product. But for an existing product of
>~100 tables and ~400 stored procedure, it is costly because this needs
>modification of all the tables and stored procedure and populate them to
>all the customer.
>
>Any other suggestions?
>
>Hubert
>

yep. you're still going to have a database change, but no application or stored proc changes (at least for reads. as for writes to a view, that's ok in some versions of Oracle server, but there are some gotchas, too)..

start with one of your tables. I'll call it MY_EMPLOYEES.

alter a column for user as mentioned onto the table.

rename the table as MY_EMPLOYEES_ALL;

create a view of the table called MY_EMPLOYEES  as follows

create view my_employees as select
my_employees_all.* from my_employees me
where me.user_name=user

user is an Oracle function that returns the logged on user name.

>
>
>
Received on Fri Jan 03 1997 - 00:00:00 CST