This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Feb 21, 2012 4:06 PM by 919057 Go to original post RSS
  • 15. Re: how to restrict toad or any other tool to access database using a username.
    riedelme Expert
    Currently Being Moderated
    user576726 wrote:
    Hi
    Thanks
    I have developed my tool using forms 10g. And this is being used by a lot of users having different oracle userid.
    Obviously I have to give them access to database objects like select,insert, update, delete, execute etc.

    If the user logs in using TOAD, he/she can tamper the data (because business logics are there in forms).
    He/She may insert data in a table without using the sequence (suppose primary key is based on a sequence). Later when some ohter user inserts data using the forms, it may throw primary key error.

    users (non skilled) may exucute a query or statement that may cause locks to some important database objects. That may degrade the performance.

    he/she may create unnecessary database object in the database.

    I don't want them to access database using any ohter connectivity like jdbc etc and many more......

    regards
    The issue appears to be not so much with Toad, SQL*PLUS and other tools so much as absolutely forcing users to use your interface for proper processing. This should be possible through careful planning and assignment of schema privileges.

    You can write packages to handle the DML statements belonging to the table owner. The other users can have privilges to execute the packages but NOT perform DML on the tables belonging to the table schema directly. You would unfortunately have to revise your forms to use the packages to perform DML through the package, which will be executed as the package (and table) owner having granted execute privilege to the appropriate user(s) - role privileges will not work for package execution grants.

    Hopefully there is a simpler solution but I can't think of it at this moment
  • 16. Re: how to restrict toad or any other tool to access database using a username.
    748594 Newbie
    Currently Being Moderated
    I have a workable solution for you but you need to do some effort on your part.

    1. Create a table called app_users (userid number, username char(30), TableName char(30), access_level varchar2(30), passwrd char(10));

    2. Create a simple program using forms to insert/delete/update the above table
    Example of records:
    userid username TableName access_level
    A001 John customer SELECT
    A001 John orders Update and so on

    3. Create a application schema (Like HRMS etc) and create all objects under it.
    Keep the password of this schema secured

    4. Then create a logon screen for your application which prompts for userid and password
    Behind the scene, you would have already connected to the database using the schema in step3 above
    Validate the userid and pwd against the app_users table and then let him logon or reject
    Here you are not creating any database users for them, just logical records and they all will be
    connected with schema HR, but the wont know the pwd as thats compiled inside ur application.

    Now depening on the roles you let each user work on application.

    Cheers
    www.oraclefusions.com
    Please visit my site for free performance tuning oracle tools.
    The only real time Server-side SQL Sniffer tool

    developed:http://www.oraclefusions.com/applications.html#sniffer
  • 17. Re: how to restrict toad or any other tool to access database using a username.
    ajallen Pro
    Currently Being Moderated
    Here is what I would do.
    1. Create a default role for the users. Grant it minimum privileges, like select on certain tables.
    2. Revoke table privileges from the users.
    3. Grant that default role to the users. That way they can do reports and ad hoc queires all they like.
    4. Create a password protected role for the application to use when doing CRUD.
    5. Modify the application so that on connect it grants that role to the user's session.

    Now, the only way users can modify the data is through the application, and they can use whatever tool they like to query the data.
  • 18. Re: how to restrict toad or any other tool to access database using a username.
    riedelme Expert
    Currently Being Moderated
    ajallen wrote:
    Here is what I would do.
    1. Create a default role for the users. Grant it minimum privileges, like select on certain tables.
    2. Revoke table privileges from the users.
    3. Grant that default role to the users. That way they can do reports and ad hoc queires all they like.
    4. Create a password protected role for the application to use when doing CRUD.
    5. Modify the application so that on connect it grants that role to the user's session.
    I like that better than my answer - if it works as expected :)
  • 19. Re: how to restrict toad or any other tool to access database using a username.
    662605 Newbie
    Currently Being Moderated
    Hi,

    you can use Database Vault Features,
    Take a look at :
    Tutorial: Preventing Ad Hoc Tool Access to the Database
    [http://download.oracle.com/docs/cd/E11882_01/server.112/e10576/cfgfact.htm#CIHCDJJA]
  • 20. Re: how to restrict toad or any other tool to access database using a username.
    919057 Newbie
    Currently Being Moderated
    The low-privilege default role for general use, and password-protected non-default role for application access is a key part of the security for our third-party Student/Finance application. The business logic in the forms, plus additional security restricting particular forms to specific users is critical to our protection from people using their application accounts from the 'back door'.

    It works!
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points