提取用户对象及系统权限DDL
提取用户对象及系统权限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" 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语句时
