Granting role to another role:

We can grant a role to another role. We can see from this view that what role has what role granted to it.

Desc role_role_privs

select grantee, granted_role from dba_role_privs where grantee like 'HR';

Script for granting role of one user to another user:

Profile:

A profile can be assigned to an user. It can limit the user. For example through profile we can limit any user to only be able to create one session at any point of time. That will make sure that user can only login at once at at the same time. We can create more limits for any user with profile.

View to see profile details:

dba_rofiles

select profile, resource_name, resource_type, limit from dba_profiles where profile like 'DEFAULT';

Joining users view with profile view:

select a.username, a.user_id, b.profile, b.resource_name, b.resource_type, b.limit from dba_users a inner join dba_profiles b on a.profile = b.profile where a.default_tablespace like 'DAMMY';

Here we limit the output with using the tablespace name.

Improtant Note:

If someone asks dba to grant all the privileges of an existing user it will mean to grant the system privileges, object privileges and roles of that existing user to the new user not all privileges (insert, select, update, delete) on the objects owned by that user.

Its script:

set heading off set feedback off set echo off spool /home/oracle/grant_revoke_temp.sql select 'grant '||privilege||' to &new_user;' from dba_sys_privs where lower(grantee) like '&another_user'; select 'grant '||granted_role||' to &new_user;' from dba_role_privs where lower(grantee) like '&another_user'; select 'grant '||privilege||' on '||owner||'.'||object_name||' to &new_user;' from dba_tab_privs where lower(grantee) like '&&another_user' and lower(object_type) like 'table'; spool off @/home/oracle/grant_revoke_temp.sql set heading on set feedback on set echo on