oracle 开发者在 Dev 数据库实例中应该拥有哪些权限

What permissions should Developers have in the Dev database instance

提问by Andy

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

...以及如何授予这些权限。我在一个拥有 70 多个应用程序的大型 IT 部门工作，其中一些在 SQL Server 中，大多数在 oracle 中。每个系统都有一个 prod、QA 和 Dev 实例。我们（我是一名开发人员）拥有对 prod/qa 的只读访问权限，我对此很满意。在 SQL Server 开发实例中，开发人员被赋予了 db_owner，它完全可以正常工作。争论的焦点是我应该在 DEV oracle 数据库中拥有哪些权限。

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.

我也对如何应用这些权限感兴趣。在 oracle 中，通过角色授予的权限在 PL/SQL 执行期间不处于活动状态，因此角色（即使是“dba”角色）也没有用。这使得使用内置帐户（系统）或跨数十个数据库创建数十个用户并直接向每个用户授予数十个权限。在我看来，让开发人员以系统身份登录是很有意义的，但我们的 DBA 声称这是一个坏主意。

采纳答案by David

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:

这是我们现在要做的：

1. the Application has it's own account (aka schema).
2. Developers have their own accounts
3. Data resides in the application schema
4. We have an ant build script to build code into whatever schema you want.
   • code includes views, packages, objects etc..
   • the build script includes a step to run a stored procedure to grant explicit rights to developers to the application data
5. Developers make changes in their own schema
6. When happy they check that into subversion
7. The Application's dev schema is built from the new subversion build.
8. Developers can check out and rebuild their own environments.
9. DDL changes to table structures are done via the DBA
   • these can be scripted as well

1. 应用程序有它自己的帐户（又名模式）。
2. 开发者有自己的账号
3. 数据驻留在应用程序模式中
4. 我们有一个 ant 构建脚本，可以将代码构建到您想要的任何模式中。
   • 代码包括视图、包、对象等。
   • 构建脚本包括运行存储过程以向开发人员授予对应用程序数据的显式权限的步骤
5. 开发人员在自己的架构中进行更改
6. 当高兴时，他们将其检查为颠覆
7. 应用程序的开发模式是从新的 subversion 版本构建的。
8. 开发人员可以检出并重建他们自己的环境。
9. 表结构的 DDL 更改是通过 DBA 完成的
   • 这些也可以编写脚本

This has the benefit of ensure any front end application is not broken by database developers constantly rebuilding everything.

这样做的好处是可以确保任何前端应用程序不会被数据库开发人员不断重建所有内容而破坏。

回答by Gary Myers

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.

如果您正在开发存储的 PL/SQL 对象，那么拥有这些对象的模式需要，正如您提到的，对所使用的对象进行显式授权。如果您有一个单一的“数据”架构，但正在您自己的单独架构中开发代码，那么您应该能够将数据架构对象的访问权限授予您的开发架构。通常我希望数据模式的用户名/密码。

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)

关于系统权限（例如 CREATE），我希望 CREATE TABLE、TYPE、VIEW、PROCEDURE TRIGGER、SYNONYM。其他可能是合适的（例如上下文），具体取决于您的工作。DBA 可能会排除 CREATE DIRECTORY，因为如果使用不当可能会造成损害。同理，其中包含 ANY 的权限（例如，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.

对于性能调优/系统监控，在开发数据库上 SELECT_CATALOG_ROLE 很好。如果 DBA 是规避风险的，您可能需要就个人观点协商授予。浏览您的版本的参考指南，并询问您可能使用的任何内容。

回答by Justin Cave

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.

我假设拥有实际对象的应用程序帐户数量相对较少。因此，一个或多个逻辑应用程序由特定 Oracle 用户拥有的表组成。这不是 SYSTEM 或 SYS，也不是 Oracle 公司提供的任何帐户。这将是您的 DBA 创建的帐户。如果您熟悉 Oracle 示例模式，那么 HR 用户拥有 HR 模式中的所有表，这些表构成 HR 应用程序的后端。

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.

下一步是为每个开发人员提供一个单独的模式进行开发，这可能与数据库中的公共同义词负载以及应用程序代码中缺少模式限定符相结合，以便在开发人员模式中创建的任何对象自动覆盖该对象的共享版本。这提供了更好的隔离。通常通过创建包含开发人员需要的所有授权的脚本或通过创建将所有权限从“已知良好”帐户复制到新帐户的脚本来授予权限。两者都不是特别难写——您只需要确保所有开发人员最终都拥有相同的权限集，这通常只是在授予新权限时运行的另一个脚本。

回答by berlebutch

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.

我的团队支持大约 100 个应用程序，其中大约 20 个拥有自己的 Oracle 架构。我们已经走上了每个开发人员都有模式密码的道路，这很方便。但是，事后看来，我建议每个开发人员使用自己的 Oracle 帐户进行开发。主要原因是审计。

回答by JW.

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.

我认识到最好的情况是让每个开发人员在他们的工作站上运行他们自己的实例进行开发，但由于数据库的大小，这不被认为是一种选择。

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.

有没有办法解决这个问题，也许是通过减少个人副本中的数据量？这似乎是理想的解决方案，因为它允许您进行所需的任何更改。然后您可以在准备好后将它们提交给 DBA，并让他更新共享开发服务器。

回答by northpole

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.

DBA 的工作之一是管理用户权限。我认为 system 不是一个好主意，原因有几个，尤其是能够删除整个架构，我相信你不想要。话虽如此，我认为将所有权限授予您的用户并让 DBA 管理这些权限是完全没问题的，无论可能有多少个帐户。无论如何，大多数 DBA 都会拥有可用于管理这些权限的脚本。

Listen to your DBAs, they generally know what they are talking about.

听听您的 DBA，他们通常知道他们在说什么。

回答by gn22

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.

如果它只是一个开发实例；我希望所有用户都将个人帐户添加到管理员角色中。这样你仍然可以在每个用户的基础上记录活动；但要给开发人员足够的喘息空间来做他们的事情。