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.
This is common practice both for the reason cited by your admin and also for the fact that you exert a precise level of read/write control within your applications. By giving your applications only permissions that they need you are limiting potential security violations.
@Rutt create a new user for each applcation. Even though your apps might currently have overlapping permissions there is no guarantee that an app's permissions wont change. You are sand boxing each set of permissions in a way that they are not affected by other applications emerging requirements.
发布评论
评论（2）
在某种程度上，这取决于“应用程序”的含义，因为这是一个非常有弹性的术语。它还取决于您是否正在谈论创建新用户，然后拥有特定应用程序的所有表，或者您是否正在谈论创建将用于登录数据库但这些用户将具有访问权限的新用户存储在单独的（可能更小）用户模式集中的对象。
如果 50 多个应用程序意味着您有 50 多个独立的表集，这些表由 50 个通常独立的代码块单独访问，那么为每个应用程序创建单独的用户和单独的模式是非常合理的。它可以更轻松地跟踪哪些应用程序正在使用最多的磁盘空间、哪些应用程序正在造成性能问题等。它还可以相对容易地锁定每个帐户的权限，因为每个帐户只需要访问一个模式中的数据并且任何系统级权限授予都将特定于特定应用程序。
另一方面，如果有 50 多个应用程序访问同一个数据库，这些应用程序很可能实际上更像是一个较大应用程序的组件，最终需要共享对各种对象的访问。例如，如果您有一个应用程序让 HR 输入有关新员工、解雇、辞职等的数据，另一个应用程序让经理输入员工评论，而第三个应用程序让 HR 管理组织结构图，那么很可能所有这三个应用程序都需要访问相同的基本表集，可能有一些例外。如果一个用户拥有员工表，而另一个用户拥有存储组织层次结构的表，而第三个用户拥有存储员工评论的表，这将是非常烦人的。从技术上讲，构建这种系统是可行的，但管理同义词、权限授予和构建过程可能会很快变得难以处理，而且当不同的应用程序将本质上相同的数据放在不同的应用程序中时，更有可能引入数据完整性问题。表，因为他们不知道其他一些应用程序已经有该实体的表。
此外，您还必须考虑应用程序的部署方式等问题。如果您有 50 个小型 Java 应用程序，例如 2 个连接到单个 Oracle 数据库的应用程序服务器，那么拥有 50 个独立的 Oracle 用户至少可以说会给连接池带来挑战。即使每个连接池只有 5 个数据库连接，当应用程序服务器旋转并随着您添加应用程序服务器或应用程序以及用户负载增加而创建更多连接时，您仍然在谈论 50*5*2 = 500 个数据库连接。虽然当然可以支持数百或数千个数据库连接，但这种方法也会产生各种管理难题。
根据管理员的具体目标，可能还有更好的方法（代替单独的数据库帐户或除了单独的数据库帐户之外）。例如，Oracle 提供了
DBMS_APPLICATION_INFO包，应用程序可以轻松地使用该包来添加检测，以向 DBA 展示数据库会话当前正在执行的操作。与简单地拥有单独的数据库帐户相比，这可以为 DBA 提供更细粒度的信息。例如，如果您有一个三层应用程序，那么当它从连接池中提取连接以传入实际用户名和SET_MODULE时，很容易调用SET_CLIENT_INFO过程。 > 定义当前应用程序，甚至用户当前位于应用程序的哪个部分。然后，此信息将被放置在 DBA 已经查询的各种视图中，使 DBA 可以轻松深入了解正在运行的会话。消耗大量资源来查看人类用户是“JustinCave”，并且他当前位于“InventoryManagement”应用程序的“QuarterlyReporting”模块中，DBA 可能从经验或上下文中知道该模块很可能会长时间运行，但仍然存在重要查询。即使您有可以查阅的应用程序日志来获取所有这些信息，将其直接提供给 DBA 也可以使故障排除更加有效，并有助于提高开发和支持团队之间的沟通效率。例如，DBA 可以告诉应用程序开发人员，特定应用程序的特定模块似乎正在产生性能问题，或者询问为什么有人在月中使用 QuarterlyReporting 模块，而不是简单地说“InventoryManagement 应用程序速度很慢” ”或者要求其他人查看应用程序日志以找出特定时间点发生的情况。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_INFOpackage 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 theSET_CLIENT_INFOprocedure when it pulls a connection from the connection pool to pass in the actual user's name andSET_MODULEto 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.这是常见的做法，既是因为您的管理员引用的原因，也是因为您在应用程序中施加了精确级别的读/写控制。通过仅向您的应用程序授予它们需要的权限，您可以限制潜在的安全违规行为。
@Rutt 为每个应用程序创建一个新用户。即使您的应用程序当前可能具有重叠的权限，也不能保证应用程序的权限不会更改。您对每组权限进行沙箱处理，使其不受其他应用程序新出现的需求的影响。
This is common practice both for the reason cited by your admin and also for the fact that you exert a precise level of read/write control within your applications. By giving your applications only permissions that they need you are limiting potential security violations.
@Rutt create a new user for each applcation. Even though your apps might currently have overlapping permissions there is no guarantee that an app's permissions wont change. You are sand boxing each set of permissions in a way that they are not affected by other applications emerging requirements.