Should a new user be creeated/used for every application created?
I got a "best practice" question. I'm not a db admin so I don't have a great insight into this, but our admin is pushing for process in which every application we build would require a new db user to be created (or potentially a user for each "like" app) so he can identify which user is potentially eating up resources. I understand the theory behind this decision however, I'm wondering if this is a standard practice. It seems to me that it would be causing a management nightmare. For example, we have say 50+ applications running internally so if each of them have a different user tied than it would not only be difficult to manage, but I would see a lot of these would have overlapping permissions. It becomes a chore to identify which user to use or if a new user needs to be created for each new app.
Any thoughts on this?
To some extent, it depends on what you mean by "application" since that is a very elastic term. It also depends on whether you're talking about creating new users that will then own all the tables for a particular application or whether you are talking about creating new users that will be used to log in to the database but those users will then have access to objects stored in a separate (and presumably smaller) set of user schemas.
If 50+ applications means that you have 50+ self-contained sets of tables that are accessed by 50 separate hunks of generally independent code, creating separate users and separate schemas for each application is very reasonable. It makes it easier to trace which applications are using the most disk space, which applications are creating the performance problems, etc. It also makes it relatively easy to lock down the privileges of each account since each account only needs to access data in one schema and any system-level privilege grants are going to be specific to a particular application.
On the other hand, if you've got 50+ applications hitting the same database, it's somewhat likely that these applications are really more like components of one larger application that will end up needing to share access to various objects. If, for example, you have one application that lets HR enter data about new hires, terminations, resignations, etc., another application that lets managers enter employee reviews, and a third application that lets HR manage the org chart then it is very likely that all three of these applications would need access to the same basic sets of tables potentially with a handful of exceptions. It would be intensely annoying if one user owned the table of employees while another user owned the table that stores the organizational hierarchy and a third user owned the table that stored employee reviews. It's technically possible to build this sort of system but managing the synonyms, the privilege grants, and the build process can get unwieldy very quickly in addition to making it more likely that data integrity problems are introduced when different applications put essentially the same data in different tables because they weren't aware that some other application already had a table for that entity.
Additionally, you have to consider things like how the applications are deployed. If you have 50 small Java applications sitting on, say, 2 app servers connecting to a single Oracle database, having 50 separate Oracle users will make connection pools challenging to say the least. Even if each connection pool only had 5 database connections, you're still talking about 50*5*2 = 500 database connections just when the app servers spin up with more connections created as you add app servers or applications and as user load increases. While it is certainly possible to support hundreds or thousands of connections to a database, this sort of approach can also generate a variety of administrative headaches.
Depending on the admin's specific goals are, there may also be better approaches (either instead of or in addition to separate database accounts). For example, Oracle provides the DBMS_APPLICATION_INFO package that applications can easily use to add instrumentation to show the DBA what a database session is currently doing. This can provide much more fine-grained information to a DBA than simply having separate database accounts. For example, if you have a three-tier application, it's easy to call the SET_CLIENT_INFO procedure when it pulls a connection from the connection pool to pass in the actual user's name and SET_MODULE to define the current application and even what part of the application the user is currently in. This information is then placed in a variety of views that the DBA is already querying and makes it easy for the DBA to drill down on a session that is consuming a bunch of resources to see that the human user is "JustinCave" and he's currently in the "QuarterlyReporting" module of the "InventoryManagement" application which the DBA might know from experience or from context is reasonably likely to issue long-running but still important queries. Even if you have application logs that could be consulted to get all this information, making it directly available to the DBA can make troubleshooting much more efficient and help make communication between the development and support groups more efficient. The DBA can tell the application developers, for example, that a particular module of a particular application seems to be creating performance problems or ask why someone is in the QuarterlyReporting module in the middle of the month rather than simply saying "the InventoryManagement application is slow" or asking someone else to go through application logs to figure out what was going on at a particular point in time.