DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: How to ceate user .

  1. #1
    Join Date
    Mar 2001
    Posts
    38
    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).

    el472@hotmail.com

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Feb 2001
    Posts
    389
    Try using dbms_application_info package in logon database trigger.

  4. #4
    Join Date
    Feb 2001
    Posts
    184
    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

  5. #5
    Join Date
    Mar 2001
    Posts
    38
    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]
    el472@hotmail.com

  6. #6
    Join Date
    Feb 2001
    Posts
    184
    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

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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 :-?

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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:

    [url]http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:95412348059[/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:

    [url]http://asktom.oracle.com/pls/ask/f?p=4950:8:3781::NO::F4950_P8_DISPLAYID[/url],F4950_P8_CRITERIA:791026226790,{dbms_obfuscation}

    HTH,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    Mar 2001
    Posts
    38

    Red face

    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@hotmail.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width