Privileges on Tables and Synonyms

In an ANSI-compliant database, if you create a table, only you, its owner, have any table-level privileges until you explicitly grant them to others.

When you create a table in a database that is not ANSI compliant, however, PUBLIC receives Select, Insert, Delete, Under, and Update privileges for that table and its synonyms. (The NODEFDAC environment variable, when set to yes, prevents PUBLIC from automatically receiving these table-level privileges.)

To allow access only to some users, or only on some columns in a database that is not ANSI compliant, you must explicitly revoke the privileges that PUBLIC receives by default, and then grant only the privileges that you intend. For example, this series of statements grants privileges on the entire customer table to users john and mary, but restricts PUBLIC access to the Select privilege on only four of the columns in that table:

REVOKE ALL ON customer FROM PUBLIC;
GRANT ALL ON customer TO john, mary;
GRANT SELECT (fname, lname, company, city) ON customer TO PUBLIC;