Re: missing privileges on tables after full import DB

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 --