Security, in regards to the database, is pretty broad. There are a multitude of ways to secure your database. From physical or logical access to SQL Injection.
This is a (not so) pretty picture of how I visualize security from an application point of view.
The base of any application are the tables. Access to those tables should be limited to database views and PL/SQL packages (API) that are owned by the application (schema).
SELECT privileges on those views should be given to only users who need it (users also includes other applications). Views present the data in the format that is required by the application. VPD (column or row) can be used to further restrict access to sensitive data.
EXECUTE privileges on the APIs (PL/SQL) should only be given to those applications that need it. Under no circumstances, should direct INSERT/UPDATE/DELETE privileges be given to another user.
Much of my thoughts on this matter are directly traceable to reading AskTom for so many years. I can't point to any one specific article, I think it's the accumulation/internalization of reading for so long.
It makes sense though.
Who better to decide the functions (INSERT/UPDATE/DELETE) that can be performed against a given set of tables than the person(s) who created them? If said person has left, make someone else responsible for them. I've fought hard over the years to implement this "pattern" and have been fairly unsuccessful. It is difficult to go against years of doing it one way (full access to the underlying tables) to forcing someone or something to use the API.
The idea to visualize it came to me recently and I need to put it down.
What do you think? Am I full of it? Is this reasonable? Anything I left out?