DB2 permission management

A part of the record related to permission management.

Instance-level permissions: SYSADM has the highest management permission, which is usually used by DBAs. SYSCTRL has the highest system control permission and has all SYSMAINT and SYSMON permissions. SYSMAINT sub-system control permission Minimum instance-level management permissions for SYSMON
View commands in Unix/Linux: Db2 get dbm cfg | grep "SYSADM" Db2 get dbm cfg | grep "SYSCTRL" Db2 get dbm cfg | grep "SYSMAINT" Db2 get dbm cfg | grep "SYSMON"View commands in Windows: Db2 get dbm cfg | find/I "SYSADM" Db2 get dbm cfg | find/I "SYSCTRL" Db2 get dbm cfg | find/I "SYSMAINT" Db2 get dbm cfg | find/I "SYSMON"

How to authorize:

DB2 instance-level management permissions are bound to operating system users. Therefore, you must first create an operating system user. Example: assume that you have a user: db2ctlusr group: db2ctlgrp Linux: Groupadd db2ctlgrp Useradd-g db2ctlgrp-m-d/home/db2ctlusr db2ctlusrAIX: Groupadd-g 996 db2ctlgrp Mkuser id = 1005 pgrp = db2ctlgrp home/db2ctlusr db2ctlusr
[Root @ O11g64 bin] # Id db2ctlusrUid = 1053 ( Db2ctlusr) Gid = 1021 (db2ctlgrp) groups = 1021 ( Db2ctlgrp)
Compare db2inst1:$ Id db2inst1Uid = 110 ( Db2inst1) Gid = 104 (db2grp1) groups = 1 (staff), 101 ( Dasadm1) Db2inst1 db2iadm1 DB2 instance user, user group Db2fenc1 db2fadm1 protected users, user groups. Run user-defined functions and processes Dasusr1 dasadm1 creates DB2 Management Server users and user groups.
1. Specify the user group in the parameter: A.[Root @ O11g64 bin] # ./Db2 get dbm cfg | grep "SYSCTRL"

SYSCTRL group name (SYSCTRL_GROUP) =

B.[Db2inst1 @ O11g64 ~] $ Db2 update dbm cfg using SYSCTRL_GROUP db2ctlgrpDB20000I The update database manager configuration command completedsuccessfully. SQL1362W One or more of the parameters submitted for immediate modificationwere not changed dynamically. client changes will not be valid tive until thenext time the application is started or the TERMINATE command has been issued. server changes will not be valid tive until the next DB2START command.
C. db2stop D. db2start
E.[Db2inst1 @ O11g64 ~] $ Db2 get dbm cfg | grep "SYSCTRL"SYSCTRL group name (SYSCTRL_GROUP) = DB2CTLGRP

2. Grant User Permissions Db2 connect to xcldb

Db2 "grant dbadm on database to user db2ctlusr"

Db2 connect reset

3. revoke permissions

Db2 connect to xcldb Db2 "revoke dbadm on database from user db2ctlusr" Db2 connect reset

FAQ: SQL5001N error: The reason is that the SYSADM permission is required to change the Database Manager configuration file.Run the following command to find out the group [db2inst1 @ O11g64 ~] $ Db2 get dbm cfg | grep "SYSADM" SYSADM group name (SYSADM_GROUP) = DB2IADM1 users in this group, run the update permission [root @ O11g64 bin] # id db2inst1uid = 1051 (db2inst1) gid = 1010 (db2iadm1) groups = 1010 (db2iadm1), 1020 (db2fadm1)

Table authorization command:-- Public: All Users Grant select on emp to group db2ctlgrp2
Query all permission-related system tables: Db2 "select substr (tabname, 1, 20) from syscat. tables where tabschema = 'syscat' and tabname like '% auth '"; Db2 "select * from DBAUTH"
Role Create role myrole Grant select on table vacation to rle myrole Grant role myrole to user myusr1, user myus2 Revoke role myrole from user myusr1;
-- The user can grant or revoke other roles, which is similar to Oracle. Grant role myrole to user myusr1 with admin option

More fine-grained access control, Tag-based access control (LBAC)
The principle is to set security labels at the row level, column level, or column level so that specific users can access certain rows or columns in a table.
It seems useless.
MAIL: xcl_168@aliyun.comBlog: http://blog.csdn.net/xcl168

