asked on

Oracle 10gR2 - Copy User Permissions to Another User

I have an Oracle user that is associated with many roles (with admin option), system privileges, object privileges, etc and I would like to copy this user's permissions to another Oracle user without having to go through each role and privilege. Can this be done with a simple command? If so, please advise on syntax.

ASKER CERTIFIED SOLUTION

ajexpert

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AUSA_IT5

ASKER

No we are not currently using TOAD. We use SQL Developer instead (cost savings). Are there any native commands that you know of? If not, we may can do a trial download of Toad.

johnsone

There are no native commands that would do it.

However, you can write queries to do it.

I didn't test what is below, but it should give you the basic idea of what needs to be done.

declare
  new_user varchar2(20) := 'new';
  old_user varchar2(20) := 'old';
begin
  for c1rec in (select 'grant ' || privilege || ' to ' || new_user stmt from dba_sys_privs where grantee = old_user) loop
    execute immediate c1rec.stmt;
  end loop;
  for c1rec in (select 'grant ' || privilege || ' on ' || owner || '.' || table_name || ' to ' || new_user stmt from dba_tab_privs where grantee = old_user) loop
    execute immediate c1rec.stmt;
  end loop;
  for c1rec in (select 'grant ' || granted_role || ' to ' || new_user stmt from dba_role_privs where grantee = old_user) loop
    execute immediate c1rec.stmt;
  end loop;
end;

Open in new window

Franck Pachot

Hi,

select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', user ) from dual
UNION ALL
select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', user ) from dual
UNION ALL
select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', user ) from dual;

will give you all the grants statements for all users.

Regards,
Franck.