How to safely allow different end users to insert data into a table stored in an Oracle database?

Question

This is a basic question, but I don't have any sense of what other developers do in this scenario.

The Situation

I am creating an interface to allow end users to insert and update data in a table stored in an Oracle database. The interface will ask for an Excel worksheet and then generate a MERGE script with SQL to insert the data into the database table. I want the users to have to log into the database before they can perform these types of transactions.

I can think of a few ways to accomplish this:

1. Ask my DBA to create a new schema in the database, store this table there, and give all end users permission to edit tables in this schema.

2. Ask my DBA to create a new schema (aka "user" in the case of Oracle) and have end users all use this one userId and password. So maybe the userID is named "Accounting" and the password is "hunter2" and everyone in Accounting is given this user ID and password. Then I store the table in the "Accounting" schema.

Number 1 seems like a better option than number 2, but I'm not confident that either of these are good ideas.

My questions

Could anyone recommend a general strategy or some best practices on how to deal with this type of situation? Perhaps I'm thinking about this all the wrong way?

Answer 1

There are mainly two approaches to implement user management in a relational database:

• use the inbuilt user/role mechanism of the database itself for managing users, so for each real person using the DB there must be a related DB user

• have one dedicated DB user for accessing all the data for each application (or application system), and implement your own user management mechanism. This typically includes some own "User" table, with the attributes and relations required for your application.

Both approaches have their pros and cons (however, in Oracle, since users = schemas, I guess the second approach is more likely). From your question I assume there is already an existing DB system, and I guess the decision was already made if the inbuilt user/role mechanism shall be used for managing users, or not. Your "interface" (as you call your application) does not "live" in a vacuum and is probably not the only application which accesses the DB, so it should follow that formerly made decision, and not try to reinvent the wheel.

In short: inform yourself how other applications for this database work, and follow that lead.