[数据库][DB2]数据库对象授权处理
来源:互联网 发布:卡盟源码官方最新 编辑:程序博客网 时间:2024/06/05 02:39
在 DB2 V10 以后的版本,对所有的数据库对象的授权开始严格管控起来,通常的只有创建对象的用户才能拥有较多的操作权限,而别的认证用户往往需要主动显示赋权,否则将报出以下错误信息:
SQL0551N "JANE" does not have the privilege to perform operation "SELECT" on object "AS.KBPSZ".SQL0552N "JANE" does not have the privilege to perform operation "INSERT" on object "AS.KBPSZ".SQL0553N "JANE" does not have the privilege to perform operation "UPDATE" on object "AS.KBPSZ".SQL0554N "JANE" does not have the privilege to perform operation "ALTER" on object "AS.KBPSZ".SQL0555N "JANE" does not have the privilege to perform operation "INDEX" on object "AS.KBPSZ".SQL0556N "JANE" does not have the privilege to perform operation "DELETE" on object "AS.KBPSZ".SQL0557N "JANE" does not have the privilege to perform operation "REFERENCES" on object "AS.KBPSZ".SQL0558N "JANE" does not have the privilege to perform operation "BIND" on object "AS.KBPSZ".SQL0559N "JANE" does not have the privilege to perform operation "EXECUTE" on object "AS.KBPSZ".SQL0560N "JANE" does not have the privilege to perform operation "ALTERIN" on object "AS.KBPSZ".SQL0561N "JANE" does not have the privilege to perform operation "DROPIN" on object "AS.KBPSZ".SQL0562N "JANE" does not have the privilege to perform operation "CREATEIN" on object "AS.KBPSZ".SQL0563N "JANE" does not have the privilege to perform operation "CONTROL" on object "AS.KBPSZ".
此时需要使用grant命令来赋权。使用拥有权限的授权用户来对需要赋权的用户赋权
db2 grant <权限> on <对象种类> <对象名> to [user] <用户> eg. db2 grant select on table as.kbpsz to user jim
权限词说明:
如果需要对全部用户处理,赋权给public就可以了。
db2 grant control on as.demo_table to public
附上全数据库对象赋权的脚本:
#!/bin/sh# 只能在inst用户下执行db2 CONNECT TO dbDemo# 表 & 视图TBS=` db2 -x "SELECT CHAR( RTRIM(TABSCHEMA) || '.' || TABNAME , 120) FROM SYSCAT.TABLES WHERE TABSCHEMA IN ( SELECT SCHEMANAME FROM SYSCAT.SCHEMATA WHERE DEFINERTYPE = 'U')" `# 函数 & 存储过程FUNCS=` db2 -x "SELECT CHAR(RTRIM(FUNCSCHEMA) || '.' || FUNCNAME, 80) FROM SYSCAT.FUNCTIONS WHERE FUNCSCHEMA IN ( SELECT SCHEMANAME FROM SYSCAT.SCHEMATA WHERE DEFINERTYPE = 'U') " `# 序列SEQS=` db2 -x "SELECT CHAR( RTRIM(SEQSCHEMA) || '.' || SEQNAME, 80) FROM SYSCAT.SEQUENCES WHERE SEQTYPE = 'S' AND SEQSCHEMA IN ( SELECT SCHEMANAME FROM SYSCAT.SCHEMATA WHERE DEFINERTYPE = 'U')" `for t in ${TBS[@]}; do db2 GRANT CONTROL ON $t TO PUBLIC ; donefor t in ${FUNCS[@]}; do db2 GRANT EXECUTE ON FUNCTION $t TO PUBLIC ; donefor t in ${SEQS[@]}; do db2 GRANT USAGE ON SEQUENCE $t TO PUBLIC ; donedb2 CONNECT RESET
0 0
- [数据库][DB2]数据库对象授权处理
- DB2数据库碎片处理
- DB2 数据库对象
- 关于DB2 数据库授权的研究
- DB2数据库SQL1366N错误处理
- DB2学习笔记--数据库对象
- 数据库授权
- 数据库授权
- DB2数据库
- DB2数据库
- db2数据库
- DB2数据库
- DB2数据库
- DB2数据库
- 数据库---db2
- DB2数据库日期时间的处理
- 数据库大对象处理
- 关于DB2数据库的ADM11003E和ADM0501C错误的处理
- win7 32位安装Python3.5问题
- 给图片添加倒影
- Hack技巧之属性级hack
- Block定义,记录一下
- 第八周项目三(4)
- [数据库][DB2]数据库对象授权处理
- 关于android TV开发的若干心得
- 第八周 项目3:顺序串算法
- .DS_Store 文件是什么macOS
- 第八周项目3 逆序排列
- 第八周项目1-建立顺序串的算法库
- getReader() is called by
- Spring Bean Scope 有状态的Bean 无状态的Bean
- 第七周排队看病模拟(队列)