oracle数据库创建用户标准1

2019独角兽企业重金招聘Python工程师标准>>>

---------------------------------------------------------------------------------------------------------------------
--------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------------------

 

转载于:https://my.oschina.net/u/3862440/blog/2208537