On Fri, Jan 1, 2021 at 7:35 PM Isaac Morland <isaac.morland@gmail.com> wrote:
> The use case is to ensure that after doing my GRANTs the permissions are in a known state, no matter what they were
before.Typically, one would follow a reset command with some GRANTs. So maybe my permissions script contains:
>
> GRANT UPDATE ON TABLE t1, t2 TO u1, u2;
>
> Later, I revise this to:
>
> GRANT UPDATE ON TABLE t1, t2 TO u1;
>
> But the obsolete permissions will still be available to u2. I would like to be able to put something like this at the
topof the permissions script:
>
> RESET PERMISSIONS ON ALL TABLES IN SCHEMA test;
>
> Or in a different context:
>
> RESET PERMISSIONS ON TABLE t1, t2;
>
> Note: I'm not particularly fond of "RESET PERMISSIONS" as the syntax; I just wrote that as an example of what it
mightlook like.
>
> If the tables are newly created this would have no effect; if they were existing tables it would change the
permissionsto what newly created tables would have.
>
> In the absence of default privileges, I think it's clear that this means setting the acl column (relacl, proacl, ...)
toNULL; with default privileges, I think it probably means resetting acl to NULL and then applying the current default
privilegesas if the object had just been created by its owner. As you point out, it's possible the object never had
thisprivilege set, which is an argument against using the word "reset" in describing the feature. Maybe "GRANT
DEFAULT"?But it's weird for GRANT to actually revoke privileges, as it would for most object types.
Exactly what's wrong with "REVOKE ALL ON ALL TABLES IN SCHEMA test" at
the top of your script? You say there is a problem, but don't describe
the precise problem. Can you give a fully worked example so we can
understand how to resolve?
The meaning of GRANT and REVOKE is now defined by SQL Standard, so not
something we can easily change.
--
Simon Riggs http://www.EnterpriseDB.com/