DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 19 of 19

Thread: How to ceate user .

  1. #11
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  2. #12
    Join Date
    Jan 2001
    Posts
    2,828

    Talking

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

  3. #13
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    [QUOTE][i]Originally posted by hrishy [/i]
    [B]have you tried pupbld.sql package glogin.sql look at these options to [/B][/QUOTE]

    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???
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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

  5. #15
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    [QUOTE][i]Originally posted by el472 [/i]
    [B]thanks a lot jmodic,
    I can setup users like that,But still they can logon
    through sqlplus.[/B][/QUOTE]
    Sure they can, but they won't be able to see/change anything...

    [QUOTE][B]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!!!!!!!!!!!!!!! [/B][/QUOTE]
    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.

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

  6. #16
    Join Date
    Mar 2001
    Posts
    38
    Jmodic,
    Actually it does shows the original username with
    the session id ,so u can see which user is doing what.
    el472@hotmail.com

  7. #17
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    [QUOTE][i]Originally posted by el472 [/i]
    [B]Jmodic,
    Actually it does shows the original username with
    the session id ,so u can see which user is doing what. [/B][/QUOTE]
    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)?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #18
    Join Date
    Mar 2001
    Posts
    38
    I am able to see in instance manager different user like
    el472,..... connected through app.
    el472@hotmail.com

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