How do I grant a user SYSADM, SYSCTRL, SYSMAINT, or SYSMON privileges in Db2?
As a newbie to the database administration world, I was tasked with giving a pretend user the ability to create databases in a virtual environment setup for learning. I easily found that the lowest level of permission that the user would need was SYSCTRL. The hard part was figuring out how to grant that to my user.
As described by Chong, Liu, Qi, and Snow (2005), I was not trying to grant privileges, I was trying to give authority to the user. In Db2, there are two types of permissions: system authority and database privileges. Database privileges are the set of rights and abilities that give users power to act on or change database objects. System authorities are the higher set of rights that give users power over db2 instances. System authorities cannot be granted to users like database privileges can, instead they are controlled by membership to key groups. The group membership is defined in the OS or security facility. The groups are assigned those authorities in the Db2 database manager configuration(dbm cfg). The ability to view the database manager configuration requires SYSADM authority. Each authority can only be assigned to one group.
The command to view the file is:
$ db2 get dbm cfg | grep group
In order to change or otherwise update the groups assigned to the various system authorities you would run:
$ db2 update dbm cfg using [system authority]_group [group_name]
As an example, if you were wanting the group group1 to have SYSCTRL authority and the group group2 to have SYSMON authority you would run the command like so (if these authorities were already assigned to groups, running this command will overwrite them, which would cause members of the previous groups to lose their authority):
$ db2 update dbm cfg using sysctrl_group group1 sysmon group2
If you need to remove groups from an assigned authority you would replace the group’s name with the word ‘null’ like this:
$ db2 update dbm cfg using sysmaint_group null
Keep in mind that the database manager configuration is not configurable online. This means that the instance will have to be stopped and restarted in order for the changes to take effect. This may take some planning in a production environment. Once you have your groups assigned in the database manager configuration, giving users these authorities is a simple matter of making them members of the appropriate group.
References
Chong, R. F., Liu, C., Qi, S. F., & Snow, D. R. (2005). Understanding DB2: Learning visually with examples ;. Upper Saddle River, NJ: Prentice Hall Professional Technical Reference.