Re: Dblink in Oracle10g
- From: Kurt Franke <Kurt-Franke@xxxxxx>
- To: jkstill@xxxxxxxxx, Greg Norris <spikey.mcmarbles@xxxxxxxxx>
- Date: Thu, 20 Nov 2008 22:31:59 +0100
a simple methode to create a db link in another schema is -
assuming you have the necessary privileges - to build a single use
definer rights procedure (default in pl/sql procedures) in the another schema
which does an execute immediate on the create statement.
of course this an example of code which has to be different for
different oracle versions: using the "by value" clause from version 10 upward
but not otherwise.
after execution drop it.
one may do automation on this mechanism and code a central procedure
create_any_dblink() which does creation, execution and drop of the
single use procedure in the respective schema
kf
>
> Great idea, I always forget about proxy users.
>
> BTW, I have this same exact script in my scratchpad directory,
> dated Aug 20th.
>
> You must have posted it then as well.
>
> Works in 10g but not 9i. I think that proxy was available in 9i, but
> this connection syntax is new in 10g.
>
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
>
> ... deleted ...
>
> On Wed, Nov 19, 2008 at 12:22 PM, Jared Still <jkstill@xxxxxxxxx>
> wrote:
> Here is something to check for when temporarily changing passwords.
>
> The account may be assigned to a profile that prevents the reuse of a
> password,
> and may also prevent the use of your temporary password if it doesn'
> t
> meet the criteria of the verify password function.
>
> Best to check beforehand and plan for it.
>
> There is a way to work around the profile problem.
>
> Create a new profile with no options, for temporary use only.
>
> I will call it empty_profile here.
>
> alter user dblink_owner identified by new_password profile empty_
> profile;
>
> Do the rest of the work as this user.
>
> Change the password back;
>
> alter user dblink_owner identified by old_password;
> alter user dblink_owner profile <original_profile>;
>
> You best document it if you are routinely audited, as these changes
> will appear in sys.user_history$, which a sharp auditor may know
> about.
>
> Jared
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
