Oracle: how to set only some roles as not default for a user (no GUI)

oraclepermissionsrolesyntax

Scenario:

I have instanceA
I have instanceB
I have USER1 in instanceA
I have USER1 in instanceB
USER1 in instancA has four roles grante to it:
- ROLE1 default yes admin_option no
- ROLE2 default false admin_option yes
- ROLE3 default false admin_option no
- ROLE3 default yes admin_option yes
USER1 in instanceB has no roles granted
All mentioned roles exist also in instanceB although not granted to USER1
I have already writen a script that generates the DDL in order to duplicate the role grants for USER1 in instanceB.

Problem:

I haven't found a way to programatically do the following because the DEFAULT=NO part doesn't exist in the GRANT ROLE clause.
- grant ROLE1 to USER1; — admin option no
- grant ROLE2 to USER1 DEFAULT NO with admin option;
- grant ROLE2 to USER1 DEFAULT NO;
- grant ROLE3 to USER1 with admin option;
Notice than I want ro replicate in instanceB the same role set USER1 has in instanceA, meaning two of the roles are default and two aren't.
I've studied setting the default role false for ROLE2 and ROLE3 after granting them, using ALTER USER DEFAULT ROLE but that only works for setting ALL roles to default or setting ALL roles to non-default with NONE.
I cannot find a way for setting only ROLE2 and ROLE3 as not-default and ROLE1 and ROLE2 as default.
I know that I can set that using the visual console but I need to automate this so I need syntax way to do it.
I don't want to make the roles ask for password. All roles in the databases are not password roles.

Best Answer

You have to use the GRANT command to grant roles (with admin option if required), then use ALTER USER to set roles to default (or not). So, the order of commands is,

grant roles (with admin option as needed)
alter user to set all roles to non-default
alter user to set only required roles to default.

A way to generate sql using sql is as follows, you can run this sql in instanceA, and actually execute the generated script in instanceB by replacing username if required. Make sure to test though,

with x as (select 'USER1' as usr from dual)
select cmd from (
select 1 as ord, 'grant ' || granted_role || ' to ' || grantee || case when admin_option = 'YES' then ' with admin option' end  || ';' as cmd
from dba_role_privs, x where grantee = x.usr 
union all
select distinct 2 as ord, 'alter user ' || grantee || ' default role none;' as cmd from dba_role_privs, x where grantee  = x.usr
union all
select 3 as ord, 'alter user ' || grantee || ' default role ' || granted_role || ';' as cmd from dba_role_privs, x where grantee  = x.usr and default_role = 'YES')
order by ord

Related Solutions

Oracle – list users with access to certain tables

List all users who have been assigned a particular role

-- Change 'DBA' to the required role
select * from dba_role_privs where granted_role = 'DBA'

List all roles given to a user

-- Change 'PHIL@ to the required user
select * from dba_role_privs where grantee = 'PHIL';

List all privileges given to a user

select
  lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
  (
  /* THE USERS */
    select 
      null     grantee, 
      username granted_role
    from 
      dba_users
    where
      username like upper('%&enter_username%')
  /* THE ROLES TO ROLES RELATIONS */ 
  union
    select 
      grantee,
      granted_role
    from
      dba_role_privs
  /* THE ROLES TO PRIVILEGE RELATIONS */ 
  union
    select
      grantee,
      privilege
    from
      dba_sys_privs
  )
start with grantee is null
connect by grantee = prior granted_role;

Note: Taken from http://www.adp-gmbh.ch/ora/misc/recursively_list_privilege.html

List which tables a certain role gives SELECT access to?

-- Change 'DBA' to the required role.
select * from role_tab_privs where role='DBA' and privilege = 'SELECT';

List all tables a user can SELECT from?

--Change 'PHIL' to the required user
select * from dba_tab_privs where GRANTEE ='PHIL' and privilege = 'SELECT';

List all users who can SELECT on a particular table (either through being given a relevant role or through a direct grant (ie grant select on atable to joe))? The result of this query should also show through which role the user has this access or whether it was a direct grant.

-- Change 'TABLENAME' below
select Grantee,'Granted Through Role' as Grant_Type, role, table_name
from role_tab_privs rtp, dba_role_privs drp
where rtp.role = drp.granted_role
and table_name = 'TABLENAME' 
union
select Grantee,'Direct Grant' as Grant_type, null as role, table_name
from dba_tab_privs
where table_name = 'TABLENAME' ;

SQL Server Permissions – Detect Execute Permission Granted to Role Without ON Clause

GRANT EXECUTE TO [principal] is simply a shortcut for GRANT EXECUTE ON DATABASE::<dbname> TO [principal];

You can check this using the following:

SELECT dp.name
    , perms.class_desc
    , perms.permission_name
    , perms.state_desc
FROM sys.database_permissions perms
    INNER JOIN sys.database_principals dp ON perms.grantee_principal_id = dp.principal_id 
WHERE dp.name = 'MyRole'

Best Answer

Related Solutions

Oracle – list users with access to certain tables

SQL Server Permissions – Detect Execute Permission Granted to Role Without ON Clause

Related Question