SQL Server 和 Oracle 读/写角色
我需要在 SQL Server 和 Oracle 上创建一些角色（我不知道如何正确调用它，所以我将其命名为“角色:)”，而且我真的对这两个数据库服务器了解不多，所以我不知道我应该寻找什么。
我需要创建 2 个角色：
- 只能从表（SELECT）中过滤数据的用户。
- 可以修改数据（即 DELETE 语句）但不能修改架构（在数据库中添加/删除表）的用户
我还需要帮助，如何使用 .Net 中的这些角色。
你能帮助我，并给我一些我应该寻找什么的线索吗？或者也许您已经有现成的解决方案。我真的很感谢任何帮助。 TIA。
I need to create some roles (I don't know how to call it correctly, so I'll name it 'role :)) on SQL Server and Oracle, and I really don't know much about both DB servers, so I don't know what I should look for.
I need to create 2 roles:
- users who can only filter data from tables (SELECT).
- users who can modify data (i.e. DELETE statement), but can't modify schema (adding/deleting tables in DB)
I also need help, how to use those roles from .Net.
Can you help me, and give me some clues what I should look for ? Or maby you have a ready solution. I really appreciate any help. TIA.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我无法与 Oracle 对话，因为我从未使用过它，但在 SQL Server 中，有 2 个内置角色可以完成此任务：db_datareader 和 db_datawriter。请注意，它们是独立的，因此要读取和写入，您将需要这两个角色。如果您想在 .net 应用程序中使用这些角色，那么最简单的方法是在连接字符串中使用集成 Windows 身份验证，然后将这些角色添加到 SQL Management Studio 中相关数据库的 Windows 用户。
I can't speak to Oracle as I have never used it, but in SQL Server there are 2 built in roles that do exactly that: db_datareader and db_datawriter. Please note these are separate so to read and write you will need both roles. If you want to use these roles in a .net application, then the easiest way is to use integrated windows authentication in your connection string then add those roles to the windows user for the relevant database in SQL management studio.
Oracle 没有提供与 SQL Server 角色相当的功能。这是因为它鼓励遵守最小权限原则。然而，完全有可能提出我们自己的。
类似地，我们可以通过向角色授予各种 DML ANY 权限来创建等效的 db_datawriter。通常我会建议不要这样做。在大多数情况下，只有 DBA 需要拥有 ANY 级别的权限，并且已经有适合他们的 DBA 角色。
Oracle doesn't come with an equivalent of the SQL Server roles. This is because it encourages adherence to the principle of Least Privilege. However, it is perfectly possible to come up with our own.
Similarly we can knock up an equivalent db_datawriter by granting the various DML ANY privileges to the role. As a rule I would advise against this. In most cases only a DBA needs to have the ANY level of privilege, and there is already a DBA role for them.
Oracle 有一组系统权限，可以授予帐户来实现此目的。通常是附加有
ANY一词的权限。例如，权限：
Oracle has a set of system privileges that can be granted to an account to achieve this. Usually the permissions that have the word
ANYattached to them.For example, among the privileges:
简短的回答：MSSQL 有名为 db_datareader 和 db_datawriter 的内置角色，它们很接近您想要的。 Oracle 没有（据我所知，我可能是错的），所以你必须定义你自己的。
长答案：MSSQL 和 Oracle 中的权限可能很复杂，特别是因为它们具有完全不同的登录/用户/数据库/模式等概念。您确实需要投入时间来理解它们，以便有效地管理权限，否则机会很好，你会做一些你不应该做的事情。如果您自己没有时间，请考虑寻找有时间的人。
您还可以在应用程序级别管理权限，但这并没有多大帮助，因为您仍然需要知道如何管理应用程序用于连接到数据库的帐户并避免出现问题。
Short answer: MSSQL has built-in roles called db_datareader and db_datawriter that are close to what you want. Oracle does not (as far as I know, I may be wrong), so you would have have to define your own.
Long answer: permissions in MSSQL and Oracle can be complex, especially since they have quite different concepts of logins / users / databases / schemas etc. You really need to invest the time to understand them in order to manage permissions effectively, or the chances are good that you will do something you shouldn't. If you don't have the time yourself, then consider finding someone who does.
You could also manage permissions at the application level, but that doesn't help much because you still need to know how to manage the account that your application uses to connect to the database and avoid issues there.