开发人员在 Dev 数据库实例中应拥有哪些权限
...以及如何授予这些权限。我在一个大型 IT 部门工作，拥有 70 多个应用程序，其中一些在 SQL Server 中，大部分在 Oracle 中。每个系统都有一个 prod、QA 和 Dev 实例。我们（我是一名开发人员）可以只读访问 prod/qa，我对此很满意。在 SQL Server 开发实例中，开发人员被赋予 db_owner，这完全可以正常工作。争论的焦点是我应该在 DEV oracle 数据库中拥有哪些权限。
我认识到最好的情况是让每个开发人员在其工作站上运行自己的实例进行开发，但由于数据库的大小，这并未被视为一种选择。
我也对如何应用这些权限感兴趣。在 Oracle 中，通过角色授予的权限在 PL/SQL 执行期间不处于活动状态，因此角色（甚至“dba”角色）没有用处。这就需要使用内置帐户（系统）或在数十个数据库中创建数十个用户并直接向每个用户授予数十个权限。 在我看来，让开发人员作为系统登录是很有意义的，但我们的 DBA 声称这是一个坏主意。
...and how should those permissions be granted. I work in a large IT dept with 70+ applications, some in SQL server and most in oracle. Each system has a prod, QA and Dev instance. We (I'm a developer) have readonly access to prod/qa, which I'm fine with. In SQL server development instances devs are given db_owner, which works totally fine. The debate is over what permissions I should have in the DEV oracle databases.
I recognize that best case would be to have each dev run their own instance on their workstation for development, but because of the size of the databases this has not been considered an option.
I'm also interested in HOW these permissions should be applied. In oracle permissions granted via a role are not active during PL/SQL execution so roles (even the "dba" role) are not useful. That leaves using a built in account (system) or creating dozens of users accross dozens of database and directly granting dozens of permissions to each.
In my mind just letting the devs login as system is making a lot of sense, but our DBAs claim that's a bad idea.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
我们过去只允许开发人员访问应用程序帐户。这适用于小商店，但随着开发商数量的增加，这种情况很快就会失控。
这就是我们现在要做的：
这样做的好处是确保任何前端应用程序都不会因为数据库开发人员不断重建所有内容而被破坏。
We used to just give developers access to the application account. This works for small shops but rapidly gets out of hand as the number of developers increase.
Here's what we do now:
This has the benefit of ensure any front end application is not broken by database developers constantly rebuilding everything.
我假设拥有实际对象的应用程序帐户数量相对较少。因此，一个或多个逻辑应用程序由特定 Oracle 用户拥有的表组成。这不是由 SYSTEM 或 SYS 提供的，也不是 Oracle 公司提供的任何帐户。这将是您的 DBA 创建的帐户。如果您熟悉 Oracle 示例模式，HR 用户拥有 HR 模式中构成 HR 应用程序后端的所有表。
从“可能有效的最简单的事情”的原则出发，我的第一个想法是看看开发人员是否可以直接登录这些应用程序帐户。这不是最安全的配置，并且您可能会发现开发人员无意或故意造成一些难以跟踪或轻松解决的损害。但它可以很好地工作，具体取决于组织。权限管理很简单——应用程序所有者帐户已经拥有它最有可能需要的所有权限。
下一步将是为每个开发人员提供一个单独的模式来进行开发，大概与数据库中的公共同义词负载以及应用程序代码中缺少模式限定符相结合，以便在开发人员模式中创建的任何对象自动覆盖该对象的共享版本。这提供了更好的隔离。通常通过创建包含开发人员所需的所有权限的脚本或创建将所有权限从“已知良好”帐户复制到新帐户的脚本来授予权限。两者都不是特别难编写——您只需确保所有开发人员最终都拥有相同的权限集，这通常只是授予新权限时运行的另一个脚本。
I assume that there are a relatively small number of application accounts that own the actual objects. So one or more logical applications are comprised of tables owned by a particular Oracle user. This would not by SYSTEM or SYS, it would not be any of the accounts that Oracle the company delivers. It would be an account that your DBAs created. If you are familiar with the Oracle sample schemas, the HR user owns all the tables in the HR schema which comprise the back end for an HR application.
Starting from the principle of "the simplest thing that could possibly work," my first thought would be to see if the developers could log in directly to those application accounts. This isn't the securest possible configuration, and you are opening up the possibility that a developer accidentally or intentionally does some damage that may be difficult to track or easily resolve. But it can work reasonably well depending on the organization. Privilege management is trivial-- the application owner account already has all the privileges it needs most likely.
The next step up would be giving every developer a separate schema to develop in, presumably in conjunction with a load of public synonyms in the database and an absence of schema qualifiers in the application code, so that any object created in the developer's schema automatically overrides the shared version of that object. This provides much better isolation. Permissions are generally granted by either creating scripts that contain all the grants a developer needs or by creating a script that copies all the privileges from a "known good" account to the new account. Neither is particularly difficult to write-- you just have to make sure that all the developers end up with the same set of privileges, which is generally just another script that gets run when a new privilege is granted.
如果您正在开发存储的 PL/SQL 对象，那么正如您所提到的，拥有这些对象的模式需要对所使用的对象进行显式授权。如果您有一个“数据”模式，但正在您自己的单独模式中开发代码，那么您应该能够向您的开发模式授予对数据模式对象的访问权限。通常我期望数据模式的用户名/密码。
关于系统权限（例如CREATE），我期望CREATE TABLE、TYPE、VIEW、PROCEDURE TRIGGER、SYNONYM。其他可能合适（例如上下文），具体取决于您所做的事情。 DBA 可能会排除 CREATE DIRECTORY，因为如果使用不当，可能会造成损害。对于其中包含 ANY 的权限（例如 SELECT ANY TABLE、DELETE ANY TABLE）也是如此。
对于性能调优/系统监控，在开发数据库上 SELECT_CATALOG_ROLE 很好。如果 DBA 不愿意承担风险，您可能必须根据个人观点协商拨款。浏览您的版本的参考指南，并询问您可能使用的任何版本。
If you are developing stored PL/SQL objects, then the schema owning those objects needs, as you mentioned, explicit grants on the objects used. If you have a single 'data' schema but are developing code in your own individual schemas then you should get the ability to grant access on the data schema objects to your development schemas. Normally I'd expect username/password for the data schema.
In regards to system privileges (eg CREATE), I'd expect CREATE TABLE, TYPE, VIEW, PROCEDURE TRIGGER, SYNONYM. Others may be appropriate (eg CONTEXT) depending on what you do. The DBA may rule out CREATE DIRECTORY as that could be damaging if mis-used. Ditto for privileges with ANY in them (eg SELECT ANY TABLE, DELETE ANY TABLE)
For performance tuning / system monitoring, on a dev database SELECT_CATALOG_ROLE is good. If the DBA is risk-averse, you may have to negotiate grants on individual views. Go through the REFERENCE guide for your version and ask for any you may use.
DBA 的工作之一是管理用户权限。我认为系统不是一个好主意，原因有几个，尤其是能够删除整个模式，我确信您不希望这样做。话虽这么说，我认为将所有权限授予用户并让 DBA 管理这些权限是完全可以的，无论有多少个帐户。大多数 DBA 都会有可用于管理这些权限的脚本。
听听 DBA 的意见，他们通常知道自己在说什么。
One of the DBA's jobs is to manage user privileges. I don't think system is a good idea for a few reasons, not the least being the ability to drop an entire schema which I am sure you don't want. That being said, I think it is perfectly fine to grant all to your users and let the DBAs manage these permissions no matter how many dozens of accounts there may be. Most DBAs will have scripts they can use to manage these permissions anyway.
Listen to your DBAs, they generally know what they are talking about.
如果它只是一个开发实例；我希望所有用户都将个人帐户添加到管理员角色中。这样您仍然可以记录每个用户的活动；但要给开发人员足够的喘息空间来完成他们的工作。
If it's just a dev instance; i'd have all users have individual accounts added to the admin role. That way you can still log activity on a per-user basis; but give the devs enough breathing room to do their thing.
我的团队支持大约 100 个应用程序，其中大约 20 个应用程序拥有自己的 Oracle 模式。我们已经走上了每个开发人员都有模式密码的道路，这很方便。不过，事后看来我会建议每个开发人员使用自己的 Oracle 帐户进行开发。主要原因是审计。
My group supports about 100 apps with about 20 of them having their own Oracle schema. We have gone down the road of every developer has the password to the schema and it is convenient. However, in hindsight I would recommend that each developer use their own Oracle account to develop. The main reason is auditing.
有没有办法解决这个问题，也许可以通过减少个人副本中的数据量来解决？这似乎是理想的解决方案，因为它允许您进行所需的任何更改。然后，您可以在准备好后将它们提交给 DBA，并让他更新共享开发服务器。
Is there a way to deal with this, maybe by reducing the amount of data in your personal copies? This seems like the ideal solution, since it would allow you to make any changes you need. Then you could submit them to the DBA when you're ready, and have him update the shared development server.