Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: TOAD Access to other Schemas

RE: TOAD Access to other Schemas

From: <>
Date: Thu, 14 Jun 2007 08:44:21 -0400
Message-ID: <>

Even if the had DBA privs the code should have to pass TEST anyway... where TEST has duplicate security to PROD.

SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY should be plenty... if the developer is writing stored procedures against other schemas they need 'direct' grants... and probably SYNONYMS.

Create a script or show the grantor and grantee how to dynamically create the statements for dev environment. Since there is probably more than one developer, the synonyms need to be private anyway.... to avoid conflict. Several grantors can allow access... and the developer can choose between them (or mix and match), by repointing his synonyms to where ever he has been granted access.

Joel Patterson
Database Administrator
904 727-2546

-----Original Message-----
[] On Behalf Of Tony van Lingen Sent: Wednesday, June 13, 2007 10:46 PM
Cc:; Subject: Re: TOAD Access to other Schemas

I've got to differ with Peter here. We've come from a situation where developers had DBA access in dev. As Raj already hinted, this lead to liberal use of DBA privileges in the applications they produced and we ended up to have to grant the application owners in Prod the DBA privs as well. This is *BAD* for all kinds of reasons (applications that let users change database parameters on the fly for instance).

We went through a long and painful process of weaning both the production users and developers off that blanket access. Bottomline: You

really don't want developers to develop production applications whilst having DBA access.

TOAD will show all objects that a user has access to in all schemas. There is no reason to have DBA access, unless they intend to use the database tuning capabilities that TOAD also has... and you really don't want your developers to do that.


Peter McLarty wrote:
> Denise
> Tell your developers to go jump
> Now what they probably want is DBA privileges and that is debatable
> them as it seems you don't have anyone with the level of experience to
> be trusted.
> You can grant them select on any table to see table data in other
> schemas ok in a true development environment. If you have a copy of
> production data with sensitive information like payroll details then I
> would say no.
> Find out what they are trying to do, they have access to a lot of
> objects now it is default in normal user security.
> Maybe on a server even the same one install a copy of the XE database
> and give them dba rights there and tell them if they break it you will
> fix it be reinstalling the database ie they loose everything and let
> them learn from their mistakes there.
> I let trusted developers have DBA privileges in true development
> ie it has the schema of prod with dummy or limited data.
> Cheers
> Peter
> -----Original Message-----
> From: []
> Sent: Thursday, 14 June 2007 08:54 AM
> To:
> Subject: TOAD Access to other Schemas
> I have some users who want the dynamic ability of TOAD for
> In particular they want to be able to see any objects under any other
> schema.
> They say that they need to be granted SYSDBA. Is this truly the only
> way to give these users the access they want under TOAD?
> Denise Gwinn
> --
> --


WARNING: This e-mail (including any attachments) has originated from a Queensland Government department and may contain information that is confidential, private, or covered by legal professional privilege, and may be protected by copyright.

You may use this e-mail only if you are the person(s) it was intended to be sent to and if you use it in an authorised way. No one is allowed to use, review, alter, transmit, disclose, distribute, print or copy this e-mail without appropriate authority. If you have received this e-mail in error, please inform the sender immediately by phone or e-mail and delete this e-mail, including any copies, from your computer system network and destroy any hardcopies.

Unless otherwise stated, this e-mail represents the views of the sender and not the views of the Environmental Protection Agency.

Although this e-mail has been checked for the presence of computer viruses, the Environmental Protection Agency provides no warranty that all viruses have been detected and cleaned. Any use of this e-mail could harm your computer system. It is your responsibility to ensure that this e-mail does not contain and is not affected by computer viruses, defects or interference by third parties or replication problems (including incompatibility with your computer system).

E-mails sent to and from the Environmental Protection Agency will be electronically stored, managed and may be audited, in accordance with the law and Queensland Government Information Standards (IS31, IS38, IS40, IS41 and IS42) to the extent they are consistent with the law.


Received on Thu Jun 14 2007 - 07:44:21 CDT

Original text of this message