Revoking All Permissions - Secure by Default
I am designing my software to be 'secure by default'.
I only grant permissions to entities that actually require them, to help prevent unexpected attacks like injection attacks. To do this, I have a SQL script that configures all the permissions on my database. It is basically a series of GRANT commands. Every time I modify a stored procedure or perform a similar action that causes the permissions on an object to be lost, I run the script and it reconfigures all the security correctly, on every object.
Over time, permissions have been granted that are not necessary, or no longer required. This may have happened for many reasons, development and testing not least.
At the top of my script, I want to REVOKE all permissions explicitly granted or denied to my user (for the purposes of this question, PUBLIC) so that I know that only permissions explicitly granted in the script will be given to the user.
There are too many objects in my database to maintain a list of "REVOKE ALL ON <object> TO PUBLIC" commands with any reliability. The statement "REVOKE ALL ON ALL TO PUBLIC" does not work either.
Is there a simple way to do this?
