Exclude roles/grants to roles in schema compares

Hello all,
Im not the best at joining a lot of queries together. I am trying to figure out how to exclude grants to roles and or users with specific stings.

We have beta schemas and tester roles that i would like to exclude because it can make the compare seems horribly out of sync when in reality its pretty good.
for example
select * from dba_tab_privs where grantee not like '%BETA%' or grantee not like '%TESTER%' or grantee not like '%DEV%';

I could simplify it by putting the roles or users in a table but still trying to figure out how to filter them out of the compare itself.

Thnaks for any assistance

You don't need to worry about any fancy query writing. The easiest thing here would be to use the Schema Exclusion File

To start from scratch:

Do your compare without a filter
On the difference details tab, uncheck a few things that you'd like to have excluded
Right-click and choose "Create exclusion file from unchecked items"

Here's the file it created for me. You can edit with notepad. Note the wildcard options at the top

# Object type is not required.  It is just there for your reference.
# You may exclude object type when adding more objects to this file.
# Wildcards may be specified.  Some examples:
# Grant:* To BOB     <- excludes all grants to BOB
# Table:A*           <- excludes tables that start with an 'A'
# Table:*A*          <- excludes tables that contain an 'A'
# Table:*A           <- excludes tables that end with an 'A'
# *TEST*             <- excludes any object containing or named 'TEST'
# Special cases:
# Schema:SourceOnly  <- excludes objects that exist only in the source schema
# Schema:TargetOnly  <- excludes objects that exist only in the target schema
# Schema:Both        <- excludes objects that exist in both schemas, but have differences
# If the use of wildcards or special cases allows you make this file smaller, then do so.
# The smaller this file is, the faster it will perform.

On your next compare, the file is specified here

1 Like

cool, let me give that a shot. Currently it will say grant select on REAL_APP_SCHEMA.TABLE1 to BETA_TESTER_ROLE.
those dont make it to production systems so its more noise to ensure they are all the beta grants.
as always thanks for the fast turnaround

1 Like

I have been facing the same challenge with roles and grants in schema comparisons. It would be great if there was an option to exclude them. I hope the developers consider adding this feature in future updates. Looking forward for insights or solutions from the community!

you can exclude specific grants as I described above. You can exclude all of them by unchecking them here