Re: Remove everything from a schema


"Charles Hooper" <hooperc2000@xxxxxxxxx> a écrit dans le message de news:
5531a5e5-995c-4926-b4ba-77edd63e02ed@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Nov 28, 11:38 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
Yes, it is an alternative.
The flaw of DROP USER is that it implies that you regrant each privilege
you previously had after recreating the user.

Regards
Michel

Michel,

I am fairly certain that when the data is imported using imp (or impdp
on 10g and above), that the grants which were previously assigned in
the database from which the export was created will be recreated
during the import. If I remember correctly, there is a problem with
third party grants not being retained after an import (UserA gives
permissions on UserB's objects so that UserC may access those
objects).

That said, the method that you provided is a great (impressive)
alternative to the method which I provided. Just a couple comments:
* The OP is running Oracle version 9.2.0.1.0, so he is able to skip
the step to purge the recycle bin - I assume that you intentionally
separated the purge operation from the drop operation so that the same
code could easily be adapted to work with versions prior to Oracle
10g.
* If there are foreign key constraints between objects, it is possible
that some of the drops of the parent tables will fail. Maybe it would
be possible to modify the script to disable the foreign key
constraints before the drops?

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.


--------------------------------------------------

Charles,

I was talking about the privileges the account you delete the objects
has on other schema and system privileges.
With my test user that uses this script, I can't drop grant myself any
privilege and of course can't drop and recreate my account.
These are the points for me to create this script.

Yes, I separate "purge recyclebin" to be able to execute the script
from any version. It could be enhanced to prevent from the error
raising in case of version lower than 10g but for myself I don't
care having a "SP2-0042: unknown command "recycle bin"" or
"ORA-00900: invalid SQL statement" error in my script.

The script handles all cases I thought about with the "decode"
part. For instance, for foreign keys, the table and cluster parts add
the clause "cascade constraints".
Maybe I missed some. I will be grateful if you find and mention them.

Regards
Michel


.

Relevant Pages

  • Re: Connect and RefreshLink in TableDefs
    ... The script runs without failure but my table does not refresh. ... deleting it and recreating it. ... Dim sConnect As String ... .TableDefs.Delete sTableName ...
    (microsoft.public.access.modulesdaovba)
  • Re: Help needed with .js and AJAX
    ... JBOSS or Perl script - it doesn't matter. ... has to be *identical* for all three sources (page, script, server call) ... still doesn't give it any privileges: it only gives *a privilege to ask ... If the tier 1 above is passed OK, ...
    (comp.lang.javascript)
  • Re: Help needed with .js and AJAX
    ... JBOSS or Perl script - it doesn't matter. ... has to be *identical* for all three sources (page, script, server call) ... still doesn't give it any privileges: it only gives *a privilege to ask ... If the tier 1 above is passed OK, ...
    (comp.lang.javascript)
  • Re: Help needed with .js and AJAX
    ... JBOSS or Perl script - it doesn't matter. ... has to be *identical* for all three sources (page, script, server call) ... still doesn't give it any privileges: it only gives *a privilege to ask ... If the tier 1 above is passed OK, ...
    (comp.lang.javascript)
  • Re: Help needed with .js and AJAX
    ... JBOSS or Perl script - it doesn't matter. ... has to be *identical* for all three sources (page, script, server call) ... still doesn't give it any privileges: it only gives *a privilege to ask ... If the tier 1 above is passed OK, ...
    (comp.lang.javascript)