0071-0073
Page 71
For example, before I can create a trigger on a table (even if I own the table as an Oracle user), I must have a system privilege called CREATE TRIGGER either assigned to my Oracle user account or assigned to a role given to the user account.
The CREATE SESSION privilege is another frequently used system-level privilege. In order to make a connection to the database, an Oracle account must have the CREATE SESSION system level privilege assigned to it. This gives the account the privilege to make connections to the database.
Object-Level Privileges
Object-level privileges provide the capability to perform a particular type of action (select, insert, update, delete, and so on) on a specific object. The owner of the object has full control over the object and can perform any action on it; he doesn't need to have object-level privileges assigned to him. In fact, the owner of the object is the Oracle user who grants object-level privileges to others.
For example, if the user who owns a table wants another user to select and insert rows from his table (but not update or delete), he grants the select and insert object-level privileges on that table to the other user.
You can assign object-level privileges either directly to users or to roles that are then assigned to one or more Oracle user accounts.
Users and Roles
A role is a type of object that you can use to simplify the administration of system and object-level privileges. Instead of assigning privileges directly to user accounts, you can assign the privileges to roles that are then assigned to users.
Roles are essentially groupings of system and object-level privileges. They make the administration of privileges much easier because you can configure the privileges for a particular type of user once and assign those privileges to a role. When a user needs that set of privileges, you can use a single role assignment command to set that user up. Without the use of roles, you'd need to issue many commands for each of the different privileges required.
In addition, you can set up different roles with the correct privileges even though you don't yet have Oracle user accounts that require those assignments. You can assign a role to another role, building hierarchies of roles. Also, you can protect a role with a password that the user must supply when he wants to enable the role.
As already discussed, a physical database could contain many Oracle user accounts that are protected by passwords. You must supply the username and password regardless of which tool you use to gain access to the database. Roles are not the same as Oracle users; you cannot connect to the database by supplying a role name and password.
Page 72
Auditing
Oracle's auditing mechanism provides three types of audit trails. One audit trail tracks which system privileges are used. Statement auditing keeps track of which SQL statements are used without regard to specific objects. Object-level auditing audits access to specific objects. You can initiate these audit trails to track when the statements succeed, when they fail, or both, so that all accesses are audited . You can use auditing to keep track of anyone attempting to break into the system.
In addition, you can set up how all the different types of auditing record the entries. The audit trail can record one entry per operation regardless of how many attempts are made on the operation during the connection session. Alternatively, request one entry in the audit trail for every attempt (successful or not) on the operation during the session.
If it's set up and enabled, the audit trail keeps the audit information in a data dictionary table owned by the user SYS. This table indicates the operation being audited, the user performing the operation, and the date and time of the operation. Oracle provides a set of data dictionary views to make the information in the dictionary audit table more meaningful. Although the audit trail is implemented in a data dictionary table, it keeps the insertion of rows in the audit trail even if the user rolls back his transaction.
The database administrator can clear out or archive the audit trail periodically.
Backup and Recovery
This section discusses some of the options that the architecture gives you when it comes to backing up and recovering your database. See Chapter 22, "Backup and Recovery," for more details.
Backup and Recovery Options
This section outlines at a high level some of the options available for backing up and restoring your database. We discuss the types of failure that can occur and the actions to take. The major part of this section describes preventive action to guard against loss of your database files.
This section discusses in theory the available options. The backup and recovery options mentioned here, along with other available options, are discussed in greater detail in Chapter 22.
Different Types of Failure
The major types of failure that can occur are statement failure, user process failure, machine failure, distributed transaction failure, instance failure, and disk failure/file loss.
Statement Failure
In Oracle, a DML statement such as UPDATE operates on either all the rows satisfying its where clause or none at all.
Page 73
Failure with a statement occurs for a myriad of reasons. For example, when you insert rows into a table, the table might require more storage; if the database software discovers that no more free storage is available, it returns an error message to the user. Oracle does not leave only half the rows updated. Even if the failure occurs halfway through the statement, the rows already modified are "unmodified." This is known as statement-level rollback.
Note that other DML statements in the transaction remain in a pending state ready for a commit or rollback.
User Process Failure
A user process failure occurs when the user process making the connection to the database terminates abnormally during execution. For example, the system administrator could have killed the user process. If this does occur, the Oracle background process PMON automatically rolls back any changes for the current transaction. All changes already committed by the user process are saved, but inserts , updates, and deletes since the last commit or rollback are reversed .
Also, the PMON background process releases any locks, rollback segments, and other system resources acquired by the user process when it was alive . No database administrator involvement is necessary. The database continues to function as usual, and the tables are accessible to other users. (A slight delay could occur before the locks are released.)
Machine Failure
When the machine on which the database server is running fails and shuts down (the power is turned off, for example), the Oracle instance stops running. As long as no database files are lost, the only action required of the database administrator is restarting the Oracle instance. When you do this, the SMON background process reads the online redo log files and reapplies any changes for committed transactions. Any changes that had not been committed are rolled back.
Remember that a COMMIT statement writes only the changes to the redo log files; it does not write the database blocks back to disk at the point at which the commit was issued. If the database blocks with committed changes were written to the database files before the machine failure, the SMON background process obviously does not need to reapply the changes for those blocks.
Instance Failure
Instance failure occurs when the machine is still up and running but the Oracle instance itself fails (perhaps one of the background processes was killed). This situation is very similar to machine failure in that the database administrator needs only to restart the instance; the SMON process reapplies any changes. When restarting the instance after this kind of failure, you will notice no difference from when the instance is started after a normal shutdown.
Distributed Transaction Failure
A distributed transaction is one that involves changes to more than one database. If a failure occurs during a distributed transaction, the RECO background process (if it's running) automatically synchronizes the rollbacks of the transaction's changes across all the databases involved.
EAN: 2147483647
Pages: 391