提取用户对象及系统权限DDL(二)
IDENTIFIED BY VALUES '69BAB63C70A4AA28' -->注，生成的脚本文件执行前需要添加分号，不能直接执行
*
ERROR at line 1: -->执行时由于用户存在，所有收到了冲突提示
ORA-01920: user name 'GX_ADMIN' conflicts with another user or role name
Grant succeeded.
Grant succeeded.
Grant succeeded.
--校验同步后的结果，返回480行记录
--注，如果你的DB结构或数据库对象不一致，可以存在两边结果不一样的情形
--如，原库有表tb1，gx_admin对其有DML权限，而目标库没有，则目标库执行ddl语句时会报错，提示对象不存在
--也可能目标库权限总是比原库多的情形，可以先移出目标库上指定用户的所有权限后，再同步
--Author : Leshami
--Blog : http://blog.csdn.net/leshami
sys@BO2SZ> @all_perm_specified_user
Enter value for input_username: GX_ADMIN
GX_ADMIN ObjPrivs SYS.COL$ - SELECT (With Grant Option)
GX_ADMIN ObjPrivs SYS.DBA_DATA_FILES - SELECT (With Grant Option)
GX_ADMIN ObjPrivs SYS.DBA_EXTENTS - SELECT (With Grant Option)
GX_ADMIN ObjPrivs SYS.DBA_FREE_SPACE - SELECT (With Grant Option)
GX_ADMIN ObjPrivs SYS.V_$_LOCK - SELECT (With Grant Option)
GX_ADMIN ROLE DBA
GX_ADMIN ROLE EXP_FULL_DATABASE
GX_ADMIN ROLE IMP_FULL_DATABASE
GX_ADMIN ROLE JAVAUSERPRIV
GX_ADMIN SysPrivs ALTER ANY OUTLINE
GX_ADMIN SysPrivs ALTER SESSION (With Admin Option)
GX_ADMIN SysPrivs CREATE ANY DIRECTORY
..............
480 rows selected.
3、查看、提取用户系统权限DDL脚本
[sql]
--查看指定用户所有权限的脚本
robin@SZDB:~/dba_scripts/custom/sql> more all_perm_specified_user.sql
# get all permission of specified user
# file_name: all_perm_specified_user.sql
# Author : Leshami
set echo off
set verify off
set pagesize 999
set linesize 200
col type format a20
SELECT *
FROM (SELECT a.username, 'ROLE' AS TYPE,
b.granted_role || DECODE (admin_option, 'YES', ' (With Admin Option)', NULL) what_granted
FROM sys.dba_users a, sys.dba_role_privs b
WHERE a.username = b.grantee
UNION
SELECT a.username, 'SysPrivs' AS TYPE,
b.privilege || DECODE (admin_option, 'YES', ' (With Admin Option)', NULL) what_granted
FROM sys.dba_users a, sys.dba_sys_privs b
WHERE a.username = b.grantee
UNION
SELECT a.username,
'ObjPrivs' AS TYPE,
b.owner || '.' || b.table_name || ' - ' || b.privilege ||
DECODE (grantable, 'YES', ' (With Grant Option)', NULL) what_granted
FROM sys.dba_users a, sys.dba_tab_privs b
WHERE a.username = b.grantee
ORDER BY 1)
WHERE username = upper('&input_username');
--提取指定用户权限DDL脚本，通过Oracle自带的包dbms_metadata.get_ddl来实现
robin@SZDB:~/dba_scripts/custom/sql>
more generate_user_ddl.sql
-- Oracle 10g above
clear screen
accept uname prompt 'Enter User Name : '
accept outfile prompt ' Output filename : '
spool &&outfile..gen
SET LONG 2000000 PAGESIZE 0 head off verify off feedback off linesize 180
SELECT dbms_metadata.get_ddl('USER','&&uname') FROM dual;
SELECT dbms_metadata.get_granted_ddl('SYSTEM_GRANT','&&uname') from dual;
SELECT dbms_metadata.get_granted_ddl('ROLE_GRANT','&&uname') from dual;
SELECT dbms_metadata.get_granted_ddl('OBJECT_GRANT','&&uname') from dual;
spool off
--下面的脚本是通过手动方式读取数据字典来实现
robin@SZDB:~/dba_scripts/custom/sql> more generate_user_ddl_manual.sql
clear screen
accept uname prompt 'Enter User Name : '
accept outfile prompt ' Output filename : '
col username noprint
col lne newline
set heading off pagesize 0 verify off feedback off linesize 180
spool &&outfile..gen
prompt -- genarate user ddl
SELECT username, 'CREATE USER '||username||' '||
DECODE(password, 'EXTERNAL'