Oracle control and redo log files

Control and Redo Log Files

Maintaining the Control File

You can think of the control file as a metadata repository for the physical database. It has the structure of the database:the data files and redo log files that constitute a database. The control file is a binary file, created when the database is created, and is updated with the physical changes whenever you add or rename a file.

The control file is updated continuously and should be available at all times. Don’t edit the contents of the control file; only Oracle processes should update its contents. When you start up the database, Oracle uses the control file to identify the data files, redo log files, and open them. Control files play a major role when recovering a database.

The contents of the control file include the following:

  • Database name to which the control file A control file can belong to only one database.
  • Database creation
  • Data files—name, location, and online/offline status
  • Redo log files—name and
  • Redo log archive
  • Tablespace
  • Current log sequence number, a unique identifier that is incremented and recorded when an online redo log file is
  • Most recent checkpoint A checkpoint occurs when all the modified database buffers in the SGA are written to the data files. The system change number (SCN), a number sequentially assigned to each transaction in the database, is also recorded in the control file against the data file name that is taken offline or made read-only.
  • Begin and end of undo
  • Recovery Manager’s (RMAN’s) backup RMAN is the Oracle utility you use to back up and recover databases.

The control file size is determined by the MAX clauses you provide when you create the database:                               MAXLOGFILES,  MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES.

Oracle pre-allocates space for these maximums in the control file. Therefore, when you add or rename a file in the database, the control file size does not change.

When you add a new file to the database or relocate a file, an Oracle server process immediately updates the information in the control file. Back up the control file after any structural changes. The log writer process (LGWR) updates the control file with the current log sequence number. The checkpoint process (CKPT) updates the control file with the recent checkpoint information. When the database is in ARCHIVELOG mode, the archiver process (ARCn) updates the control file with archiving information such as the archive log file name and log sequence number.

The control file contains two types of record sections: reusable and not reusable. Recovery Manager Information is kept in the reusable section. Items such as the names of the backup data files are kept in this section, and once this section fills up, the entries are re-used in a circular fashion.

Multiplexing Control Files

Since the control file is critical for the database operation, Oracle recommends a minimum of two control files. You duplicate the control file on different disks either by using the multiplexing feature of Oracle or by using the mirroring feature of your operating system. The next two sections discuss the two ways you can implement the multiplexing feature: using init.ora and using an SPFILE.

Multiplexing Control Files Using init.ora (Pfile)

Multiplexing is defined as keeping a copy of the same control file in different locations. Copying the control file to multiple locations and changing the CONTROL_FILES parameter in the initialization file init.ora to include all control file names specifies the multiplexing of the control file. The following syntax shows three multiplexed control files.

CONTROL_FILES = (‘/ora01/oradata/MYDB/ctrlMYDB01.ctl’, ‘/ora02/oradata/MYDB/ctrlMYDB02.ctl’, ‘/ora03/oradata/MYDB/ctrlMYDB03.ctl’)

By storing the control file on multiple disks, you avoid the risk of a single point of failure. When multiplexing control files, updates to the control file can take a little longer, but that is insignificant when compared with the benefits. If you lose one control file, you can restart the database after copying one of the other control files or after changing the CONTROL_FILES parameter in the initialization file.

When multiplexing control files, Oracle updates all the control files at the same time, but uses only the first control file listed in the CONTROL_FILES parameter for reading.

When creating a database, you can list the control file names in the CONTROL_FILES parameter, and Oracle creates as many control files as are listed. You can have a maximum of eight multiplexed control file copies.

If you need to add more control file copies, do the following:

  1. Change the initialization parameter file to include the new control file name(s) in the parameter CONTROL
  2. Shut down the
  3. Copy the control file to more locations by using an operating system
  4. Start up the

After creating the database, you can change the location of the control files, rename the control files, or drop certain control files. You must have at least one control file for each database. To add, rename, or delete control files, you need to follow the preceding steps. Basically, you shut down the database, use the operating system copy command (copy, rename, or drop the control files accordingly), edit the CONTROL_FILES parameter in init.ora and start up the database.

Multiplexing Control Files Using an SPFILE

Multiplexing using an SPFILE is similar to multiplexing using init.ora; the major difference being how the CONTROL_FILES parameter is changed.

Follow these steps:

  1. Alter the SPFILE while the database is still open:

SQL> ALTER SYSTEM SET CONTROL_FILES =

‘/ora01/oradata/MYDB/ctrlMYDB01.ctl’, ‘/ora02/oradata/MYDB/ctrlMYDB02.ctl’, ‘/ora03/oradata/MYDB/ctrlMYDB03.ctl’, ‘/ora04/oradata/MYDB/ctrlMYDB04.ctl’ SCOPE=SPFILE;

This parameter change will only take effect after the next instance restart by using the SCOPE=SPFILE qualifier. The contents of the binary SPFILE are changed immediately, but the old specification of CONTROL_FILES will be used until the instance is restarted.

  1. Shut down the

SQL> SHUTDOWN NORMAL

  1. Copy an existing control file to the new location:

$ cp /ora01/oradata/MYDB/ctrlMYDB01.ctl

/ora01/oradata/MYDB/ctrlMYDB04.ctl

  1. Start the

SQL> STARTUP

TIP: If you lose one of the control files, you can shut down the database, copy a control file, or change the CONTROL_FILES parameter and restart the database.

Creating New Control Files

You can create a new control file by using the CREATE CONTROLFILE command. You will need to create a new control file if you lose all the control files that belong to the database, if you want to change any of the MAX clauses in the CREATE DATABASE command, or if you want to change the database name. You must know the data file names and redo log file names to create the control file. Follow these steps to create the new control file:

  1. Prepare the CREATE CONTROLFILE You should have the complete list of data files and redo log files. If you omit any data files, they can no longer be a part of the database. The following is an example of the CREATE CONTROLFILE command.

CREATE CONTROLFILE SET DATABASE “ORACLE” NORESETLOGS NOARCHIVELOG MAXLOGFILES 32

MAXLOGMEMBERS 2

MAXDATAFILES 32

MAXINSTANCES 1

MAXLOGHISTORY 1630 LOGFILE

GROUP 1 ‘C:\ORACLE\DATABASE\LOG2ORCL.ORA’ SIZE 500K, GROUP 2 ‘C:\ORACLE\DATABASE\LOG1ORCL.ORA’ SIZE 500K

DATAFILE

‘C:\ORACLE\DATABASE\SYS1ORCL.ORA’, ‘C:\ORACLE\DATABASE\USR1ORCL.ORA’, ‘C:\ORACLE\DATABASE\RBS1ORCL.ORA’, ‘C:\ORACLE\DATABASE\TMP1ORCL.ORA’, ‘C:\ORACLE\DATABASE\APPDATA1.ORA’,

‘C:\ORACLE\DATABASE\APPINDX1.ORA’;

The options in this command are similar to the CREATE DATABASE command. The NORESETLOGS option specifies that the online redo log files should not be reset.

  1. Shut down the database.
  2. Start up the database with the NOMOUNT Remember, to mount the database, Oracle needs to open the control file.
  3. Create the new control file with a command similar to the preceding example. The control files will be created using the names and locations specified in the initialization parameter CONTROL_FILES.
  4. Open the database by using the ALTER DATABASE OPEN

