How to assign minimal privileges for create view

Question

In Db2 v11.1 on Linux we have a request to create a new user with access only to the "create view" privilege.

What are minimal permissions I have to set in order for the user to be able create views and of course check if the view is working fine?

Answer 1

1. On Database level CONNECT privilege is required:

   db2 grant CONNECT on database to user user1

   Note: This may not be required if the PUBLIC group has connect privilege (which is the default setting).

2. Every table that is referenced in the view has to have the SELECT privilege

   db2 grant select on table schema1.table1 to user user1

3. To create a view user has to have a privilege to create/alter/drop objects in the schema

   db2 grant createin, alterin, dropin on schema schema1 to user user1

4. Instead of step 3 the ownership of schema can be transferred to the new user (if desired)

   db2 transfer ownership of schema schema1 to user user1