Meeting TOAD's Database Connectivity Needs
TOAD must be able to communicate with your Oracle database. That means TOAD must be able to both connect and then work with your database whether it's local (on PC) or remote (on server). This requires that the Oracle network client libraries exist on your PC for TOAD to call. Of course, Oracle in its infinite wisdom has renamed the client software and libraries as shown in Table 1.1.
Table 1.1. Oracle Connectivity Version
Terminology
Client Library
Oracle 7
SQL*Net
ORA7x.DLL
Oracle 8
Net8
ORACLIENT8.DLL
Oracle 8i
Net8
ORACLIENT8.DLL
Oracle 9i
Oracle Net
ORACLIENT9.DLL
Oracle 9iR2
Oracle Net
ORACLIENT9.DLL
Furthermore, you must also verify that your PC's environment variable for PATH includes the directory where those network client libraries are installed (that is, the Oracle installation directory). Of course, this too has changed over the Oracle versions and can also be modified by the person doing the install. Here are some examples of locations where they might be found:
C:\ORAWIN\BIN
C:\ORAWIN95\BIN
C:\ORANT\BIN
C:\ORACLE\BIN
C:\ORACLE\ORA92\BIN
Failure to install and reference these client libraries will prevent TOAD from functioning. However, you must also keep in mind your version of the client libraries versus the database you are talking to. Having client libraries from an older version of Oracle but talking to newer versions of Oracle is a recipe for disaster. You may encounter Oracle OCI API error messages from within TOAD. This might occur as TOAD attempts to call an OCI function for some new features or capability of the newer database version that the older network client library does not support.
For example, using an 8i client with a 9i database and attempting to work with columns whose data type is XMLTYPE will yield the cryptic message: OCI-21500: internal error code, arguments: [kocgpn129], [2], [], [], [], [], [], []. This is not a TOAD bug or lack of TOAD support for new features, but a simple user error of trying to use old or incompatible network client libraries with newer versions of the database. The best advice is to always use the latest and greatest network client, as it is always backward-compatible with prior database versions.
Understanding TOAD's Oracle-Based Security
Probably the most frequently asked question by DBAs new to TOAD is: "Will TOAD permit my developers to do things that they should not?" The simple answer is definitely not, because TOAD cannot override or supersede Oracle's security. TOAD users have only whatever roles, system privileges, or object grants that exist for them within the database. Thus they can do no more in TOAD than they could in SQL*Plus (they just can do it more easily and faster). To reiterate, TOAD only permits users to do whatever the DBA has granted them—there are no loopholes or exceptions.
But this does require the DBAs managing the Oracle schemas (that is, users) to have a very firm grasp on all the privileges they are actually handing out. For example, far too many DBAs still grant the predefined roles CONNECT, RESOURCE, and DBA to their users—even though Oracle states that these roles are there merely for backward compatibility and that you should create and grant your own customized roles. Unfortunately, many people seem to have missed this fact and still overuse the predefined roles. But some DBAs do not fully realize which system privileges the predefined roles grant. So granting a schema CONNECT means that the user can create clusters, database links, sequences, synonyms, tables, and views via TOAD, because those are the privileges that CONNECT possesses. So know your predefined roles well if you're going to use them. However, the recommendation is to create your own custom roles and grant those to your DBAs and developers,
-- Role: Junior Developer
-- Trusted to do some things
CREATE ROLE DEVELOPER_JR NOT IDENTIFIED;
--
-- Obviously required priv's
GRANT CREATE SESSION TO DEVELOPER_JR;
GRANT ALTER SESSION TO DEVELOPER_JR;
GRANT ALTER USER TO DEVELOPER_JR;
--
-- Junior Developer Priv's
GRANT CREATE PROCEDURE TO DEVELOPER_JR;
GRANT CREATE SEQUENCE TO DEVELOPER_JR;
GRANT CREATE SYNONYM TO DEVELOPER_JR;
GRANT CREATE TRIGGER TO DEVELOPER_JR;
GRANT CREATE TYPE TO DEVELOPER_JR;
GRANT CREATE VIEW TO DEVELOPER_JR;
-- Role: Senior Developer
-- Trusted to do most things
CREATE ROLE DEVELOPER_SR NOT IDENTIFIED;
--
-- Inherit All Junior Priv's
GRANT DEVELOPER_JR TO DEVELOPER_SR;
--
-- Senior Developer Priv's
GRANT CREATE DATABASE LINK TO DEVELOPER_SR;
GRANT CREATE DIMENSION TO DEVELOPER_SR;
GRANT CREATE INDEXTYPE TO DEVELOPER_JR;
GRANT CREATE LIBRARY TO DEVELOPER_SR;
GRANT CREATE MATERIALIZED VIEW TO DEVELOPER_SR;
GRANT CREATE OPERATOR TO DEVELOPER_JR;
GRANT CREATE TABLE TO DEVELOPER_SR;
Another area of possible security oversight is to forget the PUBLIC schema and its granted roles, system privileges, or object grants. For example, granting SELECT ANY TABLE to PUBLIC (which is generally not advisable) means that TOAD users can see the entire database's table data. So manage PUBLIC wisely. But do note that TOAD uses the data dictionary views for ALL_xxx and DBA_xxx that have been granted to PUBLIC. And don't worry about the DBA_xxx views; again, TOAD only allows users to see those based upon Oracle security. Thus a user must have SELECT ANY TABLE, SELECT ANY DICTIONARY, or SELECT_CATALOG_ROLE (depending on Oracle version and O7_DICTIONARY_ACCESSIBILITY setting) in order for TOAD to reference the DBA_xxx views. Remember that as you create roles for users who will be using TOAD's DBA module. For those users, one of the following security scenarios should be adopted:
O7_DICTIONARY_ACCESSIBILITY = true
O7_DICTIONARY_ACCESSIBILITY = true, SELECT ANY DICTIONARY granted, SELECT ANY TABLE not granted (second best recommendation)
O7_DICTIONARY_ACCESSIBILITY = true, SELECT ANY DICTIONARY granted, SELECT ANY TABLE granted
O7_DICTIONARY_ACCESSIBILITY = true, SELECT ANY DICTIONARY not granted, SELECT ANY TABLE not granted, SELECT_CATALOG_ROLE granted (best recommendation)
Creating the All-Important TOAD Schema
Strictly speaking, you do not have to create any database server-side objects in order to use TOAD. You can simply install TOAD on your PC and go on your merry way. But there are screens where a developer will require access to an Oracle explain plan table. For example, the SQL Editor window has a tab for displaying the explain plan for the current SQL statement. Thus TOAD will need access to a plan table in order to process and then display the resulting explain plan. You have three options here.
First, sometimes DBAs prefer to create a DBA schema-owned, general-purpose and shared explain plan table using Oracle's scripts. So the steps to implement might look something like this:
Connect as SYSTEM (or other DBA account).
Run Oracle's RDBMS/ADMIN/UTLXPLAN.SQL.
GRANT ALL ON SYSTEM.PLAN_TABLE TO PUBLIC
CREATE PUBLIC SYNONYM PLAN_TABLE FOR SYSTEM.PLAN_TABLE
TOAD can work with such a setup. You should merely set the TOAD options for the Explain Plan Table Name field under the Oracle category to PLAN_TABLE (meaning the public synonym for the general-purpose table set up by the DBA).
There are two caveats with this method. First, you must make sure the DBA schema-owned explain plan table is current for the Oracle version being used. It's really quite easy to forget to update this table with major Oracle upgrades. This can cause TOAD to encounter problems with missing columns (that is, a call to Oracle will try to populate newer columns that don't exist in the old plan table structure). And second, TOAD will not be able to save and recall plans if you use this method because TOAD requires its own plan table to support such operations. You must instead use one of the two remaining methods.
Second (and recommended), you can also create a special TOAD schema to own a general-purpose and shared explain plan table using TOAD's TOADPREP.SQL script (found in the TEMPS subdirectory of the TOAD install directory). TOADPREP.SQL first creates the TOAD schema, and then creates its required explain plan objects. To accomplish this implementation, the steps are as follows:
Edit TOAD's TEMPS/TOADPREP.SQL.
Connect as SYSTEM (or other DBA account).
Run TOAD's TEMPS/TOADPREP.SQL.
TOAD will now be able to support the save and recall of explain plans, as long as you remember to check Save Previous Explain Plan Results in the TOAD Options screen under the Oracle category, as shown in Figure 1.2.
The TOADPREP.SQL script has changed significantly with version 7.4. Although the script has always created both the TOAD schema and all its explain-plan required objects, it has been updated such that you only need to modify the first three DEFINE statements in the script in order to control the script's behavior. It used to be that you had to review the entire SQL script for possible changes. So the new TOADPREP.SQL script shown in Listing 1.1 is much easer to work with.
Listing 1.1 TOADPREP.SQL Script
REM This script will create TOAD objects in their own
REM schema. If you DO NOT want to create a unique system
REM schema for TOAD objects, load the file NOTOAD.SQL
REM
REM Otherwise, start a new Oracle connection as SYSTEM ( or
REM any other user with privileges to create a new USER)
REM and, while connected as that user, execute the following
REM by clicking the third toolbar button in a SQL Edit OR
REM by selecting the menu option "SQL_Window/Execute as Script"
REM
REM Ver Date Description
REM === ========== =======================================
REM 1.1 10/06/1999 1. Added STORAGE clauses to the table
REM create commands so that not too much
REM disk space will be allocated.
REM 2. Removed obsolete TOAD_TEMP and
REM TOAD_DEP_TEMP.
REM 1.2 11/17/1999 1. Changed index on TOAD_PLAN_TABLE from
REM unique to non-unique.
REM 1.3 05/23/2001 1. Added partition-related columns and
REM DISTRIBUTION to TOAD_PLAN_TABLE
REM 1.4 01/18/2001 1. Added OBJECTNAME function
REM 1.5 03/29/2002 Added EXECUTE ANY PROCEDURE
REM 1.6 03/31/2002 Reworked entire script so user custimizations
REM can be made via DEFINE variables in one place
REM --------- Make all changes right here --------------------
REM --------- Do not change the name of the TOAD user --------
DEFINE UPW_TOAD=TOAD
DEFINE DEF_TSPACE=USER_DATA
DEFINE TMP_TSPACE=TEMPORARY
REM ------------------ Create the TOAD User ------------------
DROP USER TOAD CASCADE;
CREATE USER TOAD
IDENTIFIED BY &UPW_TOAD
DEFAULT TABLESPACE &DEF_TSPACE
TEMPORARY TABLESPACE &TMP_TSPACE
QUOTA UNLIMITED ON &DEF_TSPACE
QUOTA 0K ON SYSTEM;
GRANT CONNECT TO TOAD;
GRANT RESOURCE TO TOAD;
GRANT CREATE PUBLIC SYNONYM TO TOAD;
--The following grant is only necessary if you intend to install the TOAD
--Profiler objects into the TOAD schema
GRANT EXECUTE ANY PROCEDURE TO TOAD;
REM ----------------- Drop public synonyms --------------------
DROP PUBLIC SYNONYM TOAD_PLAN_SQL;
DROP PUBLIC SYNONYM TOAD_PLAN_TABLE;
DROP PUBLIC SYNONYM TOAD_SEQ;
REM ----------------- Connect as TOAD -------------------------
CONNECT TOAD/&UPW_TOAD;
REM ----------------- Create the Explain Plan objects
DROP TABLE TOAD.TOAD_PLAN_SQL;
DROP TABLE TOAD.TOAD_PLAN_TABLE;
DROP SEQUENCE TOAD.TOAD_SEQ;
CREATE TABLE TOAD.TOAD_PLAN_SQL (
USERNAME VARCHAR2(30),
STATEMENT_ID VARCHAR2(32),
TIMESTAMP DATE,
STATEMENT VARCHAR2(2000) )
STORAGE (INITIAL 40K NEXT 24K);
CREATE UNIQUE INDEX TOAD.TPSQL_IDX ON
TOAD.TOAD_PLAN_SQL ( STATEMENT_ID );
CREATE TABLE TOAD.TOAD_PLAN_TABLE (
STATEMENT_ID VARCHAR2(32),
TIMESTAMP DATE,
REMARKS VARCHAR2(80),
OPERATION VARCHAR2(30),
OPTIONS VARCHAR2(30),
OBJECT_NODE VARCHAR2(128),
OBJECT_OWNER VARCHAR2(30),
OBJECT_NAME VARCHAR2(30),
OBJECT_INSTANCE NUMBER,
OBJECT_TYPE VARCHAR2(30),
SEARCH_COLUMNS NUMBER,
ID NUMBER,
COST NUMBER,
PARENT_ID NUMBER,
POSITION NUMBER,
CARDINALITY NUMBER,
OPTIMIZER VARCHAR2(255),
BYTES NUMBER,
OTHER_TAG VARCHAR2(255),
PARTITION_ID NUMBER,
PARTITION_START VARCHAR2(255),
PARTITION_STOP VARCHAR2(255),
DISTRIBUTION VARCHAR2(30),
OTHER LONG)
STORAGE(INITIAL 80K NEXT 36K) ;
CREATE INDEX TOAD.TPTBL_IDX ON
TOAD.TOAD_PLAN_TABLE ( STATEMENT_ID );
CREATE SEQUENCE TOAD.TOAD_SEQ START WITH 1 CACHE 20;
CREATE PUBLIC SYNONYM TOAD_PLAN_SQL FOR TOAD.TOAD_PLAN_SQL;
CREATE PUBLIC SYNONYM TOAD_PLAN_TABLE FOR TOAD.TOAD_PLAN_TABLE;
CREATE PUBLIC SYNONYM TOAD_SEQ FOR TOAD.TOAD_SEQ;
GRANT SELECT, INSERT, UPDATE, DELETE ON TOAD.TOAD_PLAN_SQL TO PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON TOAD.TOAD_PLAN_TABLE TO PUBLIC;
GRANT SELECT, ALTER ON TOAD.TOAD_SEQ TO PUBLIC;
REM -------- Create the ObjectName function for use in Kill/Trace -----------
DROP FUNCTION TOAD.MYOBJECTNAME;
CREATE OR REPLACE function TOAD.ObjectName(in_object_id in number) return
varchar
is
return_string varchar2(100);
begin
select OWNER||'.'||OBJECT_NAME
into return_string
from all_objects
where object_id = in_object_id;
return return_string;
end ObjectName;
/
GRANT EXECUTE ON TOAD.OBJECTNAME TO PUBLIC;
Third and finally, you can also create a private TOAD explain plan table and all its required objects per TOAD user via the NOTOAD.SQL script (also found in the TEMPS subdirectory of the TOAD install directory). To accomplish this implementation, the steps are as follows:
Connect as each and every TOAD user.
Run TOAD's TEMPS/NOTOAD.SQL.
This method is supported for backward compatibility, but it's very hard to imagine a case where you would want to choose this implementation option. This method would require every TOAD user to have CREATE TABLE privilege, CREATE SEQUENCE privilege, and some tablespace quota on at least one tablespace. Plus it would result in numerous additional database objects as a whole (that is, two tables, two indexes, and one sequence created per TOAD user). But the option does exist for those who want to use it. The only advantage to this approach is that, like the prior method, it too supports the save and recall of explain plans. Because this method is not recommended, the NOTOAD.SQL script is not shown.
Activating TOAD's "Read-Only" Mode
Most people don't realize that TOAD comes with two modes of operation entirely under their control. TOAD can operate in either read/write or read-only mode, simply controlled by the license files in the TOAD install directory. When the TOAD.LIC file is a copy of the FULLTOAD.LIC file (default), TOAD operates in read/write mode. And when the TOAD.LIC file is a copy of the READONLY.LIC file, TOAD operates in read-only mode. Note that read-only refers to the fact that users cannot save or commit anything to the database. However, read-only users are still permitted to create, modify, and save other files on the local PC. They cannot do anything but read database data.
Note that this was the original method by which TOAD supported a read-only mode of operation. Beginning in version 7.3, TOAD's entirely new advanced security offering (see the following section) offers a much cleaner and simpler method for making TOAD read-only.
Using TOAD's New Advanced Security
Beginning with version 7.3, TOAD offers an entirely new and completely customizable security mechanism. With it, you can very easily define what features or functions should work in TOAD by either database user or role. For example, you can take the DEVELOPER_JR database role from the prior section and further control what a junior developer can do within TOAD as shown in Figure 1.3. This screen is callable from the main menu at Tools, Toad Security. However, to use this screen, you must first have a TOAD schema (like the one created by TOADPREP.SQL) and then run the TOADSECURITY.SQL script (found in the TEMPS subdirectory of the TOAD install directory and shown in Listing 1.2).
Listing 1.2 TOADSECURITY.SQL Script
/*
This is the script for setting up TOAD Features Security.
This file should be run after the TOAD user has been created
through toadprep.sql. Load this script into the SQL Editor
and press "Run as script." You will be prompted for TOAD's
password on your database. Then you will be prompted for the name
of the user (should be a DBA) who will serve as the TOAD
Security administrator. Bear in mind that users with the
DBA role are not bound by TOAD Security.
*/
/*
Date Description
---------- -------------------------------------------
03/05/2002 Recreated script for 7.3's rewrite of TOAD Security
*/
CONNECT TOAD
CREATE TABLE TOAD_RESTRICTIONS (
USER_NAME VARCHAR2(32) NOT NULL,
FEATURE VARCHAR2(20) NOT NULL,
CONSTRAINT TOAD_RES_PK
PRIMARY KEY ( FEATURE, USER_NAME ) );
REM grant all to the toad tables WITH grant option to any users
REM who will be using the TOAD Features Security Window to administer
REM TOAD security features.
REM
GRANT ALL ON TOAD_RESTRICTIONS TO &SOME_DBA_USER WITH GRANT OPTION;
Examine Figure 1.3 in more detail. The left side shows a list of all the available functions, also considered enabled. These fall into two categories: menu and non-menu. Menu refers to actual menu items within TOAD, whereas non-menu means functions that might be accessible from multiple places within TOAD. The right side then shows just those functions you want to remove or disable for that user or role. the DEVELOPER_JR has had the following removed:
Menu: Data subset (cannot run data subset wizard)
Menu: Profiler analysis (cannot run TOAD profiler)
Non-Menu: Analyze table (cannot analyze tables)
Non-Menu: DBA module (cannot access DBA features)
Non-Menu: Drop table (cannot drop tables)
Non-Menu: Truncate table (cannot truncate tables)
Note that the left side offers a Non-Menu choice of "Read only override." This is the new and preferable way to activate TOAD in read-only mode (see the preceding section). This is by far the easiest and most reliable method for defining your read-only TOAD users.
Enabling TOAD's PL/SQL Profiler Support
TOAD has a very capable PL/SQL profiler for detailed analysis of code execution behavior. But many people seem to have problems initially getting this feature to work. At first, the menu item and toolbar icon may not appear as enabled. That's because there are some TOAD schema objects that must be created. People next encounter issues on the profiler screen, because unknown to them there are Oracle profiler packages that must be created by the DBA in the SYS account (and which Oracle does not create by default). But in reality the steps to set up TOAD's profiler are quite easy:
Connect as SYS.
Run Oracle's RDBMS/ADMIN/PROFLOAD.SQL.
Connect as TOAD.
Run TOAD's TEMPS/TOADPROFILER.SQL.
If you don't create and use a TOAD schema, it would still be possible to enable TOAD profiler support in one of two ways:
Run TOAD's TEMPS/TOADPROFILER.SQL as SYSTEM or other DBA account, because it creates public synonyms and grants object privileges to public.
Run TOAD's TEMPS/TOADPROFILER.SQL as each and every TOAD user (just remember to comment out the public grants and synonyms portions near the bottom of the script).
Saving and Restoring All Your TOAD Settings
If there's one thing TOAD has, it's lots and lots of options. So as you use TOAD over time, you will naturally set many of these options so as to customize TOAD to the way you like to work. But what happens when you go to work on a different PC (for example, if you work on your neighbor's PC while yours is being serviced)? You lose all your settings because they are stored on the local PC hard drive in both the TOAD install directory and the TEMPS subdirectory. It would take some time if you lost those settings and had to visit every screen where they are defined throughout the tool and reset them. So what's needed is a TOAD settings backup and restoration utility, which is provided in the following examples.
The following Windows command processor batch script will back up all your most important TOAD settings. You simply modify the script's first four SET statements for your particular environment and save the script to a file called TOAD_SAVE.BAT. Then you can merely type TOAD_SAVE in a command window, or better yet, place an icon on your desktop so that you can save all your settings quickly and easily with a single click.
@echo off
set save_dir1=c:\temp\TOAD
set save_dir2=%save_dir1%\temps
set toad_dir1=c:\program files\quest software\toad
set toad_dir2=%toad_dir1%\temps
echo.
echo *** Saving TOAD Settings ***
echo.
echo Target:
echo save_dir1 = %save_dir1%
echo save_dir2 = %save_dir2%
echo.
echo Source:
echo toad_dir1 = %toad_dir1%
echo toad_dir2 = %toad_dir2%
if not exist "%save_dir1%" mkdir "%save_dir1%"
if not exist "%save_dir2%" mkdir "%save_dir2%"
del /Q %save_dir%\*.*
del /Q %save_dir%\*.*
echo.
echo Copying Files:
for %%i in ("%toad_dir1%"\*.ini,
"%toad_dir1%"\*.dat,
"%toad_dir1%"\*.sdf,
"%toad_dir1%"\*.jdf,
"%toad_dir1%"\*.flt,
"%toad_dir1%"\*.tni,
"%toad_dir1%"\*.tbl,
"%toad_dir1%"\*.tmd,
"%toad_dir1%"\*.lst) do copy /Y "%%i" "%save_dir1%\"
for %%i in ("%toad_dir2%"\*.ini,
"%toad_dir2%"\*.dat,
"%toad_dir2%"\*.sdf,
"%toad_dir2%"\*.jdf,
"%toad_dir2%"\*.flt,
"%toad_dir2%"\*.tni,
"%toad_dir2%"\*.tbl,
"%toad_dir2%"\*.tmd,
"%toad_dir2%"\*.lst) do copy /Y "%%i" "%save_dir2%\"
Similarly, the following Windows command processor batch script will restore all of your most important TOAD settings. You modify the script's first four SET statements for your particular environment and save the script to a file called TOAD_RESTORE.BAT. Then you can just type TOAD_RESTORE in a command window, or better yet, place an icon on your desktop so that you can restore all your settings with a single click.
@echo off
set save_dir1=c:\temp\TOAD
set save_dir2=%save_dir1%\temps
set toad_dir1=c:\program files\quest software\toad
set toad_dir2=%toad_dir1%\temps
echo.
echo *** Restoring TOAD Settings ***
echo.
echo Source:
echo save_dir1 = %save_dir1%
echo save_dir2 = %save_dir2%
echo.
echo Target:
echo toad_dir1 = %toad_dir1%
echo toad_dir2 = %toad_dir2%
echo.
echo Copying Files:
for %%i in ("%save_dir1%"\*.ini,
"%save_dir1%"\*.dat,
"%save_dir1%"\*.sdf,
"%save_dir1%"\*.jdf,
"%save_dir1%"\*.flt,
"%save_dir1%"\*.tni,
"%save_dir1%"\*.tbl,
"%save_dir1%"\*.tmd,
"%save_dir1%"\*.lst) do copy /Y "%%i" "%toad_dir1%\"
for %%i in ("%save_dir2%"\*.ini,
"%save_dir2%"\*.dat,
"%save_dir2%"\*.sdf,
"%save_dir2%"\*.jdf,
"%save_dir2%"\*.flt,
"%save_dir2%"\*.tni,
"%save_dir2%"\*.tbl,
"%save_dir2%"\*.tmd,
"%save_dir2%"\*.lst) do copy /Y "%%i" "%toad_dir2%\"
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment