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. Thus in Figure 1.3, 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.