DB2：user,schema,权限

DB2建用户

 

【步骤1】创建OS用户

useradd --help

cat /etc/group |grep -i db2iadm

useradd -g db2iadm -m <user>

passwd <user>

【步骤2】创建schema

create schema,create bufferpool,create tablespace

【步骤3】给user授权

grant: database,schema,tablespace

说明:<user>的长度<=8

 

 

删除db2用户:

【步骤1】给user收权

revoke:database,schema,tablespace

【步骤2】创建schema

drop schema <schema> restrict;  --schema没有数据库对象

call admin_drop_schema('<schema_name>',null,'<errschema>','<errtable>');  --schema有数据库对象

【步骤3】删除OS用户

userdel -r <user>

说明:grant新建的OS用户后，在客户端QC的users下可以看到该用户;revoke后，该用户会从QC的users删除。

 

 

--表空间

create regular tablespace  tbs_test

pagesize 32k

managed by database

using (file '/home/db2inst1/data/tbs_test.dbf' 32768)

bufferpool buf_pool_32k

extentsize 8

prefetchsize 16;  --表空间名的长度不能超过18,注意参数顺序

1GB=32768 * 32KB  --32768为pagesize的个数

 

 

--模式

create schema <schema> authorization <user>;

 

--授权

grant dbadm on database to user <user>;

grant connect on database to user <user>;

grant createtab on database to user <user>;

grant implicit_schema on database to user <user>;

grant bindadd on database to user <user>;

grant create_not_fenced_routine on database to user <user>; 

grant load on database to user <user>;

grant create_external_routine on database to user <user>;

grant quiesce_connect on database to user <user>;

 

 

grant createin on schema <schema> to user <user>;

grant alterin on schema <schema> to user <user>;

grant dropin on schema <schema> to user <user>;

 

grant use of tablespace <tablespace> to user <user>;

grant use of tablespace <temporary_tablespace> to user <user>;