From oracle-l-bounce@freelists.org Mon May 9 15:41:48 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j49KfmMQ014333 for ; Mon, 9 May 2005 15:41:48 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j49Kfm4Z014329 for ; Mon, 9 May 2005 15:41:48 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1C54D191ED6; Mon, 9 May 2005 14:39:08 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 15043-10; Mon, 9 May 2005 14:39:08 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 974D6191E79; Mon, 9 May 2005 14:39:07 -0500 (EST) x-mimeole: Produced By Microsoft Exchange V6.0.6603.0 content-class: urn:content-classes:message MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Subject: RE: [Q] way to re-gernerate ROLE privilege ? Date: Mon, 9 May 2005 15:37:20 -0400 Message-ID: <51327ABA927BEF4B96590554CEA7832C25D48B@enhbgpri05.pa.lcl> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [Q] way to re-gernerate ROLE privilege ? Thread-Index: AcVUy7NjPByWMOY8TxiELCk6OJg26wAApSOw From: "Freeman, Donald" To: , X-OriginalArrivalTime: 09 May 2005 19:37:20.0603 (UTC) FILETIME=[84171AB0:01C554CE] X-archive-position: 19476 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: dofreeman@state.pa.us Precedence: normal Reply-To: dofreeman@state.pa.us X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=0.1 required=5.0 tests=AWL,UPPERCASE_25_50 autolearn=no version=2.63 This will get you started. SELECT 'CREATE ROLE '||role||' NOT IDENTIFIED;' FROM DBA_ROLES WHERE ROLE NOT IN ('CONNECT', 'RESOURCE', 'DBA', 'SELECT_CATALOG_ROLE', 'EXECUTE_CATALOG_ROLE', 'DELETE_CATALOG_ROLE', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE', 'RECOVERY_CATALOG_OWNER', 'GATHER_SYSTEM_STATISTICS', 'LOGSTDBY_ADMINISTRATOR', 'AQ_ADMINISTRATOR_ROLE', 'AQ_USER_ROLE', 'OEM_MONITOR', 'HS_ADMIN_ROLE', 'WM_ADMIN_ROLE'); SELECT 'GRANT '||privilege||' ON '||owner||'.'||table_name||' TO = '||grantee||';' FROM dba_tab_privs where owner not in ('SYS','SYSTEM','WMSYS') ORDER BY privilege / SELECT 'GRANT '||privilege||' TO '||grantee||';' FROM dba_sys_privs where grantee not in ('SYS', 'SYSTEM', 'CSMIG', 'WMSYS', 'EXP_FULL_DATABASE', 'DBA', 'IMP_FULL_DATABASE', 'EXECUTE_CATALOG_ROLE', 'SELECT_CATALOG_ROLE', 'OUTLN', 'OEM_MONITOR', 'LOGSTDBY_ADMINISTRATOR', 'CONNECT', 'RECOVERY_CATALOG_OWNER', 'AQ_ADMINISTRATOR_ROLE', 'RESOURCE') ORDER BY privilege / Don Freeman Database Administrator 1 Pennsylvania Dept of Health Bureau of Information Technology 717-783-8095 Ext 337 -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of dba1 mcc Sent: Monday, May 09, 2005 3:16 PM To: oracle-l@freelists.org Subject: [Q] way to re-gernerate ROLE privilege ? We have a ORACLE role with 50 different object privileges grant to it. Does there has way to generate a script which I can bring this script to another database and re-create it? Thanks. =09 __________________________________=20 Do you Yahoo!?=20 Yahoo! Mail - Helps protect you from nasty viruses.=20 http://promotions.yahoo.com/new_mail -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l