These steps are very basic. Depending on the situation, you might have to perform additional steps.

After creating the control file, determine whether any of the data files listed in the dictionary are missing in the control file. If you query the V$DATAFILE view, the missing files will have the name MISSING. If you created the control file by using the RESETLOGS option, the missing data files cannot be added back to the database. If you created the control file with the NORESETLOGS option, the missing data file can be included in the database by performing a media recovery.

You can back up the control file when the database is up by using the command

ALTER DATABASE BACKUP CONTROLFILE TO ‘<filename>’ REUSE;

Querying Control File Information

The Oracle data dictionary holds all the information about the control file. The view V$CONTROLFILE lists the names of the control files for the database. The STATUS column should always be NULL; when a control file is missing, the STATUS would be INVALID, but that should never occur because when Oracle cannot update one of the control files, the instance crashes – you can start up the database only after copying a good control file.

For example, in order to obtain control information you do as following:

SQL> SELECT * FROM V$CONTROLFILE;

STATUS NAME

—— ————————————–

/ora01/oradata/MYDB/ctrlMYDB01.ctl

/ora02/oradata/MYDB/ctrlMYDB02.ctl

/ora03/oradata/MYDB/ctrlMYDB03.ctl 3 rows selected.

You can also use the SHOW PARAMETER command to retrieve the names of the control files.

SQL> show parameter control_files

NAME TYPE VALUE

—————- ———– ——————————

control_files string H:\Oracle\oradata\or90\CONTROL01.CTL,

H:\Oracle\oradata\or90\CONTROL02.CTL,

H:\Oracle\oradata\or90\CONTROL03.CTL

Maintaining and Monitoring Redo Log Files

Redo logs record all changes to the database. The redo log buffer in the SGA is written to the redo log file periodically by the LGWR process. The redo log files are accessed and are open during normal database operation; hence they are called the online redo log files. Every Oracle database must have at least two redo log files. The LGWR process writes to these files in a circular fashion. For example, say there are three online redo log files. The LGWR process writes to the first file, and when this file is full, it starts writing to the second file, and then to the third file, and then again to the first file (overwriting the contents).

Online redo log files are filled with redo records. A redo record, also called a redo entry, is made up of a group of change vectors, each of which is a description of a change made to a single block in the database. Redo entries record data that you can use to reconstruct all changes made to the database, including the undo segments. When you recover the database by using redo log files, Oracle reads the change vectors in the redo records and applies the changes to the relevant blocks.

LGWR writes redo information from the redo log buffer to the online redo log files under a variety of circumstances: A user commits a transaction, even if this is the only transaction in the log buffer.

  • The redo log buffer becomes one-third
  • When there is approximately 1MB of changed records in the This total does not include deleted or inserted records.
NOTE: LGWR always writes its records to the online redo log file before DBWn writes new or modified database buffer cache records to the datafiles.

Each database has its own online redo log groups. A log group can have one or more redo log members (each member is a single operating system file). If you have a Real Application Cluster configuration, in which multiple instances are mounted to one database, each instance will have one online redo thread. That is, the LGWR process of each instance writes to the same online redo log files, and hence Oracle has to keep track of the instance from where the database changes are coming. For single instance configurations, there will be only one thread, and that thread number is 1. The redo log file contains both committed and uncommitted transactions. Whenever a transaction is committed, a system change number is assigned to the redo records to identify the committed transaction.

The redo log group is referenced by an integer; you can specify the group number when you create the redo log files, either when you create the database or when you create the control file. You can also change the redo log configuration (add/drop/rename files) by using database commands. The following example shows a CREATE DATABASE command.

CREATE DATABASE “MYDB01”

LOGFILE ‘/ora02/oradata/MYDB01/redo01.log’ SIZE 10M, ‘/ora03/oradata/MYDB01/redo02.log’ SIZE 10M;

Two log file groups are created here; the first file will be assigned to group 1, and the second file will be assigned to group 2. You can have more files in each group; this practice is known as the multiplexing of redo log files, which we’ll discuss later. You can specify any group number — the range will be between 1 and MAXLOGFILES. Oracle recommends that all redo log groups be the same size. The following is an example of creating the log files by specifying the groups.

CREATE DATABASE “MYDB01”

LOGFILE GROUP 1 ‘/ora02/oradata/MYDB01/redo01.log’ SIZE 10M, GROUP 2 ‘/ora03/oradata/MYDB01/redo02.log’ SIZE 10M;

Log Switch Operations

The LGWR process writes to only one redo log file group at any time. The file that is actively being written to is known as the current log file. The log files that are required for instance recovery are known as the active log files. The other log files are known as inactive. Oracle automatically recovers an instance when starting up the instance by using the online redo log files. Instance recovery may be needed if you do not shut down the database properly or if your computer crashes.

The log files are written in a circular fashion. A log switch occurs when Oracle finishes writing to one file and starts writing to the next file. A log switch always occurs when the current redo log file is completely full and log writing must continue. You can force a log switch by using the ALTER SYSTEM command. A manual log switch may be necessary when performing maintenance on the redo log files by using the ALTER SYSTEM SWITCH LOGFILE command. Figure 1 shows how LGWR writes to the redo log groups in a circular fashion.

Whenever a log switch occurs, Oracle allocates a sequence number to the new redo log file before writing to it. As stated earlier, this number is known as the log sequence number. If there are lots of transactions or changes to the database, the log switches can occur too frequently. Size the redo log file appropriately to avoid frequent log switches. Oracle writes to the alert log file whenever a log switch occurs.

Fig. 1. Redo log file usage.

Database checkpoints are closely tied to redo log file switches. A checkpoint is an event that flushes the modified data from the buffer cache to the disk and updates the control file and data files. The CKPT process updates the headers of data files and control files; the actual blocks are written to the file by the DBWn process. A checkpoint is initiated when the redo log file is filled and a log switch occurs, when the instance is shut down with NORMAL, TRANSACTIONAL, or IMMEDIATE, when a tablespace status is changed to readonly or put into BACKUP mode, when a tablespace or datafile is taken offline, or when other values specified by certain parameters (discussed later) are reached.

You can force a checkpoint if needed. Forcing a checkpoint ensures that all changes to the database buffers are written to the data files on disk.

ALTER SYSTEM CHECKPOINT;

Another way to force a checkpoint is by forcing a log file switch.

ALTER SYSTEM SWITCH LOGFILE;

Multiplexing Log Files

You can keep multiple copies of the online redo log file to safeguard against damage to these files. When multiplexing online redo log files, LGWR concurrently writes the same redo log information to multiple identical online redo log files, thereby eliminating a single point of redo log failure. All copies of the redo file are the same size and are known as a group, which is identified by an integer. Each redo log file in the group is known as a member. You must have at least two redo log groups for normal database operation.

When multiplexing redo log files, it is preferable to keep the members of a group on different disks, so that one disk failure will not affect the continuing operation of the database. If LGWR can write to at least one member of the group, database operation proceeds as normal; an entry is written to the alert log file. If all members of the redo log file group are not available for writing, Oracle shuts down the instance. An instance recovery or media recovery may be needed to bring up the database.

