Consider security using One Big Application User model
Oracle recommends that, where possible, you build applications in which application users are database users. In this way, you can leverage the security mechanisms of the database.
Unfortunately from my experience for a lot of commercial packaged software, application users are not database users. For these applications, multiple users authenticate themselves to the application, and the application then connects to the database as a single, highly-privileged schema user containing all the database objects. This is known as so-called One Big Application User model. Why, in spite of Oracle recommendation, the software vendors use in development that database authentication model? The typical answer of a software vendor is usually consists of 2 major points. First, due to the application compatibility requirements – most of applications run on different databases platforms, not only on Oracle database. Second is to simplify the development process.
Note, these simplifications however lead to different application maintenance problems including reduced application security and availability.
Applications built in this fashion generally cannot use many of the security features of the database, because the identity of the user is not known to the database. They must build security enforcement into the application rather than use database security mechanisms. Because it is the application, and not the database, that recognizes users, the application itself must enforce security measures for each user. This approach means that each application that accesses data must reimplement security. Security becomes expensive, because organizations must implement the same security policies in multiple applications. Each new application requires an expensive reimplementation.
Application software, whose users are also database users can rely upon intrinsic database security mechanisms such as granular privileges, virtual private databases, roles, stored procedures, and auditing. When security is enforced in the database itself, rather than in the application, it cannot be bypassed. The main shortcoming of application-based security is that security is bypassed if the user bypasses the application to access data. For example, a user who has SQL*Plus access to the database can execute queries without going through the application. The user, therefore, bypasses all of the security measures in the application.
I always recommend software development teams keeping at least the schema users containing the application database objects locked accessing them via another “Small Application User”. Remember, in Oracle database there is no standard way to prevent a schema user from destroying/dropping its own objects and limiting some of their powerful user rights. Read my previous article about that: The power of Oracle CREATE SESSION privilege.
Unique locked schemas do not allow connections to the database, but are used to contain a related set of objects. Schemas of this sort are created as normal users, and yet are not granted the CREATE SESSION system privilege. However, you must temporarily grant the CREATE SESSION and RESOURCE privilege to such schemas if you want to use the CREATE SCHEMA statement to create multiple tables and views in a single transaction.
For example, the schema objects for a specific application might be owned by a given schema. If application users have the privileges to do so, then they can connect to the database using typical database user names and use the application and the corresponding objects. However, no user can connect to the database using the schema set up for the application. This configuration prevents access to the associated objects through the schema, and provides another layer of protection for schema objects. In this case, the application could issue an
ALTER SESSION SET CURRENT_SCHEMA statement to connect the user to the correct application schema.