Windows stand-alone: Grant privileges to IBM DB2 for i database users
Configuration and runtime database users are granted a different set of privileges, depending on whether these users are schema owners or not. We can create a copy of the SQL scripts and edit this copy to manually grant permissions to configuration and runtime database users.
- Prerequisites
- Windows stand-alone: Modify properties for IBM DB2 for i
- Windows stand-alone: Create groups and assign users for IBM DB2 for i
- Windows stand-alone: Create user profiles for IBM DB2 for i
- Windows stand-alone: Set up IBM DB2 for i databases automatically
Required privileges of the configuration database user
When a configuration database user is a schema owner, the property...
domain.DbUser
...is assigned the same value as the property...
domain.DbSchema
...and a role is created for a configuration user in each database domain.
This role is created and assigned automatically when you run the create-database configuration task. As an alternative to creating and assigning this role automatically, we can create a copy of the SQL scripts templates located in the installation directory of IBM WebSphere Portal to use as a guide for creating executable scripts for manually granting permissions. These read-only templates should not be modified and contain invalid SQL syntax. Create our own version of these files to create runnable scripts.
Permissions granted to the schema-owning configuration database user:
Database domain Location of template Release PORTAL_HOME\base\wp.db.impl\config\templates\setupdb\db2_iseries\release\createConfigRoleForSameSchema.sql Community PORTAL_HOME\base\wp.db.impl\config\templates\setupdb\db2_iseries\community\createConfigRoleForSameSchema.sql Customization PORTAL_HOME\base\wp.db.impl\config\templates\setupdb\db2_iseries\customization\createConfigRoleForSameSchema.sql JCR PORTAL_HOME\base\wp.db.impl\config\templates\setupdb\db2_iseries\jcr\createConfigRoleForSameSchema.sql Feedback PORTAL_HOME\pzn\prereq.pzn\config\templates\setupdb\db2_iseries\feedback\createConfigRoleForSameSchema.sql Likeminds PORTAL_HOME\pzn\prereq.pzn\config\templates\setupdb\db2_iseries\likeminds\createConfigRoleForSameSchema.sql Non-schema-owning configuration database user:
Database domain Location of template Release PORTAL_HOME\base\wp.db.impl\config\templates\setupdb\db2_iseries\release\createConfigRoleForDifferentSchema.sql Community PORTAL_HOME\base\wp.db.impl\config\templates\setupdb\db2_iseries\community\createConfigRoleForDifferentSchema.sql Customization PORTAL_HOME\base\wp.db.impl\config\templates\setupdb\db2_iseries\customization\createConfigRoleForDifferentSchema.sql JCR PORTAL_HOME\base\wp.db.impl\config\templates\setupdb\db2_iseries\jcr\createConfigRoleForDifferentSchema.sql Feedback PORTAL_HOME\pzn\prereq.pzn\config\templates\setupdb\db2_iseries\feedback\createConfigRoleForDifferentSchema.sql Likeminds PORTAL_HOME\pzn\prereq.pzn\config\templates\setupdb\db2_iseries\likeminds\createConfigRoleForDifferentSchema.sql
Required privileges for the runtime database user
When the runtime database user is a schema owner, the property...
domain.DbUser
...is assigned the same value as the properties domain.DbRuntimeUser and domain.DbSchema. The runtime database user typically does not create tables used to query and manipulate data and does not by default have access to these tables. To grant minimum privileges to a runtime database user to work with these tables, access needs to be provided for the objects individually. A role is created for runtime database users in each database domain. These roles are created and assigned automatically when you run the create-database configuration task before database transfer and later run grant-runtime-db-user-privileges configuration after database transfer. Before you run these configuration tasks, the runtime database user can only access the database to validate configurations. As an alternative to creating and assigning this role automatically, we can create a copy of the SQL scripts templates located in the installation directory of IBM WebSphere Portal to use as a guide for creating executable scripts for manually granting permissions. These read-only templates should not be modified and contain invalid SQL syntax. Create our own version of these files to create runnable scripts.
Permissions granted to the schema-owning configuration database user:
Database domain Location of template Release PORTAL_HOME\base\wp.db.impl\config\templates\setupdb\db2_iseries\release\createRuntimeRoleForSameSchema.sql Community PORTAL_HOME\base\wp.db.impl\config\templates\setupdb\db2_iseries\community\createRuntimeRoleForSameSchema.sql Customization PORTAL_HOME\base\wp.db.impl\config\templates\setupdb\db2_iseries\customization\createRuntimeRoleForSameSchema.sql JCR PORTAL_HOME\base\wp.db.impl\config\templates\setupdb\db2_iseries\jcr\createRuntimeRoleForSameSchema.sql PORTAL_HOME\jcr\wp.content.repository.install\config\templates\setupdb\db2_iseries\jcr\grantPermissionsToRuntimeRoleStatic.sql
Feedback PORTAL_HOME\pzn\prereq.pzn\config\templates\setupdb\db2_iseries\feedback\createRuntimeRoleForSameSchema.sql Likeminds PORTAL_HOME\pzn\prereq.pzn\config\templates\setupdb\db2_iseries\likeminds\createRuntimeRoleForSameSchema.sql Non-schema-owning runtime database user:
Database domain Location of template Release PORTAL_HOMEbase\wp.db.impl\config\templates\setupdb\db2_iseries\release\createRuntimeRoleForDifferentSchema.sql Community PORTAL_HOME\base\wp.db.impl\config\templates\setupdb\db2_iseries\community\createRuntimeRoleForDifferentSchema.sql Customization PORTAL_HOME\base\wp.db.impl\config\templates\setupdb\db2_iseries\customization\createRuntimeRoleForDifferentSchema.sql JCR PORTAL_HOME\base\wp.db.impl\config\templates\setupdb\db2_iseries\jcr\createRuntimeRoleForDifferentSchema.sql PORTAL_HOME\jcr\wp.content.repository.install\config\templates\setupdb\db2_iseries\jcr\grantPermissionsToRuntimeRoleStatic.sql
Feedback PORTAL_HOME\pzn\prereq.pzn\config\templates\setupdb\db2_iseries\feedback\createRuntimeRoleForDifferentSchema.sql Likeminds PORTAL_HOME\pzn\prereq.pzn\config\templates\setupdb\db2_iseries\likeminds\createRuntimeRoleForDifferentSchema.sql
Parent: Windows stand-alone: Prepare IBM DB2 for i
Previous: Windows stand-alone: Set up IBM DB2 for i databases automatically
Next: Windows stand-alone: Configure the portal to use DB2 for IBM i
Related:
Database users