Sample Exam Questions

From the objective of OSS-DB Exam Silver
S3.1 SQL commands (Function definition / Procedure definition)

Sample Question


The following commands were used to define tables and functions:

create table testa(id integer primary key, val varchar) ;
create function testfunc(x integer) returns varchar language sql as $$
select val from testa where id = x $$ ;

Choose the most appropriate privilege required to execute the following SELECT statement.

select testfunc(1);

  1. There are no particular restrictions, and any user can execute it.

  2. Only the owner of the function testfunc can execute it.

  3. This function can be executed if the EXECUTE privilege for the function testfunc has been GRANTED.

  4. It can be executed if the SELECT privilege on the table 'testa' has been GRANTED.

  5. Both the EXECUTE privilege for the function testfunc and the SELECT privilege on the table 'testa' must be GRANTED.

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

Answer and Explanation

In PostgreSQL, functions can be defined in various languages, including SQL and PL/pgSQL. The example defines a function in SQL.
To execute a function, you must have the EXECUTE privilege for that function GRANTED.

Furthermore, functions are executed with the privileges of the executor, regardless of their owner. Therefore, when a function accesses an object such as a table, it must have the appropriate access permissions for that object. Since the function in the example only performs a SELECT operation on the table 'testa', the SELECT privilege on 'testa' must also be GRANTED.

Therefore, the correct answer is E.