Proxy user for DDL

oracle

I'm experimenting with the proxy user feature of oracle.

My sample: user_a has only object privileges and user_b has only system privileges. user_b is allowed to log in as user_a. So DDL and DML are separated.

The docs says, that user_b as user_a only has user_a privileges. That would mean, he loses its own rights?

Which difference does the CONNECT THROUGH WITH ROLE clause? Because a role could only chosen, if the user_a already got the role.

The only solution that I know, are system privileges with ANY clause (and without proxying), but then user_b could change objects in all other users, but I don't want that.

How could this problem solved?

Best Answer

User_B will be the owner of the objects and user_A is the user of those objects, which is a good separation and guarantees that the objects can not be changed by the application. You would not want user_A to be able to change any structure.

Normally you want an application manager to be able to connect as user_B to make the changes needed for a new version or something like that. If this is the case, you want your admins to have the possibility to connect through their personal accounts to user_B to do the changes. Since user_B is the owner of the objects, user_B also has the rights to use them.

So the solution is to use an application manager user to do the ddl using the proxy user. That is way better than giving dba privileges or ANY privileges to an application manager.

Related Solutions

Privileges needed for Oracle Text

What I ended up doing was giving the DMG schema DBA access right before creating the index then revoking it immediately after. I was afraid that would cause the index to fail to sync but it seems to work just fine. I was able move everything into the DMG schema= and remove the initial grants to the ctxsys schema.

I'm still not clear on what specific permissions I needed to give the DMG schema, but I got around my problem.

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

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

Best Answer

Related Solutions

Privileges needed for Oracle Text

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

Related Question