Sample Exam Questions

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

Sample Question


"There is a table defined by the following SQL statement:
create table test(id integer, flag varchar);
What privileges does a user need to be GRANTED to execute the following SQL statement on this table? Choose all that apply.

update test set flag = 'x' where id < 10;"






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

Answer and Explanation

In databases, you can finely control the privileges for executing operations such as SELECT, INSERT, UPDATE, or DELETE on tables. The owner of the table possesses all privileges for the table, but other users, by default, do not have any permissions, thus they cannot view or update tables created by others. The owner of the table can grant various privileges using the GRANT statement and can revoke granted privileges using the REVOKE statement.

In terms of table privileges, in addition to the five options in this question, there are also TRUNCATE and TRIGGER privileges. SELECT/INSERT/UPDATE/DELETE/TRUNCATE privileges, as the name suggests, allow you to execute the SQL specified by the privilege on that table. However, SELECT privilege is required not only when executing an explicit SELECT statement, but also when the table is referenced in clauses such as the SQL WHERE clause. REFERENCES privilege is required when creating a foreign key constraint that references the table.
TRIGGER privilege is required when creating a trigger on the table.

Now, in this sample question, naturally, the UPDATE privilege is required to execute the UPDATE statement. INSERT, DELETE, and REFERENCES are not necessary. If there is no WHERE clause, you could execute with only the UPDATE privilege, but because the id column is referenced, the SELECT privilege is also required. It would be fair to say that for practical execution of UPDATE or DELETE, both UPDATE/DELETE and SELECT privileges are necessary.

Therefore, the correct answers are A and C.