Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Scenario:

  1. I have instanceA
  2. I have instanceB
  3. I have USER1 in instanceA
  4. I have USER1 in instanceB
  5. 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
  6. USER1 in instanceB has no roles granted
  7. All mentioned roles exist also in instanceB although not granted to USER1
  8. I have already writen a script that generates the DDL in order to duplicate the role grants for USER1 in instanceB.

Problem:

  1. 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;
  2. 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.

  3. 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 role to non-default with NONE.

  4. I cannot find a way for setting only ROLE2 and ROLE3 as not-default and ROLE1 and ROLE2 as default.
  5. 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.
  6. I don't want to make the roles ask for password. All roles in the databases are not password roles.
share|improve this question

1 Answer 1

up vote 1 down vote accepted

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,

  1. grant roles (with admin option as needed)
  2. alter user to set all roles to non-default
  3. 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
share|improve this answer
    
Excellent script ! I didn't event know the "WITH X AS" construct. –  user1598390 Oct 15 '13 at 19:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.