ITPub博客

首页 > Linux操作系统 > Linux操作系统 > db2 用户-组-权限 测试

db2 用户-组-权限 测试

原创 Linux操作系统 作者:fengjin821 时间:2009-07-30 15:34:11 0 删除 编辑

添加组:


我的电脑-右键-管理-本地用户和组-组-新建组-添加成员
db2sysadm_group,db2sysctrl_group,db2sysmaint_group,sysmon_group

db2test_group

新创建的用户组没有任何的权限:


F:\DB2\IBM\SQLLIB\BIN>db2 grant connect on database to db2test_group
DB20000I  SQL命令成功完成。

F:\DB2\IBM\SQLLIB\BIN>db2 revoke connect on database from  db2test_group
DB20000I  SQL命令成功完成。

 


F:\DB2\IBM\SQLLIB\BIN>db2 get dbm cfg | find /i "group"
 SYSADM 组名                              (SYSADM_GROUP) =
 SYSCTRL 组名                            (SYSCTRL_GROUP) =
 SYSMAINT 组名                          (SYSMAINT_GROUP) =
 SYSMON 组名                              (SYSMON_GROUP) =
 组插件                                   (GROUP_PLUGIN) =

 

一   实例级权限:

F:\DB2\IBM\SQLLIB\BIN>db2 update dbm cfg using sysadm_group db2sysadm_group
DB20000I  UPDATE DATABASE MANAGER CONFIGURATION命令成功完成。

F:\DB2\IBM\SQLLIB\BIN>db2 update dbm cfg using sysctrl_group db2sysctrl_group
DB20000I  UPDATE DATABASE MANAGER CONFIGURATION命令成功完成。

F:\DB2\IBM\SQLLIB\BIN>db2 update dbm cfg using sysmaint_group db2sysmaint_group
DB20000I  UPDATE DATABASE MANAGER CONFIGURATION命令成功完成。

F:\DB2\IBM\SQLLIB\BIN>db2 update dbm cfg using sysmon_group db2sysmon_group
DB20000I  UPDATE DATABASE MANAGER CONFIGURATION命令成功完成。


sysadm,sysctrl,sysmaint,sysmon 这四个权限只能通过组间接地获取,不能直接授予

 

二  数据库级的权限:


通过控制面板  创建用户 test 并且将其添加到db2test_group 中,新创建的test用户权限还不少:


F:\DB2\IBM\SQLLIB\BIN>db2 connect to fengjin user test using test

   数据库连接信息

 数据库服务器         = DB2/NT 9.5.0
 SQL 授权标识         = TEST
 本地数据库别名       = FENGJIN


F:\DB2\IBM\SQLLIB\BIN>db2 values current schema

1

-------------------------------------------------------------------------
TEST


  1 条记录已选择。


F:\DB2\IBM\SQLLIB\BIN>db2 get authorizations

 当前用户的管理权限

 直接 SYSADM 权限                           = NO
 直接 SYSCTRL 权限                          = NO
 直接 SYSMAINT 权限                         = NO
 直接 DBADM 权限                            = YES
 直接 CREATETAB 权限                        = YES
 直接 BINDADD 权限                          = YES
 直接 CONNECT 权限                          = YES
 直接 CREATE_NOT_FENC 权限                  = YES
 直接 IMPLICIT_SCHEMA 权限                  = YES
 直接 LOAD 权限                             = YES
 直接 QUIESCE_CONNECT 权限                  = YES
 直接 CREATE_EXTERNAL_ROUTINE 权限          = YES
 直接 SYSMON 权限                           = NO

 间接 SYSADM 权限                           = NO
 间接 SYSCTRL 权限                          = NO
 间接 SYSMAINT 权限                         = NO
 间接 DBADM 权限                            = NO
 间接 CREATETAB 权限                        = NO
 间接 BINDADD 权限                          = NO
 间接 CONNECT 权限                          = NO
 间接 CREATE_NOT_FENC 权限                  = NO
 间接 IMPLICIT_SCHEMA 权限                  = YES
 间接 LOAD 权限                             = NO
 间接 QUIESCE_CONNECT 权限                  = NO
 间接 CREATE_EXTERNAL_ROUTINE 权限          = NO
 间接 SYSMON 权限                           = NO


