This chapter is from the book
This chapter is from the book
This chapter is from the book
TOAD Adheres to Oracle Security
Probably the question most frequently asked by shops 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 inherent security. A TOAD user has only whatever roles, system privileges, or object grants exist for the user within the database. Thus users can do no more in TOAD than they could in SQL*Plus (they simply can do it more easily and faster via TOAD). To reiterate, TOAD permits database users to have only whatever rights the DBA has granted them—there are no loopholes or exceptions.
This approach does require the DBA managing the Oracle schemas (i.e., users) to have a very firm grasp of all the privileges being handed out. For example, far too many DBAs grant the predefined roles CONNECT, RESOURCE, and DBA to their users—even though Oracle states plainly that these roles are provided merely for backward-compatibility purposes 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. Some DBAs do not fully realize which system privileges the predefined roles grant. For example, granting a schema the CONNECT role means that the user can create clusters, database links, sequences, synonyms, tables, and views via TOAD, because those are the privileges that CONNECT possesses. Know your predefined roles well if you plan to use them!
We recommend that you create your own custom roles and grant those to your TOAD users. Listing 1.4 shows some database roles we often create in our database for granting privileges to—and thus controlling—various TOAD users.
Listing 1.4. Example TOAD Database Roles
-- Role: Junior Developer -- Trusted to do some things CREATE ROLE DEVELOPER_JR NOT IDENTIFIED; -- -- Obviously required privileges GRANT CREATE SESSION TO DEVELOPER_JR; GRANT ALTER SESSION TO DEVELOPER_JR; GRANT ALTER USER TO DEVELOPER_JR; -- -- Junior Developer privileges 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 Developer privileges GRANT DEVELOPER_JR TO DEVELOPER_SR; -- -- Senior Developer privileges 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 not to forget the PUBLIC schema and its granted roles, system privileges, or object grants. For example, granting the privilege of SELECT ANY TABLE to PUBLIC (which generally isn't advisable) means that TOAD users can see the entire database's table data. Given this widespread authority of the grant, you should oversee PUBLIC rights management very wisely. TOAD will not disobey your security paradigm, but if you leave loopholes open, people generally will find and abuse them. TOAD will simply make the process of locating them easier for users by its very nature of making anything Oracle related easier.