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
Dave
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.
#
Grant:AA_DEPT TO COMP1
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
@domsalvator9
you can exclude specific grants as I described above. You can exclude all of them by unchecking them here
ok, maybe Im going something wrong.
I created the file and Im still seeing grants on objects to unwanted roles.
example
REVOKE SELECT ON TEST_SCHEMA1.TEST_TABLE FROM TEST_BETA_ROLE;
I would like to exclude that.
select * from dba_tab_privs where grantee not like '%BETA%' and grantee not like '%TEST%' and so on.
Is it possible to add sql to the exclude file?
running another test now. I noticed I missed the ROLE:BETA
ROLE:DEV
ROLE:BETA
Grant:* To BETA
BETA
Grant:* To DEV
DEV
Grant:* To TEST
TEST
Hi Dave.
It is not possible to add SQL to that file. But you can specify wildcards as noted in the file.
If you go to the last step, then uncheck the grant that you don't want in the difference details tab, then right-click to create the exclusion file based on uncheck items....does that not exclude what you unchecked?
ok, I saw your edit about re-testing. If something still doesn't work, post your file and give me a screen shot of what's not being excluded and I'll to reproduce it.
ok so how do I exclude these in the compare.
Its not that i just want to exclude them from sync script but I want to remove them from the compare all together.
my exclude file
ROLE:DEV
ROLE:BETA
ROLE:TEST
Grant:* To BETA
BETA
Grant:* To DEV
DEV
Grant:* To TEST
TEST
Its not that i just want to exclude them from sync script but I want to remove them from the compare all together.
I understand that you are saying that you'd like these items to not show up in the comparison at all.
Are you also saying that you got them to be excluded from sync script by using the exclude file?
Can I explicitly put them in the exclude file? if so I have no issue with doing that, what would the syntax be?
this is whats currently in the exclude file, the spaces are not there, Do spaces matter?
ROLE: * DEV *
ROLE: * BETA *
ROLE: * TEST *
Grant: * To * BETA *
- BETA *
Grant: * To * DEV *
- DEV *
Grant:* To * TEST *
- TEST *
Can I explicitly put them in the exclude file?
Yes.
what would the syntax be?
To see the syntax:
- Run your comparison w/o the file
- Uncheck the grants on the "Difference details" tab
- 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.
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".
ok cool, that does work.
It would be nice if they just wouldnt come back because it did cause the compare to hang and displayed 4210 differences.
I like the Difference details page 
Thanks again for the fast responses
1 Like
The Like\Not Like filter at the top will exclude things completely, but that would apply to all object types so it's not very practical sometimes.
How long was the hang?
about 30 sec or so, I assume its just the size of the temp file and the garbage running on my system.
A normal PC probably wouldnt hang.
I look at the sync script and it is very small so I think this is a good solution.
I just tested this with about 3000 tables in a pair of schemas, and different grants (so I had around 3000 grant differences, all filtered out with the exclusion file). For me, there wasn't any hang, but I probably didn't have as complicated of schemas as you.
My PC is pretty fast, but nothing extraordinary.
government pc so even if I had a beast it would still lag. My cpu is always 100% maxed 
As always thanks for your fast work. Toad is a great tool and saves me countless hours
1 Like
I'm sure you have no privs to install things on your PC, but if you did, this might come in handy: https://bitsum.com/
1 Like
