在Oracle中将一个用户的权限授予另一个用户 [英] Grant permission from one user to another in Oracle

问题描述

我想知道如何授予特定用户有权访问另一用户的所有数据库对象的权限。

I want to know how to grant permission of all database objects a particular user has access to to another user.

为了说明这一点，Oracle数据库中有四个用户：A，B，C和D。

To illustrate, there are four users in Oracle database: A, B, C, and D.

1. 用户A不拥有任何对象，但是可以访问B和C中的某些对象


2. 用户B和C拥有一些对象，但也可以访问B，C和D中的其他数据库对象。

现在，我希望用户A拥有用户B和C拥有的所有特权（其中一些A早已拥有）。

Now I want the user A to have all the privileges that the user B and C have (some of them A would have already).

执行此操作的正确方法是什么？我应该使用查询还是过程？是否应该始终在登录数据库对象所有者的情况下运行此过程？例如，B可以访问D中的某个对象，B可以将D的对象授予授予权限吗？

What is the proper way to do this. Should I use query or procedure? Should this procedure be run always with login of database object owner? For example, B has access to some object in D, can B give grant permission of D's object to A?

推荐答案

您可以使用角色对系统和对象特权进行分组，然后将这些角色分配给用户和其他角色。这样，您可以将角色所包含的特权整体授予用户，这简化了安全性管理（请参阅权限和角色授权 （在数据库概念中）。

You can group system and object privileges using roles and then assign those roles to users and other roles. This way you can grant the privileges the roles contain to the users en masse, which simplifies the security administration (See "Privilege and Role Authorization" in Database Concepts).

B有权访问D中的某个对象，B可以将D的对象的授予权限授予A吗？

For example, B has access to some object in D, can B give grant permission of D's object to A?

用户 SCOTT 可以将对象特权，系统特权或角色授予其他用户或角色，前提是该用户已被授予此特权或角色具有管理员选项或允许选项。例如，角色 select_hr 有权查询用户 HR 拥有的某些表：

The user SCOTT can grant the object privilege, system privilege, or a role to another user or role if he has been granted this privilege or role WITH ADMIN OPTION or WITH GRANT OPTION. For example, the role select_hr has privileges to query some tables owned by the user HR:

create role select_hr;
grant select on hr.employees to select_hr;
grant select on hr.departments to select_hr;

现在您可以将此角色分配给 SCOTT 这样：

Now you can assign this role to SCOTT this way:

grant select_hr to scott with admin option;

SCOTT 现在可以分配此角色其他具有或不具有管理选项的数据库用户：

SCOTT is now able to assign this role to other database users with or without admin option:

grant select_hr to bob;

请参见《数据库安全指南》中的授予用户权限和角色。

See "Granting a User Privileges and Roles" in Database Security Guide.

这篇关于在Oracle中将一个用户的权限授予另一个用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！