You can create multiple copies of the online redo log files when you create the database. For example, the following statement creates two redo log file groups with two members in each.

CREATE DATABASE “MYDB01” LOGFILE

GROUP 1 (‘/ora02/oradata/MYDB01/redo0101.log’, ‘/ora03/oradata/MYDB01/redo0102.log’) SIZE 10M,

GROUP 2 (‘/ora02/oradata/MYDB01/redo0201.log’, ‘/ora03/oradata/MYDB01/redo0202.log’) SIZE 10M;

The maximum number of log file groups is specified in the clause MAXLOGFILES, and the maximum number of members is specified in the clause MAXLOGMEMBERS. You can separate the filenames (members) by using a space or a comma.

Creating New Groups

You can create and add more redo log groups to the database by using the ALTER DATABASE command. The following statement creates a new log file group with two members.

ALTER DATABASE ADD LOGFILE

GROUP 3 (‘/ora02/oradata/MYDB01/redo0301.log’, ‘/ora03/oradata/MYDB01/redo0402.log’) SIZE 10M;

If you omit the GROUP clause, Oracle assigns the next available number. For example, the following statement also creates a multiplexed group.

ALTER DATABASE ADD LOGFILE

(‘/ora02/oradata/MYDB01/redo0301.log’ ‘/ora03/oradata/MYDB01/redo0402.log’) SIZE 10M;

To create a new group without multiplexing, use the following statement.

ALTER DATABASE ADD LOGFILE

‘/ora02/oradata/MYDB01/redo0301.log’ REUSE;

You can add more than one redo log group by using the ALTER DATABASE command— just use a comma to separate the groups.

TIP: If the redo log files you create already exist, use the REUSE option and don’t specify the size. The new redo log size will be same as that of the existing file.

Adding New Members

If you forgot to multiplex the redo log files when creating the database or if you need to add more redo log members, you can do so by using the ALTER DATABASE command. When adding new members, you do not specify the file size, because all group members will have the same size.

If you know the group number, using the following statement will add a member to group 2.

ALTER DATABASE ADD LOGFILE MEMBER

‘/ora04/oradata/MYDB01/redo0203.log’ TO GROUP 2;

You can also add group members by specifying the names of other members in the group, instead of specifying the group number. Specify all the existing group members with this syntax.

ALTER DATABASE ADD LOGFILE MEMBER

‘/ora04/oradata/MYDB01/redo0203.log’ TO (‘/ora02/oradata/MYDB01/redo0201.log’, ‘/ora03/oradata/MYDB01/redo0202.log’);

Renaming Log Members

If you want to move the log file member from one disk to another or just want a more meaningful name, you can rename a redo log member. Before renaming the online redo log members, the new (target) online redo files should exist. The SQL commands in Oracle change only the internal pointer in the control file to a new log file; they do not change or rename the operating system file. You must use an operating system command to rename or move the file. Follow these steps to rename a log member:

  1. Shut down the database (a complete backup is recommended).
  2. Copy/rename the redo log file member to the new location by using an operating system
  3. Start up the instance and mount the database (STARTUP MOUNT).
  4. Rename the log file member in the control Use ALTER DATABASE RENAME FILE ‘<old_redo_file_name>’ TO ‘<new_redo_file_name>’;
  5. Open the database (ALTER DATABASE OPEN).
  6. Back up the control

Dropping Redo Log Groups

You can drop a redo log group and its members by using the ALTER DATABASE command. Remember that you should have at least two redo log groups for the database to function normally. The group that is to be dropped should not be the active group or the current group—that is, you can drop only an inactive log file group. If the log file to be dropped is not inactive, use the ALTER SYSTEM SWITCH LOGFILE command.

To drop the log file group 3, use the following SQL statement.

ALTER DATABASE DROP LOGFILE GROUP 3;

When an online redo log group is dropped from the database, the operating system files are not deleted from disk. The control files of the associated database are updated to drop the members of the group from the database structure. After dropping an online redo log group, make sure that the drop is completed successfully, and then use the appropriate operating system command to delete the dropped online redo log files.

Dropping Redo Log Members

Similar to conditions for dropping a redo log group, you can drop only the members of an inactive redo log group. Also, if there are only two groups, the log member to be dropped should not be the last member of a group. You can have a different number of members for each redo log group, though it is not advised. For example, say you have three log groups, each with two members. If you drop a log member from group 2, and a failure occurs to the sole member of group 2, the instance crashes. So even if you drop a member for maintenance reasons, ensure that all redo log groups have the same number of members.

To drop the log member, use the DROP LOGFILE MEMBER clause of the ALTER DATABASE command.

ALTER DATABASE DROP LOGFILE MEMBER

‘/ora04/oradata/MYDB01/redo0203.log’;

The operating system file is not removed from the disk; only the control file is updated. Use an operating system command to delete the redo log file member from disk.

TIP: If a database is running in ARCHIVELOG mode, redo log members cannot be deleted unless the redo log group has been archived.

Clearing Online Redo Log Files

Under certain circumstances, a redo log group member (or all members of a log group) may become corrupted. To solve this problem, you can drop and re-add the log file group or group member. It is much easier, however, to use the ALTER DATABASE CLEAR LOGFILE command. The following example clears the contents of redo log group 3 in the database:

ALTER DATABASE CLEAR LOGFILE GROUP 3;

Another distinct advantage of this command is that you can clear a log group even if the database has only two log groups, and only one member in each group. You can also clear a log group member even if it has not been archived yet by using the UNARCHIVED keyword. In this case, it is advisable to do a full database backup at the earliest convenience, because the unarchived redo log file is no longer usable for database recovery.

Setting ARCHIVELOG

Specifying these parameters does not start writing the archive log files; you should place the database in ARCHIVELOG mode to enable archiving of the redo log files. You can specify the ARCHIVELOG clause while creating the database. However, most DBAs prefer to create the database first and then enable ARCHIVELOG mode. To enable ARCHIVELOG mode, follow these steps:

  1. Shut down the Set up the appropriate initialization parameters.
  2. Start up and mount the
  3. Enable ARCHIVELOG mode by using the command ALTER DATABASE
  4. Open the database by using ALTER DATABASE

To disable ARCHIVELOG mode, follow these steps:

  1. Shut down the
  2. Start up and mount the
  3. Disable ARCHIVELOG mode by using the command ALTER DATABASE
  4. Open the database by using ALTER DATABASE

You can enable automatic archiving by setting the parameter LOG_ARCHIVE_START = TRUE. If you set the parameter to FALSE, Oracle does not start the ARCn process. Therefore, when the redo log files are full, the database will hang, waiting for the redo log files to be archived. You can initiate the automatic archive process by using the command ALTER SYSTEM ARCHIVE LOG START, which starts the ARCn processes; to manually archive all unarchived logs, use the command

ALTER SYSTEM ARCHIVE LOG ALL.

Querying Log and Archive Information

 

You can query the redo log file information from the SQL command ARCHIVE LOG LIST or by querying the dynamic performance views.

The ARCHIVE LOG LIST command shows whether the database is in ARCHIVELOG mode, whether automatic archiving is enabled, the archival destination, and the oldest, next, and current log sequence numbers.