$ Id db2inst1uid = 110 (db2inst1) gid = 104 (db2grp1) groups = 1 (staff), 101 (dasadm1)
$ Db2 get dbm cfg SYSADM group name (SYSADM_GROUP) = DB2GRP1 SYSCTRL group name (SYSCTRL_GROUP) = SYSMAINT group name (SYSMAINT_GROUP) = SYSMON group name (SYSMON_GROUP) =
P104 instance-level permission: Maximum SYSADM management permission, which is usually used by DBA to control SYSCTRL highest system permission. All SYSMAINT and SYSMON permissions are granted to SYSMAINT sub-system. The minimum instance-level management permission is SYSMON.
View commands in Unix/Linux: db2 get dbm cfg | grep "SYSADM" db2 get dbm cfg | grep "SYSCTRL" db2 get dbm cfg | grep "SYSMAINT" db2 get dbm cfg | view in Windows command: db2 get dbm cfg | find/I "SYSADM" db2 get dbm cfg | find/I "SYSCTRL" db2 get dbm cfg | find/I "SYSMAINT" db2 get dbm cfg | find/ I "SYSMON"

How to authorize:

DB2 instance-level management permissions are bound to operating system users. therefore, you must first create an operating system user. example: Assume that a user is in the db2ctlusr group: db2ctlgrp Linux: groupadd db2ctlgrp useradd-g db2ctlgrp-m-d/home/db2ctlusr db2ctlusr AIX: groupadd-g 996 db2ctlgrp mkuser id = 1005 pgrp = db2ctlgrp home/db2ctlusr db2ctlusr

[Root @ O11g64 bin] # id db2ctlusruid = 1053 (db2ctlusr) gid = 1021 (db2ctlgrp) groups = 1021 (db2ctlgrp)

Db2inst1 db2iadm1 DB2 instance user, user group db2fenc1 db2fadm1 protected user, user group. Run user-defined functions and process dasusr1 dasadm1 to create user of DB2 Management Server, user group.
1. Specify the user group in the parameter: [root @ O11g64 bin] #./db2 get dbm cfg | grep "SYSCTRL"

SYSCTRL group name (SYSCTRL_GROUP) =

[Db2inst1 @ O11g64 ~] $ Db2 update dbm cfg using SYSCTRL_GROUP db2ctlgrpDB20000I The update database manager configuration command completedsuccessfully. SQL1362W One or more of the parameters submitted for immediate modificationwere not changed dynamically. client changes will not be valid tive until thenext time the application is started or the TERMINATE command has been issued. server changes will not be valid tive until the next DB2START command.
Db2stopdb2start
[Db2inst1 @ O11g64 ~] $ Db2 get dbm cfg | grep "SYSCTRL" SYSCTRL group name (SYSCTRL_GROUP) = DB2CTLGRP

2. Grant user permissions to db2 connect to xcldb

Db2 "grant dbadm on database to user db2ctlusr"

Db2 connect reset

3. revoke permissions

Db2 connect to xcldbdb2 "revoke dbadm on database from user db2ctlusr" db2 connect reset

FAQ: SQL5001N error: The cause is that SYSADM permission is required to change the Database Manager Configuration File. Use the following command to find the group [db2inst1 @ O11g64 ~] $ Db2 get dbm cfg | grep "SYSADM" SYSADM group name (SYSADM_GROUP) = DB2IADM1 users in this group, run the update permission [root @ O11g64 bin] # id db2inst1uid = 1051 (db2inst1) gid = 1010 (db2iadm1) groups = 1010 (db2iadm1), 1020 (db2fadm1)

[Root @ O11g64 bin] #. /db2 update dbm cfg using SYSCTL_GROUP db2ctlgr sql0104n An unexpected token "SYSCTL_GROUP" was found following "USING ". expected tokens may include: "AGENTPRI ". SQLSTATE = 42601

Other -- public is grant select on emp to group db2ctlgrp2 for all users.
Query -- all permission-related system tables db2 "select substr (tabname,) from syscat. tables where tabschema = 'syscat' and tabname like '% auth' "; db2" select * from DBAUTH"

Role create role myrolegrant select on table vacation to rle myrole grant role myrole to user myusr1, user myus2revoke role myrole from user myusr1;
-- The user can grant or revoke other roles. This is the same as the Oracle grant role myrole to user myusr1 with admin option.


There is also more fine-grained access control. The label-based access control (LBAC) works by setting Security labels at the row level, column level, or row level, so that specific users can access certain rows or columns in a table.
It seems useless.