Exclude roles/grants to roles in schema compares

Toad for Oracle & DevOps Toolkit
11

Can I explicitly put them in the exclude file?

Yes.

what would the syntax be?

To see the syntax:

  1. Run your comparison w/o the file
  2. Uncheck the grants on the "Difference details" tab
  3. Right-click and choose "create exclusion file from unchecked items". This will create a file containing commands to exclude the items that you unchecked.

I just created this role and made some differing grants to it from 2 schemas:

create role ABC_DEV_ROLE

grant select on COMP1.ADD_1 to ABC_DEV_ROLE;
grant select on COMP1.ADD_2 to ABC_DEV_ROLE;
grant select on COMP1.ADD_3 to ABC_DEV_ROLE;

grant select, insert on COMP2.ADD_1 to ABC_DEV_ROLE;
grant select, insert on COMP2.ADD_2 to ABC_DEV_ROLE;
grant select, insert on COMP2.ADD_3 to ABC_DEV_ROLE;

They show up in schema compare and I uncheck them.

image
image797×752 33.5 KB

This excludes them from sync script.
Then If I right-click and choose "create exclusion file", it looks like this:

# 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.
#
Grant:ADD_1 TO ABC_DEV_ROLE
Grant:ADD_2 TO ABC_DEV_ROLE
Grant:ADD_3 TO ABC_DEV_ROLE

If I use that file as-is, those grants that I unchecked will be unchecked automatically.

To re-do this file using wildcards, I would change it to:

Grant:* TO *DEV*

or even

Grant:*DEV*

Don't put a space before or after the colon. If you just make yours like this, it should work.

Grant:*DEV*
Grant:*BETA*
Grant:*TEST*

Notice I used "Grant". You specified "Role" above in your file. Well I guess you used both. Anyway, use "Grant".