I’ve seen this in a few places lately. When I ask the database guys there about it, they think this DDL would never work or “it does nothing but does not return an error”.

Here is an example, All of the following is performed by a DBA user. We create a table, let’s call it TB. It is owned by user A. User B is granted select privilege to A.TB. A synonym is created for user B pointing to A.TB, let’s call it SN. Now we grant select on B.SN to user C.

User C => (user B) B.SN => (user A) A.TB

C can log on and do a select from B.SN and see the data from A.TB. Life is good. However, now it is decided to remove the synonym B.SN so that C can no longer see the data in A.TB. It always work to just drop a view so why not?

Well… A view is an object that has much more weight than a synonym. A synonym is essentially a lightweight pointer. Think a file link in Unix/Linux or a shortcut in Windows.

Same thing in the database, the privilege does not belong to a synonym the way it does for a view. After revoking the view, a crafty user can still select data from A.TB.

One client though that by removing their API-schema all access from another system was shut down. It was not and some accesses had by mistake been coded as direct (as if user C wrote a select from A.TB instead of from B.SN)

How can this be helped? Use views when creating objects that should carry privileges of their own. Use the user that owns objects to run all DDL making it harder to do this by mistake, B cannot grant access on A.TB to C. Catch grants on synonym in peer reviews and add reports showing grants going further than you intend. In the example, only user B which is the API-user should have access to objects user A has created. So if there are any other privileges then SQL can be used to find issues like this.