StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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.</p> <p>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.</p> <p>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.</p> <p>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.</p> <p>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 <a href="http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_appinf.htm#i999290" rel="nofollow"><code>DBMS_APPLICATION_INFO</code></a> 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 <code>SET_CLIENT_INFO</code> procedure when it pulls a connection from the connection pool to pass in the actual user's name and <code>SET_MODULE</code> 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.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload