Script to Capture Role Grants (Doc ID 18079.1)

Abstract
Script For Capturing Role Grants
Product Name, Product Version	Oracle Server Enterprise Edition Versions 8.1.7 , 9.0.1 , 9.2.0, 10.1, 10.2, 11.1, 11.2
Platform	Generic
Date Created	29-Oct-2002
Checked for relevance	04-Jun-2013
Instructions
Checked for relevance on 02-Apr-2007 Use sqlplus, 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.
Description
The following is a script that once run will generate another script that will include all the grant statements for all grants made to roles within the database. The remarks should be reviewed carefully before running this script.
References
Note:13615.1 Roles and Privileges Administration and Restrictions Note:180028.1 Set up a Secure Access to Application Data within a Database: DBAs, Schemas and Users
Script
REM REM SCRIPT FOR CAPTURING ROLE GRANTS REM REM This script must be run by a user with the DBA role. REM REM This script is intended to run with Oracle versions 7.3.X, 8.0.X, and 8.1.X. REM REM Running this script will in turn create a script of all the grants REM of roles to users and other roles. This created script, grant_roles.sql, REM must be run by a user with the DBA role. REM REM Since role grants are not dependant on the schema that issued the grant, REM the grant_roles.sql script will not issue the grant of a role by the REM original grantor. All grants will be issued by the user specified when REM running this script. REM REM NOTE: Grants made to 'SYS' are not captured. REM REM Only preliminary testing of this script was performed. Be sure to test REM it completely before relying on it. REM set verify off set feedback off set termout off set echo off; set pagesize 0 set termout on select 'Creating role grant script...' from dual; set termout off spool grant_roles.sql select 'GRANT ' || lower(granted_role) || ' TO ' || lower(grantee) || decode(admin_option,'YES',' WITH ADMIN OPTION;',';') from sys.dba_role_privs where grantee != 'SYS' order by grantee / spool off exit
Sample Output
GRANT dba TO clubmom; GRANT resource TO clubmom; GRANT connect TO ctxsys; GRANT resource TO ctxsys; GRANT dba TO darcy; GRANT delete_catalog_role TO dba WITH ADMIN OPTION; GRANT execute_catalog_role TO dba WITH ADMIN OPTION; GRANT exp_full_database TO dba; GRANT imp_full_database TO dba; GRANT java_admin TO dba; GRANT plustrace TO dba WITH ADMIN OPTION; GRANT select_catalog_role TO dba WITH ADMIN OPTION; GRANT connect TO dbsnmp; GRANT resource TO dbsnmp; GRANT snmpagent TO dbsnmp; GRANT dba TO user1; GRANT connect TO developer; ...
Disclaimer
EXCEPT WHERE EXPRESSLY PROVIDED OTHERWISE, THE INFORMATION, SOFTWARE, PROVIDED ON AN "AS IS" AND "AS AVAILABLE" BASIS. ORACLE EXPRESSLY DISCLAIMS ALL WARRANTIES OF ANY KIND, WHETHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NON-INFRINGEMENT. ORACLE MAKES NO WARRANTY THAT: (A) THE RESULTS THAT MAY BE OBTAINED FROM THE USE OF THE SOFTWARE WILL BE ACCURATE OR RELIABLE; OR (B) THE INFORMATION, OR OTHER MATERIAL OBTAINED WILL MEET YOUR EXPECTATIONS. ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE DOWNLOADED OR OTHERWISE OBTAINED IS DONE AT YOUR OWN DISCRETION AND RISK. ORACLE SHALL HAVE NO RESPONSIBILITY FOR ANY DAMAGE TO YOUR COMPUTER SYSTEM OR LOSS OF DATA THAT RESULTS FROM THE DOWNLOAD OF ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE. ORACLE RESERVES THE RIGHT TO MAKE CHANGES OR UPDATES TO THE SOFTWARE AT ANY TIME WITHOUT NOTICE.
Limitation of Liability
IN NO EVENT SHALL ORACLE BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL OR CONSEQUENTIAL DAMAGES, OR DAMAGES FOR LOSS OF PROFITS, REVENUE, DATA OR USE, INCURRED BY YOU OR ANY THIRD PARTY, WHETHER IN AN ACTION IN CONTRACT OR TORT, ARISING FROM YOUR ACCESS TO, OR USE OF, THE SOFTWARE. SOME JURISDICTIONS DO NOT ALLOW THE LIMITATION OR EXCLUSION OF LIABILITY. ACCORDINGLY, SOME OF THE ABOVE LIMITATIONS MAY NOT APPLY TO YOU.