Support Article
Issue with Admin user Access (Split Schema)
SA-4298
Summary
As per 7.1.5 deployment guide, we need to have an admin user with all the privileges listed in the deployment guide. But if we create such user then this particular user will also get access to other schemas in the same/share database.
As a security principle we don't want to have such kind of user which will get access to other schemas in the same DB.
Does PRPC required DDL grants on the RULES schema (of a split schema configuration) beyond the Development environment (where we would be conducting our build and development), either at runtime or during BAU deployment of new rulesets? (excluding first-time installation)?
Please Note, this issue is more or less a problem with Oracle only. Because other database platforms we always able to assign db admin/owner level's privilege. Also because those platforms allow a db user owns multiple schema vs Oracle a db user owns one schema.
Resolution
The explanation for this behaviour is as follows:
PRPC split schema requires an Admin user to install and make run-time schema change. Deployment of new rule-sets on any other environment beyond Development may require run-time schema changes. The subsequent imports may also require the addition of tables or columns to the PRPCRules schema but the primary connection which is authenticated from PRPC/Installer is the PRPCData schema/user. We are considering that PRPCRules(or PRPCAdmin) user is specified to use while connecting PRPCRules schema.
This user will require additional privileges to perform activities to add tables or columns to PRPCRules schema as well as on the PRPCData schema. This is because same user connecting to perform this activity on PRPCRules require GRANT access to the PRPCData user on that object.
This isn’t possible unless you connect PRPC split schema(PRPCRules & PRPCData) using PRPCAdmin which has DBA permission
To overcome this there are two possible solutions that we are aware of:
Allow 'PRPCRules' schema user to connect and make ddl for any objects which would reside in its own schema.
Let 'PRPCRules' schema grant access privilege for any objects inside its own schema to 'PRPCData' user.
Normal ddl/dml still using 'PRPCData' schema connection only.
With this approach, we may add some connections for rule schema's ddl activities during run time, but it won't impact daily normal work of dml and query statements.
Alternatively, we can use 'Oracle Database Vault' Privileged User Controls(for more infomation please search for it in oracle documentation). This will be useful in centralized database environment where we want to restrict the DBA permission to user only for its own PRPCData and PRPCRules schemas.
Suppose we have a database where multiple schemas are there and we want to install, upgrade, or deploy.
Then by using realm we can grant the DBA permission to the PRPCAdmin user and this will be having the DBA permission to the PRPCData and PRPCRules schema only.
Though the PRPCAdmin has DBA permission but he cannot perform any operation on other schemas except its PRPCData and PRPCRules.
In the above context we can make sure the PRPCAdmin user can not break any other schema in the database.
Published January 31, 2016 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.