*Course 9: Managing Privileges and Roles
Privileges and roles make Oracle database security highly flexible in terms of which objects are available to which users. This course covers what you need to know for managing privileges and roles.
Access to connect to the database, the objects the user is permitted to see, and the objects the user is allowed to create — basically the use of every database object and system resource — are all controlled by privileges. You will learn about system and object privileges and how to grant and revoke privileges.
Roles take some of the complexity out of administrating user privileges. You can think of a role as a virtual user that can be granted different system and object privileges. This role can then be granted directly to the users. You will learn how to create and change roles, control their availability, and remove roles.
This course presents information relevant to the following Oracle 1Z1-031: Oracle9
i DBA Fundamentals I exam objectives:
Managing Privileges
- Identify system and object privileges
- Grant and revoke privileges
- Identify auditing capabilities
Managing Roles - Create and modify roles
- Control availability of roles
- Remove roles
- Use predefined roles
- Display role information from the data dictionary
Lesson 2. System Privileges
The two categories of privileges, system and object, control all access in an Oracle database.
After completing this lesson, you should be able to:
- Understand the difference between system and object privileges
- Identify the types of system privileges
All access in an Oracle database requires database privileges. Access to connect to the database, the objects the user is permitted to see, and the objects the user is allowed to create are all controlled by privileges.
Use of every database object and system resource is governed by privileges. There are privileges required to create objects, to access objects, to change data within tables, to execute stored procedures, to create users, and so on.
Because access to every object is governed by privileges, security in the Oracle database is highly flexible in terms of which objects are available to which users.
There are two categories of privileges:
system and
object.
System privileges control the creation and maintenance of many database objects, such as rollback segments, synonyms, tables, and triggers. Additionally, the ability to use the
analyze command and the Oracle database
audit capability is governed by system privileges.
Object privileges govern a user's ability to manipulate database objects owned by other users in the database. This lesson covers system privileges.
*System Privileges Identified
Generally speaking, there are several subcategories of system privileges that relate to each object. Those categories determine the scope of ability that the privilege grantee will have.
The subcategories of system privileges are covered on the following pages. Basically, the privileges themselves give you the ability to perform the action against your own database objects, whereas the
any keyword refers to the ability to perform the action against any database object of that type in Oracle.
Admin function privileges relate to activities typically reserved for and performed by the DBA.
Privileges include
alter system,
audit system,
audit any,
alter database,
analyze any,
SYSDBA,
SYSOPER, and
grant any privilege. Users with admin function privileges must still have the
create session privilege to connect to Oracle.
More information about
SYSDBA and
SYSOPER privileges and the activities they permit you to do is discussed elsewhere in this course.
Database access privileges control who accesses the database, when they can access it, and what they can do regarding management of their own session. Privileges include
create session,
alter session, and
restricted session.
Tablespaces are disk resources used to store database objects. Tablespace privileges determine who can maintain these disk resources, although they are typically reserved for DBAs.
Privileges include
create tablespace,
alter tablespace,
manage tablespace,
drop tablespace, and
unlimited tablespace.
You cannot grant
unlimited tablespace to a role. More information on roles appears elsewhere in the course.
User privileges are used to manage users on the Oracle database. Typically, user privileges are reserved for DBAs or security administrators. Privileges include
create user,
become user,
alter user, and
drop user.
Undo, or rollback, segments are disk resources that make aspects of transaction processing possible. Undo privileges include
create rollback segment,
alter rollback segment, and
drop rollback segment.
You only need these privileges if you plan to administer undo segments manually. If you plan to use automatic undo management, no users need to be granted these privileges.
Tables store data in the Oracle database. Table privileges govern which users can create and maintain tables.
The privileges include
create table,
create any table,
alter any table,
backup any table,
drop any table,
lock any table,
comment any table,
select any table,
insert any table,
update any table, and
delete any table.
The
create table or
create any table privileges also enable you to drop the table. The
create table privilege also allows you to create indexes on the table and run the
analyze command on the table. You must have the
drop any table privilege to be able to truncate a table.
Clusters are used to store tables commonly used together in close physical proximity on disk. Cluster privileges include
create cluster,
create any cluster,
alter any cluster, and
drop any cluster. The
create cluster and
create any cluster privileges also enable you to alter and drop those clusters.
Indexes are used to improve SQL statement performance on tables containing lots of row data.
Index privileges include
create any index,
alter any index, and
drop any index. You should note that there is no
create index system privilege.
The
create table privilege also enables you to alter and drop indexes that you own and that are associated with the table.
A synonym is a database object that enables you to reference another object by a different name. A public synonym means that the synonym is available to every user in the database for the same purpose.
The synonym privileges include
create synonym,
create any synonym,
drop any synonym,
create public synonym, and
drop public synonym. The
create synonym privilege also enables you to alter and drop synonyms that you own.
A view is an object containing a SQL statement that behaves like a table in Oracle, except that it stores no data. View privileges include
create view,
create any view, and
drop any view. The
create view privilege also enables you to alter and drop views that you own.
A sequence is an object in Oracle that generates numbers according to rules you can define. Sequence privileges include
create sequence,
create any sequence,
alter any sequence,
drop any sequence, and
select any sequence. The
create sequence privilege also enables you to drop sequences that you own.
Database links are objects in Oracle that, within your session connected to one database, enable you to reference tables in another Oracle database without making a separate connection.
A public database link is one available to all users in Oracle, whereas a private database link is one that only the owner can use.
Link privileges include
create database link,
create public database link, and
drop public database link. The
create database link privilege also enables you to drop private database links that you own.
Roles are objects that can be used for simplified privilege management. You create a role, grant privileges to it, and then grant the role to users. Role privileges include
create role,
drop any role,
grant any role, and
alter any role.
Transaction privileges are for resolving in-doubt, distributed transactions being processed on the Oracle database. Privileges include
force transaction and
force any transaction.
PL/SQL privileges enable you to create, run, and manage those different types of blocks.
The privileges include
create procedure,
create any procedure,
alter any procedure,
drop any procedure, and
execute any procedure. The
create procedure privilege also enables you to alter and drop PL/SQL blocks that you own.
Triggers are another type of PL/SQL block that execute when a specified DML activity occurs on the table to which the trigger is associated. Privileges include
create trigger,
create any trigger,
alter any trigger, and
drop any trigger. The
create trigger privilege also enables you to alter and drop triggers that you own.
Profiles are objects in Oracle that enable you to impose limits on resources for users in the machine hosting Oracle. Profile privileges include
create profile,
alter profile,
drop profile, and
alter resource cost.
Snapshots are objects in Oracle that enable you to replicate data from a table in one database to a copy of the table in another. Snapshot privileges include
create snapshot,
create any snapshot,
alter any snapshot, and
drop any snapshot.
Directories in Oracle are objects that refer to directories on the machine hosting the Oracle database. These directories are used to identify a directory that contains objects Oracle keeps track of that are external to Oracle, such as objects of the BFILE type. Directory privileges include
create any directory and
drop any directory.
Types in Oracle correspond to user-defined types you can create in the Objects Option. Privileges include
create type,
create any type,
alter any type,
drop any type, and
execute any type. The
create type privilege also enables you to alter and drop types that you own.
A library is an object that enables you to reference a set of procedures external to Oracle. Currently, only C procedures are supported. Privileges include
create library,
create any library,
alter any library,
drop any library, and
execute any library.
Question 1
Question 2
Question 3
Question 4
In this lesson, you learned that privileges control all access in an Oracle database.
You learned that object privileges control a user's ability to manipulate database objects owned by other users in the database.
You also learned that system privileges control the creation and maintenance of many database objects, including tables, undo segments, clusters, synonyms, and roles.
Lesson 3. Special and Object Privileges
Oracle provides special and object privileges to cover those tasks not taken care of by system privileges.
After completing this lesson, you should be able to:
- Understand the SYSDBA privilege
- Understand the SYSOPER privilege
- Identify object privileges
The
SYSOPER and
SYSDBA privileges are a subset of the admin function privileges. The
SYSOPER privilege is usually assigned to database operator accounts and
SYSDBA to DBA accounts. Only users who are assigned these privileges may log into the database using a password file with unrestricted privileges and perform operations on the database and database objects.
The
SYSOPER privilege enables the user assigned this privilege to do the following:
- STARTUP, SHUTDOWN
- ALTER DATABASE OPEN
- ALTER DATABASE MOUNT
- ALTER DATABASE BACKUP CONTROLFILE
- RECOVER DATABASE
- ALTER DATABASE ARCHIVELOG
The
SYSDBA privilege enables the user assigned this privilege to do the following:
- Receive all SYSOPER privileges
- CREATE DATABASE
- ALTER DATABASE BACKUP
- ALTER DATABASE BEGIN BACKUP
- ALTER DATABASE END BACKUP
- RESTRICTED SESSION
- RECOVER DATABASE
*System Privilege Restrictions
Oracle provides an option to prevent regular database users from accessing the base tables that make up the data dictionary.
Users with
UPDATE ANY TABLE should be able to update the base tables of the data dictionary and by doing so they could jeopardize the integrity of entire database.
To prevent such mishaps, Oracle provides an initialization parameter O7_DICTIONARY_ACCESSIBILITY as a dictionary protection mechanism.
If the dictionary protection is enabled by setting O7_DICTIONARY_ACCESSIBILITY to FALSE, then access to objects in SYS schema (containing the data dictionary base tables) is restricted to users who connect as SYSDBA or SYS.
Users with system privileges providing access to any schema will not be allowed access to SYS schema objects. This prevents non-DBA users from accessing the base tables that make up the data dictionary views and from modifying them.
By default, this parameter is set to FALSE. By setting this parameter to TRUE, the users whose system privilege enables access to objects in any schema are also given access to SYS schema objects.
*Identifying Object Privileges
The other category of privileges granted on the Oracle database is the set of
object privileges. Object privileges permit the owner of database objects, such as tables, to administer access to those objects according to the following types of access. The eight types of object privileges are as follows:
- select
- Permits the grantee of this object privilege to access the data in a table, sequence, view, or snapshot.
- insert
- Permits the grantee of this object privilege to insert data into a table or, in some cases, a view.
- update
- Permits the grantee of this object privilege to update data into a table or view.
- delete
- Permits the grantee of this object privilege to delete data from a table or view.
- alter
- Permits the grantee of this object privilege to alter the definition of a table or sequence only. The alter privileges on all other database objects are considered system privileges.
- index
- Permits the grantee of this object privilege to create an index on a table already defined.
- references
- Permits the grantee to create or alter a table in order to create a foreign key constraint against data in the referenced table.
- execute
- Permits the grantee to run a stored procedure or function.
A trick to being able to distinguish whether something is a system or object privilege is as follows. Because there are only eight object privileges, you can memorize them. If you see a privilege that is not one of the eight object privileges, it is a system privilege.
Question 5
Question 6
Question 7
Topic 3.1 Exercises
* Exercise 1
Try changing the system privilege restrictions for the base tables that make up the data dictionary in Oracle.
Examine the following table
| Step | Action |
| 1 | Please Note: For the exercises in this course, you should set up an isolated Oracle9i system for practice. Do not use your production Oracle9i system for these practice exercises.
Change the initialization parameter for the dictionary protection mechanism to allow users to update the base tables of the data dictionary. |
| 2 | Sign on as a user and attempt to update the base tables. |
| 3 | Change the initialization parameter back to restrict users from changing the base tables. |
| 4 | Sign on again as a user and attempt to update the base tables. |
In this lesson, you learned about the
SYSOPER and
SYSDBA privileges, which allow users to log into the database with unrestricted privileges.
You also learned about the O7_DICTIONARY_ACCESSIBILITY initialization parameter, which prevents a regular database user from accessing the base tables that make up the data dictionary and possibly jeopardizing the integrity of the entire database.
Finally, you learned about the eight object privileges that allow the owner of an object to administer access to those objects. These privileges include
select,
insert,
update,
delete,
alter,
index,
references, and
execute.
Lesson 4. Granting and Revoking Privileges
System and object privileges can be granted to users, database objects, and columns within the database object.
After completing this lesson, you should be able to:
- Grant privileges to users
- Grant column privileges
- Understand the use of the execute privilege
Giving system and object privileges to users is done with the
grant command. System privileges are first given to the SYS and SYSTEM users, and to any other user with the
grant any privilege permission. As other users are created, they must be given privileges, based on their needs, with the
grant command. For example, executing the following
grant statements provides the user SPANKY the ability to create a table and object privileges on another table in the database:
GRANT CREATE TABLE TO spanky; -- system
GRANT SELECT, UPDATE ON athena.emp TO spanky; -- object
To grant object privileges to others, you must own the database object, or you must have been given the object privilege
with grant option.
In addition to database objects, privileges can also be granted on columns within the database object. The privileges that can be administered on the column level are the
insert,
update, and
references privileges.
However, the grantor of column privileges must be careful when administering them in order to avoid problems — particularly with the
insert privilege.
If a user has the
insert privilege on several columns in a table but not all columns, the privilege administrator must ensure that all of the columns in the table that have the
insert privilege granted are NULL columns.
Consider the following example. Table EMP has two columns: NAME and EMPID. Both columns have not NULL constraints on them. The
insert access is granted for the EMPID column to SPANKY, but not the NAME column.
When SPANKY attempts to
insert an EMPID into the table, Oracle generates a NULL for the NAME column, and then produces an error stating that the user can't
insert a not NULL value into the NAME column because the column has a not NULL constraint on it.
Administration of
update and
insert object privileges at the column level must be handled carefully, whereas using the
references privilege on a column level seems to be more straightforward.
Some special conditions relate to the use of the
execute privilege.
If a user has the ability to execute a stored procedure owned by another user, and the procedure accesses some tables, the object privileges required to access those tables must be granted to the owner of the procedure, and not the user to whom
execute privileges were granted.
What's more, the privileges must be granted directly to the user, not through a role. When a user executes a stored procedure, the user is able to use whatever privileges are required to execute the procedure.
For example,
execute privileges are given to SPANKY on procedure
process_deposit( ) owned by ATHENA, and this procedure performs an update on the BANK_ACCOUNT table using an
update privilege granted to ATHENA.
SPANKY will be able to perform that
update on BANK_ACCOUNT via the
process_deposit( ) procedure even though the
update privilege is not granted to SPANKY.
However, SPANKY will
not be able to issue an
update statement on table BANK_ACCOUNT from SQL*Plus, because the appropriate privilege was not granted to SPANKY directly.
Question 8
Question 9
In this lesson, you learned how to give system and object privileges to users using the
grant command.
You also learned how to grant the
insert,
update, and
references privileges on columns within a database object.
Finally, you learned how to grant the
execute privilege and the special conditions that relate to its use.
Lesson 5. Granting Administrative Abilities
Granting administrative abilities gives users the ability to grant and revoke system privileges to others.
After completing this lesson, you should be able to:
- Grant administrative abilities with privileges
- Revoke system and object privileges
- Obtain dictionary data on privileges
When the first two statements below are executed, SPANKY will have the ability to execute the
create table command in her user schema and to
select and
update row data on the EMP table in ATHENA's schema.
However, SPANKY can't give these privileges to others, nor can she relinquish them without the help of the DBA. In order to give user SPANKY some additional power to administer to other users the privileges granted to her, the owner of the object can execute the second two statements:
GRANT CREATE TABLE TO spanky; -- system
GRANT SELECT, UPDATE ON athena.emp TO spanky; -- object
GRANT CREATE TABLE TO spanky WITH ADMIN OPTION; -- system privileges
GRANT SELECT, UPDATE ON emp TO spanky WITH GRANT OPTION; -- object privileges
The
with admin option clause gives SPANKY the ability to give or take away the system privilege to others. Additionally, if a role is granted to SPANKY
with admin option, SPANKY can alter the role or even remove it.
The
with grant option clause for object privileges gives SPANKY the same kind of ability as
with admin option for system privileges. SPANKY can
select and
update data from EMP, and can give that ability to others as well. Only privileges given
with grant option or
with admin option can be administered by the grantee.
Additionally, there is a consolidated method for granting object privileges using the keyword
all. Note that
all in this context is not a privilege; it is merely a specification for all object privileges for the database object.
GRANT CREATE TABLE TO spanky WITH ADMIN OPTION; -- system privileges
GRANT SELECT, UPDATE ON emp TO spanky WITH GRANT OPTION; -- object privileges
GRANT ALL ON emp TO spanky;
*Revoking System Privileges from Users
There may also come a time when users must have privileges revoked as well. This task is accomplished with the
revoke command, as shown below. Revoking the
create table privilege also takes away any administrative ability given along with the privilege or role. No additional syntax is necessary for revoking either a system privilege granted
with admin option or an object privilege granted
with grant option.
REVOKE CREATE TABLE FROM spanky;
REVOKE SELECT, UPDATE ON emp FROM spanky;
In the same way, roles can be revoked from users, even if the user created the role and thus has the
admin option.
The ability to revoke any role comes from the
grant any role privilege, whereas the ability to grant or revoke certain system privileges comes from being granted the privilege with the
admin option.
When a system privilege is revoked, there are no cascading events that take place along with it. Thus, if SPANKY created several tables while possessing the
create table privilege, those tables are not removed when the privilege is revoked. Only the
drop table command will remove the tables.
Understand the following scenario completely before continuing: User X has a system privilege granted to her,
with admin option. User X then grants the privilege to user Y, with the administrative privileges. User Y does the same for user Z. Then X revokes the privilege from user Y. User Z will still have the privilege. Why? Because there is no cascading effect to revoking system privileges other than the fact that the user no longer has the privilege.
*Revoking Object Privileges from Users
By contrast, when an object privilege is revoked, there are some cascading events.
If you have the
update privilege on SPANKY's EMP table and SPANKY revokes it, then you will not be able to change records in the table.
The rows you've already changed don't get magically transformed back the way they were before.
However, there are several considerations to make when revoking object privileges. For instance, if a privilege has been granted on two individual columns, the privilege cannot be revoked on only one column — the privilege must be revoked entirely and then re-granted, if appropriate, on the individual column.
Also, if the user has been given the
references privilege and used it to create a foreign key constraint to another table, then there is some cascading that must take place in order to complete the revocation of the
references privilege.
In this example, not only is the privilege to create referential integrity revoked, but any instances where that referential integrity was used on the database are also revoked. If a foreign key constraint was created on the EMP table by user SPANKY, and the
revoke statement was issued without the
cascade constraints clause, then the
revoke statement will fail.
REVOKE REFERENCES ON emp FROM spanky CASCADE CONSTRAINTS;
Understand this fact sequence before proceeding. User X grants user Y an object privilege,
with grant option, and user Y then grants the same privilege to user Z. When user X revokes the object privilege for user Y, user Z will also have that privilege revoked. This is because Oracle cascades the revocation of object privileges.
*Open to the Public
Another aspect of privileges and access to the database involves a special user on the database. This user is called PUBLIC.
If a system privilege, object privilege, or role is granted to the PUBLIC user, then every user in the database has that privilege.
Typically, it is not advised that the DBA should grant many privileges or roles to PUBLIC, because if the privilege or role ever needs to be revoked, then every stored package, procedure, or function will need to be recompiled.
*Dictionary Information on Privileges
To display privileges associated with users and roles, you can use the following views:
- DBA_SYS_PRIVS Shows all system privileges associated with this user.
- DBA_TAB_PRIVS Shows all object privileges associated with this user.
- SESSION_PRIVS Shows all privileges available in this session for this user.
You can find information about system privileges granted to all users in the DBA_SYS_PRIVS view and the privileges available to you as the current user in the session using the SESSION_PRIVS dictionary view. You can also find information about the object privileges granted in the database with the DBA_TAB_PRIVS and DBA_COL_PRIVS dictionary views.
Question 10
Question 11
Question 12
Question 13
In this lesson, you learned how to grant administrative abilities with privileges for users so that they can then give system privileges to others or take them away.
You also learned how to revoke system and object privileges from users. You learned that when revoking system privileges, there are no cascading events, but there are when you revoke object privileges.
Finally, you learned how to obtain dictionary data on privileges associated with users and roles, using the DBA_SYS_PRIVS, DBA_TAB_PRIVS, and SESSION_PRIVS views.
Lesson 6. Identifying Audit Capabilities
You can use auditing to monitor your Oracle database for suspicious or inappropriate use.
After completing this lesson, you should be able to:
- Identify audit capabilities
- Understand database auditing
- Understand value-based auditing
Securing the database against inappropriate activity is only one part of the total security package Oracle offers the DBA or security administrator of an Oracle database.
The other major component of the Oracle security architecture is the ability to monitor database activity to uncover suspicious or inappropriate use. Oracle provides this functionality via the use of database auditing.
Auditing your database requires a good deal of additional space allocated to the SYSTEM tablespace for storing the audit data generated.
Several things about your database are always audited.
They include privileged operations that DBAs typically perform, such as starting and stopping the instance and logins
as sysdba or
as sysoper.
You can find information about these activities in the ALERT log on your database, along with information about log switches, checkpoints, and tablespaces taken offline or put online.
You can also configure system-wide auditing with the AUDIT_TRAIL
initsid.ora parameter. Valid values for this parameter include DB (TRUE), operating system (FALSE), or NONE.
DB indicates that the database architecture will be used to store audit records.
You can alternately specify TRUE for AUDIT_TRAIL to accomplish the same result DB gives.
Operating system indicates that the audit trail will be stored externally to Oracle, using some component of the operating system. You also specify FALSE for AUDIT_TRAIL to accomplish the same result operating system gives.
Finally, NONE indicates that no database auditing will be conducted at all. After changing the value set for this parameter, the instance must be shut down and started again.
When AUDIT_TRAIL is set to operating system, your audit trail information will be stored in the directory named by the AUDIT_FILE_DEST parameter, which is set to the
rdbms/audit directory under your Oracle software home directory by default. When AUDIT_TRAIL is set to DB, your audit trail information is stored in the AUD$ table owned by SYS.
*Database Auditing
There is a difference between database auditing and value-based auditing.
Database auditing pertains to audits on database object access, user session activity, startup, shutdown, and other database activity.
The information about these database events is stored in the audit trail, and the information can then be used to monitor potentially damaging activities, such as rows being removed from tables.
The data can also be used by the DBA for statistical analysis of database performance over time.
*Value-Based Auditing
Value-based auditing pertains to audits on actual column/row values that are changed as the result of database activity. The Oracle audit trail does not track value-based audit information, so instead you must develop triggers, tables, PL/SQL code, or client applications that handle this level of auditing in the database.
A good example of value-based auditing in a package delivery application would be to track status changes on existing deliveries from the time the order is received to the time it is delivered.
Customers can then call in or access the system via the Web to find out the package delivery status. Each time the package reaches a certain milestone, such as "picked up at local office" or "signed over to recipient," the delivery status is updated and a historical record is made of the old status, the time of status change, and the username of the person making the change.
However, as you might imagine, value-based auditing is specific to an application. Thus, the DBA will focus much of her time managing database auditing with Oracle's audit features.
*Using Database Auditing
A database audit is most effective when the DBA or security administrator knows what he is looking for.
The best way to conduct a database audit is to start the audit with a general idea about what may be occurring on the database.
Once the goals are established, set the audit to monitor those aspects of database use and review the results to either confirm or disprove the hypothesis.
Conducting an audit in this way is important because database auditing generates lots of information about database access.
If the DBA tries to audit everything, the important facts will get mixed into a great deal of unnecessary detail.
With a good idea about the general activity that seems suspicious as well as knowledge of the types of statements or related objects on the database that should be looked at, the DBA can save a lot of time sorting through excess detail later.
Question 14
Question 15
Question 16
Question 17
In this lesson, you learned how to identify audit capabilities including finding information about audited activities in the ALERT log, as well as configuring the AUDIT_TRAIL parameter to store audit records.
You also learned that database auditing performs audits on database object access, user session activity, startup, shutdown, and other database activity.
Finally, you learned that value-based auditing performs audits on actual column/row values that are changed as the result of database activity.
Lesson 7. Using the Audit Command
The audit command allows you to set up the monitoring of database activities.
After completing this lesson, you should be able to:
- Use the audit command for privilege or statement audits
- Use the audit command for object audits
- Use audit definition shortcuts
You do not need to set the AUDIT_TRAIL
init.ora parameter in order to use the
audit SQL command to set up the auditing options you want to use.
You can set auditing features to monitor database activities including starting, stopping, and connecting to the database. Or you can set up audits on statements involving the creation or removal of database objects.
Additionally, you can set up audits on direct database use, such as table updates or inserts.
The general syntax for setting up auditing on statements or system privileges is as follows. State the name of the statement (such as
update) or system privilege (such as
create table) that will be audited. Then state which users will be monitored, either
by username,
by session, or
by access.
Finally, state whether or not the audit should record successful or unsuccessful executions of the activity in question. The following code block shows an example of an
audit statement:
AUDIT CREATE TABLE, ALTER TABLE, DROP TABLE
BY spanky
WHENEVER SUCCESSFUL;
The following statement demonstrates how you can record the data-change operations that happen on a particular table using the
by access clause:
AUDIT UPDATE, DELETE
ON spanky.cat_toys
BY ACCESS
WHENEVER NOT SUCCESSFUL;
Consider some other unique features in the
audit syntax. The person setting up audits need not name particular users on which to monitor activity. Rather, the activities of this sort can be monitored every time the statement is issued with the
by access clause.
Additionally, when the
not successful option is specified, audit records are generated only when the command executed is unsuccessful. The omission of clauses from the audit syntax causes
audit to default to the widest scope permitted by the omission.
For example, an audit can be conducted on all inserts on table PRODUCTS, regardless of user and completion status, by omitting the
by and
whenever clauses:
AUDIT INSERT ON products;
You can use the
default option of the
audit command to specify auditing options for objects that have not yet been created. Once you have established these default auditing options, any subsequently created object is automatically audited with those options. The following code block demonstrates use of the
default keyword:
AUDIT INSERT
ON DEFAULT
WHENEVER SUCCESSFUL;
*Using the audit Command for Object Audits
Any privilege that can be granted can also be audited. However, because there are more than 100 system and object privileges that can be granted on the Oracle database, the creation of an
audit statement can be an excessively long task.
As an alternative to naming each and every privilege that goes along with a database object, Oracle enables the administrator to specify the name of an object or type of object to audit, and Oracle will audit all privileged operations. Instead of listing all privileged operations related to the type of object that would be audited, the security administrator can name the type of object and achieve the desired result.
In the statement shown below, any table that is successfully modified by the user spanky will be audited.
AUDIT TABLE
BY spanky
WHENEVER SUCCESSFUL;
Finally, the person setting up auditing can also specify that audit records are to be compiled by session. This means that
audit will record data for audited activities in every session, as opposed to
by access. Eliminating the
when successful clause tells
audit to record every table creation, alteration, or drop activity for every session that connects to the database, regardless of whether or not they were successful.
AUDIT TABLE
BY SESSION;
*Using Audit Definition Shortcuts
There are other options available to consolidate the specification of database activities into one easy command for auditing. These commands are listed in the following:
- connect
- Audits the user connections to the database. It can be substituted with session for the same effect. connect audits the login and logout activities of every database user.
- resource
- Audits detailed information related to the activities typically performed by an application developer or a development DBA, such as creating tables, views, clusters, links, stored procedures, and rollback segments.
- dba
- Audits activities related to "true" database administration, including the creation of users and roles, and granting system privileges and system audits.
- all
- Is the equivalent of an on/off switch, where all database activities are monitored and recorded.
A single PL/SQL procedure or SQL statement may reference several different objects or statements being audited. Thus, many audit trail entries can be produced by one single statement.
Question 18
Question 19
Question 20
Topic 7.1 Exercises
* Exercise 1
Try using the
audit command in Oracle9
i.
Examine the following table
| Step | Action |
| 1 | Please Note: For the exercises in this course, you should set up an isolated Oracle9i system for practice. Do not use your production Oracle9i system for these practice exercises.
Set up auditing for a system privilege, monitoring by access, and have it record successful executions of the privilege being audited. |
| 2 | View the records for this audit. |
| 3 | Set up auditing for an object and have it record all activity for every session that connects to the database, regardless of whether or not the activity is successful. |
| 4 | View the records for this audit. |
In this lesson, you learned how to use the
audit command to set up audits on database activity, audits on statements involving the creation or removal of database objects, and audits on direct database use.
You also learned that you can use the
audit command, specify the name of an object to be audited, and Oracle will audit all privileged operations on that object.
Finally, you learned there are four shortcut auditing options —
connect,
resource,
dba, and
all — that combine the specification of database activities into one easy command.
Lesson 8. Managing Auditing
Once auditing is set up, you must be able to manage the information from auditing and disable auditing when needed.
After completing this lesson, you should be able to:
- Disable audit configuration
- View audit options in the data dictionary
- Manage audit information
There are two methods used to disable auditing. The first method is to change the initialization parameter AUDIT_TRAIL to NONE or FALSE. On database shutdown and restart, this option will disable the audit functionality on the Oracle database.
Note, however, that because you don't need to set AUDIT_TRAIL in order to use the SQL
audit command, the other option used for changing the activities that
audit will record is called
noaudit.
This option can be executed in two ways. The first is used to turn off selective areas that are currently being audited, as shown below.
NOAUDIT INSERT ON application.products;
In some cases, however, the person conducting the audit may want to shut off all auditing processes going on and simply start auditing over again. Perhaps the auditor has lost track of which audits were occurring on the database. All auditing can be turned off using the ALL option. This statement can be further modified to limit turning off auditing to a particular database object.
NOAUDIT ALL;
NOAUDIT ALL PRIVILEGES;
NOAUDIT ALL ON application.products;
*Viewing Enabled Auditing Options
The following views offer information about the enabled audit options configured in the Oracle database:
- DBA_OBJ_AUDIT_OPTS
- A list of auditing options for views, tables, and other database objects
- DBA_PRIV_AUDIT_OPTS
- A list of auditing options for all privileges on the database
- DBA_STMT_AUDIT_OPTS
- A list of auditing options for all statements executed on the database
- ALL_DEF_AUDIT_OPTS
- A list of all default options for auditing database objects
*Retrieving and Maintaining Auditing Information
The following data dictionary views are used to find results from audits currently taking place in the Oracle database. These views are created by the
cataudit.sql script found in
rdbms/admin under the Oracle software home directory. This script is run automatically at database creation by the
catalog.sql script.
Some additional audit information is stored in the ALERT log, as explained elsewhere, and more audit information will be stored in an operating system file if operating system auditing is used.
- DBA_AUDIT_EXISTS
- A list of audit entries generated by the exists option of the audit command
- DBA_AUDIT_OBJECT
- A list of audit entries generated for object audits
- DBA_AUDIT_SESSION
- A list of audit entries generated by session connects and disconnects
- DBA_AUDIT_STATEMENT
- A list of audit entries generated by statement options of the audit command
- DBA_AUDIT_TRAIL
- A list of all entries in the AUD$ table collected by the audit command
*Managing Audit Information
Once created, all audit information will stay in the AUD$ table owned by SYS. In cases where several auditing options are used to gather information about database activity, the AUD$ table can grow to be large.
In order to preserve the integrity of other tables and views in the data dictionary, and to preserve overall space in the SYSTEM tablespace (where all data dictionary objects are stored), the DBA or security administrator must periodically remove data from the AUD$ table, either by deleting or by archiving and then removing the records.
Additionally, in the event that audit records on an Oracle database are being kept to determine whether there is suspicious activity, the security administrator must take additional steps to ensure that the data in the AUD$ table is protected from tampering.
Above all else in handling database audits for inappropriate activity is the importance of protecting the evidence. The DBA must ensure that no user can remove records from the audit logs undetected. Therefore, a key step in auditing is to audit the audit trail.
This step might include write-protecting the
$ORACLE_HOME/rdbms/audit directory using operating system commands (such as
chmod in UNIX), and it might also include monitoring the removal of data from the SYS.AUD$ table, as demonstrated in the following code block:
AUDIT delete ON sys.aud$;
You may want to move the AUD$ table outside the SYSTEM tablespace because of the volatile and high-growth nature of audit data. To do so, create another table with AUD$ data, using the
alter table move tablespace statement. Next, drop AUD$ and rename your other table to AUD$.
Next, create one index on the new AUD$ table on the SESSIONID and SES$TID columns in the new tablespace (storing the index outside of the SYSTEM tablespace, of course).
Finally, grant
delete on the new AUD$ table to DELETE_CATALOG_ROLE.
In order to prevent a problem with storing too much audit data, the general guideline in conducting database audits is to record enough information to accomplish the auditing goal without storing a lot of unnecessary information.
The amount of information that will be gathered by the auditing process is related to the number of options being audited and the frequency of audit collection (namely,
by username,
by access, and
by session).
What if problems occur because too much information is collected? To remove records from AUD$, a user with the
delete any table privilege, the SYS user, or a user to whom SYS has granted
delete access to AUD$ must log onto the system and remove records from AUD$.
Before doing so, however, it is generally advisable for archiving purposes to make a copy of the records being deleted.
This task can be accomplished by copying all records from AUD$ to another table defined with the same columns as AUD$, spooling a
select statement of all data in AUD$ to a flat file, or using EXPORT to place all AUD$ records into a database dump file.
After this step is complete, all or part of the data in the AUD$ table can be removed using either
delete from AUD$ or
truncate table AUD$. But remember to protect the audit trail using methods already outlined.
Question 21
Question 22
Question 23
In this lesson, you learned that you can use the
NOAUDIT command to disable auditing on certain areas being audited, all auditing processes taking place, or just for a particular database object.
You also learned how to use the data dictionary views to obtain information about currently enabled audit options and to find results from audits currently taking place.
Finally you learned how to manage audit information, including how to protect, archive and delete the data in the AUD$ table.
Lesson 9. Creating and Modifying Roles
Roles take some of the complexity out of administrating user privileges.
After completing this lesson, you should be able to:
- Create roles
- Grant roles
- Modify roles
As users add more objects to the database, privilege management can become a nightmare. This is where roles come in.
Roles are named, logical groupings of privileges that can be administered more easily than the individual privileges.
Before creating roles, the DBA determines which types of users exist on the database and which privileges on the database can be logically grouped together. Roles are created on databases in the following manner.
*Creating Roles
In order to create a role that will support user privilege management, one of the following statements can be executed. Once the role is created, there are no privileges assigned to it until you explicitly grant privileges to the role. The following statement can be used to create a role in Oracle:
CREATE ROLE role_name;
The following code block shows a variant of the
create role command, where we explicitly state that this role does not require a password in order to be enabled for the user. This is the default behavior for roles in Oracle. The statement creates a role named
cat_priv.
CREATE ROLE cat_priv NOT IDENTIFIED;
You can also set up the role to require the user to supply a password whenever the role is to be enabled for use. This role must be enabled by the user before the privileges associated with the role can be utilized. This statement requires the role
cat_priv to use the password
meow.
CREATE ROLE cat_priv IDENTIFIED BY meow;
*Granting Roles
Once created, roles must have privileges granted to them. The DBA must determine which object and system privileges each class of user will require. Privileges are granted to roles in the following manner.
Instead of granting the privileges directly to users, you grant privileges to the roles themselves.
GRANT SELECT, INSERT, UPDATE ON cat_food TO cat_privs;
GRANT SELECT, INSERT, UPDATE ON litter_box TO cat_privs;
GRANT SELECT ON fav_sleeping_spots TO cat_privs;
Roles enable dynamic privilege management as well. If several users already have a role granted to them, and you create a new table and grant
select privileges on it to the role, then all the users who have the role will be able to
select data from your table.
Once granted, the ability to use the privileges granted via the role is immediate. Roles can be granted to other roles as well.
However, you should take care not to grant a role to itself (even via another role) or else Oracle will return an error.
*Granting Roles to Users
Once you've created a role and granted some privileges to it, you must then grant the role to a user. This allows the user to have access to the privileges the role is meant to manage. You give the role to a user with the
grant command and take it away with the
revoke command. The following code block shows how to
grant a role to a user:
GRANT cat_privs TO spanky;
*Passwords and Roles
Using a password to authenticate users of a role is optional. If used, however, the password provides an extra level of security over the authentication process at database login.
For heightened security when using roles with passwords, set the role authenticated by a password to be a nondefault role for that user. That way, if the user tries to execute a privilege granted via the role, she will first have to supply the role's password.
Like users, roles have no owner, nor are they part of a schema. The name of a role must be unique among all roles and users of a database.
*Altering Roles
Later on, you may want to change a role using the
alter role command. All items that are definable in
create role are also definable using
alter role, as shown in the following code block:
ALTER ROLE role_name NOT IDENTIFIED;
ALTER ROLE role_name IDENTIFIED BY role_password;
*Administrative Ability and Roles
By default, only the user who creates the role has administrative privileges over that role, including the ability to grant that role to other users.
Other users can, of course, grant privileges to the role — so long as they are permitted to grant those privileges. However, like privileges, roles can be granted with an administrative ability to users.
This is accomplished using the
grant role_name to user_name with admin option, the same option as that used for granting administrative abilities with system privileges.
Question 24
Question 25
Question 26
Question 27
Topic 9.1 Exercises
* Exercise 1
Try creating, granting, and modifying a role in Oracle 9
i.
Examine the following table
| Step | Action |
| 1 | Please Note: For the exercises in this course, you should set up an isolated Oracle9i system for practice. Do not use your production Oracle9i system for these practice exercises.
Create a role named SALES that is does not require a password. |
| 2 | Grant insert and update privileges for a table to the role. |
| 3 | Grant the role to a user. |
| 4 | Alter the role to require a password. |
In this lesson, you learned how to create roles using the
CREATE ROLE command and that you can specify whether or not a user needs to supply a password when the role is enabled.
You also learned how to grant privileges to roles and how to grant roles to users using the
GRANT command.
Finally, you learned how to modify roles after they have been created using the
ALTER ROLE command.
Lesson 10. Controlling Availability of Roles
Controlling the availability of roles allows you to set default roles for users.
After completing this lesson, you should be able to:
- Enable roles
- Disable roles
- Remove roles
A user may have several roles granted when he logs on. By default, all the roles assigned to a user are enabled at logon without the need of a password.
Some, all, or none of these roles can be set as default roles, which means that the privileges given via the role will be available automatically when the user logs on to Oracle.
There is no limit to the number of roles that can be granted to a user; however, if there are privileges granted to a user through a nondefault role, the user may have to switch roles in order to use those privileges.
All roles granted to a user are default roles initially. You can change which roles are default roles after granting the role to the user with the
alter user statement. The
alter user default role all statement, as shown below sets all roles granted to SPANKY to be default roles.
Other options available for specifying user roles include explicitly listing one or more roles that are to be the default, or specifying all roles except for the ones named using
all except (role_name, another_role_name) or
none.
ALTER USER spanky DEFAULT ROLE ALL;
The following statement sets the ORG_USER and ORG_DEVELOPER roles as default roles for user SPANKY:
ALTER USER spanky DEFAULT ROLE org_user, org_developer;
In the previous statement, the roles ORG_USER and ORG_DEVELOPER are assigned as the default roles, and the rest of the granted roles are not default roles for the user SPANKY. The following statement makes the ORG_MGR role the only nondefault role SPANKY has:
ALTER USER spanky DEFAULT ROLE ALL EXCEPT (org_mgr);
The following statement makes SPANKY have no default role set up at login. The only privileges that the user SPANKY has at login will be those privileges assigned directly to the user.
ALTER USER spanky DEFAULT ROLE NONE;
Note that
default role is an option used for only the
alter user statement. You do not define a default role in
create user because no roles have been granted to the user yet. Keep in mind the default roles are subsets of roles granted to the user that are enabled automatically when the user logs into Oracle.
*Enabling or Disabling Roles
When a user logs in, the user's default roles are automatically activated. However, it is possible to enable and disable roles that are not default roles using the
set role command.
Those roles enabled or disabled using the
set role command will apply as long as the user session is active. Once the user exits from the session and logs back into Oracle, only the default roles will again be active. The following code block shows an example of the
set role command:
SET ROLE cat_privs;
Note that, if the
cat_privs role requires a password, you must supply the password when you enable the role using the
set role command. The following code block shows this syntax:
SET ROLE cat_privs IDENTIFIED BY meow;
You can also enable many roles at once (so long as none of the roles requires a password) using the
set role all command. You can also exclude certain roles from being enabled using the
set role all except role_name command. Additionally, you can disable all roles granted to you using the
set role none command.
The DBMS_SESSION package contains a procedure called
set_role( ), which is equivalent to the
set role statement.
It can enable or disable roles for a user and can be issued from Oracle forms, reports, anonymous blocks, or any other tool that enables PL/SQL, except for stored PL/SQL functions, procedures, and packages.
Finally, note that enabling or disabling a role using the
set role command does not change whether or not the role is a default role for this user. Only the
alter user default role command issued by the DBA can change whether the role is a default role for the user.
ALTER USER spanky DEFAULT ROLE org_user;
*Removing Roles
Another way to restrict role use is to revoke the role from the user. This is accomplished with the
revoke command in the same way that a privilege is revoked. The effect is immediate — the user will no longer be able to use privileges associated with the role.
You can drop a role to restrict its use as well. You don't need to revoke the role from users before dropping it — Oracle handles that task for you. However, you must have the
drop any role privilege or have been granted the role
with admin option in order to drop it.
REVOKE cat_privs FROM spanky;
DROP ROLE cat_privs;
In order to drop a role, you must either have been granted the role
with admin option (which will be the case, if you created the role), or you must have a
DROP ANY ROLE system privilege.
Question 28
Question 29
Question 30
Question 31
Topic 10.1 Exercises
* Exercise 1
Try controlling the availability of roles in Oracle 9
i.
Examine the following table
| Step | Action |
| 1 | Please Note: For the exercises in this course, you should set up an isolated Oracle9i system for practice. Do not use your production Oracle9i system for these practice exercises.
Set two roles as default for a user. |
| 2 | Set no roles as default for the user. |
| 3 | Set all roles as default for the user. |
| 4 | Revoke a role from the user. |
| 5 | Remove a role from the database. |
In this lesson, you learned how to change which roles are default using the
alter user statement.
You also learned how to enable and disable roles using the
set role command, which applies as long as the user session is active.
Finally you learned how to remove roles using the
revoke command to restrict the role and the
drop role command to remove it from the database.
Lesson 11. Using Predefined Roles
There are some special, predefined roles available to the users of a database.
After completing this lesson, you should be able to:
- Recognize predefined roles
- Use predefined roles
The predefined roles available at database creation in Oracle7 and higher include the first five roles below. Additionally, Oracle8
i and higher adds the last three roles to the mix.
- CONNECT Enables the user extensive development abilities within his or her own user schema, including the ability to use create table, create cluster, create session, create view, create sequence, and more. The privileges associated with this role are platform-specific, and therefore the role can contain a different number of privileges, but typically the role never enables the creation of stored procedures.
- RESOURCE Enables the user moderate development abilities within his or her own user schema, such as the ability to execute create table, create cluster, create trigger, and create procedure. The privileges associated with this role are platform-specific, and therefore the role can contain a different number of privileges.
- DBA Enables the user to administer and use all system privileges.
- EXP_FULL_DATABASE Enables the user to export every object in the database using the EXPORT utility.
- IMP_FULL_DATABASE Enables the user to import every object from an export dump file using the IMPORT utility.
- DELETE_CATALOG_ROLE Extends delete privileges on SYS-owned dictionary tables in response to the new restriction on delete any table privileges that prevents grantees from removing rows from SYS-owned dictionary tables.
- EXECUTE_CATALOG_ROLE Enables the user to receive execute privileges on any SYS-owned package supplied with the Oracle software.
- SELECT_CATALOG_ROLE Enables the user to select data from any SYS-owned dictionary table or view.
*Using Other Predefined Roles
Other optional, predefined roles are available in Oracle and are usually defined by the DBA using SQL scripts provided with the database.
For example, AQ_ADMINISTRATOR_ROLE and AQ_USER_ROLE are created by the
dbmsaqad.sql script. These roles are used with the advanced queuing feature in the Oracle database.
Other roles you might find on your database include PLUSTRACE, which is created by running the
plustrce.sql script for setup of the autotrace feature in SQL*Plus.
Question 32
Question 33
Question 34
Question 35
In this lesson, you learned how to recognize eight predefined roles, including CONNECT, RESOURCE, and DBA, and three other optional predefined roles including AQ_USER_ROLE and PLUSTRACE. In addition, you learned how these predefined roles are used in Oracle.
Lesson 12. Displaying Role Information
The data dictionary can give you information about roles in your Oracle database.
After completing this lesson, you should be able to:
- Display role information from the data dictionary
- Use fine-grained access control
You can find information about the roles created in your Oracle database in the data dictionary. Following are the various views available for finding information about your created roles:
- DBA_ROLES All the roles created on the database and whether a password is required to use each role.
- DBA_ROLE_PRIVS All users and the roles granted to them in the database.
- ROLE_ROLE_PRIVS All the roles and the roles that are granted to them in the database.
- DBA_SYS_PRIVS All the roles and users who have been granted system privileges and the granted system privileges.
- ROLE_SYS_PRIVS All the system privileges granted only to roles in Oracle.
- ROLE_TAB_PRIVS All the object privileges granted only to roles in Oracle.
- SESSION_ROLES All the roles available in the current session of Oracle.
*Fine-Grained Access Control
Fine-grained access control enables you to implement security policies with functions and then associate those security policies with tables or views. The database server automatically enforces those security policies.
You can use different policies for
select,
insert,
update, and
delete. You can also use security policies only where you need them (for example, on salary information).
Finally, you can use more than one policy for each table, including building on top of base policies in packaged applications.
The function or package that implements the security policy you create returns a predicate (a
where condition). This predicate controls access as set out by the policy. Rewritten queries are fully optimized and sharable. The PL/SQL package DBMS_RLS enables you to administer your security policies. Using this package, you can add, drop, enable, disable, and refresh the policies you create.
Question 36
Question 37
Question 38
Question 39
In this lesson, you learned how to display role information from the data dictionary about roles created in your Oracle database, including which views you can use to find specific information.
You also learned that you can use security policies with functions and associate the policies with tables or views in order to obtain fine-grained access control over your database.
Lesson 13. Course In Review
This lesson includes questions and material covering the entire course. You can use it to review and study for the exam.
Topic 13.1 Review Notes
*Review Notes
Examine the following table
| 1. | Be sure you can distinguish system privileges from object privileges in Oracle. The easiest way to do so is to memorize the object privileges, and assume everything else is a system privilege.
|
| 2. | Know the purpose of the SYSDBA and SYSOPER privileges. These "superuser" privileges give users the ability to perform administrative tasks on the database.
|
| 3. | Know how to give system and object privileges to users and how to take them away. Also, understand which clauses are required for giving the ability to administrate other users' ability to have a privilege for both system and object privileges.
|
| 4. | Understand the role of the PUBLIC user in Oracle: when privileges are given to PUBLIC, every user has the privilege.
|
| 5. | Know where to look in the data dictionary for information about privileges granted to users in Oracle.
|
Examine the following table
| 6. | Understand the purpose served by auditing in the database and the difference between database auditing and value-based auditing.
|
| 7. | Know that the auditing feature in Oracle is enabled using the audit command.
|
| 8. | Know that all audit records are stored in the SYS.AUD$ dictionary base table in Oracle, and that there are many dictionary views that give you information from this table.
|
| 9. | Auditing generates a lot of information being stored in the SYSTEM tablespace. If you plan to use this feature, it's best that you know what you're looking for first, rather than setting up auditing haphazardly and having a lot of extraneous audit information fill your SYSTEM tablespace.
|
Examine the following table
| 10. | Understand what a role is conceptually. To use roles, you must first create a role and then grant privileges to it. Then you can consolidate the granting of those privileges to the users by simply granting the role to those users.
|
| 11. | Roles can be created to enforce password authentication. When password authentication is used, the user must supply the password in order to use privileges associated with the password-authenticated role. Passwords on roles are optional in Oracle.
|
| 12. | By default, only the owner of the role has the ability to grant that role to others. However, roles can be granted to users along with the administrative ability over that role using the grant rolename to username with admin option command.
|
Examine the following table
| 13. | Roles granted to users are considered default roles unless you specify otherwise. You make or do not make a role a default role using the alter user username default role rolename command.
|
| 14. | When a user logs into Oracle, he will be able to use all privileges assigned to him directly and via default roles.
|
| 15. | Assigning a role as a nondefault role to a user adds a level of security, especially when the nondefault role has a password. In this case, users must employ the set role rolename identified by password command in order to enable use of the role.
|
| 16. | The set role command does not make the role a default role — it merely enables the role for the current session. The user will have to use the set role command in order to enable those role-given privileges again.
|
| 17. | Know that the revoke command is used for taking roles away from other users, and that the drop role command is used for removing roles from the database.
|
| 18. | Roles needn't be revoked from all grantees before they are dropped, so long as you have permission to drop the role.
|
Examine the following table
| 19. | Understand the predefined roles in Oracle. CONNECT, RESOURCE, and DBA are very popular for giving users the ability to get started quickly. However, you should be careful when using these roles because they may give far more privileges to users than you care to offer them.
|
| 20. | Know the purpose behind the catalog roles. These roles give users the ability to see data in the data dictionary. You don't always need to grant these roles to end users in order for the user to see certain dictionary views, but in the event a user cannot see an object in the data dictionary, the catalog roles will give the user that ability.
|
| 21. | The DBA_ROLES view shows you all the roles in your Oracle database. ROLE_TAB_PRIVS and ROLE_SYS_PRIVS show all object and system privileges granted to roles in Oracle.
|
| 22. | DBA_ROLE_PRIVS shows roles granted to users in Oracle, whereas ROLE_ROLE_PRIVS shows you the roles granted to other roles in Oracle.
|
| 23. | The SESSION_ROLES view shows all current roles active in a user's session.
|
Question 40
Question 41
Question 42
Question 43
Question 44
Question 45
Question 46
Topic 13.2 Exam Preparation
*Exam Preparation
This page contains a bulleted list of fast facts to review or crib notes for the days leading up to the exam.
- Database privileges govern access for performing every permitted activity in the Oracle database.
- There are two categories of database privileges: system privileges and object privileges.
- System privileges enable the creation of every object on the database, along with the ability to execute many commands and connect to the database.
- Object privileges enable access to data within database objects.
- There are three basic classes of system privileges for some database objects: create, alter, and drop. These privileges give grantees the power to create database objects in their own user schema.
- Some exceptions exist to the preceding rule. The alter table privilege is an object privilege, while the alter rollback segment privilege is a system privilege. The create index privilege is an object privilege as well.
- Three oddball privileges are grant, audit, and analyze. These privileges apply to the creation of all database objects and to running powerful commands in Oracle.
- The any modifier gives the user extra power to create objects or run commands on any object in the user schema.
- The final system privilege of interest is the restricted session privilege, which enables the user to connect to a database in restricted session mode.
- Object privileges give the user access to place, remove, change, or view data in a table or one column in a table, as well as to alter the definition of a table, create an index on a table, and develop foreign key constraints.
- When system privileges are revoked, the objects a user has created will still exist.
- A system privilege can be granted using with admin option to enable the grantee to administer others' ability to use the privilege.
- When object privileges are revoked, the data placed or modified in a table will still exist, but you will not be able to perform the action allowed by the privilege anymore.
- An object privilege can be granted using with grant option to another user in order to make him or her an administrator of the privilege.
- The grant option cannot be used when granting a privilege to a role.
- Roles are used to bundle privileges together and to enable or disable them automatically.
- A user can create objects and then grant the nongrantable object privileges to the role, which then can be granted to as many users require it.
- The roles created by Oracle when the software is installed are CONNECT, RESOURCE, DBA, EXP_FULL_DATABASE, IMP_FULL_DATABASE, DELETE_CATALOG_ROLE, EXECUTE_CATALOG_ROLE, and SELECT_CATALOG_ROLE.
- The CONNECT role can connect to the database and create clusters, links, sequences, tables, views, and synonyms. This role is good for table schema owners and development DBAs.
- The RESOURCE role can connect to the database and create clusters, sequences, tables, triggers, and stored procedures. This role is good for application developers. It also has unlimited tablespace.
- The DBA role can use any system privilege using with admin option.
- The EXP_FULL_DATABASE role can export all database objects to an export dump file.
- The IMP_FULL_DATABASE role can import all database objects from an export dump file to the database.
- The DELETE_CATALOG_ROLE extends delete privileges on SYS-owned dictionary tables in response to the new restriction on delete any table privileges that prevents grantees from removing rows from SYS-owned dictionary tables.
- The EXECUTE_CATALOG_ROLE role enables the grantee to have execute privileges on any SYS-owned package supplied with the Oracle software.
- The SELECT_CATALOG_ROLE role enables the grantee to select data from any SYS-owned dictionary table or view.
- Roles can have passwords assigned to them to provide security for the use of certain privileges.
- Users can alter their own roles in a database session. Each role requires 4 bytes of space in the Program Global Area (PGA) in order to be used. The amount of space each user requires in the PGA can be limited with the MAX_ENABLED_ROLES initialization parameter.
- When a privilege is granted to the user PUBLIC, every user in the database can use the privilege. However, when a privilege is revoked from PUBLIC, every stored procedure, function, or package in the database must be recompiled.
- Auditing the database can be done either to detect inappropriate activity or to store an archive of database activity.
- Auditing can collect large amounts of information. In order to minimize the amount of searching, the person conducting the audit should limit the auditing of database activities to where she thinks a problem lies.
- Any activity on the database can be audited either by naming the privilege or by naming an object in the database.
- The activities of one or more users can be singled out for audit, or every access to an object or privilege, or every session on the database, can have its activities audited.
- Audits can monitor successful activities surrounding a privilege, unsuccessful activities, or both.
- In every database audit, starting and stopping the instance, as well as every connection established by a user with DBA privileges as granted by SYSDBA and SYSOPER, are monitored regardless of any other activities being audited.
- Audit data is stored in the data dictionary in the AUD$ table, which is owned by SYS.
- The main dictionary views for seeing data in the AUD$ table are DBA_AUDIT_EXISTS, DBA_AUDIT_OBJECT, DBA_AUDIT_SESSION, DBA_AUDIT_STATEMENT, and DBA_AUDIT_TRAIL
- If auditing is in place and monitoring session connections, and if the AUD$ table fills, no more users can connect to the database until the AUD$ table is (archived and) emptied.
- The AUD$ table should be audited, whenever in use, to detect any tampering of its data.
