From the objective of OSS-DB Exam Silver
- Operation management - basic operation management work
Sample Question
1.60
The following series of SQL statements were executed by the administrator user. Please select all correct answers to explain the result of the below statements.
CREATE USER foo;
CREATE USER bar;
GRANT SELECT ON table1 TO foo;
ALTER TABLE table2 OWNER TO foo;
GRANT foo TO bar;
-
User foo can SELECT from table table1
- User foo can UPDATE table table1
-
The last GRANT statement results in a syntax error
-
User bar can be SELECTed from table table1
-
User bar can be SELECTed from table table
※This sample exam is different from those that appear in the actual OSS-DB Exam.
2019/05/30
Answer and Explanation
To grant various access privileges on objects such as tables, use the GRANT statement. The target object is not limited to tables, but sequences, functions, schemas, table spaces, and so on.
The syntax of a general GRANT statement is
GRANT {kind of access right (SELECT etc.)}
ON [Object type (can be omitted in the case of TABLE)] Object name
TO user name (or role name);
In addition, you can grant the user all the privileges granted to the role at once.
GRANT role name TO user name;
GRANT user1 TO user2;
In PostgreSQL implementation, users and roles are exactly the same, then so all of the privileges granted to user1 are granted to user2.
In the example case,
With the third GRANT statement, user SELECT privilege is granted to user foo and user bar is granted privilege of user foo by the fifth GRANT statement, so users foo and bar have SELECT privilege on table 1.
In the fourth ALTER TABLE statement, we changed table2's owner to foo.
This will cause foo to have access to table 2, which is also granted to bar by the fifth GRANT statement.
Therefore, the correct answer is A, D, E.