- 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.
I haven't found a way to programatically do the following because the
DEFAULT=NOpart doesn't exist in the
- 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 ROLEbut that only works for setting ALL roles to default or setting ALL role 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.