SQL> archive log list

Database log mode                                 Archive Mode

Automatic archival                                 Enabled

Archive destination                                C:\Oracle\oradata\ORADB02\archive

Oldest online log sequence                    194

Next log sequence to archive                 196

Current log sequence                             196

SQL>

TIP: The view V$DATABASE shows whether the database is in ARCHIVELOG mode or in NOARCHIVELOG mode.

V$LOG

This dynamic performance view contains information about the log file groups and sizes and its status. The valid status codes in this view and their meanings are as follows:

UNUSED New log group, never used.

CURRENT Current log group.

ACTIVE Log group that may be required for instance recovery.

CLEARING You issued an ALTER DATABASE CLEAR LOGFILE command.

CLEARING_CURRENT Empty log file after issuing the ALTER DATABASE CLEAR LOGFILE command.

INACTIVE The log group is not needed for instance recovery.

V$LOGFILE

The V$LOGFILE view has information about the log group members. The filenames and group numbers are in this view. The STATUS column can have the value INVALID (file is not accessible), STALE (file’s contents are incomplete), DELETED (file is no longer used), or blank (file is in use).

SQL> SELECT * FROM V$LOGFILE

2                           ORDER BY GROUP#; GROUP# STATUS TYPE MEMBER

——— ——- ——- ————————————

1 ONLINE C:\ORACLE\ORADATA\ORADB02\REDO11.LOG
1 ONLINE D:\ORACLE\ORADATA\ORADB02\REDO12.LOG
2 STALE ONLINE C:\ORACLE\ORADATA\ORADB02\REDO21.LOG
2 ONLINE D:\ORACLE\ORADATA\ORADB02\REDO22.LOG
3 ONLINE C:\ORACLE\ORADATA\ORADB02\REDO31.LOG
3 ONLINE D:\ORACLE\ORADATA\ORADB02\REDO32.LOG

6 rows selected.

Managing Users and Security

 

Profiles

 

You use profiles to control the database and system resource usage. Oracle provides a set of predefined resource parameters that you can use to monitor and control database resource usage. You can define limits for each resource by using a database profile. You also use profiles for password management. You can create profiles for different user communities and then assign a profile to each user. When you create the database, Oracle creates a profile named DEFAULT, and if you do not specify a profile for the user, Oracle assigns the user the DEFAULT profile.

Managing Resources

Oracle lets you control the following types of resource usage through profiles:

  • Concurrent sessions per user
  • Elapsed and idle time connected to the database
  • CPU time used
  • Private SQL and PL/SQL area used in the SGA (System Global Area)
  • Logical reads performed
  • Amount of private SGA space used in Shared Server configurations

Resource limits are enforced only if you have set the parameter RESOURCE_LIMIT to TRUE. Even if you have defined profiles and assigned profiles to users, Oracle enforces them only when this parameter is set to TRUE. You can set this parameter in the initialization parameter file so that every time the database starts, the resource usage is controlled for each user using the assigned profile. You enable or disable resource limits using the ALTER SYSTEM command. The default value of RESOURCE_LIMIT is FALSE.

The limits for each resource are specified as an integer; you can set no limit for a given resource by specifying UNLIMITED, or you can use the value specified in the DEFAULT profile by specifying DEFAULT. The DEFAULT profile initially has the value UNLIMITED for all resources. After you create the database, you can modify the DEFAULT profile.

Most resource limits are set at the session level; a session is created when a user connects to the database. You can control certain limits at the statement level (but not at the transaction level). If a user exceeds a resource limit, Oracle aborts the current operation, rolls back the changes made by the statement, and returns an error. The user has the option of committing or rolling back the transaction, because the statements issued earlier in the transaction are not aborted. No other operation is permitted when a session-level limit is reached. The user can disconnect, in which case the transaction is committed. You use the following parameters to control resources:

SESSIONS_PER_USER Limits the number of concurrent user sessions. No more sessions from the current user are allowed when this threshold is reached.

CPU_PER_SESSION Limits the amount of CPU time a session can use. The CPU time is specified in hundredths of a second.

CPU_PER_CALL Limits the amount of CPU time a single SQL statement can use. The CPU time is specified in hundredths of a second. This parameter is useful for controlling runaway queries, but you should be careful to specify this limit for batch programs.

LOGICAL_READS_PER_SESSION Limits the number of data blocks read in a session, including the blocks read from memory and from physical reads.

LOGICAL_READS_PER_CALL Limits the number of data blocks read by a single SQL statement, including the blocks read from memory and from physical reads.

PRIVATE_SGA Limits the amount of space allocated in the SGA for private areas, per session. Private areas for SQL and PL/SQL statements are created in the SGA in the multithreaded architecture. You can specify K to indicate the size in KB or M to indicate the size in MB. If you specify neither K or M, the size is in bytes. This limit does not apply to dedicated server architecture connections.

CONNECT_TIME Specifies the maximum number of minutes a session can stay connected to the database (total elapsed time, not CPU time). When the threshold is reached, the user is automatically disconnected from the database; any pending transaction is rolled back.

IDLE_TIME Specifies the maximum number of minutes a session can be continuously idle, that is, without any activity for a continuous period of time. When the threshold is reached, the user is disconnected from the database; any pending transaction is rolled back.

COMPOSITE_LIMIT A weighted sum of four resource limits: CPU_PER_SESSION, LOGICAL_READS_PER_SESSION, CONNECT_TIME, and

PRIVATE_SGA. You can define a cost for the system resources (the resource cost on one database may be different from another, based on the number of transactions, CPU, memory, and so on) known as the composite limit. The upcoming section “Managing Profiles” discusses setting the resource cost.

Managing Password

You also use profiles to manage passwords. You can set the following by using profiles:

Account locking Number of failed login attempts, and the number of days the password will be locked.

Password expiration How often passwords should be changed, whether passwords can be reused, and the grace period after which the user is warned that the password change is due.

Password complexity Use of a customized function to verify the password complexity—for example, the password should not be the same as the user ID, cannot include commonly used words, and so on.

You can use the following parameters in profiles to manage passwords:

FAILED_LOGIN_ATTEMPTS Specifies the maximum number of consecutive invalid login attempts (providing an incorrect password) allowed before the user account is locked.

PASSWORD_LOCK_TIME Specifies the number of days the user account will remain locked after the user has made FAILED_LOGIN_ATTEMPTS number of consecutive failed login attempts.

PASSWORD_LIFE_TIME Specifies the number of days a user can use one password. If the user does not change the password within the number of days specified, all connection requests return an error. The DBA then has to reset the password.

PASSWORD_GRACE_TIME Specifies the number of days the user will get a warning before the password expires. This is a reminder for the user to change the password.

PASSWORD_REUSE_TIME Specifies the number of days a password cannot be used again after changing it.

PASSWORD_REUSE_MAX Specifies the number of password changes required before a password can be reused. You cannot specify a value for both PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX; one should always be set to UNLIMITED, because you can enable only one type of password history method.

PASSWORD_VERIFY_FUNCTION Specifies the function you want to use to verify the complexity of the new password. Oracle provides a default script, which you can modify.

