以下示例,数据库实例名cmis,用户bsbview,规范必须遵守如下:
a)所有表空间的数据文件应放在/oradata/cmis目录下
b)数据库用户名的密码必须含有:数字,字母和其他非字母和数字的字符,如#,$,!等
c)所有数据库用户不能拥有DBA权限。
1)创建表空间和临时表空间
sqlplus / as sysdba
SQL> create tablespace tbs01 datafile '/oradata/cmis/tbs01_1.dbf' size 10m,'/oradata/cmis/tbs01_2.dbf' size 10m;
SQL> create temporary tablespace temp01 tempfile '/oradata/cmis/temp01_1.dbf' size 10m;
2)创建用户指定默认表空间和临时表空间
sqlplus / as sysdba
SQL> create user bsbview identified by bsbview12# default tablespace tbs01 temporary tablespace temp01;
SQL> grant resource,connect to bsbview;
若未指定,则可修改:
alter user bsbview default tablespace tbs01 ;
alter user bsbview temporary tablespace temp01;
3)创建表,表空间若使用用户默认表空间,则建表语句可以不指定表空间
SQL> conn bsbview/bsbview12#;
SQL> create table t2(column1 int, column2 char(80)) tablespace tbs01;
4)权限管理用两个角色来实现:
#--创建角色 bsb_write_role:具有DML操作权限
create role bsb_write_role;
#--创建角色 bsb_read_role:具有只读权限
create role bsb_read_role;
#-----------------------------------------------#
#--给DML角色 bsb_write_role 授权
GRANT CONNECT,RESOURCE,exp_full_database,imp_full_database TO bsb_write_role;
GRANT ALTER ANY CLUSTER TO bsb_write_role;
GRANT ALTER ANY INDEX TO bsb_write_role;
GRANT ALTER ANY PROCEDURE TO bsb_write_role;
GRANT ALTER ANY ROLE TO bsb_write_role;
GRANT ALTER ANY SEQUENCE TO bsb_write_role;
GRANT ALTER ANY SNAPSHOT TO bsb_write_role;
GRANT ALTER ANY TABLE TO bsb_write_role;
GRANT ALTER ANY TRIGGER TO bsb_write_role;
GRANT ALTER PROFILE TO bsb_write_role;
GRANT ALTER SESSION TO bsb_write_role;
GRANT ALTER USER TO bsb_write_role;
GRANT BACKUP ANY TABLE TO bsb_write_role;
GRANT CREATE ANY CLUSTER TO bsb_write_role;
GRANT CREATE ANY INDEX TO bsb_write_role;
GRANT CREATE ANY PROCEDURE TO bsb_write_role;
GRANT CREATE ANY SEQUENCE TO bsb_write_role;
GRANT CREATE ANY SNAPSHOT TO bsb_write_role;
GRANT CREATE ANY SYNONYM TO bsb_write_role;
GRANT CREATE PUBLIC SYNONYM TO bsb_write_role;
GRANT CREATE SYNONYM TO bsb_write_role;
GRANT CREATE ANY TABLE TO bsb_write_role;
GRANT CREATE ANY TRIGGER TO bsb_write_role;
GRANT CREATE ANY VIEW TO bsb_write_role;
GRANT CREATE DATABASE LINK to bsb_write_role;
grant select any table to bsb_write_role;
grant select any dictionary to bsb_write_role ;
#-----------------------------------------------#
#--给只读角色 bsb_read_role 授权
GRANT CONNECT,exp_full_database TO bsb_read_role;
grant SELECT ANY DICTIONARY to bsb_read_role;
grant select any table to bsb_read_role;
#-----------------------------------------------#
#--给只读用户 bsbview 授予只读角色 bsb_read_role
grant role bsb_read_role to user bsbview
#--给应用用户 bsbapp 授予DML角色 bsb_write_role
grant role bsb_write_role to user bsbapp