对比一下fengjin 的权限:
F:\DB2\IBM\SQLLIB\BIN>db2 connect to fengjin

   数据库连接信息

 数据库服务器         = DB2/NT 9.5.0
 SQL 授权标识         = FENGJIN
 本地数据库别名       = FENGJIN


F:\DB2\IBM\SQLLIB\BIN>db2 get authorizations

 当前用户的管理权限

 直接 SYSADM 权限                           = NO
 直接 SYSCTRL 权限                          = NO
 直接 SYSMAINT 权限                         = NO
 直接 DBADM 权限                            = NO
 直接 CREATETAB 权限                        = NO
 直接 BINDADD 权限                          = NO
 直接 CONNECT 权限                          = NO
 直接 CREATE_NOT_FENC 权限                  = NO
 直接 IMPLICIT_SCHEMA 权限                  = NO
 直接 LOAD 权限                             = NO
 直接 QUIESCE_CONNECT 权限                  = NO
 直接 CREATE_EXTERNAL_ROUTINE 权限          = NO
 直接 SYSMON 权限                           = NO

 间接 SYSADM 权限                           = YES
 间接 SYSCTRL 权限                          = NO
 间接 SYSMAINT 权限                         = NO
 间接 DBADM 权限                            = NO
 间接 CREATETAB 权限                        = NO
 间接 BINDADD 权限                          = NO
 间接 CONNECT 权限                          = NO
 间接 CREATE_NOT_FENC 权限                  = NO
 间接 IMPLICIT_SCHEMA 权限                  = YES
 间接 LOAD 权限                             = NO
 间接 QUIESCE_CONNECT 权限                  = NO
 间接 CREATE_EXTERNAL_ROUTINE 权限          = NO
 间接 SYSMON 权限                           = NO

 

 

撤销test用户的权限

F:\DB2\IBM\SQLLIB\BIN>db2 revoke dbadm on database from test
DB20000I  SQL命令成功完成。

F:\DB2\IBM\SQLLIB\BIN>db2 revoke createtab on database from test
DB20000I  SQL命令成功完成。

F:\DB2\IBM\SQLLIB\BIN>db2 revoke bindadd on database from test
DB20000I  SQL命令成功完成。

F:\DB2\IBM\SQLLIB\BIN>db2 revoke connect on database from test
DB20000I  SQL命令成功完成。

F:\DB2\IBM\SQLLIB\BIN>db2 revoke create_not_fenced on database from test
DB20000I  SQL命令成功完成。

F:\DB2\IBM\SQLLIB\BIN>db2 revoke load on database from test
DB20000I  SQL命令成功完成。

F:\DB2\IBM\SQLLIB\BIN>db2 revoke quiesce_connect on database from test
DB20000I  SQL命令成功完成。

F:\DB2\IBM\SQLLIB\BIN>db2 revoke CREATE_EXTERNAL_ROUTINE on database from test
DB20000I  SQL命令成功完成。


执行完了后运行:

F:\DB2\IBM\SQLLIB\BIN>db2 connect to fengjin user test using test
SQL1060N  用户 "TEST    " 不具有 CONNECT 特权。  SQLSTATE=08004

F:\DB2\IBM\SQLLIB\BIN>db2 grant connect on database to db2test_group
DB20000I  SQL命令成功完成。

F:\DB2\IBM\SQLLIB\BIN>db2 connect to fengjin user test using test

   数据库连接信息

 数据库服务器         = DB2/NT 9.5.0
 SQL 授权标识         = TEST
 本地数据库别名       = FENGJIN

