30. Oracle Database (profile, script for granting every privilege for another user)

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