Limit db access to users using Access or Toad

[chikkodi]

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!

[dbasan]

Any other ideas that I can try? Thanks!

Read only tablespaces and read only database

[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.

[dbasan]

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.

[ixion]

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

[davey23uk]

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

[LKBrwn_DBA]

Check out if this works for you.

[OracleDoc]

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).