Re: Connect Through PLSQL with different user question

In article <8vf6vb$jdp$1_at_nnrp1.deja.com>,   kachkar_at_my-deja.com wrote:
> In article <8vf4f9$h9g$1_at_nnrp1.deja.com>,
> mkrolewski_at_rosetta.org wrote:
> > In article <8vf32m$g95$1_at_nnrp1.deja.com>,
> > kachkar_at_my-deja.com wrote:
> > > Hi all,
> > >
> > > Is it possible in PLSQL through stored procedures to connect to
 the
> > > database with different user. I’ve tried this EXECUTE
> > > IMMEDIATE 'Connect john/test' ;
> > > But it did not work
> > > All I want to do is when user X ( he owns the procedure ) calles
 the
> > > stored procedure , that stored procedure try to connect user Y to
 the
> > > database that is all .
> > >
> > > So is it possible to achieve this task through PLSQL and if Yes
 please
> > > show me how.
> > >
> > > Thanks in advance
> > >
> > > Khaled
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> > >
> >
> > Your question appears to be mechanism to circumvent the entire
 security
> > scheme. Why does a specific user need to be logged in as another?
> > Generally, roles and grants can accomplish any authorized transfer
 of
> > privilege.
> >
> > Michael Krolewski
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> >
>
> Michael ,
> The client application can not trap error ora-28002 : the password
 will
> expire within 5 days
> So I need to use stored procedure to trap that error then pass it to
 my
> application so I can inform the user about that.
> That is why , again , can I do that or not ?
> Thank you
> Khaled
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

To make this work, you would need the passwords for each user to login. The database stores the information encrypted. Even SYS cannot read it. SYS can overwrite it.

Also assuming that you could issue a new connect, the consequence of being successful is that the script is no longer active. You cannot get the next element in the cursor.

It might be easier to track when the password expires and report that.

It may be possible for SYS to see the expiration dates in the password table. I have not tried to check that.

Personally, I would rewrite the application to detect this and other exceptions and respond to it.

Generally speaking -- at least in C++ -- this is extremely easy. Add in checking the sqlca.sqlcode after each EXEC SQL. Generally speaking only 0 (success) and 100 or -1403 (no records are found) are the only errors that one typically accepts. All others should generate an exception.

I created a class that holds the complete sql statement (if available), the Oracle error and a short comment that I can assign.

Add a try-catch block around the SQL statement or procedure. In the catch block, I display the error generated. And you can detect any errors.

As to the particular error -- why does the user have a limited password expiration data. It is easier to leave the password expiration date open. Generally limiting user access is done by removing the user.

Michael Krolewski

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Nov 22 2000 - 12:38:24 CST