Understanding TOAD s Oracle-Based Security


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, as in the following example. (Of course, you could create these roles by using TOAD's GUI for managing roles from the Schema Browser as shown in Figure 1.1.)

Figure 1.1. TOAD role management screen.

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



TOAD Handbook
TOAD Handbook (2nd Edition)
ISBN: 0321649109
EAN: 2147483647
Year: 2003
Pages: 171

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net