We are planning for DB consolidation of all Non-prod environments into single DB.
Current Setup ----------------------
DB Name: Test1 Schemas : ADMIN, PEGADATA, PEGARULES
DB Name: Test2 Schemas: ADMIN, PEGADATA, PEGARULES
After consolidation DB setup
DB Name: Test1
DEV Schemas :ADMIN_DEV, PEGADATA_DEV, PEGARULES_DEV
SIT Schemas :ADMIN_SIT, PEGADATA_SIT, PEGARULES_SIT
After consolidation different environments user will use same DB name & different schemas as per the above mentioned.
ADMIN_DEV schema will get access on all other environment schemas as well because ADMIN_DEV user is having 'ANY' privilege. With this he will be able to access and modify all user objects ( including Oracle default users). We want to restrict that access only to PEGADATA_DEV,PEGAUSERS_DEV by giving access on individual objects of these schemas.. Will there be any issue if we remove CREATE/SELECT/UPDATE/DELETE ANY privilege from ADMIN_DEV?
Note: * User have to explicitely give access on each object of PEGADATA and PEGARULES. In future, if any object(table/procedure) is added to these schemas, again the privilege has to be given manually on these newly created object. A lot of manual intervention is needed in this case.
In the current environment, ADMIN schema (used for application configuration) is given SELECT/UPDATE/ELETE ANY privilege. With this the ADMIN user is able to access and modify PEGADATA & PEGARULES objects along with oracle default SYS and SYSTEM objects.
As per the new security policies, we wanted to restrict the ADMIN user not to have access to SYS/SYSTEM objects, by revoking the existing privileges and providing access only on PEGADATA and PEGARULES explicitely.
We would like to know the dependency of ADMIN user on Oracle default objects (SYS/SYSTEM objects). If there is no such dependency, we can go ahead and restrict.
If ADMIN user is meant to access SYS/SYSTEM objects for its functionality, please elaborate the dependency in details.
Kindly review and provide best feasible approach for consolidation.
In your environment do you have both a jdbc/PegaRULES and jdbc/AdminPegaRULES datasource or just PegaRULES? What database user is being used in each? The user running the system does not need access to anything in sys/system but does need select_catalog_role privileges.