From the objective of OSS-DB Exam Silver
- Operation management - basic operation management work
Sample Question
1.45
Choose one option that is incorrect as a way to use the GRANT statement that grants privileges on the database. In the following options, user1 and user2 are existing user names, table1 is an existing table name, column1 is a column name in table1, and schema1 is an existing schema name.
- GRANT SELECT ON table1 TO user1;
- GRANT UPDATE (column1) ON table1 TO user1;
- GRANT CREATE ON SCHEMA schema1 TO user1;
-
GRANT user2 TO user1;
-
GRANT CREATEROLE TO user1;
※This sample exam is different from those that appear in the actual OSS-DB Exam.
2019/05/30
Answer and Explanation
By default, access rights to objects such as tables are owned only by their owner, that is, the creator, and no other users can access that object at all. Use the GRANT statement to grant access rights.
You can also grant access privileges on a table not only for each table like A, but also for certain columns in a table like B.
Objects to be granted can be granted the CREATE privilege to the schema as well as the table, as in C, in this case you grant the privilege to create new objects in schema 1.
Instead of granting individual privileges to individual users, you can grant a priviledge to role and grant the role to the user. D is an example of this, all privileges granted to user 2 will be GRANTed to user1. Since there is no distinction between users and roles in PostgreSQL, the role name to be GRANTed can be a user name with login authority.
Privileges on database clusters such as user creation (CREATEROLE) and database creation (CREATEDB) are granted with CREATE USER or ALTER USER statement instead of GRANT statement. How to grant user creation privileges to user1
ALTER USER user1 CREATEROLE;
Because it is a matter of choosing what is wrong, the correct answer is E.