提取使用者許可權或是不同資料庫使用者許可權的同步
1、獲取指定使用者所有許可權
[sql] view plaincopyprint?
--首先獲取源資料庫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] view plaincopyprint?
--下面直接透過指令碼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指令碼
[sql] view plaincopyprint?
--檢視指定使用者所有許可權的指令碼
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
[sql] view plaincopyprint?
--首先獲取源資料庫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] view plaincopyprint?
--下面直接透過指令碼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指令碼
[sql] view plaincopyprint?
--檢視指定使用者所有許可權的指令碼
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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23490154/viewspace-1061690/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- PostgreSQL資料庫使用者許可權管理SQL資料庫
- 授權|取消授權MYSQL資料庫使用者許可權MySql資料庫
- 不同使用者python path 許可權問題Python
- vsftpd多例項多使用者不同許可權FTP
- linux使用者許可權Linux
- 許可權系統：一文搞懂功能許可權、資料許可權
- 資料庫的許可權管理資料庫
- mysql 床架新資料庫並分配使用者許可權MySql資料庫
- Linux使用者與許可權Linux
- mysql使用者許可權管理MySql
- 使用者和組的許可權
- 企業級地理資料庫（3）為不同需求的使用者設定許可權資料庫
- MYSQL學習筆記13: DCL許可權控制(使用者許可權操作)MySql筆記
- Oracle資料庫scott使用者建立view檢視許可權Oracle資料庫View
- 許可權之選單許可權
- Linux使用者、組、許可權管理Linux
- MySQL使用者及許可權管理MySql
- Oracle使用者角色許可權管理Oracle
- linux5-使用者許可權Linux
- MySql查詢使用者許可權MySql
- MySQL 使用者及許可權管理?MySql
- 淺談PostgreSQL使用者許可權SQL
- MongoDB 使用者與許可權管理MongoDB
- linux 檔案許可權 s 許可權和 t 許可權解析Linux
- NAS使用者許可權的設定
- UserService 查詢使用者沒許可權的索引，增加臨時許可權queryIndicesNoPermission分析索引
- mysql 給了使用者所有許可權ALL PRIVILEGES，但是該使用者沒有grant許可權MySql
- Mysql資料庫許可權問題MySql資料庫
- 資料庫學習：許可權管理資料庫
- 如何用 Vue 實現前端許可權控制（路由許可權 + 檢視許可權 + 請求許可權）Vue前端路由
- DBA 日常：規模使用者資料庫訪問許可權管理資料庫訪問許可權
- SpringSecurity許可權管理系統實戰—九、資料許可權的配置SpringGse
- 使用者角色許可權管理架構架構
- mysql 8.0.21使用者及許可權操作MySql
- 入門MySQL——使用者與許可權MySql
- MySQL入門--使用者與許可權MySql
- 普通使用者許可權執行dockerDocker
- OpenShift 使用者許可權管理例項
- 如何檢視postgresql使用者許可權SQL