Re: Looking for suggestion on security

In article <37racn$i8d_at_mailer.fsu.edu>, raz_at_garfield.freac.fsu.edu (ISPA System Admin.) says:
>
>
> I'm looking for suggestions regarding security in a multi-user
>client-server environment. We run Oracle 7 on UnixWare as the server and
>a client front end for MS Windows and Macintosh. We're using SQL*Net v2
>as well as ODBC to access the remote database on a TCP/IP network.
>
> The application keeps track of a lot different budgets for research
>projects. The are a lot of transactions that affect the budgets such as
>budget allocations, money spend on equipment, appointments, etc, etc...
>
> The number of budgets and daily transactions against each budget is
>large enough that we assign several budgets to different fiscal assistants
>to keep them up to date.
>
> We are constantly creating reports against this database to see who
>is doing what kind research. The money that they are bringing in. How
>much money is spend on equipment, payroll, estimates on future funding
>and expenditures, etc, etc...
>
> Currently, the schema for the application is maintained under one
>Oracle user. This simplifies the process of creating the reports
>mentioned above. It also simplifies the problem of the end of year closing
>backup. We just create another user (i.e. fy93, fy94, etc) in the database
>and backup the data there in case it needs to be referenced in real time.
>
> The problem of having only one DB user and several fiscal assistants
>log in under the same user, I feel, is a security risk. It also makes it
>hard to keep logs on the transactions performed by each fiscal assistant
>since to the database they all appear to be the same user. So what I would
>like to do is have each fiscal assistant log in under his/her own user name
>while still maintaining the database schema under one DB user. The are
>two ways we can do this, 1) in addition to the main database user, we
>create a user for each fiscal assistant in the database. The user won't
>have any tables but synonyms of the main database schema. 2) Create an
>additional table on the main database schema of fiscal assistants. This
>table will contain, user names, passwords, assigned budgets, and
>applications each fiscal assistant can use. Then create a client server
>front end that will check this table to maintain security.
>
> Both solutions have their good points and bad. I'm wondering how
>other people have dealt with this problem. I'm interested in any ideas,
>comments or suggestions.
>

Try creating an ID for each user as well as the table you mentioned above to indicate what each user has responsibility for. You could then make a synonym for each user such that the synonym doesn't refer to the base tables, but rather to a view of those tables that only shows them what they can access.

The following is a brief (and probably ineffecient) sample:

create table BaseTable
(
  Account int,
  something float
);

create table UserAccessTable
(
  UserID varchar2(8),
  Account int
);

create view MyTable as
 select * from BaseID.BaseTable

        where account in (select Account from UserAccessTable
                            where UserID = USER);

create public synonym BaseTable for BaseID.MyTable; Received on Thu Oct 20 1994 - 22:16:26 CET