oracle x01,oracle数据库创建用户标准1

---------------------------------------------------------------------------------------------------------------------

--------begin--创建data用户--------------

---------------------------------------------------------------------------------------------------------------------

--建data等表空间，分ASM和非ASM

---ASM

create tablespace xxx_data

datafile '+$ORACLE_SID_DATA' size 500m

autoextend on;

---非ASM

create tablespace xxx_data

logging

datafile '/crbank/$ORACLE_SID/data01/xxx_data01.dbf' size 500m

autoextend on

extent management local autoallocate

segment space management auto ;

--建idx等data之外的表空间，分ASM和非ASM

---ASM

create tablespace xxx_IDX

datafile '+$ORACLE_SID_DATA' size 500m

autoextend on;

---非ASM

create tablespace xxx_IDX

logging

datafile '/crbank/$ORACLE_SID/data01/xxx_idx01.dbf' size 500m

autoextend on

extent management local autoallocate

segment space management auto ;

--建data用户

create user xxxdata identified by &pw

profile crbank_pw_profile

default tablespace xxx_data

temporary tablespace temp

quota unlimited on xxx_data

password expire;

--授权

grant create session          to xxxdata;

grant create table            to xxxdata;

grant create sequence         to xxxdata;

grant create trigger          to xxxdata;

grant create view             to xxxdata;

grant create public synonym   to xxxdata;

grant drop public synonym     to xxxdata;

grant create procedure        to xxxdata;

grant create type             to xxxdata;

grant execute on sys.dbms_job        to xxxdata;

grant select on sys.v_$session       to xxxdata;

grant select on sys.dba_jobs_running to xxxdata;

grant select on sys.dba_jobs         to xxxdata;

grant create database link    to xxxdata;

grant create any synonym      to xxxdata;

grant drop any synonym        to xxxdata;

grant create job              to xxxdata;

grant create any context      to xxxdata;

grant create materialized view to xxxdata;

--如果有除非默认表空间之外的表空间，则授予其他表空间可使用权限：

alter user xxxdata quota unlimited on xxx_idx;

-- 增加审计

audit connect by xxxdata;

audit insert table, delete table, update table, select table

by xxxdata

by session

whenever successful;

-----------end--创建data用户--------------

-------------------------------------------------------------------

-----------begin--创建OPR用户-------------

--------------------------------------------------------------------

create user xxxopr identified by &pw

default tablespace xxx_data

temporary tablespace temp

quota unlimited on xxx_data

password expire;

--授系统权限

grant create session to xxxopr;

grant alter  session to xxxopr;

--受角色权限：根据用户需求申请单授予data用户查询角色权限r_&xxxdata_qry，或者data用户对象dml角色权限r_&xxxdata_dml：

--授予r_&xxxdata_qry：

create role r_&xxxdata_qry;

grant r_&xxxdata_qry to xxxopr ;

select 'grant select on '|| owner ||'.'|| table_name ||' to r_&data_qry;' from dba_tables where owner='&xxxdata';

-- 执行上面sql生成的授权脚本--

--授予r_&xxxdata_dml：

create role r_&xxxdata_dml;

grant  r_&xxxdata_dml to xxxopr;

select 'grant select on '|| owner ||'.'|| table_name ||' to r_&data_dml;' from dba_tables where owner='&xxxdata';

-- 执行上面sql生成的授权脚本--

audit insert table, delete table, update table, select table

by xxxopr

by session

whenever successful;

audit connect by xxxopr;

-----------end--创建OPR用户--------------

-------------begin dbmonopr------------------

CREATE USER dbmonopr IDENTIFIED BY dbmonopr123

default tablespace users

temporary tablespace temp

quota unlimited on users;

audit insert table, delete table, update table

by dbmonopr

by session

whenever successful;

audit connect by dbmonopr;

grant create session,alter session to dbmonopr;

grant create table to dbmonopr;

grant create sequence to dbmonopr;

grant create trigger to dbmonopr;

grant create procedure to dbmonopr;

grant create view      to dbmonopr;

grant select on dba_segments to dbmonopr;

grant select on dba_free_space to dbmonopr;

grant select on dba_data_files to dbmonopr;

grant select on dba_temp_files to dbmonopr;

grant select on v_$temp_space_header to dbmonopr;

grant select on dba_tablespaces to dbmonopr;

-------------end  dbmonopr------------------