Re: missing privileges on tables after full import DB

export and import are ill-thought out. they've been increasingly hacked over the years to support new features of the database.

No objects (including grants) belonging to sys are exported because sys owns the database and you will be importing into a pre-created database. In theory (around about version 5) all the required objects owned by sys will already be created. Otherwise, there would have to be some sort of version control e.g. when importing a version 8 export into a version 9 database and oracle are too busy writing crap java applications to do that.

hieu <nguyen.hieu_at_citicorp.com> wrote in message news:b060bd9f.0202071800.7b4b73f0_at_posting.google.com...
> Oh, I did exactly as Syltrem and I got the same result(export and
> import were ok, no errors) I don't know why Oracle does like this,
> they should import the rights also. Or I missed something ?
>
> Hieu
>
> "Syltrem" <syltrem_at_videotron.spammenot.ca> wrote in message
news:<Hbx88.4008$EI.21575_at_tor-nn1.netcom.ca>...
> > I did that once (EXCP FULL=Y) and the grants granted by user SYS did not
> > come back.
> >
> > Now I run a SQL before the EXPort and run the result after the IMPort
> > Here it it.
> > HTH
> >
> > set verify off echo off feedback off pagesize 0 heading off
> > spool IMPORT_SYS_GRANTS.sql
> > select 'grant '||privilege||' on '||owner||'.'||
> > table_name||' to '||grantee||' '||
> > decode(grantable,'YES','WITH Grant option')||';'
> > from dba_tab_privs
> > where owner = 'SYS';
> >
> > select 'grant '||privilege||' ('||column_name||') '||
> > ' on '||owner||'.'||table_name||' to '||grantee||' '||
> > decode(grantable,'YES','WITH Grant option')||';'
> > from dba_col_privs
> > where owner = 'SYS' and substr(table_name,1,1) <> '_';
> >
> > spool off
> >
> >
> > --
> >
> > Syltrem
> > http://pages.infinit.net/syltrem (OpenVMS related web site - en
français)
> > To reply to myself directly, remove .spammenot from my address
> >
> > "Mark D Powell" <mark.powell_at_eds.com> a écrit dans le message de news:
> > 178d2795.0202070616.593cf936_at_posting.google.com...
> > > Ganesh Raja <ganesh_at_gtfs-gulf.com> wrote in message
> > news:<rde46u8mams00tusehrkb60rjiof6tqt34_at_4ax.com>...
> > > > On 6 Feb 2002 23:15:48 -0800, nguyen.hieu_at_citicorp.com (hieu) wrote:
> > > >
> > > > >Hi,
> > > > >I have found this very common with 8i which would causes user's
> > > > >functions, procedures, packages or views invalid after import. Let
say
> > > > >user A granted SELECT on table EMP to user B, but the SELECT
privilege
> > > > >on A.EMP will not be granted to B during full DB import. So every
time
> > > > >I have to check in old DB then make a script to re-run again in new
DB
> > > > >then recompile invlid objects. Is there a way to fix this ?
> > > > >
> > > > >Thanks
> > > > >Hieu
> > > >
> > > > Did u do the exports with the grants = Y [ which is the default] and
> > > > the grants =Y in Imports...
> > > >
> > > > Maybethere lies the error...
> > > >
> > > > [Additions and Corrections Always Welcome.]
> > > > Best Regards,
> > > > Ganesh R
> > >
> > > We usually make two exports, the second of which is made with the
> > > rows=n option so that no data is exported. Then after running a
> > > regular full import we run the second import and it brings anything
> > > lost in the first export.
> > >
> > > The import utility also has a rows=n option. Try using it to bring in
> > > any lost objects/grants. I believe you will still want the ignore=y
> > > option. Please review the option in the Utility manual before using.
> > >
> > > -- Mark D Powell --
Received on Thu Feb 07 2002 - 22:45:16 CST