How to ceate user .

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.

Hope this would help,
Sam

Try using dbms_application_info package in logon database trigger.

Hi,
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.

Thanks

thanks for the reply
oracle8db,can u show me one example and how u set different user to connect using one username.

[Edited by el472 on 03-12-2001 at 11:08 AM]

i DON'T KNOW THE DESIGN OF YOUR APPLICATION.
In our application we have the set who columns in which we we can track the user, who is changing and updating any thing in that table.

This is alos populated by the front end application.
Thanks

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.

well we have a table which contains users information such as login password etc
the problem is these data are not encrypted, does anyone know how to encrypt data of a table in oracle :-?

Pando,

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:

http://asktom.oracle.com/pls/ask/f?p...ID:95412348059

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:

http://asktom.oracle.com/pls/ask/f?p...0_P8_DISPLAYID,F4950_P8_CRITERIA:791026226790,{dbms_obfuscation}

HTH,

Thanks a lot guys.
To jmodic,
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!!!!!!.

el472,

Yes, obviously you must first connect to database to be able to unlock anything ;). Right after the connection your application (being java or c++ or Forms or VisualBasic or cobol or...., it is irrelevant which) must isue the following call:

"SET ROLE app_role IDENTIFIED BY top_secret;"

That's it, anything is now unlocked to your user.

have you tried pupbld.sql package glogin.sql look at these options to

Quote:

---

Originally posted by hrishy
have you tried pupbld.sql package glogin.sql look at these options to

---

Can you ellaborate what have you had in mind with the above statement?

Maybe you haven't follow this thread from the begining. The original question was "How to assure users have their needed privileges enaibled only when they are connected through certain applicaton, and disable all the privileges for the same users when they try to connect through any other aplication or tool (like SQP*Plus)?".

Both pupbld and glogin.sql are efective only for SQL*Plus and no other tool or aplication. How would you use those two scripts to achieve the above requirenments???

thanks a lot jmodic,
I can setup users like that,But still they can logon
through sqlplus.i have seen some application written in oracle forms,in which i was able to create a user and grant some app
role.They are able to connect through only that appl.they
even can't able to login throuh sqlplus.App. has only one superuser which can use same password to login through
any tool.it was a third app.,so i don't how they setup.

any ideas!!!!!!!!!!!!!!!

Quote:

---

Originally posted by el472
thanks a lot jmodic,
I can setup users like that,But still they can logon
through sqlplus.

---

Sure they can, but they won't be able to see/change anything...

Quote:

---

i have seen some application written in oracle forms,in which i was able to create a user and grant some app
role.They are able to connect through only that appl.they
even can't able to login throuh sqlplus.App. has only one superuser which can use same password to login through
any tool.it was a third app.,so i don't how they setup.

any ideas!!!!!!!!!!!!!!!

---

Well, this should not be to hard to accomplish, although I would never use this concepts if I could avoid it. The scenario should be something like:

1. An application prompts the user to enter the usernam and pwd. Let's say user enters BLACK/LION.
2. An application then connects to the database under a different, predefined common account, which is used for all connections through this particular application. Let's say this common account has username/pwd APPUSER/APPPWD. This username (and particullarly its password) is not known to user BLACK or any other users.
3. Immediately after the connection, the apllication looks in a table of all application users. If it finds a record with username/password = BLACK/LION it continues with the work, otherwise it disconnects and show apprpriate message to the user ("Invalid username/password" or something like that).
3. If user BLACK tries to connect through SQL*Plus or any other tool, he will be refused because the schema BLACK doesn't exists in a database. The user would only be able to connect like APPUSER/APPPWD (because the schema APPUSER actually exists in the database), but he doesn't know the username and/or pasword.

Why I said I don't favour this concept? because when DBA looks at the database to find what is going on and which user is causing troubles, he finds 500 sessions, all under the same username APPUSER! I guess very few DBAs would be too enthusiastic about this scenario... However I agree sometimes this is the only feasible option, particuallary for the databases opened to internet applications with unlimited number of potential users.

Jmodic,
Actually it does shows the original username with
the session id ,so u can see which user is doing what.

Quote:

---

Originally posted by el472
Jmodic,
Actually it does shows the original username with
the session id ,so u can see which user is doing what.

---

I've lost you here. What do you mean by their "original username"? If they all are connected under the same account, V$SESSION will show you the name of this account for all connected users. True, the session ids will be different, but this will tell you nothing.

For example, I login to application with my username "jmodic". The application connects me to the database under common account "appl_user". Then you login yourself to application under your username "el472" and application conects you under common account "appl_user". So now DBA will se two sessions, but both under username APPL_USER! Where will he see our "original usernames" (jmodic and el472)?

I am able to see in instance manager different user like
el472,..... connected through app.

Jmodic,
i think whats happening is after connecting to app.
it looks into user_table and check for valid user and password.
and then change only password ..................