提取用户对象及系统权限DDL
在工作中难免碰到需要提取用户权限或是不同数据库用户权限的同步问题。我们知道，Oracle数据库的任意一个用户，必须有相应的权限才可以登录以及操纵数据库对象。同时这些用户存在对象权限、系统权限以及所属用户组的情形，或这三种情况同时存在。本文首先通过脚本获取任意指定用户的所有权限，然后产生特定用户所有权限相关的DDL，最后演示了一个权限同步的例子。 有关用户角色的相关概念可参考： Oracle 用户、对象权限、系统权限 Oracle 角色、配置文件
1、获取指定用户所有权限
--首先获取源数据库BOTST上GX_ADMIN的所有权限，我们需要将其同步到数据BO2SZ，GX_ADMIN用户下
--注，BOTST与BO2SZ具有相同的数据库结构及其对象，是两个不同的DB，就好比一个是Prod，一个是Dev环境
--环境
sys@BOTST> select * from v$version where rownum<2;
BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
sys@BOTST> @all_perm_specified_user
Enter value for input_username: GX_ADMIN
USERNAME TYPE WHAT_GRANTED
--------------------------- -------------------- --------------------------------------------------------------
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.DBA_HIST_ACTIVE_SESS_HISTORY - SELECT (With Grant Option)
GX_ADMIN ObjPrivs SYS.DBA_INDEXES - SELECT (With Grant Option)
GX_ADMIN ObjPrivs SYS.DBA_IND_COLUMNS - 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
GX_ADMIN SysPrivs CREATE ANY OUTLINE
GX_ADMIN SysPrivs CREATE ANY TABLE
GX_ADMIN SysPrivs CREATE DATABASE LINK
.......................
480 rows selected.
--获取BO2SZ数据库GX_ADMIN用户所拥有的权限
--如下所示，仅仅返回了18行记录
sys@BO2SZ> @all_perm_specified_user
Enter value for input_username: GX_ADMIN
USERNAME TYPE WHAT_GRANTED
---------------------------- -------------------- ---------------------------------------------------------
GX_ADMIN ObjPrivs SYS.DBA_OBJECTS - SELECT
GX_ADMIN ObjPrivs SYS.DBMS_DATAPUMP - EXECUTE
GX_ADMIN ObjPrivs SYS.DBMS_LOCK - EXECUTE
GX_ADMIN ObjPrivs SYS.DBMS_LOCK_ALLOCATED - SELECT
GX_ADMIN ObjPrivs SYS.DB_DUMP_DIR - READ (With Grant Option)
GX_ADMIN ObjPrivs SYS.DB_DUMP_DIR - WRITE (With Grant Option)
GX_ADMIN ObjPrivs SYS.V_$LOCK - SELECT
GX_ADMIN ObjPrivs SYS.V_$LOCKED_OBJECT - SELECT
GX_ADMIN ObjPrivs SYS.V_$PARAMETER - SELECT
GX_ADMIN ObjPrivs SYS.V_$PROCESS - SELECT
GX_ADMIN ObjPrivs SYS.V_$SESSION - SELECT
11 rows selected. 2、提取用户系统权限DDL示例
--下面直接通过脚本generate_user_ddl来提取指定用户GX_ADMIN下的所有权限
sys@BO2SZ> @generate_user_ddl
Enter User Name : GX_ADMIN
Output filename : $LOG/sync_GX_ADMIN_BO2SZ
CREATE USER "GX_ADMIN" IDENTIFIED BY VALUES '69BAB63C70A4AA28'
DEFAULT TABLESPACE "GOEX_USERS_TBL"
TEMPORARY TABLESPACE "GOEX_TEMP"
GRANT SELECT ANY DICTIONARY TO "GX_ADMIN" WITH ADMIN OPTION
GRANT DROP ANY OUTLINE TO "GX_ADMIN"
GRANT ALTER ANY OUTLINE TO "GX_ADMIN"
GRANT CREATE ANY OUTLINE TO "GX_ADMIN"
...............
--下面是脚本输出的ddl文件
sys@BOTST> ho ls -hltr $LOG/sync_GX_ADMIN_BO2SZ*
-rw-r--r-- 1 robin oinstall 37K 2013-11-04 11:49 /users/robin/dba_scripts/custom/log/sync_GX_ADMIN_BO2SZ.gen
sys@BOTST> exit
--下面我们将其同步到数据库BO2SZ下gx_admin
robin@SZDB:~/dba_scripts/custom/sql> sqlplus sys/xxx@BO2SZ as sysdba
sys@BO2SZ> @/users/robin/dba_scripts/custom/log/sync_GX_ADMIN_BO2SZ.gen
CREATE USER "GX_ADMIN" 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脚本
--查看指定用户所有权限的脚本
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', 'IDENTIFIED EXTERNALLY',
'IDENTIFIED BY VALUES '''||password||''' ') lne,
'DEFAULT TABLESPACE '||default_tablespace lne,
'TEMPORARY TABLESPACE '||temporary_tablespace||';' lne
FROM DBA_USERS
WHERE USERNAME LIKE UPPER('%&&uname%')
OR UPPER('&&uname') IS NULL
ORDER BY USERNAME;
SELECT username, 'ALTER USER '||username||' QUOTA '||
DECODE(MAX_BYTES, -1, 'UNLIMITED', TO_CHAR(ROUND(MAX_BYTES/1024))||'K')
||' ON '||tablespace_name||';' lne
FROM DBA_TS_QUOTAS
WHERE USERNAME LIKE UPPER('%&&uname%')
OR UPPER('&&uname') IS NULL
ORDER BY USERNAME;
col grantee noprint
select grantee, granted_role granted_priv,
'GRANT '||granted_role||' to '||grantee||
DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')
from dba_role_privs
where grantee like upper('%&&uname%')
UNION
select grantee, privilege granted_priv,
'GRANT '||privilege||' to '||grantee||
DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')
from dba_sys_privs
where grantee like upper('%&&uname%')
order by 1, 2;
spool off本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2013年11月04日，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读