0606-0607

Previous Table of Contents Next

Page 606

Object Security Model

The DBA must consider other factors when setting up a security plan. Not only should the setup of the database users be considered , but also the ownership of the database objects. Although there is no right or wrong way to go about this, the following sections outline some of the concerns faced by the DBA when setting up object ownership models.

Protected Object Ownership Schema

One security model implemented by many sites is the protected schemasometimes called the pure schema. Under this model, the DBA sets up an account that is not associated with any specific database user. This account is used as an ownership account for all the database objectstables, views, and so on. Public synonyms are set up for each database object, and grants are made to each user for each database object. Therefore, a single user owns the objects, but the account can be restricted by not issuing passwords to any users except those who perform database object maintenance.

There is nothing incredibly mystical about this setup. The object owner exists as just another account within the database. Depending on the environment, the DBA can configure the database to have only connection or resource privileges during maintenance windows and then revoke those privileges when completed. Thus, access to the object owner account can be given to other userswho might, for example, want to look at the contents of the CAT or USER_TABLES tablewithout enabling them to make changes to the database objects themselves .

One important note here is that the DBA or person responsible for database object maintenance should maintain a build script for the object. Although this information can be obtained from the Oracle data dictionary, it is important to have this information accessible in emergency situations. A good place to keep the DDL for objects belonging to a schema is either under the ORACLE_HOME directory or in a safe place only accessible to the schema owner.

Capacity Planning Requirements

In dealing with database objects, one of the key elements for which the DBA is responsible is the capacity planning requirements of the database. Many sites hold to the philosophy that the creation and maintenance of the database objects responsibilities separate from overall database maintenance; most of these sites still agree that capacity planning is a responsibility of the DBA. Everything in the database is stored physically in database files.

Volumes have been written concerning the best ways to optimize the capacity planning of database objects within tablespaces. The main concern of capacity planning in this chapter is on security. Because users other than the DBA might be involved in creating database objects, he should stay abreast of modifications as they occur. For example, it takes only a typographical error in the STORAGE clause to inadvertently fill up a tablespace1,000KB and 10,000KB

Page 607

are different by just one zero. When no further space is available to expand the tablespace, this can bring production databases to a screeching halt. By the same token, if the next extent sizes are set wrong forgetting the KB in 512KB makes it 512 bytesa database object can quickly reach its MAXEXTENTS. When this happens, the only option is to rebuild the object with proper extent sizes. Depending on how much data is stored, this might be no small feat. Another important setting to keep a handle on is the value specified for pctincrease. The pctincrease causes each NEXT extent as specified in the STORAGE clause to be increased by the given percent value (for example, 10 for 10 percent). If this value is set too high on a dynamically growing table, the DBA will have little control over the growth of each next extent, causing unnecessary deprivation of free space in the residing tablespace.

The bottom line on capacity planning requirements from a database security standpoint is to be certain that accountability exists. Object creations should generally be limited to developers or analysts who have the technical knowledge to understand what object creation entails. It should not be necessary to hold anyone 's hand. Likewise, nobody should be given a blank check.

Avoiding Tablespace Fragmentation Issues

Given the prevalence of tools such as Defrag by ARIS and TSReorg by Platinum Technology, tablespace fragmentation is an obvious problem for most DBAs. Tablespace fragmentation, illustrated in Figure 24.1, occurs when free space is available in a tablespace, but when the blocks of free space are not grouped in contiguous blocks. That is, they are not together.

Many DBAs might wonder what tablespace fragmentation has to do with database security. The answer is simple: Steps that can be built into the security plan of the database help minimize some of the main causes of database fragmentation.

Suppose, for example, that a developer calls and complains that he cannot create a new table in tablespace XYZ. Whenever he tries to issue the CREATE TABLE command from SQL, he receives this error message:

 cannot allocate extent of size 99 in tablespace XYZ 

A quick check of the view DBA_FREE_SPACE shows the amount of free space available in the tablespace, so it is possible to calculate the total amount of free space capable of holding the table. The first question that the DBA should pose to the developer is, "How often are you dropping tables and indexes?" This is the most common cause of tablespace fragmentation, especially for tablespaces to which developers have access. As a rule, developers perform CREATE TABLE/INDEX and DROP TABLE/INDEX operations on a regular basis, which inevitably leads to problems.

It is a good idea to limit or eliminate access to the tablespaces on which production objects reside. You can do this by using tablespace quotas and by not giving anyone other than the protected schema access to the tablespaces. Generally, a special work tablespace called WORK or MISC is created. Developers can perform adds and drops on it. If this tablespace fragments , it

Previous Table of Contents Next