Oracle 生成用户及权限复制
来源:互联网 发布:北风网大数据视频破解 编辑:程序博客网 时间:2024/06/06 14:11
1. 生成建立用户的脚本
set pagesize 0
set escape on
select 'create user ' || U.username || ' identified ' ||
DECODE(password,
NULL, 'EXTERNALLY',
' by values ' || '''' || password || ''''
)
|| chr(10) ||
'default tablespace ' || default_tablespace || chr(10) ||
'temporary tablespace ' || temporary_Tablespace || chr(10) ||
' profile ' || profile || chr(10) ||
'quota ' ||
decode ( Q.max_bytes, -1, 'UNLIMITED', NULL, 'UNLIMITED', Q.max_bytes) ||
' on ' || default_tablespace ||
decode (account_status,'LOCKED', ' account lock',
'EXPIRED', ' password expire',
'EXPIRED & LOCKED', ' account lock password expire',
null)
||
';'
from dba_users U, dba_ts_quotas Q
-- Comment this clause out to include system & default users
where U.username not in ('SYS','SYSTEM',
'SCOTT','DBSNMP','OUTLN','WKPROXY','WMSYS','ORDSYS','ORDPLUGINS','MDSYS',
'CTXSYS','XDB','ANONYMOUS','OWNER','WKSYS','ODM_MTR','ODM','OLAPSYS',
'HR','OE','PM','SH','QS_ADM','QS','QS_WS','QS_ES','QS_OS','QS_CBADM',
'QS_CB','QS_CS','PERFSTAT')
and U.username=Q.username(+) and U.default_tablespace=Q.tablespace_name(+)
;
set pagesize 100
set escape off
2. 生成对象授权语句的脚本
set verify off
set feedback off
set termout off
set pagesize 500
set heading off
set recsep off
set termout on
select 'Creating object grant script by user...' from dual;
set termout off
create table g_temp (seq NUMBER, grantor_owner varchar2(20),
text VARCHAR2(800));
DECLARE
cursor grant_cursor is
SELECT ur$.name, uo$.name, o$.name, ue$.name,
m$.name, t$.sequence#,
decode(NVL(t$.option$,0), 1, ' WITH GRANT OPTION;',';')
FROM sys.objauth$ t$, sys.obj$ o$, sys.user$ ur$,
sys.table_privilege_map m$, sys.user$ ue$, sys.user$ uo$
WHERE o$.obj# = t$.obj# AND t$.privilege# = m$.privilege AND
t$.col# IS NULL AND t$.grantor# = ur$.user# AND
t$.grantee# = ue$.user# and
o$.owner#=uo$.user# and
t$.grantor# != 0
order by sequence#;
lv_grantor sys.user$.name%TYPE;
lv_owner sys.user$.name%TYPE;
lv_table_name sys.obj$.name%TYPE;
lv_grantee sys.user$.name%TYPE;
lv_privilege sys.table_privilege_map.name%TYPE;
lv_sequence sys.objauth$.sequence#%TYPE;
lv_option VARCHAR2(30);
lv_string VARCHAR2(800);
lv_first BOOLEAN;
procedure write_out(p_seq INTEGER, p_owner VARCHAR2, p_string VARCHAR2) is
begin
insert into g_temp (seq, grantor_owner,text)
values (lv_sequence, lv_grantor, lv_string);
end;
BEGIN
OPEN grant_cursor;
LOOP
FETCH grant_cursor INTO lv_grantor,lv_owner,lv_table_name,lv_grantee,
lv_privilege,lv_sequence,lv_option;
EXIT WHEN grant_cursor%NOTFOUND;
lv_string := 'GRANT ' || lv_privilege || ' ON ' || lower(lv_owner) ||
'.' ||
lower(lv_table_name) || ' TO ' || lower(lv_grantee) ||
lv_option;
write_out(lv_sequence, lv_grantor,lv_string);
END LOOP;
CLOSE grant_cursor;
END;
/
spool tfscsopv.lst
break on guser skip 1
col text format a60 word_wrap
select 'connect ' || grantor_owner || '/' guser, text
from g_temp
order by seq, grantor_owner
/
spool off
drop table g_temp;
3. 生成系统授权语句的脚本
set verify off
set feedback off
set termout off
set pagesize 0
set termout on
select 'Creating system privilege grant script...' from dual;
set termout off
spool tfscsspv.sql
select 'GRANT ' || rpad(lower(privilege),30) || ' TO ' || lower(grantee) || decode(admin_option,'YES',' WITH ADMIN OPTION;',';')
from sys.dba_sys_privs
where grantee not in ('CONNECT','RESOURCE','DBA','EXP_FULL_DATABASE','IMP_FULL_DATABASE')
order by grantee
/
spool off
4. 生成授予角色的脚本
set verify off
set feedback off
set termout off
set pagesize 0
set termout on
select 'Creating role build script...' from dual;
set termout off
spool tfscsrol.sql
select 'CREATE ROLE ' || lower(role) || ' NOT IDENTIFIED;'
from sys.dba_roles
where role not in ('CONNECT','RESOURCE','DBA', 'EXP_FULL_DATABASE','IMP_FULL_DATABASE')
and password_required='NO'
/
select 'CREATE ROLE ' || lower(role) || ' IDENTIFIED BY VALUES ' ||
'''' || password || '''' || ';'
from sys.dba_roles, sys.user$
where role not in ('CONNECT','RESOURCE','DBA', 'EXP_FULL_DATABASE','IMP_FULL_DATABASE')
and password_required='YES' and dba_roles.role=user$.name
and user$.type=0
/
select 'GRANT ' || lower(granted_role) || ' TO ' || lower(grantee) ||
' WITH ADMIN OPTION;'
from sys.dba_role_privs
where admin_option='YES'
and granted_role not in ('CONNECT','RESOURCE','DBA', 'EXP_FULL_DATABASE','IMP_FULL_DATABASE')
order by grantee
/
spool off
- Oracle 生成用户及权限复制
- Oracle 生成用户及权限复制(脚本)
- Oracle查看用户及权限
- Oracle 用户及权限设置
- ORACLE用户及权限管理
- ORACLE 用户、Profile 及权限
- ORACLE创建用户、角色及权限管理
- oracle用户创建及权限设置
- Oracle用户、权限及角色管理
- oracle用户创建及权限设置
- oracle用户创建及权限设置
- oracle用户创建及权限设置
- Oracle用户创建及权限授予
- ORACLE 创建用户及赋权限
- oracle用户创建及权限设置
- oracle用户创建及权限设置
- oracle用户创建及权限设置
- oracle用户创建及权限设置
- Linux 进程管理之进程切换
- 我又被当当骗了!!!
- 百度权重问题
- 入手mini2440
- SQLite数据库如何存储和读取二进制数据
- Oracle 生成用户及权限复制
- 初试Ruby/Tk
- 浪潮之巅 第三章 “水果”公司的复兴 (1) 传奇小子
- VSS的自动备份
- net time
- oracle 解锁 unlock
- Window.open的一个用法,写API比较好
- 从今天开始,记录我的技术脚步
- linux file lock