Sample Exam Questions

From the objective of OSS-DB Exam Silver
S2.5 Basic server administration (Table privileges (GRANT / REVOKE))

Sample Question

2.35

Choose the most appropriate description about managing table access privileges with GRANT/REVOKE.

 
  1. If you have access privileges, you can GRANT them to other users.

  2. Only superusers and table owners can revoke GRANTED access privileges with REVOKE.

  3. The owner of a table has all privileges on the table, so if the SELECT privilege is revoked with REVOKE, you can still SELECT data from the table.

  4. You GRANT a SELECT privilege on table t1 to users u1 and public, then REVOKE this privilege from u1. In this case, the user u1 cannot SELECT data of the table t1.

  5. After user s1 GRANTS user u2 a SELECT privilege on table t2, user s2 also GRANTS it to the same user u2. After this, user s1 REVOKES the SELECT privilege on t2 from u2. At this time, the user u2 can select data of the table t2. It is assumed that both users s1 and s2 have the privilege to GRANT the SELECT privilege on t2.

※This sample exam is different from those that appear in the actual OSS-DB Exam.
2024/05/08

Answer and Explanation

By default, only the table owner (usually the user who created it) has access to the table, and to allow other users to access the table, privileges must be granted using GRANT. Granted privileges can also be revoked using REVOKE.
When the GRANT command is executed with the WITH GRANT OPTION, the user granted the privilege can further grant that privilege to other users using GRANT. Conversely, if WITH GRANT OPTION is not specified, the privilege cannot be GRANTED to another user, so having the privilege does not necessarily mean that it can be granted to another user.

The ability to revoke privileges using REVOKE is only available to the user who executed GRANT. For example, if user A grants privileges to user B with WITH GRANT OPTION, and user B GRANTS those privileges to user C, only user B can REVOKE the privileges of user C. If user A revokes the privilege from user B with the CASCADE option, user C also has the privilege revoked.

The table owner has all the privileges on the table, so the table can be accessed without having the privilege explicitly GRANTED. However, you can REVOKE your own privileges to prevent SELECT, DELETE, and other operations. In this case, you can still GRANT privileges as the owner of the table, so you can grant yourself SELECT privileges and so on to regain access.

If you GRANT the SELECT privilege on table t1 to users u1 and public, and then REVOKE the privilege on u1, only the GRANT on u1 is revoked, and the GRANT on public remains. Therefore, all users, including u1, can SELECT from t2.

When users s1 and s2 each GRANT user u2 the SELECT privilege on table t2, both the privileges that s1 GRANTED to u2 and the privileges that s2 GRANTED to u2 are recorded internally in the database. Later, when s1 REVOKES the privilege, only the privilege granted by s1 is revoked, and the privilege granted by s2 remains intact, so user u2 can still SELECT from t2.

So the correct answer is E.