TIP: You can specify minutes or hours as a fraction or expression in parameters that require days as a value. One hour can be represented as 0.042 days or 1/24, and one minute can be specified as 0.000694 days, 1/24/60, or 1/1440.

Managing Profiles

 

You can create many profiles in the database that specify both resource management parameters and password management parameters. However, you can assign a user only one profile at any given time. To create a profile, you use the CREATE PROFILE command. You need to provide a name for the profile and then specify the parameter names and their values separated by space(s).

As an example, let’s create a profile to manage passwords and resources for the accounting department users. The users are required to change their password every 60 days, and they cannot reuse a password for 90 days. They are allowed to make a typo in the password only six consecutive times while connecting to the database; if the login fails a seventh time, their account is locked forever (until the DBA or security department unlocks the account).

The accounting department users are allowed a maximum of six database connections; they can stay connected to the database for 24 hours, but an inactivity of 2 hours will terminate their session. To prevent users from performing runaway queries, in this example we will set the maximum number of blocks they can read per SQL statement to 1 million.

SQL> CREATE PROFILE ACCOUNTING_USER LIMIT

SESSIONS_PER_USER 6

CONNECT_TIME 1440

IDLE_TIME 120

LOGICAL_READS_PER_CALL 1000000

PASSWORD_LIFE_TIME 60

PASSWORD_REUSE_TIME 90

PASSWORD_REUSE_MAX UNLIMITED

FAILED_LOGIN_ATTEMPTS 6

PASSWORD_LOCK_TIME UNLIMITED;

In the example, parameters such as PASSWORD_GRACE_TIME, CPU_PER_SESSION, and PRIVATE_SGA are not used. They will have a value of DEFAULT, which means the value will be taken from the DEFAULT profile.

The DBA or security officer can unlock a locked user account by using the ALTER USER command. The following example shows the unlocking of SCOTT’s account.

SQL> ALTER USER SCOTT ACCOUNT UNLOCK;

User altered.

Composite Limit

The composite limit specifies the total resource cost for a session. You can define a weight for each resource based on the available resources. The following resources are considered for calculating the composite limit:

CPU_PER_SESSION

LOGICAL_READS_PER_SESSION

CONNECT_TIME

PRIVATE_SGA

The costs associated with each of these resources are set at the database level by using the ALTER RESOURCE COST command. By default, the resources have a cost of 0, which means they should not be considered for a composite limit (they are inexpensive). A higher cost means that the resource is expensive. If you do not specify any of these resources in ALTER RESOURCE COST, Oracle will keep the previous value. For example:

SQL> ALTER RESOURCE COST

LOGICAL_READS_PER_SESSION 10

CONNECT_TIME 2; Resource cost altered.

Here CPU_PER_SESSION and PRIVATE_SGA will have a cost of 0 (if they have not been modified before).

You can define limits for each of the four parameters in the profile as well as set the composite limit. The limit that is reached first is the one that takes effect. The following statement adds a composite limit to the profile you created earlier.

SQL> ALTER PROFILE ACCOUNTING_USER LIMIT

COMPOSITE_LIMIT 1500000; Profile altered.

The cost for the composite limit is calculated as follows:

Cost = (10 × LOGICAL_READS_PER_SESSION) + (2 × CONNECT_TIME)

If the user has performed 100,000 block reads and was connected for two hours, the cost thus far would be (10 × 100,000) + (2 × 120) = 1,000,240.

The user will be restricted when this cost exceeds 1,500,000 or when the values for LOGICAL_READS_PER_SESSION or CONNECT_TIME set in the profile are reached.

Password Verification Function

You can create a function to verify the complexity of the passwords and assign the function name to the PASSWORD_VERIFY_FUNCTION parameter in the profile. When a password is changed, Oracle checks to see whether the supplied password satisfies the conditions specified in this function. Oracle provides a default verification function, known as VERIFY_FUNCTION, which is in the rdbms/admin directory of your Oracle software installation; the script is named utlpwdmg.sql. The password verification function should be owned by SYS and should have the following characteristics.

FUNCTION SYS.<function_name>

( <userid_variable> IN VARCHAR2 (30),

<password_variable> IN VARCHAR2 (30),

<old_password_variable> IN VARCHAR2 (30) ) RETURN BOOLEAN

Oracle’s default password verification function checks that the password conforms to the following:

  • Is not the same as the username
  • Has a minimum length
  • Is not too simple; a list of words is checked
  • Contains at least one letter, one digit, and one punctuation mark
  • Differs from the previous password by at least three letters

If the new password satisfies all the conditions, the function returns a Boolean result of TRUE, and the user’s password is changed.

Altering Profiles

Using the ALTER PROFILE command changes profile values. You can change any parameter in the profile using this command. The changes take effect the next time the user connects to the database. For example, to add a password verification function and set a composite limit to the profile you created in the previous example, use the following:

SQL> ALTER PROFILE ACCOUNTING_USER LIMIT

PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION

COMPOSITE_LIMIT 1500;

Profile altered.

Dropping Profiles

To drop a profile, you use the DROP PROFILE command. If any user is assigned the profile you want to drop, Oracle returns an error. You can drop such profiles by specifying CASCADE, in which case the users who have that profile will be assigned the DEFAULT profile.

SQL> DROP PROFILE ACCOUNTING_USER CASCADE;

Profile dropped.

Assigning Profiles

To assign profiles to users, you use the CREATE USER or ALTER USER command. These commands are discussed later. This example assigns the ACCOUNTING_USER profile to an existing user named SCOTT:

SQL> ALTER USER SCOTT

PROFILE ACCOUNTING_USER; User altered.

Querying Profile Information

You can query profile information from the DBA_PROFILES view. The following example shows information about the profile created previously. The RESOURCE_TYPE column indicates whether the parameter is KERNEL (resource) or PASSWORD.

SQL> SELECT RESOURCE_NAME, LIMIT

FROM DBA_PROFILES

WHERE PROFILE = ‘ACCOUNTING_USER’

AND RESOURCE_TYPE = ‘KERNEL’;

RESOURCE_NAME                                                  LIMIT

——————————————— ———-

COMPOSITE_LIMIT                                                  1500

SESSIONS_PER_USER                                             6

CPU_PER_SESSION                                                  DEFAULT

CPU_PER_CALL                                                        DEFAULT

LOGICAL_READS_PER_SESSION                        DEFAULT LOGICAL_READS_PER_CALL                                                                                      10000000

IDLE_TIME                                                                 120

CONNECT_TIME                                                       UNLIMITED

PRIVATE_SGA                                                          DEFAULT

9 rows selected.

The view USER_RESOURCE_LIMITS shows the limit defined for the current user for resource, and the view USER_PASSWORD_LIMITS shows the limit defined for the password.

Users

 

Access to the Oracle database is provided using database accounts known as usernames (users). If the user owns database objects, the account is known as a schema, which is a logical grouping of all the objects owned by the user. Persons requiring access to the database should have a valid username created in the database. The following properties are associated with a database user account:

Authentication method: Each user must be authenticated to connect to the database by using a password, through the operating system, or via the Enterprise Directory Service. Operating system authentication is discussed in the “Privileges and Roles” section.

