Looking for suggestion on security

        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.   Received on Sun Oct 16 1994 - 14:38:31 CET