From the objective of OSS-DB Exam Silver
- Operation management - basic operation management work
Sample Question
1.50
User foo performed the following operation.
CREATE TABLE aaa (id INTEGER, val TEXT);
GRANT SELECT ON aaa TO public;
GRANT UPDATE, INSERT ON aaa TO user 1;
REVOKE SELECT ON aaa FROM user 2;
At this time, choose two incorrect statements from the following.
It is assumed that user1 and user2 are existing general users, and the default access authority for the table created by user foo is left unchanged from the default.
- User foo can execute DELETE statement on table aaa.
-
User user1 can execute an UPDATE statement on table aaa.
- User user1 can execute a DELETE statement on table aaa.
-
User user2 can execute a SELECT statement on table aaa.
-
User user2 can execute UPDATE statement on table aaa.
※This sample exam is different from those that appear in the actual OSS-DB Exam.
2019/05/30
Answer and Explanation
With the default setting, for newly created objects such as tables, the creator of the object is the owner and has full authority. On the other hand, users other than the owner do not have any authority on the object, so you need to grant privileges individually.
Grant object privileges with the GRANT statement. To deprive the granted privilege, use the REVOKE statement. You can also specify individual users as GRANT (or REVOKE) targets, but if you use public as the user name, you can grant privileges to all users (or deprive the privileges you granted to public).
In a series of example operations, user foo creates table aaa, then grants SELECT privilege to public and grants UPDATE and INSERT privileges to user1. Finally, REVOKE (deprive) the authority of SELECT from user2.
User foo has all privileges when creating table aaa. You can restrict your own privileges using the REVOKE statement, but you can execute DELETE as you did not change anything in the example operation.
User1 can execute it because UPDATE is GRANTed, but DELETE is not GRANTed and can not be executed.
User2 does not have any privileges granted individually, but since SELECT is being GRANTed to public, SELECT can be executed. In the example, SELECT is being REVOKE from user2, but this is for GRANT individually assigned to user2, so it does not affect GRANT for public. Actually, this REVOKE statement has no effect (it will not result in an error) because SELECT is not GRANTed to user2. Since UPDATE is not GRANTed, it can not be executed.
Since it is a matter of choosing what is wrong, the correct answers are C and E.