Oracle8i SQL Reference
Release 3 (8.1.7)

Part Number A85397-01

Library

Product

Contents

Index

Go to previous page Go to beginning of chapter Go to next page

SQL Statements:
CREATE CLUSTER to CREATE SEQUENCE, 21 of 25


CREATE PROFILE

Purpose

Use the CREATE PROFILE statement to create a profile, which is a set of limits on database resources. If you assign the profile to a user, that user cannot exceed these limits.

See Also: Oracle8i Administrator's Guide for a detailed description and explanation of how to use password management and protection 

Prerequisites

You must have CREATE PROFILE system privilege.

To specify resource limits for a user, you must:

Syntax


resource_parameters::=


password_parameters::=


Keywords and Parameters

profile

Specify the name of the profile to be created. Use profiles to limit the database resources available to a user for a single call or a single session.

Oracle enforces resource limits in the following ways:

UNLIMITED

When specified with a resource parameter, indicates that a user assigned this profile can use an unlimited amount of this resource. When specified with a password parameter, indicates that no limit has been set for the parameter.

DEFAULT

Specify DEFAULT if you want to omit a limit for this resource in this profile. A user assigned this profile is subject to the limit for this resource specified in the DEFAULT profile. The DEFAULT profile initially defines unlimited resources. You can change those limits with the ALTER PROFILE statement.

Any user who is not explicitly assigned a profile is subject to the limits defined in the DEFAULT profile. Also, if the profile that is explicitly assigned to a user omits limits for some resources or specifies DEFAULT for some limits, the user is subject to the limits on those resources defined by the DEFAULT profile.

resource_parameters

SESSIONS_PER_USER  

Specify the number of concurrent sessions to which you want to limit the user. 

CPU_PER_SESSION  

Specify the CPU time limit for a session, expressed in hundredth of seconds. 

CPU_PER_CALL  

Specify the CPU time limit for a call (a parse, execute, or fetch), expressed in hundredths of seconds.  

CONNECT_TIME  

Specify the total elapsed time limit for a session, expressed in minutes.  

IDLE_TIME  

Specify the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit.  

LOGICAL_READS_PER_SESSION  

Specify the permitted number of data blocks read in a session, including blocks read from memory and disk. 

LOGICAL_READS_PER_CALL  

Specify the permitted the number of data blocks read for a call to process a SQL statement (a parse, execute, or fetch). 

PRIVATE_SGA  

Specify the amount of private space a session can allocate in the shared pool of the system global area (SGA), expressed in bytes. Use K or M to specify this limit in kilobytes or megabytes.  

 

Note: This limit applies only if you are using multi-threaded server architecture. The private space for a session in the SGA includes private SQL and PL/SQL areas, but not shared SQL and PL/SQL areas.

 

COMPOSITE_LIMIT  

Specify the total resources cost for a session, expressed in service units. Oracle calculates the total service units as a weighted sum of CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION, and PRIVATE_SGA.

See Also: ALTER RESOURCE COST for information on how to specify the weight for each session resource

 

password_parameters

FAILED_LOGIN_ATTEMPTS  

Specify the number of failed attempts to log in to the user account before the account is locked. 

PASSWORD_LIFE_TIME  

Specify the number of days the same password can be used for authentication. The password expires if it is not changed within this period, and further connections are rejected.  

PASSWORD_REUSE_TIME  

Specify the number of days before which a password cannot be reused. If you set PASSWORD_REUSE_TIME to an integer value, then you must set PASSWORD_REUSE_MAX to UNLIMITED

PASSWORD_REUSE_MAX  

Specify the number of password changes required before the current password can be reused. If you set PASSWORD_REUSE_MAX to an integer value, then you must set PASSWORD_REUSE_TIME to UNLIMITED

PASSWORD_LOCK_TIME  

Specify the number of days an account will be locked after the specified number of consecutive failed login attempts. 

PASSWORD_GRACE_TIME  

Specify the number of days after the grace period begins during which a warning is issued and login is allowed. If the password is not changed during the grace period, the password expires. 

PASSWORD_VERIFY_FUNCTION  

The PASSWORD_VERIFY_FUNCTION clause lets allows a PL/SQL password complexity verification script to be passed as an argument to the CREATE PROFILE statement. Oracle provides a default script, but you can create your own routine or use third-party software instead. 

 

function 

Specify the name of the password complexity verification routine. 

 

NULL 

Specify NULL to indicate that no password verification is performed. 

Restrictions on password parameters:

Examples

CREATE PROFILE Example

The following statement creates the profile prof:

CREATE PROFILE prof
  LIMIT PASSWORD_REUSE_MAX DEFAULT
        PASSWORD_REUSE_TIME UNLIMITED;

Setting Resource Limits Example

The following statement creates the profile system_manager:

CREATE PROFILE system_manager 
   LIMIT SESSIONS_PER_USER    UNLIMITED 
   CPU_PER_SESSION            UNLIMITED 
   CPU_PER_CALL               3000 
   CONNECT_TIME               45 
   LOGICAL_READS_PER_SESSION  DEFAULT 
   LOGICAL_READS_PER_CALL     1000 
   PRIVATE SGA                15K 
   COMPOSITE_LIMIT            5000000; 

If you then assign the system_manager profile to a user, the user is subject to the following limits in subsequent sessions:

Setting Password Limits Example

The following statement creates profile myprofile with password profile limits values set:

CREATE PROFILE myprofile LIMIT
   FAILED_LOGIN_ATTEMPTS 5
   PASSWORD_LIFE_TIME 60
   PASSWORD_REUSE_TIME 60
   PASSWORD_REUSE_MAX UNLIMITED
   PASSWORD_VERIFY_FUNCTION verify_function
   PASSWORD_LOCK_TIME 1/24
   PASSWORD_GRACE_TIME 10;


Go to previous page Go to beginning of chapter Go to next page
Oracle
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index