Simplify the DB2 9.7 security model

Starting in DB2 9.7, the DB2 security model was changed. Security administration (SECADM) was split off from database administration (DBADM), and users with SECADM permissions could no longer peek at data, at least not by default.

Secure by default is a great practice. It’s fantastic when deploying a production database at a financial institution. However, it’s not so good when you are a new user trying to learn a tool.

Control Center seems to run as SYSTEM by default on WinXP, which leads to errors like this:

SQL0551N  "SYSTEM" does NOT have the required authorization
OR privilege TO perform operation "SELECT" ON object "LEONSP.ACT". 
SQLSTATE=42501

Let’s remedy this by granting lots of privileges all around.

Open up the DB2 Command Window.

Connect to the database as a user that already has permissions (leonsp for me):

db2 connect TO samptest user leonsp USING password

Grant all privileges to SYSTEM:

db2 GRANT  DBADM, SECADM, CREATETAB, BINDADD, CONNECT, CREATE_NOT_FENCED_ROUTINE, IMPLICIT_SCHEMA, LOAD, CREATE_EXTERNAL_ROUTINE, QUIESCE_CONNECT ON DATABASE  TO USER system 

Did you create DB2ADMIN or some other id when installing DB2 on Windows? Let’s grant all privileges to that user as well:

db2 GRANT  DBADM, SECADM, CREATETAB, BINDADD, CONNECT, CREATE_NOT_FENCED_ROUTINE, IMPLICIT_SCHEMA, LOAD, CREATE_EXTERNAL_ROUTINE, QUIESCE_CONNECT ON DATABASE  TO USER db2admin

You should now no longer see the SELECT privilege error. Note that you should not do this in a production environment, as this is less secure than the default configuration.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s