we have 3 tier system arch.Front end is java and backend is
oracle.How to create a user and password ,so that they
have an access to database through application only and
can't login directly(eg sqlplus).
Then you have to have one database username, which is common to all middle tier users and manuver different users from the middle tier.
As far as I know, when you create a user in oracle, you atleast require to give him the connect previleges. Which would allow him to use sql. One other way is to grant him the specific proivileges on the object. As a result, you can be sure that he cannot do anything more.
But on either case, he would be able to connect to the database through the sql client, if her were to know the host information and the tnsnames.ora file.
We are using the same situation.
Probably you need to have a table that will have all your users will be authenticated through the applicatiuon. You need to have a procedure that will check the validity of the user and we have only one connection to Oracle, but 12 Users from Front end.
A fairly simple, yet highly efficient way of preventing users to perform anything on the database if they are not connected through your application is as follows.
1. Create a role (or more roles) with all privileges neaded in your aplication. Protect this role with a password, not known to users.
CREATE ROLE app_role IDENTIFIED BY top_secret;
GRANT (..all_required_privs..) TO app_role;
2. Create a user. Do not grant him anything other than CREATE SESSION system privilege (certanly do not grant him CONNECT or RESOURCE role, they are way to powerfull!). Grant him also your APP_ROLE role, but it should not be his default role, so it will not be enabled automaticaly when he connects.
CREATE USER app_user IDENTIFIED BY blahblah DEFAULT TABLESPACE .......;
GRANT CREATE SESSION TO app_user;
GRANT app_role TO app_user;
ALTER USER app_user DEFAULT ROLE NONE;
With this setup, your app_user can connect to database, but he can do practically nothing. He can select from dual or other few publicly available tables and views, but that is about all he can do. If he wants to perform any other selects or DMLs that he is allowed to do through application, he would have to isue:
"SET ROLE app_role IDENTIFIED BY top_secret;",
but he does not know the password ("top_secret")!
On the other hand, you make this password known to application (it can be hardcoded in the application, stored somewhere in a publicly unaccesible file or something like that...). When the user connects to database through application, the application unlocks the app_role with the top_secret password in the background, hidden from user. So in application user can perform anything required for that application, outside the application he can do virtualy nothing.
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
For passwords it is much more advisable to store them "hashed", not encripted. This has many advantages over encryption, and btw this is also the way Oracle stores passwords in a database. For further details how to implement your own "hashing" function follow this URL:
If you realy want to encrypt data in a table column, you can use dbms_obfuscation_toolkit package (from 8.1.6 onwards, I think). If you want a simple and easy-to-understand explanaiton how to implement encryption/decryption with this package take a look at:
Thanks a lot guys.
thanks for the reply .can you elabroate more like
how can u unlock app_role in application.like we are using
jdbc to connect to database.So in java code after the connection!!!!!!.