db2数据库角色赋权

[root@zhaoliuping /]# groupadd -g 996 secadm
[root@zhaoliuping /]# groupadd -g 995 db2users

useradd -u 1005 -g secadm -m -d /db2home/secadm db2sec
useradd -u 1006 -g db2users -m -d /db2home/users/user01 user01
useradd -u 1007 -g db2users -m -d /db2home/users/user02 user02



db2 "GRANT CONNECT ON DATABASE TO USER user01,user02"

db2 "GRANT secadm ON DATABASE TO USER db2sec"

db2 "CREATE TABLESPACE tbs1 IN DATABASE PARTITION GROUP IBMDEFAULTGROUP
PAGESIZE 8192 MANAGED BY AUTOMATIC STORAGE
AUTORESIZE YES
INITIALSIZE 32 M
MAXSIZE NONE
EXTENTSIZE 32
PREFETCHSIZE AUTOMATIC
BUFFERPOOL IBMDEFAULTBP
OVERHEAD 7.500000
TRANSFERRATE 0.060000
DROPPED TABLE RECOVERY ON"

db2 "CREATE TABLESPACE tbs2 IN DATABASE PARTITION GROUP IBMDEFAULTGROUP
PAGESIZE 8192 MANAGED BY AUTOMATIC STORAGE
AUTORESIZE YES
INITIALSIZE 32 M
MAXSIZE NONE
EXTENTSIZE 32
PREFETCHSIZE AUTOMATIC
BUFFERPOOL IBMDEFAULTBP
OVERHEAD 7.500000
TRANSFERRATE 0.060000
DROPPED TABLE RECOVERY ON"


db2 "create schema s1"
db2 "create schema s2"

db2 "create table s1.t1 (c varchar(10)) in tbs1"
db2 "create table s1.t2 (c varchar(10)) in tbs1"
db2 "create table s2.t3 (c varchar(10)) in tbs2"
db2 "create table s2.t4 (c varchar(10)) in tbs2"


db2 "insert into s1.t1 values('t1')"
db2 "insert into s1.t2 values('t2')"
db2 "insert into s2.t3 values('t3')"
db2 "insert into s2.t4 values('t4')"


[db2sec@zhaoliuping ~]$ db2 "create role ob_s1_sel"
[db2sec@zhaoliuping ~]$ db2 "create role ob_s2_dml"

[db2inst1@zhaoliuping ~]$ db2 grant select on s1.t1 to role ob_s1_sel
[db2inst1@zhaoliuping ~]$ db2 "grant insert,update,delete on s2.t3 to role ob_s2_dml"

[db2sec@zhaoliuping ~]$ db2 "grant role ob_s1_sel,ob_s2_dml to user user01"

sysibmadm.PRIVILEGES


oracle下赋权

select 'grant select on ' || owner || '.' || table_name || ' to OB_XG_SEL;'
from dba_tables
where owner = 'XG'
and table_name not in (select distinct table_name
from dba_tab_privs
where grantee in ('OB_XG_SEL'))


select 'grant insert, update, delete on ' || owner || '.' || table_name || ' to OB_XG_DML;'
from dba_tables
where owner = 'XG'
and table_name not in (select distinct table_name
from dba_tab_privs
where grantee in ('OB_XG_DML'))