提取用户对象及系统权限DDL
在工作中难免碰到需要提取用户权限或是不同
数据库用户权限的同步问题。我们知道，
Oracle数据库的任意一个用户，必须有相应的权限才可以登录以及操纵数据库对象。同时这些用户存在对象权限、
系统权限以及所属用户组的情形，或这三种情况同时存在。本文首先通过脚本获取任意指定用户的所有权限，然后产生特定用户所有权限相关的DDL，最后演示了一个权限同步的例子。
1、获取指定用户所有权限
[sql]
--首先获取源数据库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示例
[sql]
--下面直接通过脚本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"