Who should own application objects?

Question

So I am just started doing stuff resembling full-blown Oracle DBA work recently, so am still learning much of the basics of the administration part of my job. I have been tasked with bringing multiple application databases up to meet a certain set of professional standards (I'm being intentionally vague). One of these standards is that owners of objects supporting the application by and large should not also be regularly utilizing them. The major exception being DBA, but then only for certain classes of objects that we would regularly be maintaining such as indexes. Other than when they are doing installation and maintenance tasks, the application object owners should be disabled.

This finally brings me to my question: who should be owning these application objects. Should we just have a user who creates the objects and then is largely disabled for the rest of its life and the requisite usage privileges be given to this or that role? Is it fine for the DBA to own all of the application objects, or should they just own those objects that are being maintained on a daily/weekly basis? Obviously the exact answer will depend the organization's needs and what specifically this standard is asking, but what is the best practice here for application object ownership?

This question may be too broad, so let me know if I need to edit it to make it more specific. I just don't really know where to start with this or even what the right questions to ask are. Thanks for any help you can provide.

Answer 1

The Principle of Least Privilege demands two things: the runtime user (or the human user logins) should not own the objects, because you cannot stop them from modifying or dropping them (on Oracle).

The same Principle also suggests you should not need to have extensive permissions for the user who wants to update application objects.

Both combined suggests: have a system user owning the application objects and another system user with access to those objects for runtime login. Use the DBA only to set up those users.

It is a bit different on MSSQL but basically you want to avoid having a runtime user with dbo role.

Answer 2

Generally speaking, it's a bad idea to have a database object in general use that is owned by a user account. If the user's account is disabled or goes away, problems can arise.

I'm a SQL Server guy, so things are a little different, but I like to have a service account as the DB owner, so we know the account will not disappear when an employee leaves.

Answer 3

In Oracle, it is common to setup an Owner user that owns the tables, and stored code (packages, functions, etc). This is a dedicated user for this purpose.

On larger databases supporting multiple applications, there may be several such owners. They may be granted access to tables owned by other users as needed.

Applications and users will have their own userids. These often do not have the ability to create database objects. They are allowed to access the code they need to, as well as any tables required to perform their purpose.

As others have noted, the principle of least privilege should be applied. Users may be allowed only select (read) access on some table. In some cases, they may have access to a subset of columns (via a view or other mechanisms).

It is unusual to routinely create objects owned by a DBA userid. The DBA users should be used to manage the database. They may create objects owned by other users. This is required if the owner userids haven't been granted login privileges.