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.
Best Answer
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.