Chinaunix首页 | 论坛 | 博客
  • 博客访问: 542403
  • 博文数量: 92
  • 博客积分: 980
  • 博客等级: 准尉
  • 技术积分: 1426
  • 用 户 组: 普通用户
  • 注册时间: 2011-09-25 20:34
文章分类

全部博文(92)

文章存档

2015年(3)

2014年(15)

2013年(27)

2012年(38)

2011年(9)

分类: DB2/Informix

2013-11-24 21:18:39

在DB2中,存在一个虚拟的组(即其不是操作系统的group),如果数据库参数Restrict access 没有被设置为Yse的话(此参数在建立数据库的时候可以随创建数据库语句一起指定),默认所有的操作系统用户都可以作为db2的用户连接到数据库(即所有的操作系统用户都会具有Public组权限),前提是操作系统用户拥有db2的环境变量。
Public组权限的相关内容如下:
 
1、如何查询当前Public组的权限:
可以通过查询syscat.dbauth视图来得到Public组的权限,示例如下:
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
>
 
 
阅读(5042) | 评论(0) | 转发(1) |
给主人留下些什么吧!~~