We are planning for DB consolidation of all Non-prod environments into single DB.

Current Setup
----------------------

DEV Environment

DB Name: Test1
Schemas : ADMIN, PEGADATA, PEGARULES

SIT Environment

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.

***Moderator Edit: Vidyaranjan | Updated Categories***