Re: missing privileges on tables after full import DB

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 - 20:00:14 CST