RE : Preserving permissions across DROP/CREATE

Rick,

   The plain answer to your question is no. Only synonyms for the object and stored procedures or views which reference the object (and get invalidated in the process) stay.
Depending on what you need to do you have several options :

   o Generating a script to re-grant permissions by querying the DBA views prior to dropping anything.

     Beware that privileges must be granted by the object owner in person, not SYSTEM or even SYS. This can

     be a problem, since a DBA can recreate objects in another schema but not reset the grants. A good

     workaround is using the become.sql script available from our site to log as the schema owner.

   o Using exp/imp with the ROWS=N parameter to regenerate DDL for the objects which will be impacted, and

     to run imp with IGNORE=Y once objects have been recreated. Should give you an impressive number of errors

     but will work.
   o Depending on what you want to do, truncating tables may be a better solution. If you want to resize

     tables, say, you can truncate, then deallocate unused storage to keep a smallish initial extent, then

     allocate a NEXT suitable for your needs. More difficult to explain than to do, but kind of 'keeping the

     foot in the door' tactics.

Note that you are likely to be bothered not only by privileges but by referential constraints too. When you truncate a table, it's enough to disable FKs which reference it. When you drop the table, you must drop the FKs too. Having to modify objects which are not directly impacted by what you want to do is always a pain. And don't forget to recompile all invalidated stored objects when you're done.      

-- 
HTH,

  Stephane Faroult
  email: sfaroult_at_oriolecorp.com 
  Oriole Corporation
  Voice:  +44  (0) 7050-696-269 
  Fax:    +44  (0) 7050-696-449 
  Performance Tools & Free Scripts
------------------------------------------------------------------
http://www.oriolecorp.com, designed by Oracle DBAs for Oracle DBAs
------------------------------------------------------------------


> 

> Question - is there any easy way to get Oracle to preserve permissions    

> across a DROP and CREATE?  I.e., if I have a table or other object, with a

> given set of permissions granted to others for it.  But if I need to DROP

> and then re-CREATE the table, and way to get those permissions to stick   

> around without having to re-grant them?

> 

> -Rick

> 

> +--------------------------------------------------------------------------+

> |                 Rick Osterberg   osterber_at_fas.harvard.edu                |

> |         Database Applications Specialist     FAS Computer Services       |

> +--------------------------------------------------------------------------+

> 

> -- 

> Author: Rick Osterberg