oracle 赋予其他用户最高权限,在Oracle中将一个用户的权限授予另一个用户

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

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

User A does not own any objects, but has access to certain objects in B and C.

Users B and C own some objects, but also have access to other database objects in B, C, and D.

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

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

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

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;

Now you can assign this role to SCOTT this way:

grant select_hr to scott with admin option;

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

grant select_hr to bob;