在DB2中,存在一个虚拟的组(即其不是操作系统的group),如果数据库参数Restrict access
没有被设置为Yse的话(此参数在建立数据库的时候可以随创建数据库语句一起指定),默认所有的操作系统用户都可以作为db2的用户连接到数据库(即所有的操作系统用户都会具有Public组权限),前提是操作系统用户拥有db2的环境变量。
db2inst1@hadr1:/home/db2inst1> db2 "select * from
syscat.dbauth"
GRANTOR
GRANTORTYPE
GRANTEE
GRANTEETYPE BINDADDAUTH CONNECTAUTH CREATETABAUTH DBADMAUTH EXTERNALROUTINEAUTH
IMPLSCHEMAAUTH LOADAUTH NOFENCEAUTH QUIESCECONNECTAUTH LIBRARYADMAUTH
SECURITYADMAUTH SQLADMAUTH WLMADMAUTH EXPLAINAUTH DATAACCESSAUTH
ACCESSCTRLAUTH
--------------------------------------------------------------------------------------------------------------------------------
-----------
--------------------------------------------------------------------------------------------------------------------------------
----------- ----------- ----------- ------------- --------- -------------------
-------------- -------- ----------- ------------------ --------------
--------------- ---------- ---------- ----------- --------------
--------------
SYSIBM
S
DB2INST1
U N N N Y N
N N N N N
Y N N N Y Y
SYSIBM
S PUBLIC
G Y Y Y N N
Y N N N N
N N N N N N
DB2INST1
U
LEOLIU
U N N N N N
N N N N N
N N N N Y Y
3 record(s) selected.
db2inst1@hadr1:/home/db2inst1>
通过其中的Public一行,可以得到Public组的权限清单。
2、测试使用普通操作系统用户连接数据库:
为普通用户组增加test用户:
hadr1:~ # useradd -m -g gengralGroup -s /bin/ksh -d /home/test test
hadr1:~ # passwd test
Changing password for test.
New Password:
Bad password: too simple
Reenter New Password:
Password changed.
为test用户增加db2环境变量
test@hadr1:/home/test> vi .profile
# The following three lines have been added by IBM DB2 instance
utilities.
if [ -f /home/db2inst1/sqllib/db2profile ]; then
.
/home/db2inst1/sqllib/db2profile
fi
连接数据库:
test@hadr1:/home/test> db2 connect to sample user
test using 123456 其中,user是操作系统用户名, using使用的是操作系统用户密码
Database Connection Information
Database server = DB2/LINUXX8664 9.7.0
SQL authorization ID = TEST
Local database alias = SAMPLE
test@hadr1:/home/test>
成功连接;
显示当前用户的权限
test@hadr1:/home/test> db2 get AUTHORIZATIONS
Administrative Authorizations for Current User
Direct SYSADM authority = NO
Direct SYSCTRL authority = NO
Direct SYSMAINT authority = NO
Direct DBADM authority = NO
Direct CREATETAB authority = NO
Direct BINDADD authority = NO
Direct CONNECT authority = NO
Direct CREATE_NOT_FENC authority = NO
Direct IMPLICIT_SCHEMA authority = NO
Direct LOAD authority = NO
Direct QUIESCE_CONNECT authority = NO
Direct CREATE_EXTERNAL_ROUTINE authority = NO
Direct SYSMON authority = NO
Indirect SYSADM authority = NO
Indirect SYSCTRL authority = NO
Indirect SYSMAINT authority = NO
Indirect DBADM authority = NO
Indirect CREATETAB authority = YES
Indirect BINDADD authority = YES
Indirect CONNECT authority = YES
Indirect CREATE_NOT_FENC authority = NO
Indirect IMPLICIT_SCHEMA authority = YES
Indirect LOAD authority = NO
Indirect QUIESCE_CONNECT authority = NO
Indirect CREATE_EXTERNAL_ROUTINE authority = NO
Indirect SYSMON authority = NO
test@hadr1:/home/test>
拥有的都是间接权限,且和public组中的权限一致。
如果收回public的连接权限,则普通操作系统用户不能连接数据库:
db2inst1@hadr1:/home/db2inst1> db2 revoke connect on database from
public
DB20000I The SQL command completed successfully.
db2inst1@hadr1:/home/db2inst1> db2 "select * from syscat.dbauth"
GRANTOR
GRANTORTYPE
GRANTEE
GRANTEETYPE BINDADDAUTH CONNECTAUTH CREATETABAUTH DBADMAUTH EXTERNALROUTINEAUTH
IMPLSCHEMAAUTH LOADAUTH NOFENCEAUTH QUIESCECONNECTAUTH LIBRARYADMAUTH
SECURITYADMAUTH SQLADMAUTH WLMADMAUTH EXPLAINAUTH DATAACCESSAUTH
ACCESSCTRLAUTH
--------------------------------------------------------------------------------------------------------------------------------
-----------
--------------------------------------------------------------------------------------------------------------------------------
----------- ----------- ----------- ------------- --------- -------------------
-------------- -------- ----------- ------------------ --------------
--------------- ---------- ---------- ----------- --------------
--------------
SYSIBM
S
DB2INST1
U N N N Y N
N N N N N
Y N N N Y Y
SYSIBM
S PUBLIC
G Y N Y N N
Y N N N N
N N N N N
N
DB2INST1
U
LEOLIU
U N N N N N
N N N N N
N N N N Y Y
3 record(s) selected.
db2inst1@hadr1:/home/db2inst1>
尝试连接数据库:
> db2
connect to sample
SQL1060N User "TEST " does not have the CONNECT
privilege. SQLSTATE=08004
>