At 01:10 AM 24/10/2007, you wrote:

>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?)

Well, it *is* as designed, but you will fall into a big hole if you assume that privileges are in any way hierarchical. SQL privileges were designed by a committee, which is about 50% worse than if they had been designed by a chimpanzee. Many styles of privileges overlap and create "windows of opportunity" that make REVOKE meaningless.

>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?

I am very vocal (tediously so!!) about stressing the need to *design* your privileges schema. Never treat it as something you can do as an ad libitum exercise from your favourite admin tool. Eschew particularly those tools that purport to offer a "friendly interface" to grants and revokes. Always script your privileges schema and maintain your script as the central element in your database security setup. Use roles and ONLY roles and don't use GRANT ALL ON <x> TO PUBLIC, ever. Set up as many roles as you need, maintaining them in the script. Draw diagrams and truth tables and keep them with your script. Write a script for any change you make to the schema and archive it in the documentation of your central script with date, time and signature.

To address your current problem, create a new role and copy any privileges over from 'arole' that you are sure won't need field-level modifications. Don't copy the privileges for table 'tab' at all, but set up field-level privileges for all fields. Remember to include any REFERENCES privileges and field-level privs for foreign keys.

Revoke the privilege of 'auser' to 'arole' and grant the new role in its place. And, of course, make sure that you revoke all privileges that are currently assigned to PUBLIC.

./heLen