Separate Application and User Accounts

[THoey]

Trying to figure out the best way to do something and unfortunately, I am at home and away from all my books to research it with. Hopefully you can help.

I have a schema that is the application owner. Inside this schema are all the tables, indexes, and packages that make the application work. We originally set up the application boxes that connect to the DB with this application owner login and password.

During a recent audit it was pointed out that this is a security violation and that the possibility exists of one of the developers that install to the application boxes to log into the DB and mess with the application schema. We decided that best way around this was to create a user schema that has the necessary access to the application objects that they need, but no more.

I have created the new user account, and now this seems like a good place to create a role and assign it to that new user. This role would need to grant SELECT access to all tables in the application schema, WRITE access to one specific table, and then EXECUTE access to all packages. There are no other objects that they would need permissions to (no Views, Triggers, Sequences, Procedures or Functions).

This would hopefully be dynamic so that if we add a new table in the application schema, the user schema would just need a synonym created and then it would be able to SELECT on the new table as if it was it's own.

Suggestions on if I am thinking this right and how to do this would be greatly appreciated.

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[LKBrwnDBA]

Idea is correct, except for the "needs to be dynamic" thing.

When you create a new table/view, you need to manually "grant" permissions to roles/users and manually create the synonyms.

The above is correct unless you actually create a "DDL" trigger which does the above tasks.





----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb