Looking for some suggestions on how I can workaround the following problem. My application takes a username / password, which are used to connect to Oracle. Trouble is, having given a user the username and password, they could then in theory use any application to access the database and work in a way that is not intended.
Is there a way to limit which application will work with a specific user login in Oracle?
maybe if you don't use oracle users/passwords for logging in users, but instead have a table in the database with users/passwords and have different applicatiosn use different such tables (probably in different schemas).
You created a user, represented by his username and password.
Granted him "connect" and "resource".
He comes to a PC (terminal, whatever) and types provided un/pw.
He doesn't like you very much and wants to delete data from one of your applications. Knows something about SQL. So he writes: delete from ... from what? select * from tab gives him nothing.
So, how could he, in theory, use any application?
You'll have to grant this user something more than just "connect and resource" to be able to work with application. Usually, it is a role - owner grants privileges to the role, and then the role is granted to another user.
You said that he could use any application. I'd say he could connect, but he should NOT be able to work - see above about roles and privileges. Unless all of your applications are created in one schema and dozens, or even hundreds of tables, views, etc. that have nothing to do with each other are at the same place. Or you created public synonyms and granted all to public ... but that's another story.
1) Create a user with NO priviledges to access or modify your tables. Lets call this user NOPRIV. The only table that NOPRIV can see is the USER table in my applications schema. And NOPRIV only has select privs on that table. The USER table contains the user name and an encrypted password field.
2) The applications logs on using the NOPRIV user and then asks the user to login. If they enter a valid password and username (verifyed against the USER table), The application relogs in as the real user HASPRIV. The username and password for HASPRIV is stored either in the application or encrypted in the USER table.
Last edited by beilstwh; 08-11-04 at 08:59.
You do not need a parachute to skydive. You only need a parachute to skydive twice.
Exactly... the user's concept of "logging in" should be different from the application's.
The only modification I'd make to beilstwh's suggestion is that the method that the app would use to verify the password would be to execute a stored-procedure that only he can execute. The stored-proc has privileges to use the user/pass table even though the (NOPRIV...) user does not. The stored-proc answers "yea" or "nay." If the answer is "yea, ye may enter..." the stored-proc also gives the application the database username/password combo that it should use... perhaps scrambled in some way. The application understands how to convert that data into a login, and of course, immediately scrubs the memory-areas used to store the decrypted password. But here, there's absolutely nothing "secret" that an interloper could obtain, say by reading the executable-file in a binary editor.
You can now exercise very-specific control over what users can do, and they never learn the passwords that are used by their application to gain access. They have no notion whatsoever of what is being done.
Yes . You can limit the connections to Oracle using that particular application ony. Make use of product user profile. Use pupbld.sql in oracle/home/rdbms directory to do this.
Read more about product user profile in oracle documentation.
the following link will show some light.....