May 2018
Intermediate to advanced
576 pages
30h 25m
English
We want to grant somerole the ability to view existing data and insert new data; we also want to give the ability to amend existing data, limited to column col2 only. We use the following self-evident statements:
GRANT SELECT, INSERT ON someschema.sometable2 TO somerole; GRANT UPDATE (col2) ON someschema.sometable2 TO somerole;
Let us assume the identity of role somerole and test these privileges with the following commands:
SET ROLE TO somerole; INSERT INTO someschema.sometable2 VALUES (1, 'One'); SELECT * FROM someschema.sometable2 WHERE col1 = 1;
As expected, we are able to insert a new row, and to view its contents. Let us now check our ability to update individual columns. We start with the second column, which we have ...