V$TABLESPACE AND DBA_TABLESPACES WILL LIST THE TEMPORARY TABLESPACES, AND V$TEMPFILE AND DBA_TEMP_FILES WILL LIST THEIR FILES

10. þ B, D, F, and H. V$TABLESPACE and DBA_TABLESPACES will list the temporary tablespaces, and V$TEMPFILE and DBA_TEMP_FILES will list their files.ý A, C, E, and G. A and E are wrong because V$DATAFILE and DBA_DATA_FILES do not include tempfiles. C and G are wrong because there are no views with these names.

CHAPTER 6

Oracle Security

Exam Objectives

In this chapter you will learn to• 052.7.1 Create and Manage Database User Accounts• 052.7.2 Grant and Revoke Privileges• 052.7.3 Create and Manage Roles• 052.7.4 Create and Manage Profiles• 052.11.1 Implement Database Security and Principle of Least Privilege• 052.11.2 Work with Standard Database Auditing

203

Security is an issue of vital concern at all sites. All organizations should have a security manual documenting rules and procedures. If your organization does not have such a manual, someone should be writing it—perhaps that someone should be you. In security, there is no right or wrong; there is only conformance or nonconformance to agreed procedures. If administrators follow the rules and advise on what those rules should be, then any breach of security is not their fault. But unfortunately, history shows that when something goes wrong in the security arena, there is a great desire to blame individuals. It is vitally important that administration staff should be able to point to a rule book that lays down the procedures they should follow, and to routines and logs that demonstrate that they did indeed follow them. This devolves the responsibility to the authors of the rule book, the security manual. If no such manual exists, then any problems are likely to be dumped on the most convenient scapegoat. This is often the database administrator. You have been warned.The Oracle product set provides many facilities for enforcing security up to and beyond the highest standards specified by any legislation. Many of the facilities (such as data encryption) are beyond the scope of the first OCP examination, where the treatment of security is limited to the use of privileges and auditing. This chapter discusses the basic security model governing user accounts and their authentication. The differences between a schema and a user (terms often used synonymously) are explored along with the use of privileges to permit access to as few items as necessary and the grouping of privileges into roles for ease of administration. Profiles used to manage passwords and resources to a limited extent are covered before delving into the powerful auditing features available.

Create and Manage Database User Accounts

When a user logs on to the database, they connect to a user account by specifying an account name followed by some means of authentication. The user account defines the initial access permissions and the attributes of the session. Associated with a user account is a schema. The terms “user,” “user account,” and “schema” can often be used interchangeably in the Oracle environment, but they are not the same thing. A user is a person who connects to a user account by establishing a session against the instance and logging on with the user account name. A schema is a set of objects owned by a user account, and is described in Chapter 7. The way the account was created will set up a range of attributes for the session, some of which can be changed later, while the session is in progress. A number of accounts are created at database creation time, and the DBA will usually create many more subsequently.In some applications, each user has their own database user account. This means that the database is fully aware of who is the real owner of each session. This security model works well for small applications but is often impractical for larger systems with many hundreds or thousands of users. For large systems, many users will connect to the same account. This model relies on the application to map the real end user to a database user account, and it can make session-level security and auditing more complex. This chapter assumes that every user is known to the database: they each have their own user account.

User Account Attributes

PART IA user account has a number of attributes defined at account creation time. These will be applied to sessions that connect to the account, though some can be modified by the session or the DBA while the session is running. These attributes are• Username• Authentication method• Default tablespace• Tablespace quotas• Temporary tablespace• User profile• Account statusAll of these should be specified when creating the user, though only username and authentication methods are mandatory; the others have defaults.

Username

The username must be unique in the database and must conform to certain rules. A username must begin with a letter, must have no more than 30 characters, and can consist of only letters, digits, and the characters dollar ($) and underscore (_). A user name may not be a reserved word. The letters are case sensitive but will be automatically converted to uppercase. All these rules (with the exception of the length) can be broken if the username is specified within double quotes, as shown on Figure 6-1.

Figure 6-1

How to create users with nonstandard names