Search code examples
oracle-databaseplsqldatabase-administrationoracle12c

What problems will be faced while revoking DBA role from an user?

An Oracle schema user, which is used by all the developers in the application, is given DBA role. Yeah, it is not a good practice. Now, we are planning to revoke the DBA role from this user. Revoking DBA role requires proper analysis on what privileges might be required by the user to proceed with the normal operations without disturbing the usual tasks.

The application is quite huge and we have lots of SQLs and PLSQLs involved. There are many other schemas available in Database. The application code might even refer objects of other schema in the Database without any specific privileges granted.

I wanted to know, 1. In such scenario, what kind of analysis should be done to revoke the DBA role? 2. What kind of challenges we can expect, while revoking the role?

Solution
    1. Deploy the application to a separate database/instance
    2. Revoke DBA role there
    3. Create explicit grants to fix invalid objects/application errors, be sure put all of them in a script and under version control.
    4. Do regression testing on the application to confirm everything is working as it should.
    5. Repeat step 3 and 4 until all errors are gone
    6. Use the script from step (3) to fix all deployments of the application

    Good luck!