Connect Using Just Username 1

[tekdudedude]

Hello,

I have a .sql job that I wish to run for multiple schema. My problems is that I know the schema name but I do not know the password for each (and they may change).

If I try this I get a password prompt:

connect scott@DB1
exec ctx_ddl.sync_index('Description_idx','40M')

In UNIX as root you can simpy perform an su username and you are connected as that user without a password prompt.

What can you recommend in SQLPlus running a simple .sql script?

Thanks,

TD

 

[Thargy]

tekdude,

you've got a nasty problem - i.e. too many privileges!

Comparing root with other users is a bit unfair. Since root owns everything, it's fine to let root do whatever is wanted, but not so with ordinary users.

The nearest equivalent that I can think of is that in Oracle, there is a server group called ORA_DBA (in windoze, can't remember the unix equivalent name).

You can log in without a valid sys password, as sysdba, if you're a member of the ORA_dba group, in similar style to a unix root. However, this should be used with caution, as, just like root, you can do a lot of damage if you get things wrong.

If you really have to, you can log in as sys, copy a user's password to a variable, set the password to a known value, log in using that username/password combination, do the bizz, log out, and then put the password back again.

I don't have a sample script with me right now, but if you can wait until tomorrow, I can probably dig one out.

Are you interested?

Regards

T

Grinding away at things Oracular

 

[SantaMufasa]

TekDude²,

If your objective is to synchronise your CONTEXT indexes, you can accomplish this task, and cause it to occur on a regular basis, by using the Oracle-supplied script, "drjobdml.sql"

That script resides at $ORACLE_HOME/ctx/sample/script/drjobdml.sql. You can invoke the regular synchronisation of a CTX index with this syntax:

Format:
SQL> @$ORACLE_HOME/ctx/sample/script/drjobdml.sql <index name> <interval in minutes>

Example:
SQL> @$ORACLE_HOME/ctx/sample/script/drjobdml.sql my_index 240

For automatic synch to occur on the index,[ul][li]You must log in as the owner of the index,[/li][li]That owner must have "EXECUTE" privileges on the CTX_DDL package.[/li][li]The Oracle instance must have its "job_queue_ processes" initialisation parameter set to a non-zero value.[/li][/ul]

If you have DBA privileges (or at least, "ALTER USER" privilege), you can log into any other user using the following code:

(Logged in as a DBA):
SQL> select username, password from dba_users;

(Important: Copy and paste into an empty Notepad file the username and its encrypted password of the user to whose account you wish to connect.)

SQL> alter user <username> identified by <some new password>;

User altered.

SQL> connect <username>/<new password>@<tns alias>

Connected.

SQL> @$ORACLE_HOME/ctx/sample/script/drjobdml.sql <index name> <interval in minutes>

SQL> conn <DBA user>/<password>@<tns alias>

SQL> alter user <other user>
     identified by values '<old encrypted password saved in Notepad file>';

You must use the reserved word, "VALUES" and you must enclose in single quotes the encrypted password. Once you set the user's password back to the original encrypted value, you have restored the original password.

Let us know how things go for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[tekdudedude]

Guys:

thargtheslayer,
I would like to take you up on your kind offer concerning the script. Please post. Thanks!


SantaMufasa,
Thanks for taking the time to post. That is very useful.

TD

 

[Thargy]

tekdude,

my script writes the original password to a variable, and then sets it back at the end.

However, if my script failed half way through, it might be possible to end up with the original password being lost, and incapable of reinstatement. I therefore prefer Santa's notion of writing it to a file first, so that if things go wrong, you've still got it.

I believe that I can create something along those lines. Still interested?

T

Grinding away at things Oracular

 

[tekdudedude]

Guys,

I think my approach may be flawed.
[ol]
[li] Extract PW[/li]
[li] Change PW[/li]
[li] Run process[/li]
[li] Reset PW[/li]
[/ol]

Though this technique is brilliant, for the briefest of seconds the user could not access their account (in my environment this is very possible and very bad).

You know the import tool performs an "SU" it seems for each schema. Why can't I?

Are there any other approaches?

Thanks again for you advice,

TD

 

[Welshbird]

At the risk of possibly stating the obvious - can you not grant another user (i.e. you) the rights to do whatever it is that you need to do? Then you could do this from another user (which also has the benefit of making sure that a proper audit trail is possible).

Just a thought.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]