Connection System

[jessica.ff]

DBAs I need help, so I will try to explain my problem in the best possible way:

I have a web system (PL / web and workflow), where each user user receives a user in the bank, When connecting in the application the connection is done in the bank, and if the person errs more than 3 times, the error screen appears:

Select all

Database Log In Failed  
An invalid username/password caused log in to database fail.  
 
Verify that the username/password you provided  is correct.  
 
 
ORA-28000: the account is locked Error-Code:28000 Error TimeStamp:Fri, 18 Sep 2009 13:43:50 GMT  

But I I need to change to instead of appearing this screen, load a screen developed by the team so that the user can restart the password.

Only I can not find where it calls this screen, from what I understood this is all inside Oracle.

Now the doubt:

Can you do this? How to do this?

Valeu Galera

[pauloaleo]

I did not get it right. User accounts after 3 Locam password errors. The user lock, is defined in the DBA_Users. With the ALTER USER ZZZZZ ACCOUNT UNLOCK command; Where Zzzzzz is the name of the user. This lock must be defined by Rule.

You can change the rule that does this with the command:

Select all

ALTER PROFILE yyyyyy LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;

Where Yyyyyy is the name of the rule.

For you to see which rule you type:

Select all

select PROFILE from dba_users where username = 'zzzzzzz';

Does that explain what you are wanting? As for the screen, this I did not understand.

[jessica.ff]

Hi Paulo, I do not need to modify the amount of attempts. This has to keep.

What I have is to develop a screen where the user can unlock the password and manage a new one. This is already done the [/ u] screen

What I can not get is that instead of appearing that oracle error, call my screen I created.

[pauloaleo]

Understand. Make a logon trigger, which when Lock Account error, call your screen.

If I understand correctly a logon trigger solves this

[pauloaleo]

It follows an example that records audit in case of failure logon by error 1017 - invalid username or password.

[] S

Select all

CREATE OR REPLACE TRIGGER "SYS"."LOGON_FAILURES" AFTER 
ERROR ON DATABASE DECLARE 
  osuserid VARCHAR2(30); 
  hmachine VARCHAR2(54); 
  v_module VARCHAR2(48); 
BEGIN 
 
IF (IS_SERVERERROR(1017)) THEN 
      hmachine := UPPER(sys_context('USERENV','host')) || '(' || UPPER(sys_context('USERENV','ip_address')) || ')'; 
      osuserid := UPPER(sys_context('USERENV','os_user')); 
      SELECT LOWER(module) INTO v_module 
            FROM v$session WHERE SID = (SELECT SID FROM v$mystat WHERE ROWNUM=1); 
      INSERT INTO audit_logon ( attempt_dt, os_user, program, machine, logon_failure ) 
      VALUES (SYSDATE, osuserid, v_module, hmachine, 'Y'); 
END IF; 
 
/* 
CREATE TABLE sys.AUDIT_LOGON 
( 
  ATTEMPT_DT  DATE, 
  OS_USER     VARCHAR2(50 BYTE), 
  PROGRAM     VARCHAR2(100 BYTE), 
  MACHINE     VARCHAR2(100 BYTE), 
  LOGON_FAILURE CHAR(1 BYTE) DEFAULT 'N' 
) 
*/ 
 
END;

[jessica.ff]

I'm already going to test: D

if it works

valeuuuuu: -O

[jessica.ff]

It worked!!! : D

The only thing I had to correct is that error did not work, I changed to serverror

now I need help again, within this trigger I need Connect me with a generic user so you can call my screen (package), but I can not connect. I tried to use

Select all

 
vconn := 'conn USER/password@banco.world'; 
execute immediate vconn; 

It is logical that did not work !!! hehehehe

Someone helps me ????

[pauloaleo]

Instead of doing this, no easier to create a rule with execute grant from your package and register the users in this rule?

[jessica.ff]

Hi Paulo, I could not understand very well, I could explain myself better

= D

[pauloaleo]

Better yet, why do not you do the Grant de Remuna for all the users of your bank execute this procedure that calls the screen?

[jessica.ff]

The problem is: [/ u] How will I call (load) to package if I'm not connected to the bank?

[pauloaleo]

Connect is not a PL SQL command, SQLPLUS. I will search, but I think the ideal is you call the logon trigger a program that connects to the bank, changes the password or reset and exits again, calling the main application.

[jessica.ff]

Hi everyone,

the way I wanted to do by the way will not give even ...
So what I thought about the logon trigger I call a script or bat that Make the connection and call the screen I need (a package) and press it in IE.

Well I had this idea but I do not know:
- Call a script in PL;
- and call the screen in the script;

Someone help me ??? [/ u]

[pauloaleo]

Jessica, there is no longer my beach. Try the Dr_Gori, which he manets more than pl.

[dr_gori]

For it is ...

I have already seen something similar as follows:
There is a user without any privilege, except for execute in a password exchange routine.

Then the default password of this user was not mystery, could be appearing in the link, etc ... because the only thing she did was change the password, (of course, testing if the user Changing the password was himself, etc., type old password)

This was a way we used in forms. (It was not in PLWEB)

In your case, I think the only way to perform other user's things is through db_link - or calling a shell / batch script. The problem is: when shooting the error the connection is already dead? Still how to run something via db-link? I would do a test there to see if you still have something to run.


only testing

[jessica.ff]

And how would I call a shell / batch script ????
: Roll:

[dr_gori]

Well, when I worked there, we used the Package Daemon.
See this link: http://en.glufke.net/oracle/viewtopic.php?t=2226
must have at its base. If you do not have, maybe you have to ask for a DBA to install Daemon.