ITPub博客

首页 > 数据库 > Oracle > SCRIPT: Script to Generate object privilege GRANTS (Doc ID 1020176.6)

SCRIPT: Script to Generate object privilege GRANTS (Doc ID 1020176.6)

Oracle 作者:rongshiyuan 时间:2014-04-04 15:12:44 0 删除 编辑
SCRIPT: Script to Generate object privilege GRANTS (Doc ID 1020176.6)

Oracle Server - Enterprise Edition - Version 7.3.4.0 to 11.2.0.1.0 [Release 7.3.4 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 17-Sep-2012***

Purpose

 This script generates a script of all object grants to users and roles. Spool Privileges to a file.

Requirements

 

Product Name, Product Version

Oracle Server Enterprise Edition


Versions 7.0, 8.1.7 , 9.0.1, 9.2.0, 10.2.0, 11.1.0 and 11.2.0

Platform Generic
Date Created 02-Aug-2010

Configuring

 Running this script will in turn create a script of all the object grants to users and roles. This created script is called tfscsopv.lst. Since a DBA cannot grant objects other than his own, this script will contain various connect clauses before each set of grant statements. You must add the passwords for each user before executing the script. Object grants are very dependant on the user who issues the grant, therefore, it is important that the correct user issue the grant. In addition, DO NOT change the order of the grant statement. They are spooled in sequence order, so that dependant grants are executed in the correct order. For example, lets say that Scott grants Jack select on emp with grant option, and in turn Jack grants select on Scott.emp to Steve. It is essential that Scott's grant be issued before Jack's. Otherwise, Jack's grant will fail. NOTE: This script DOES NOT include grants made by 'SYS'.

Instructions

 Running this script will in turn create a script of all the object grants to users and roles. This created script is called tfscsopv.lst. Use sqlplus and connect AS SYSDBA. PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text editors, e-mail packages, and operating systems handle text formatting (spaces, tabs, and carriage returns), this script may not be in an executable state when you first receive it. Check over the script to ensure that errors of this type are corrected.

Caution

This sample code is provided for educational purposes only and not supported by Oracle Support Services. It has been tested internally, however, and works as documented. We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it.

Proofread this sample code before using it! Due to the differences in the way text editors, e-mail packages and operating systems handle text formatting (spaces, tabs and carriage returns), this sample code may not be in an executable state when you first receive it. Check over the sample code to ensure that errors of this type are corrected.

Script

 

SET ECHO off 
REM NAME:   TFSCSOPV.SQL 
REM USAGE:"@path/tfscsopv" 
REM ------------------------------------------------------------------------ 
REM REQUIREMENTS: 
REM    DBA privs 
REM ------------------------------------------------------------------------ 
REM AUTHOR:  
REM    Anonymous      
REM    Copyright 1995, Oracle Corporation      
REM ------------------------------------------------------------------------ 
REM PURPOSE: 
REM    Running this script will in turn create a script of  
REM    all the object grants to users and roles.  This created  
REM    script is called tfscsopv.lst. 
REM 
REM    Since a DBA cannot grant objects other than his own,  
REM    this script will contain various connect clauses before  
REM    each set of grant statements.  You must add the passwords  
REM    for each user before executing the script.  Object grants  
REM    are very dependant on the user who issues the grant,  
REM    therefore, it is important that the correct user issue the  
REM    grant. 
REM 
REM    In addition, DO NOT change the order of the grant statement. 
REM    They are spooled in sequence order, so that dependant grants 
REM    are executed in the correct order.  For example, lets say  
REM    that Scott grants Jack select on emp with grant option, and  
REM    in turn Jack grants select on Scott.emp to Steve.  It is  
REM    essential that Scott's grant be issued before Jack's.    
REM    Otherwise, Jack's grant will fail. 
REM 
REM    NOTE:  This script DOES NOT include grants made by 'SYS'. 
REM ------------------------------------------------------------------------ 
REM DISCLAIMER: 
REM    This script is provided for educational purposes only. It is NOT  
REM    supported by Oracle World Wide Technical Support. 
REM    The script has been tested and appears to work as intended. 
REM    You should always run new scripts on a test instance initially. 
REM ------------------------------------------------------------------------ 
REM Main text of script follows: 
 
 
set verify off 
set feedback off 
set termout off 
set pagesize 500 
set heading off 
set recsep off 
  
set termout on 
select 'Creating object grant script by user...' from dual; 
set termout off 
  
create table g_temp (seq NUMBER, grantor_owner varchar2(20), 
                    text VARCHAR2(800)); 
  
DECLARE 
   cursor grant_cursor is  
    SELECT ur$.name, uo$.name, o$.name, ue$.name, 
              m$.name, t$.sequence#,  
              decode(NVL(t$.option$,0), 1, ' WITH GRANT OPTION;',';') 
     FROM sys.objauth$ t$, sys.obj$ o$, sys.user$ ur$, 
            sys.table_privilege_map m$, sys.user$ ue$, sys.user$ uo$ 
       WHERE o$.obj# = t$.obj# AND t$.privilege# = m$.privilege AND 
             t$.col# IS NULL AND t$.grantor# = ur$.user# AND 
             t$.grantee# = ue$.user# and  
             o$.owner#=uo$.user# and  
             t$.grantor# != 0 
       order by sequence#; 
   lv_grantor    sys.user$.name%TYPE; 
   lv_owner      sys.user$.name%TYPE; 
   lv_table_name sys.obj$.name%TYPE; 
   lv_grantee    sys.user$.name%TYPE; 
   lv_privilege  sys.table_privilege_map.name%TYPE; 
   lv_sequence   sys.objauth$.sequence#%TYPE; 
   lv_option     VARCHAR2(30); 
   lv_string     VARCHAR2(800); 
   lv_first      BOOLEAN; 
  
   procedure write_out(p_seq INTEGER, p_owner VARCHAR2, p_string VARCHAR2) is 
   begin 
      insert into g_temp (seq, grantor_owner,text) 
 values (lv_sequence, lv_grantor, lv_string); 
   end; 
  
BEGIN 
  OPEN grant_cursor; 
    LOOP 
      FETCH grant_cursor INTO lv_grantor,lv_owner,lv_table_name,lv_grantee, 
         lv_privilege,lv_sequence,lv_option; 
      EXIT WHEN grant_cursor%NOTFOUND; 
      lv_string := 'GRANT ' || lv_privilege || ' ON ' || lower(lv_owner) || 
                   '.' || 
                   lower(lv_table_name) || ' TO ' || lower(lv_grantee) || 
                   lv_option; 
      write_out(lv_sequence, lv_grantor,lv_string); 
    END LOOP; 
  CLOSE grant_cursor; 
END; 
/ 
  
spool tfscsopv.lst 
break on guser skip 1 
col text format a60 word_wrap 
  
select   'connect ' || grantor_owner || '/' guser, text 
from     g_temp 
order by seq, grantor_owner 
/ 
  
spool off 
  
drop table g_temp; 

 

 

Sample Output

GRANT dba TO bednar;

GRANT dba TO cblakey;

GRANT connect TO christy;

...

...

GRANT tr2 TO matt WITH ADMIN OPTION;

GRANT al_role TO scott WITH ADMIN OPTION;

GRANT connect TO scott;

GRANT dba TO scott;

GRANT resource TO scott;

GRANT role1 TO scott;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17252115/viewspace-1136129/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-11-24

  • 博文量
    798
  • 访问量
    3250411