Default and temporary tablespaces: The default tablespace specifies a tablespace for the user to create objects if another tablespace is not explicitly specified. The user needs a quota assigned in the tablespace to create objects, even if the tablespace is the user’s default. You use the temporary tablespace to create the temporary segments; the user need not have any quota assigned in this tablespace.

Space quota The user needs a space quota assigned in each tablespace in which they want to create the objects. By default, a newly created user does not have any space quota allocated on any tablespace to create schema objects. For the user to create schema objects such as tables or materialized views, you must allocate a space quota in tablespaces.

Profile The user can have a profile to specify the resource limits and password settings. If you don’t specify a profile when you create the user, the DEFAULT profile is assigned.

NOTE: When you create the database, the SYS and SYSTEM users are created. SYS is the schema that owns the data dictionary.

Managing Users

To create users in the database, you use the CREATE USER command. Specify the authentication method when you create the user. A common authentication method is using the database; the username is assigned a password, which is stored encrypted in the database. Oracle verifies the password when establishing a connection to the database.

As an example, let’s create a user JOHN with the various clauses available in the CREATE USER command.

SQL> CREATE USER JOHN

IDENTIFIED BY “B1S2!”

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP

QUOTA UNLIMITED ON USERS

QUOTA 1M ON INDX

PROFILE ACCOUNTING_USER

PASSWORD EXPIRE

ACCOUNT UNLOCK User created.

The IDENTIFIED BY clause specifies that the user will be authenticated using the database. To authenticate the user using the operating system, specify IDENTIFIED EXTERNALLY. The password specified is not case sensitive.

The user JOHN can connect to the database only if he has the CREATE SESSION privilege. Granting privileges and roles is discussed later, in the section “Privileges and Roles.” The CREATE SESSION privilege is granted to user JOHN by specifying the following:

SQL> GRANT CREATE SESSION TO JOHN;

Grant succeeded.

NOTE: To create extents, a user with the UNLIMITED TABLESPACE system privilege does not need any space quota in any tablespace.

Modifying User Accounts

You can modify all the characteristics you specified when creating a user by using the ALTER USER command. You can also assign or modify the default roles assigned to the user (discussed later on). Changing the default tablespace of a user affects only the objects created in the future.

The following example changes the default tablespace of JOHN and assigns a new password.

ALTER USER JOHN IDENTIFIED BY SHADOW2#

DEFAULT TABLESPACE APPLICATION_DATA;

You can lock or unlock a user’s account as follows:

ALTER USER <username> ACCOUNT [LOCK/UNLOCK]

You can also expire the user’s password:

ALTER USER <username> PASSWORD EXPIRE

Users must change the password the next time they log in, or you must change the password. If the password is expired, SQL*Plus prompts for a new password at login time.

In the following example, setting the quota to 0 revokes the tablespace quota assigned. The objects created by the user in the tablespace remain there, but no new extents can be allocated in that tablespace.

ALTER USER JOHN QUOTA 0 ON USERS;

Dropping Users

You can drop a user from the database by using the DROP USER command. If the user (schema) owns objects, Oracle returns an error. If you specify the CASCADE keyword, Oracle drops all the objects owned by the user and then drops the user. If other schema objects, such as procedures, packages, or views, refer to the objects in the user’s schema, they become invalid. When you drop objects, space is freed up immediately in the relevant tablespaces.

The following example shows how to drop the user JOHN, with all the owned objects.

DROP USER JOHN CASCADE;

WARNING: You cannot drop a user who is currently connected to the database.

Authenticating Users

In this section, we will discuss two widely used user-authenticating methods:

  • Authentication by the database
  • Authorization by the operating system

When you use database authentication, you define a password for the user (the user can change the password), and Oracle stores the password in the database (encrypted). When users connect to the database, Oracle compares the password supplied by the user with the password in the database.

When you use authorization by the operating system, Oracle verifies the operating system login account and connects to the database—users need not specify a username and password. Oracle does not store the passwords of such operating-system authenticated users, but they must have a username in the database. The initialization parameter OS_AUTHENT_PREFIX determines the prefix used for operating system authorization. By default, the value is OPS$. For example, if your operating system login name is ALEX, the database username should be

OPS$ALEX. When Alex specifies CONNECT / or does not specify a username to connect to the database, Oracle tries to connect Alex to the OPS$ALEX account. You can set the OS_AUTHENT_PREFIX parameter to a null string “”; this will not add any prefix. To create an operating-system authenticated user, use the following:

SQL> CREATE USER OPS$ALEX IDENTIFIED EXTERNALLY;

To connect to a remote database using operating system authorization, set the REMOTE_OS_AUTHENT parameter to TRUE. You must be careful in using this parameter, because connections can be made from any computer.

For example, if you have an operating system account named ORACLE and a database account OPS$ORACLE, you can connect to the database from the machine where the database resides. If you set REMOTE_OS_AUTHENT to TRUE, you can log in to any server with the ORACLE operating system account and connect to the database over the network. If a user creates an operating system ID named ORACLE and is on the network, the user can connect to the database using operating system authorization.

Querying User Information

You can query user information from the data dictionary views DBA_USERS and USER_USERS. USER_USERS shows only one row: information about the current user. You can obtain the user account status, password expiration date, account locked date (if locked), encrypted password, default and temporary tablespaces, profile name, and creation date from this view.

Oracle creates a numeric ID and assigns it to the user when the user is created. SYS has an ID of 0.

SQL> SELECT USERNAME, DEFAULT_TABLESPACE,

TEMPORARY_TABLESPACE, PROFILE,

ACCOUNT_STATUS, EXPIRY_DATE

FROM DBA_USERS

WHERE USERNAME = ‘JOHN’;

The view ALL_USERS shows the username and creation date.

SQL> SELECT * FROM ALL_USERS;

Managing Privileges

In the Oracle database, privileges control access to the data and restrict the actions users can perform. Through proper privileges, users can create, drop, or modify objects in their own schema or in another user’s schema. Privileges also determine the data to which a user should have access. You can grant privileges to a user by means of two methods:

  • You can assign privileges directly to the
  • You can assign privileges to a role, and then assign the role to the

A role is a named set of privileges, which eases the management of privileges. For example, if you have 10 users needing access to the data in the accounting tables, you can grant the privileges required to a role and grant the role to the 10 users. There are two types of privileges:

Object privileges Object privileges are granted on schema objects that belong to a different schema. The privilege can be on data (to read, modify, delete, add, or reference), on a program (to execute), or on an object (to change the structure).

System privileges System privileges provide the right to perform a specific action on any schema in the database. System privileges do not specify an object, but are granted at the database level. Certain system privileges are very powerful and should be granted only to trusted users. System privileges and object privileges can be granted to a role.

PUBLIC is a user group defined in the database; it is not a database user or a role. Every user in the database belongs to this group. Therefore, if you grant privileges to PUBLIC, they are available to all users of the database.

NOTE: A user and a role cannot have the same name.

Object Privileges

Object privileges are granted on a specific object. The owner of the object has all privileges on the object. The owner can grant privileges on that object to any other users of the database. The owner can also authorize another user in the database to grant privileges on the object to other users. For example, user JOHN owns a table named CUSTOMER and grants read and update privileges to JAMES. (To specify multiple privileges, separate them with a comma.)

