This is a basic question, but I don't have any sense of what other developers do in this scenario.
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:
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.
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.
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?