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.
Leo on Tech 