SQL> GRANT SELECT, UPDATE ON CUSTOMER TO JAMES;

JAMES cannot insert into or delete from CUSTOMER; JAMES can only query and update rows in the CUSTOMER table. JAMES cannot grant the privilege to another user in the database, because JAMES is not authorized by JOHN to do so. If the privilege is granted with the WITH GRANT OPTION, JAMES can grant the privilege to others.

SQL> GRANT SELECT, UPDATE ON CUSTOMER

TO JAMES WITH GRANT OPTION;

The INSERT, UPDATE, or REFERENCES privileges can be granted on columns also. For example:

SQL> GRANT INSERT (CUSTOMER_ID) ON CUSTOMER TO JAMES;

The following are the object privileges that can be granted to users of the database:

SELECT Grants read (query) access to the data in a table, view, sequence, or materialized view.

UPDATE Grants update (modify) access to the data in a table, column, view, or materialized view.

DELETE Grants delete (remove) access to the data in a table, view, or materialized view.

INSERT Grants insert (add) access to a table, column, view, or materialized view.

EXECUTE Grants execute (run) privilege on a PL/SQL stored object, such as a procedure, package, or function.

READ Grants read access on a directory.

INDEX Grants index creation privilege on a table.

REFERENCES Grants reference access to a table or columns to create foreign keys that can reference the table.

ALTER Grants access to modify the structure of a table or sequence.

ON COMMIT REFRESH Grants the privilege to create a refresh-on-commit materialized view on the specified table.

QUERY REWRITE Grants the privilege to create a materialized view for query rewrite using the specified table.

WRITE Allows the external table agent to write a log file or a bad file to the directory. This privilege is associated only with the external tables.

UNDER Grants the privilege to create a sub-view under a view.

The following are some points related to object privileges that you need to remember:

  • Object privileges can be granted to a user, a role, or
  • If a view refers to tables or views from another user, you must have the privilege WITH GRANT OPTION on the underlying tables of the view to grant any privilege on the view to another For example, JOHN owns a view, which references a table from JAMES. To grant the SELECT privilege on the view to another user, JOHN should have received the SELECT privilege on the table WITH GRANT OPTION.
  • Any object privilege received on a table provides the grantee the privilege to lock the
  • The SELECT privilege cannot be specified on columns; to grant column-level SELECT privileges, create a view with the required columns and grant SELECT on the
  • You can specify ALL or ALL PRIVILEGES to grant all available privileges on an object (for example, GRANT ALL ON CUSTOMER TO JAMES). Even if you have the DBA privilege, to grant privileges on objects owned by another user you must have been granted the appropriate privilege on the object WITH GRANT
  • Multiple privileges can be granted to multiple users and/or roles in one For example, GRANT INSERT, UPDATE, SELECT ON CUSTOMER TO ADMIN_ROLE, JULIE, SCOTT;

System Privileges

System privileges are the privileges that enable the user to perform an action; they are not specified on any particular object. Like object privileges, system privileges also can be granted to a user, a role, or PUBLIC. There are many system privileges in Oracle; Table 1 summarizes the privileges used to manage objects in the database. The CREATE, ALTER, and DROP privileges provide the ability to create, modify, and drop the object specified in the user’s schema.

When a privilege is specified with ANY, the user is authorized to perform the action on any schema in the database. Table 2 shows the types of privileges that are associated with certain types of objects. For example, the SELECT ANY TABLE privilege gives the user the ability to query all tables or views in the database, regardless of who owns them; the SELECT ANY SEQUENCE privilege gives the user the ability to select from all sequences in the database.

Here are some points to remember about system privileges:

  • To connect to the database, you need the CREATE SESSION
  • To truncate a table that belongs to another schema, you need the DROP ANY TABLE
  • The CREATE ANY PROCEDURE (or EXECUTE ANY PROCEDURE) privilege allows the user to create, replace, or drop (or execute) procedures, packages, and functions; this includes Java
  • The CREATE TABLE privilege gives you the ability to create, alter, drop, and query tables in a
  • SELECT, INSERT, UPDATE, and DELETE are object privileges, but SELECT ANY, INSERT ANY, UPDATE ANY, and DELETE ANY are system privileges (in other words, they do not apply to a particular object).

      Granting System Privileges

System privileges are also granted to a user, a role, or PUBLIC by using the GRANT command. The WITH ADMIN OPTION clause gives the grantee the privilege to grant the privilege to another user, role, or PUBLIC. For example, if JOHN needs to create a table under JAMES’s schema, he needs the CREATE ANY TABLE privilege. This privilege not only allows JOHN to create a table under JAMES’s schema, but also allows the creation of a table under any schema in the database.

SQL> GRANT CREATE ANY TABLE TO JOHN;

If John must be able to grant this privilege to others, he should be granted the privilege with the WITH ADMIN OPTION clause (or should have the GRANT ANY PRIVILEGE privilege).

SQL> GRANT CREATE ANY TABLE TO JOHN WITH ADMIN OPTION;

      Revoking Privileges

You can revoke a user’s object privileges and system privileges by using the REVOKE statement. You can revoke a privilege if you have granted it to the user or if you have been granted that privilege with the WITH ADMIN OPTION (for system privileges) or the WITH GRANT OPTION (for object privileges) clauses. Here are some examples of revoking privileges.

To revoke the UPDATE privilege granted to JAMES from JOHN on JOHN’s CUSTOMER table, use the following:

SQL> REVOKE UPDATE ON CUSTOMER FROM JAMES;

To revoke the SELECT ANY TABLE and CREATE TRIGGER privileges granted to JULIE, use the following:

SQL> REVOKE SELECT ANY TABLE, CREATE TRIGGER

FROM JULIE;

The following statement revokes all the privileges granted by JAMES on the STATE table to JULIE. JAMES executes this statement.

SQL> REVOKE ALL ON STATE FROM JULIE;

Keep the following in mind when revoking privileges:

  • If multiple users (or administrators) have granted an object privilege to a user, revoking the privilege by one administrator will not prevent the user from performing the action, because the privileges granted by the other administrators are still
  • To revoke the WITH ADMIN OPTION or WITH GRANT OPTION, you must revoke the privilege and re-grant the privilege without the
  • You cannot selectively revoke column privileges; you must revoke the privileges from the table and grant them again with the appropriate
  • If a user has used their system privileges to create or modify an object, and subsequently the user’s privilege is revoked, no change is made to the objects that the user has already created or The user just can no longer create or modify the object.
  • If a PL/SQL program or view is created based on an object privilege (or a DML system privilege such as SELECT ANY, UPDATE ANY, and so on), revoking the privilege will invalidate the
  • If user A is granted a system privilege WITH ADMIN OPTION, and grants the privilege to user B, user B’s privilege still remains when user A’s privilege is
  • If user A is granted an object privilege WITH GRANT OPTION, and grants the privilege to user B, user B’s privilege is also automatically revoked when user A’s privilege is revoked, and the objects that use the privileges under user A and user B are

Querying Privilege Information

You can query privilege information from the data dictionary by using various views. Table 3 lists and describes the views that provide information related to privileges.

 

