Re: Getting Role and Permissions from DB

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 31 Dec 1998 14:57:38 GMT
Message-ID: <36989009.93187596@192.86.155.100>

A copy of this was sent to "Manoj Goel" <mgoel_at_jps.net> (if that email address didn't require changing) On Thu, 31 Dec 1998 06:43:16 -0800, you wrote:

>Hi Guys,
>
> We have to many times create/drop/modify the tables in our db,as still db
>changes
> are going thru. But whenever this happens the permissions are lost on
>certain roles.
>
> Also soon we may have to move the db to a new server for testing for
>QA's.But this
> time I wanted that we don't have to go thru the same pain and loose two
>days to again figure out
> what objects lost the permissions in certain roles.
>

sounds like you might need a change management tool, you might consider looking into some of those.

>Is there any commands that we can use to restore the same privilges whenever
>we move to
> another db or do chnages in same db.??
>

well, the following will help somewhat:

use "create or replace" as opposed to drop/create whenever possible
use "views" instead of tables as the thing you grant on. Don't grant on tables, don't access table. Views can be 'created or replaced', tables cannot. You might also find you have to drop columns/rebuild tables much less frequently (you can save them up) as you can simply default the column value in the real table, remove the column from the view and the apps will be none the wiser. Same with adding columns and so on (these can be altered into the table and added to the right views).
use exp .. rows=no to get your schema and grants moved from one database to another.

>Please let me know
>
>Thanks for any help
>
>Manoj
>
>
>

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Dec 31 1998 - 08:57:38 CST