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?

Maybe.|||

You can probably write a script to query the catalogs, find the permissions granted or denied to public, and issue a revoke statement for each of them. There isn't a simple statement to do this.

Another way would be to collect all the grants/denies made to public in your scripts and issue revokes for them.

Thanks
Laurentiu

|||

I almost expected that this would be the only answer. Thanks.

Scripts are acceptable, but not 'my style', if you get my meaning. I will probably write a stored procedure that I can call to do this for me - which will have to be deployed to each of my databases - unfortunately.

Thanks.