How to GRANT and REVOKE privileges from users. Line 21 shows GRANT REFERENCES allows a user to reference another table. For instance, if a user doesn't have access to the customers1 table, but has access to another table that has a foreign key that references a primary key in customers1, the user can INSERT into the second table, and if the foreign key is rejected, that means it doesn't exist in customers1. Therefore, the user can discern information from customers1 without actually having access to customers1. Without REFERENCES, the user can't discern this info. Line 25 shows how to GRANT USAGE, which allows a user to use a domain when creating a table. If a user has access to use the domain, the user can discern the boundary limits of the domain but INSERTing values that keep getting higher or lower until they're rejected. Lines 28-36 show a good way to grant privileges to a user for all columns except one.
GRANT SELECT ON products1 TO Kirk; GRANT UPDATE (Price) ON products1 TO Kirk; GRANT INSERT ON products1 TO Kirk; REVOKE UPDATE (Price) ON products1 FROM Kirk; GRANT DELETE ON products1 TO Kirk; GRANT REFERENCES ON customers1 TO Kirk; GRANT USAGE ON DOMAIN PriceType TO Kirk; -- GRANT INSERT, DELETE, UPDATE ON customers1 TO Kirk; REVOKE UPDATE ON customers1 (CustomerID) FROM Kirk; --