View Name Description
ALL_TAB_PRIVS DBA_TAB_PRIVS USER_TAB_PRIVS Lists the object privileges. ALL_TAB_PRIVS shows only the privileges granted to the user and to PUBLIC.
ALL_TAB_PRIVS_MADE USER_TAB_PRIVS_MADE Lists the object grants made by the current user or grants made on the objects owned by the current user.
ALL_TAB_PRIVS_RECD USER_TAB_PRIVS_RECD Lists the object grants received by the current user or PUBLIC.
ALL_COL_PRIVS DBA_COL_PRIVS USER_COL_PRIVS Lists column privileges.
ALL_COL_PRIVS_MADE USER_COL_PRIVS_MADE Lists column privileges made by the current user.
ALL_COL_PRIVS_RECD USER_COL_PRIVS_RECD Lists column privileges received by the current user.
DBA_SYS_PRIVS USER_SYS_PRIVS Lists system privilege information.
SESSION_PRIVS Lists the system privileges available for the current session.

Managing Roles

 

A role is a named group of privileges that you can use to ease the administration of privileges. For example, if your accounting department has 30 users and all need similar access to the tables in the accounts receivable application, you can create a role and grant the appropriate system and object privileges to the role. You can grant the role to each user of the accounting department, instead of granting each object and system privilege to individual users.

Using the CREATE ROLE command creates the role. No user owns the role; it is owned by the database. When a role is created, no privileges are associated with it. You must grant the appropriate privileges to the role. For example, to create a role named ACCTS_RECV and grant certain privileges to the role, use the following:

CREATE ROLE ACCTS_RECV;

GRANT SELECT ON GENERAL_LEDGER TO ACCTS_RECV;

GRANT INSERT, UPDATE ON JOURNAL_ENTRY TO ACCTS_RECV;

Similar to users, roles can also be authenticated. The default is NOT IDENTIFIED, which means no authorization is required to enable or disable the role. The following authorization methods are available:

Database Using a password associated with the role, the database authorizes the role. Whenever such roles are enabled, the user is prompted for a password if the role is not one of the default roles for the user. In the following example, a role ACCOUNTS_MANAGER is created with a password.

SQL> CREATE ROLE ACCOUNTS_MANAGER IDENTIFIED BY ACCMGR;

Operating system The role is authorized by the operating system. This is useful when the operating system can associate its privileges with the application privileges, and information about each user is configured in operating system files. To enable operating system role authorization, set the parameter OS_ROLES to TRUE. The following example creates a role, authorized by the operating system.

SQL> CREATE ROLE APPLICATION_USER IDENTIFIED EXTERNALLY;

You can change the role’s password or authentication method by using the ALTER ROLE command. You cannot rename a role. For example:

SQL> ALTER ROLE ACCOUNTS_MANAGER IDENTIFIED BY MANAGER;

To drop a role, use the DROP ROLE command. Oracle will let you drop a role even if it is granted to users or other roles. When you drop a role, it is immediately removed from the users’ role lists.

SQL> DROP ROLE ACCOUNTS_MANAGER;

Using Predefined Roles

When you create the database, Oracle creates six predefined roles. These roles are defined in the sql.bsq script, which is executed when you run the CREATE DATABASE command. The following roles are predefined:

CONNECT Privilege to connect to the database, to create a cluster, a database link, a sequence, a synonym, a table, and a view, and to alter a session.

RESOURCE Privilege to create a cluster, a table, and a sequence, and to create programmatic objects such as procedures, functions, packages, indextypes, types, triggers, and operators.

DBA All system privileges with the ADMIN option, so the system privileges can be granted to other users of the database or to roles.

SELECT_CATALOG_ROLE Ability to query the dictionary views and tables.

EXECUTE_CATALOG_ROLE Privilege to execute the dictionary packages (SYS- owned packages).

DELETE_CATALOG_ROLE Ability to drop or re-create the dictionary packages.

Also, when you run the catproc.sql script as part of the database creation, the script executes catexp.sql, which creates two more roles:

EXP_FULL_DATABASE Ability to make full and incremental exports of the database using the Export utility.

IMP_FULL_DATABASE Ability to perform full database imports using the Import utility. This is a very powerful role.

Removing Roles

You can remove roles from the database using the DROP ROLE statement. When you drop a role, all privileges that users had through the role are lost. If they used the role to create objects in the database or to manipulate data, those objects and changes remain in the database. To drop a role named HR_UPDATE, use the following statement:

DROP ROLE HR_UPDATE;

To drop a role, you must have been granted the role with the ADMIN OPTION, or you must have the DROP ANY ROLE system privilege.

Enabling and Disabling Roles

If a role is not the default role for a user, it is not enabled when the user connects to the database. You use the ALTER USER command to set the default roles for a user. You can use the DEFAULT ROLE clause with the ALTER USER command in four ways, as illustrated in the following examples.

To specify the named roles CONNECT and ACCOUNTS_MANAGER as default roles, use the following:

ALTER USER JOHN DEFAULT ROLE CONNECT, ACCOUNTS_MANAGER;

To specify all roles granted to the user as the default, use the following:

ALTER USER JOHN DEFAULT ROLE ALL;

To specify all roles except certain roles as the default, use the following:

ALTER USER JOHN DEFAULT ROLE ALL EXCEPT RESOURCE, ACCOUNTS_ADMIN;

To specify no roles as the default, use the following:

ALTER USER JOHN DEFAULT ROLE NONE;

You can specify only roles granted to the user as default roles. The DEFAULT ROLE clause is not available in the CREATE USER command. Default roles are enabled when the user connects to the database and do not require a password.

You enable or disable roles using the SET ROLE command. You specify the maximum number of roles that can be enabled in the initialization parameter MAX_ENABLED_ROLES (the default is 20). You can enable or disable only roles granted to the user. If a role is defined with a password, you must supply the password when you enable the role. For example:

SET ROLE ACCOUNTS_ADMIN IDENTIFIED BY MANAGER;

To enable all roles, specify the following:

SET ROLE ALL;

To enable all roles, except the roles specified, use the following:

SET ROLE ALL EXCEPT RESOURCE, ACCOUNTS_USER;

To disable all roles, including the default roles, use the following:

SET ROLE NONE;

Querying Role Information

 

The data dictionary view DBA_ROLES lists the roles defined in the database. The column PASSWORD specifies the authorization method.

SQL> SELECT * FROM DBA_ROLES;

SQL> SELECT * FROM DBA_ROLE_PRIVS

WHERE GRANTEE = ‘JOHN’;

The view ROLE_ROLE_PRIVS lists the roles granted to the roles, ROLE_SYS_PRIVS lists the system privileges granted to roles, and ROLE_TAB_PRIVS shows information on the object privileges granted to roles.

SQL> SELECT * FROM ROLE_ROLE_PRIVS

WHERE ROLE = ‘DBA’;

SQL> SELECT * FROM ROLE_SYS_PRIVS

WHERE ROLE = ‘CONNECT’;

SQL> SELECT * FROM ROLE_TAB_PRIVS

WHERE TABLE_NAME = ‘CUSTOMER’;

You may also like...