Subject | REVOKE UPDATE on a separate field |
---|---|
Author | ettotev |
Post date | 2007-10-23T15:10:38Z |
I have a table created like this
CREATE TABLE tab (id INTEGER PRIMARY KEY);
GRANT UPDATE ON tab TO arole;
later on I add a field
ALTER TABLE tab ADD fld INTEGER;
and at this point "arole" has UPDATE on the new field. I do not want
this so I try
REVOKE UPDATE (fld) ON tab FROM arole;
There is no error, but "arole" can still update the fld. I read the
docs and I assume this is "as designed", as you can't revoke something
you didn't explicitly grant on the same level. (right?)
Any suggestions how I can achieve what I'm trying to do? Important
detail is that between the creation of the table and the creation of
the new field the table's structure could have been changed by other
statements, so revoking on table level and granting on column level
will not work.
Or should I be granting on column level from the very beginning?
CREATE TABLE tab (id INTEGER PRIMARY KEY);
GRANT UPDATE ON tab TO arole;
later on I add a field
ALTER TABLE tab ADD fld INTEGER;
and at this point "arole" has UPDATE on the new field. I do not want
this so I try
REVOKE UPDATE (fld) ON tab FROM arole;
There is no error, but "arole" can still update the fld. I read the
docs and I assume this is "as designed", as you can't revoke something
you didn't explicitly grant on the same level. (right?)
Any suggestions how I can achieve what I'm trying to do? Important
detail is that between the creation of the table and the creation of
the new field the table's structure could have been changed by other
statements, so revoking on table level and granting on column level
will not work.
Or should I be granting on column level from the very beginning?