以下示例，数据库实例名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

ITPub博客

815-Oracle规范（创建用户，表空间和表）

最新文章