About GRANT...

[Dan]

Hi,

is there a way of granting all the privileges of a user X to another user Y? Something like "GRANT * FROM UYER X TO USER Y" ? - Many thanks in advance!
Dan

[tamilselvan]

There is NO "from" and "to" in GRANT command.

[kj]

Try to create ROLE and grant all objects you need to the role. Then you can grant role to the user with only one statement.

[Mok Lo]

Dan,

You could try "cut and paste" at SQL*plus or SQLWorksheet. Run something like the following statements, copy the output and paste to run your actual grants.

To copy table rights from USER1 to USER2 for example,

select 'GRANT ' || PRIVILEGE || ' ON ' || TABLE_NAME ||
' TO USER2;' from dba_tab_privs where grantee = 'USER1';

for role privileges,
select 'GRANT ' || GRANTED_ROLE ||
' TO USER2;' from dba_role_privs where grantee = 'USER1';

for system privileges use dba_sys_privs.

Moks

[akkerend]

For granting object privileges also select the owner of the object and run the grant statements from the owners account.
And include grant option (dba_tab_privs) or admin option(dba_role_privs, dba_sys_privs):

select 'GRANT ' || PRIVILEGE || ' ON ' || OWNER ||'.'|| TABLE_NAME ||' TO ROLE1'||
decode(grantable, 'YES', ' WITH GRANT OPTION', null)||';'
from dba_tab_privs where grantee = 'USER2';