IBM Support

Can I change an already created database to be restrictive?

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:

  1. 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
  2. Export the database data. 
    db2move <database> export
  3. Run an offline backup from the database (just to be safe). 
    db2 backup <database> to <path_to_backup>
  4. Backup the database configurations. (You can also use the db2cfexp as well) 
    db2 get database configuration for <database> > db_cfg.bkp
  5. Drop and create the database with the restrictive option 
    db2 drop db <database>
db2 create db <database> <db options> restrictive
  6. 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.
  7. Use the db2look.sql created on the step # 1 to recreate the database objects. 
    db2 -tvf db2look.sql
  8. 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.

[{"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"}]

Document Information

Modified date:
31 May 2023

UID

swg21612075

Page Feedback