Limit db access to users using Access or Toad
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Limit db access to users using Access or Toad

  1. #1
    Join Date
    Jan 2001
    Posts
    216

    Limit db access to users using Access or Toad

    Here is what I want to do -

    When users login to the db using Microsoft Access or Toad, I want to make the data readonly for them.

    Here is what I tried doing -

    1. Created a role with a readonly privilege. Assign the role using dbms_session.set_role in a logon trigger

    2. Created a user with readonly access to the db. Issued a alter session set_current_schema to the new user

    The first approach did not work because we cannot set role in a trigger. The second option did not work because it only changed the user's context. It did not change the privileges.

    Any other ideas that I can try? Thanks!
    Last edited by chikkodi; 08-23-2006 at 12:24 PM.

  2. #2
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Quote Originally Posted by chikkodi
    Any other ideas that I can try? Thanks!
    Read only tablespaces and read only database
    "What is past is PROLOGUE"

  3. #3
    Join Date
    Jan 2001
    Posts
    216
    Thanks for the quick response, but to clarify my question, I just want the data to be readonly for Access and Toad users. Users logging in through the application should be able to operate normally.

  4. #4
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Quote Originally Posted by chikkodi
    Thanks for the quick response, but to clarify my question, I just want the data to be readonly for Access and Toad users. Users logging in through the application should be able to operate normally.
    Create a Role granted with SELECT any tables,..........and grant this role to the users you need.
    "What is past is PROLOGUE"

  5. #5
    Join Date
    Jun 2006
    Posts
    259
    Its not clear from you question if the same users will need write access via some other mechanism. If they don't then just grant read access to a role for each table and grant that to the user.

    Otherwise... Your needing the same user to be able to write (sometimes).

    You could see if toad/access sets up their product ids via:
    dbms_application_info.set_module

    see: v$session.program / v$session.module

    Then use FGAC to set up a context upon login that would limit their data access to read.

    A simple but much less efficient approach would be to write triggers for each table on update/insert/delete that tested the sessions context ( set via a login trigger) and if it was a toad/access context then raise an exception

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    Quote Originally Posted by dbasan
    Create a Role granted with SELECT any tables,..........and grant this role to the users you need.
    think about your answers - its the same user

  7. #7
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,478

    Cool


    Check out if this works for you.

    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  8. #8
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    You know if you were to do a little research instead of just asking it might prove fruitful. Kind of like if you were to simply click the "help" button on Toad then click on Installation and Administration, it comes up with a nice explaination.

    Toad for Oracle, Read-Only
    Toad can be made read only using the two license files: READONLY.LIC and FULLToad.LIC.

    What is Toad Read Only?
    Toad Read Only is a way to allow users to view data and SQL through Toad without making changes to the database.

    Why use Toad Read Only?
    Toad Read Only allows administrators to give their users a powerful tool without worrying about a user committing a change to a sensitive production instance.

    While Toad honors privileges granted to the Oracle user, Toad Read Only will not allow the user to do anything which changes the content of the database.

    Where to get Toad Read Only?
    You can make any Toad installation read-only, you can use Toad Security to make selected users or roles read-only, or you can get a special read-only installation that has extra exclusions to prevent users from making changes.

    How to make any Toad installation Read-Only
    Using License files

    Toad can be made read-only using the two license files: READONLY.LIC and FULLToad.LIC found in the installation directory where Toad is installed.

    Toad.EXE only reads Toad.LIC to determine if it is full Toad or read-only. The license file contains a setting for read only database access. The network administrator can copy READONLY.LIC over the Toad.LIC on an individual workstation to make Toad read-only at that workstation. Remember, the Toad.LIC file must be in the Toad folder.

    This is the least secure method of limiting Toad.

    To use read only license files

    Copy READONLY.LIC over the TOAD.LIC on an individual workstation.

    Toad Security

    Using Toad Security you can make Toad read-only to a selected user or role. This is useful if you have someone who needs to view database objects but does not have the authority to change them.

    Note: This Toad Security option does not apply to the DBA module. To restrict Toad entirely, you will also need to restrict the DBA module from the appropriate users.

    To make Toad read-only using Toad Security

    Move the Read-only Override function from the Features Non-menu list to the Restricted features list in the Toad Security window. This makes Toad read-only to the selected user.

    Toad Read Only Installation

    Quest Software, Inc. can provide a read only copy of Toad. The Toad Standard Edition - READ ONLY install is a read only executable designed to prevent its users from changing the database. For this reason, it does not include Quest ScriptRunner (which lets a user write a script that can update database objects), SQLMonitor (which logs SQL calls using the OCI layer), and Server Side Install (which lets users make changes to Toad schemas).
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

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