RE: Import Public Grants (9.2)

From: Mercadante, Thomas F (LABOR) <Thomas.Mercadante_at_labor.state.ny.us>
Date: Tue, 14 Jun 2005 09:32:18 -0400
Message-ID: <ABB9D76E187C5146AB5683F5A07336FF16E6DA@EXCNYSM0A1AJ.nysemail.nyenet>

I always create either a script or a procedure to both issue grants and create public synonyms. So an export/import followed by an execution of this procedure simply creates the syns and issues the grants again.

I consider this a standard part of any database I manage.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Powell, Mark D Sent: Tuesday, June 14, 2005 9:22 AM
To: rlsmith_at_kmg.com; oracle-l_at_freelists.org Subject: RE: Import Public Grants (9.2)

Public grants below to PUBLIC and not to the base object owner. You cannot import user PUBLIC so the only way to import public synonyms is as part of a full imp, full=3D3Dy. We usually run a imp with full=3D3Dy = and
rows=3D3Dn to get public objects after rebuilding a database, but you = =3D
cannot
do this if you only want portions of the source db.

So the solution is just to generate the create synonyms by selecting from dba_synonyms. Just add the necessary where clause to the following and a few SQLPlus set commands for feedback, linesize, etc....

select
'create '||decode(owner,'PUBLIC','PUBLIC',NULL)||' synonym '||

             decode(owner,'PUBLIC',NULL,table_owner||'.')||
           synonym_name||' for '||
           table_owner||'.'||table_name||
           decode(db_link,NULL,NULL,'@'||db_link)||';'

from
sys.dba_synonyms
Where ....

HTH -- Mark D Powell --

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Smith, Ron L. Sent: Monday, June 13, 2005 4:38 PM
To: oracle-l_at_freelists.org
Subject: Import Public Grants (9.2)

I am trying to import grants made to Public for all the objects in a schema. I have tried running a full import as SYSTEM and as the Schema owner. Neither imports the Public grants. What am I doing wrong?

Thanks!
Ron=3D3D20
--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l Received on Tue Jun 14 2005 - 09:37:23 CDT