RE: Dblink in Oracle10g

Another approach to executing a sql as another user is to use dbms_sys_sql...

Regards,

Vlado Barun, M.Sc.
Senior Database Architect
Jewelry Television
Mobile: 865 335 7652
Email: vlado.barun_at_jtv.com
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jared Still Sent: Wednesday, November 19, 2008 6:48 PM To: Greg Norris
Cc: Joel.Patterson_at_crowley.com; rjoralist_at_society.servebeer.com; oracle-l_at_freelists.org Subject: Re: Dblink in Oracle10g

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

On Wed, Nov 19, 2008 at 12:44 PM, Greg Norris <spikey.mcmarbles_at_gmail.com<mailto:spikey.mcmarbles_at_gmail.com>> wrote: Personally, I prefer to use proxy accounts in this sort of situation. SYSTEM_at_mydb> create user proxy identified by pr0xy;

User created.

SYSTEM_at_mydb> create user test identified by t3st;

User created.

SYSTEM_at_mydb> grant create session to test;

Grant succeeded.

SYSTEM_at_mydb> alter user test grant connect through proxy;

User altered.

SYSTEM_at_mydb> connect proxy[test]/pr0xy_at_mydb Connected.
TEST_at_mydb> show user
USER is "TEST"

On Wed, Nov 19, 2008 at 12:22 PM, Jared Still <jkstill_at_gmail.com<mailto:jkstill_at_gmail.com>> 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

--

"I'm too sexy for my code." - Awk Sed Fred.

--

http://www.freelists.org/webpage/oracle-l Received on Tue Nov 25 2008 - 09:00:04 CST