Question & Answer
Question
Can I change an already created database to be restrictive/restricted?
Cause
It is very common that after a database is created, due to security needs ( for example, not allow PUBLIC to access certain objects that are by default granted) a decision is made to change the database to become restricted. However this change cannot be done unless you recreate the database and its objects.
Answer
A database cannot be changed from non-restrictive to restrictive after it is created. To workaround, the database should be recreated by following these steps:
- Export the database objects (remove all public permissions that are not needed from this script)
db2look -d <database> -e -a -l -x -f -o db2look.sql
- Export the database data.
db2move <database> export
- Run an offline backup from the database (just to be safe).
db2 backup <database> to <path_to_backup>
- Backup the database configurations. (You can also use the db2cfexp as well)
db2 get database configuration for <database> > db_cfg.bkp
- Drop and create the database with the restrictive option
db2 drop db <database> db2 create db <database> <db options> restrictive
- Set the database configurations back using the backup taken at step # 4 or with the db2cfexp. This can be manually changed using the "UPDATE DATABASE CONFIGURATION" command or the db2cfimp tool.
- Use the db2look.sql created on the step # 1 to recreate the database objects.
db2 -tvf db2look.sql
- Load/Import data back using the LST file from the db2move command.
db2move <database> load
Please check the Related URL for details on the commands referenced above.
Related Information
[{"Type":"MASTER","Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"ARM Category":[{"code":"a8m500000008PlOAAU","label":"Security and Plug-Ins-\u003EAuthorization and Privilege"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions"}]
Was this topic helpful?
Document Information
Modified date:
31 May 2023
UID
swg21612075