CODEWITHSUNDEEP
oracle-databaseplsqloracleforms
gontarczuk
gontarczuk

Reputation: 21

Block users from logging in using their database accounts

I have the following business problem to solve in a big legacy application written in PL/SQL and Oracle Forms 6i:

Not allow users to log in using their database accounts.

The whole application has around 50 users who use their database accounts. The audit department doesn’t allow that users know their database accounts passwords.

Rewriting the application to use a new user rights logic (using a table like USERS, only 1 database user, etc.) is out of the question as it’s too much work.

I thought about the following solution to use the existing database users:

Create a table USERS with usernames and new passwords, and somehow use a proxy user. In a package the application will check if the provided password is in line with the table USERS, and then connect as 1 of the 50 database users – so all application logic and user rights can stay the same.

But there is one problem with this workaround – you can’t use „connect” in a package. So I can’t use it.

begin
    IF
        p_in_user == USER2 AND p_in_pass == XXX
    THEN
        EXECUTE IMMEDIATE 'conn USER2/Password123'; -- doesn't work, using a proxy neither
    END IF;
END;

Does anyone has any ideas?

Upvotes: 1

Views: 397

Answers (1)

alvalongo
alvalongo

Reputation: 571

The audit department doesn’t allow that users know their database accounts passwords

You can use encripted passwords.

Oracle allow to use encripted passwords through profile settings.

You can use a function to encript a clear password:
the clear password is given to end user
and on oracle database the user account is created/changed with the encripted password.

The user use his/her clear password to login and during login Oracle conver this to a encripted password for authentication.

Please confirm is this solution is ok, so I can post an example.

Upvotes: 1

Related Questions