F:\DB2\IBM\SQLLIB\BIN>db2 get authorizations

 当前用户的管理权限

 直接 SYSADM 权限                           = NO
 直接 SYSCTRL 权限                          = NO
 直接 SYSMAINT 权限                         = NO
 直接 DBADM 权限                            = NO
 直接 CREATETAB 权限                        = NO
 直接 BINDADD 权限                          = NO
 直接 CONNECT 权限                          = NO
 直接 CREATE_NOT_FENC 权限                  = NO
 直接 IMPLICIT_SCHEMA 权限                  = YES
 直接 LOAD 权限                             = NO
 直接 QUIESCE_CONNECT 权限                  = NO
 直接 CREATE_EXTERNAL_ROUTINE 权限          = NO
 直接 SYSMON 权限                           = NO

 间接 SYSADM 权限                           = NO
 间接 SYSCTRL 权限                          = NO
 间接 SYSMAINT 权限                         = NO
 间接 DBADM 权限                            = NO
 间接 CREATETAB 权限                        = NO
 间接 BINDADD 权限                          = NO
 间接 CONNECT 权限                          = YES
 间接 CREATE_NOT_FENC 权限                  = NO
 间接 IMPLICIT_SCHEMA 权限                  = YES
 间接 LOAD 权限                             = NO
 间接 QUIESCE_CONNECT 权限                  = NO
 间接 CREATE_EXTERNAL_ROUTINE 权限          = NO
 间接 SYSMON 权限                           = NO

 


C:\Documents and Settings\Fengjin>db2 connect to fengjin

   数据库连接信息

 数据库服务器         = DB2/NT 9.5.0
 SQL 授权标识         = FENGJIN
 本地数据库别名       = FENGJIN


C:\Documents and Settings\Fengjin>db2 grant connect on database to test
DB20000I  SQL命令成功完成。


C:\Documents and Settings\Fengjin>db2 connect to fengjin user test using test

   数据库连接信息

 数据库服务器         = DB2/NT 9.5.0
 SQL 授权标识         = TEST
 本地数据库别名       = FENGJIN


C:\Documents and Settings\Fengjin>db2 get authorizations

 当前用户的管理权限

 直接 SYSADM 权限                           = NO
 直接 SYSCTRL 权限                          = NO
 直接 SYSMAINT 权限                         = NO
 直接 DBADM 权限                            = NO
 直接 CREATETAB 权限                        = NO
 直接 BINDADD 权限                          = NO
 直接 CONNECT 权限                          = YES
 直接 CREATE_NOT_FENC 权限                  = NO
 直接 IMPLICIT_SCHEMA 权限                  = YES
 直接 LOAD 权限                             = NO
 直接 QUIESCE_CONNECT 权限                  = NO
 直接 CREATE_EXTERNAL_ROUTINE 权限          = NO
 直接 SYSMON 权限                           = NO

 间接 SYSADM 权限                           = NO
 间接 SYSCTRL 权限                          = NO
 间接 SYSMAINT 权限                         = NO
 间接 DBADM 权限                            = NO
 间接 CREATETAB 权限                        = NO
 间接 BINDADD 权限                          = NO
 间接 CONNECT 权限                          = YES
 间接 CREATE_NOT_FENC 权限                  = NO
 间接 IMPLICIT_SCHEMA 权限                  = YES
 间接 LOAD 权限                             = NO
 间接 QUIESCE_CONNECT 权限                  = NO
 间接 CREATE_EXTERNAL_ROUTINE 权限          = NO
 间接 SYSMON 权限                           = NO

 

删除某一用户下的模式:

F:\DB2\IBM\SQLLIB\BIN>db2 drop schema test restrict
DB20000I  SQL命令成功完成。

在某一个用户下的schema的权限和这个用户的权限是一样的

 

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13165828/viewspace-610842/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-04-29

  • 博文量
    191
  • 访问量
    511651