Toad World® Forums

Bug? DB Health Check, Schema, List objects for which there is a granted privilege but no corresponding synonym

When using this feature is shows ROLES that have no corresponding synonym. Roles cannot have synonyms… Bug?

I’m not seeing that. What Oracle version are you connected to?

Oracle 11g, and TOAD 12.6 I’m re-running it now. I noticed when I added a synonym to ANY of the other schemas, the issue isn’t apparent anymore.

I think the repro steps are:

  • Create a table

  • Grant the SELECT privileges to a ROLE in your database, but do NOT add a synonym to any of the schemas.

  • Run the DB Health Check

RESULTS: It shows the object, and the privilege granted to the role.

Once you add a synonym to ANY other schema in the database, the check will not show the OBJECTs that have grants to ROLEs, that are “missing a synonym” anymore.

Screenshot attached:

Synonym_Heaah.gif

Oh, I see. I misunderstood your initial post. You are right that roles cannot have synonyms. We are not checking for that.

We are checking to see if the object being granted (in your case BUILD_HISTORY) has a synonym or not. It doesn’t matter if the object is granted to a user or role.

Perhaps a suggestion for the Idea Pond?

Add a parameter for this that says “Ignore objects with grants are given only to roles”?

Reason, I initially had a lot of these, because we want persons granted a role, to use “Owner.Object” syntax. Sometimes we have similar named objects in multiple schemas, so this makes more sense than trying to grant synonyms to everything.

Ideally, anything granted to another schema or user, I want to see this as an issue because we specifically granted outside of the role. But for objects with only a grant to the role, I don’t care if there is a synonym or not.

OK, sure, I can make that a parameter. Look for it in the next beta.