Oracle 10gR2 - Copy User Permissions to Another User

AUSA_IT5
AUSA_IT5 used Ask the Experts™
on
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.

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Are you using any tool like TOAD? I believe you have copy user in there

Author

Commented:
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.
johnsoneSenior Oracle DBA

Commented:
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

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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial