Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Location
    Manchester, UK
    Posts
    143

    Unanswered: How to make it secure and safe!

    Hi all,

    We have a third party application which is about to go live. It uses an Oracle 9i database. For some stupid reason the third party suppliers have created two roles in the database which give users select, insert, update and delete access on objects such as tables. This would be fine if it was just within the confines of the application, but now the users also have access to a SQLPLUS session. I feel that this is very dangerous as they could delete anything their role gives them access to, rather than only those things specified via the code in the application. Ideally I would have liked the application to have been written using procedures, and for the users to only have execute privileges, but we cannot control the app as it is a third party product.

    And so, is there any way I can secure the database against ad hoc use through SQLPLUS? I can't just remove the users' roles as they need them for the app. Is there a role I can create that can limit the damage somehow? Also, there are lots and lots of users so a 'catch all' solution would be great rather than having to change individual's logins.

    Many many thanks,

    Paula

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Possibly through an on logon trigger, determine the connecting application and set/remove roles as required?

    You would set the role only for your third party application and/or remove it for anything else.

    Check out dbms_application_info.read_module

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  3. #3
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by Polly013
    Ideally I would have liked the application to have been written using procedures, and for the users to only have execute privileges, but we cannot control the app as it is a third party product.
    I definitely agree : this would have been the right solution.

    Well, I suppose that you can't go back and choose another application now, so your only choice AFAIK would be to create one or two new roles that would give only the needed grants to the users that would connect to the database through this app, so that they would only have rights on the objects (tables...) related to this application, not on all your objects. You would drop the old roles, grant the new roles to these users and that should be OK.

    Unfortunately AFAIK you'll have to grant rights to these new roles object by object, which may take a while...

    I hope someone else will have a better idea .

    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  4. #4
    Join Date
    Aug 2004
    Location
    Manchester, UK
    Posts
    143
    Thanks very much, I may give the trigger option a try. There is also a table called user_profile apparently which you can add records to to block users from sqlplus. I've not looked into it fully yet but it may be possible to write a script to add all relevant users to this and therefore prevent access.

    Thanks again.

    Paula.

  5. #5
    Join Date
    Aug 2004
    Location
    Manchester, UK
    Posts
    143
    OK, just to update in case anyone else needs this is the future. There is a table called product_user_profile to which rows can be added to block users from performing certain functions. Details of this can be found in the 9i SQLPlus Users Guide and Reference manual. I have now blocked the users from doing anything except SELECT in SQLPLUS, so problem solved.

    Handy to know!

    Thanks.

    Paula.

  6. #6
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Polly013, excellent information! I have never heard of it before. Thank you very much. I was about to refer you to FGAC, but this looks like a good way to enforce security over SQL*Plus, allthough it have limitations.. (doesn't work database links, etc..).

Posting Permissions

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