Release 8.0
A58397-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8 Administrator's Guide Release 8.0 A58397-01 |
|
This chapter describes how to control access to an Oracle database, and includes the following topics:
See Also: For guidelines on establishing security policies for users and profiles, see Chapter 19, Establishing Security Policies.
Privileges and roles control the access a user has to a database and the schema objects within the database. For information on privileges and roles, see Chapter 21, Managing User Privileges and Roles.
This chapter contains several references to Oracle Enterprise Manager. For more information about performing specific tasks using Enterprise Manager/GUI or Server Manager/LineMode, see the Oracle Server Manager User's Guide or Oracle Enterprise Manager Administrator's Guide.
This section describes aspects of session and user licensing, and includes the following topics:
Oracle helps you ensure that your site complies with its Oracle Server license agreement. If your site is licensed by concurrent usage, you can track and limit the number of sessions concurrently connected to a database. If your site is licensed by named users, you can limit the number of named users created in a database. In either case, you control the licensing facilities, and must enable the facilities and set the appropriate limits.
To use the licensing facility, you need to know which type of licensing agreement your site has, and what the maximum number of sessions or named users is. Your site may use either type of licensing (concurrent usage or named user), but not both.
Concurrent usage licensing limits the number of sessions that can be connected simultaneously to the database on the specified computer. You can set a limit on the number of concurrent sessions before you start an instance. In fact, you should have set this limit as part of the initial installation procedure. You can also change the maximum number of concurrent sessions while the database is running.
See Also: For information about the initial installation procedure, see Chapter 2, "Creating an Oracle Database".
After your instance's session limit is reached, only users with RESTRICTED SESSION privilege (usually DBAs) can connect to the database. When a user with RESTRICTED SESSION privileges connects, Oracle sends the user a message indicating that the maximum limit has been reached, and writes a message to the ALERT file. When the maximum is reached, you should connect only to terminate unneeded processes. Do not raise the licensing limits unless you have upgraded your Oracle license agreement.
In addition to setting a maximum concurrent session limit, you can set a warning limit on the number of concurrent sessions. After this limit is reached, additional users can continue to connect (up to the maximum limit); however, Oracle writes an appropriate message to the ALERT file with each connection, and sends each connecting user who has the RESTRICTED SESSION privilege a warning indicating that the maximum is about to be reached.
If a user is connecting with administrator privileges, the limits still apply; however, Oracle enforces the limit after the first statement the user executes.
In addition to enforcing the concurrent usage limits, Oracle tracks the highest number of concurrent sessions for each instance. You can use this "high water mark."
See Also: For information about terminating sessions, see "Terminating Sessions".
For information about Oracle licensing limit upgrades, see "Viewing Licensing Limits and Current Values".
For instances running with the Parallel Server, each instance can have its own concurrent usage limit and warning limit. However, the sum of the instances' limits must not exceed the site's concurrent usage license.
See Also: For more information about setting and changing limits in a parallel server environment, see Oracle8 Parallel Server Concepts and Administration.
To set the maximum number of concurrent sessions for an instance, set the parameter LICENSE_MAX_SESSIONS as follows:
LICENSE_MAX_SESSIONS = 80
If you set this limit, you are not required to set a warning limit (LICENSE_SESSIONS_WARNING). However, using the warning limit makes the maximum limit easier to manage, because it gives you advance notice that your site is nearing maximum use.
To set the warning limit for an instance, set the parameter LICENSE_SESSIONS_WARNING in the parameter file used to start the instance.
Set the session warning to a value lower than the concurrent usage maximum limit (LICENSE_MAX_SESSIONS).
To change either the maximum concurrent usage limit or the warning limit while the database is running, use the ALTER SYSTEM command with the appropriate option. The following statement changes the maximum limit to 100 concurrent sessions:
ALTER SYSTEM SET LICENSE_MAX_SESSIONS = 100;
The following statement changes both the warning limit and the maximum limit:
ALTER SYSTEM SET LICENSE_MAX_SESSIONS = 64 LICENSE_SESSIONS_WARNING = 54;
If you change either limit to a value lower than the current number of sessions, the current sessions remain; however, the new limit is enforced for all future connections until the instance is shut down. To change the limit permanently, change the value of the appropriate parameter in the parameter file.
To change the concurrent usage limits while the database is running, you must have the ALTER SYSTEM privilege. Also, to connect to an instance after the instance's maximum limit has been reached, you must have the RESTRICTED SESSION privilege.
|
WARNING: Do not raise the concurrent usage limits unless you have appropriately upgraded your Oracle Server license. Contact your Oracle representative for more information. |
Named user licensing limits the number of individuals authorized to use Oracle on the specified computer. To enforce this license, you can set a limit on the number of users created in the database before you start an instance. You can also change the maximum number of users while the instance is running, or disable the limit altogether. You cannot create more users after reaching this limit. If you try to do so, Oracle returns an error indicating that the maximum number of users have been created, and writes a message to the ALERT file.
This mechanism operates on the assumption that each person accessing the database has a unique username, and that there are no shared usernames. Do not allow multiple users to connect using the same username.
See Also: For instances running with the Parallel Server, all instances connected to the same database should have the same named user limit. See Oracle8 Parallel Server Concepts and Administration for more information.
To limit the number of users created in a database, set the LICENSE_MAX_USERS parameter in the database's parameter file. The following example sets the maximum number of users to 200:
LICENSE_MAX_USERS = 200
If the database contains more than LICENSE_MAX_USERS when you start it, Oracle returns a warning and writes an appropriate message in the ALERT file. You cannot create additional users until the number of users drops below the limit or until you delete users or upgrade your Oracle license.
To change the maximum named users limit, use the ALTER SYSTEM command with the LICENSE_MAX_USERS option. The following statement changes the maximum number of defined users to 300:
ALTER SYSTEM SET LICENSE_MAX_USERS = 300;
If you try to change the limit to a value lower than the current number of users, Oracle returns an error and continues to use the old limit. If you successfully change the limit, the new limit remains in effect until you shut down the instance; to change the limit permanently, change the value of LICENSE_MAX_USERS in the parameter file.
To change the maximum named users limit, you must have the ALTER SYSTEM privilege.
|
WARNING: Do not raise the named user limit unless you have appropriately upgraded your Oracle license. Contact your Oracle representative for more information. |
You can see the current limits of all of the license settings, the current number of sessions, and the maximum number of concurrent sessions for the instance by querying the V$LICENSE data dictionary view. You can use this information to determine if you need to upgrade your Oracle license to allow more concurrent sessions or named users:
SELECT sessions_max s_max, sessions_warning s_warning, sessions_current s_current, sessions_highwater s_high, users_max FROM v$license; S_MAX S_WARNING S_CURRENT S_HIGH USERS_MAX ------------------------------------------------------- 100 80 65 82 50
In addition, Oracle writes the session high water mark to the database's ALERT file when the database shuts down, so you can check for it there.
To see the current number of named users defined in the database, use the following query:
SELECT COUNT(*) FROM dba_users; COUNT(*) ---------- 174
This section describes aspects of authenticating users, and includes the following topics:
Depending on how you want user identities to be authenticated, there are three ways to define users before they are allowed to create a database session:
If you choose database authentication for a user, administration of the user account, password, and authentication of that user is performed entirely by Oracle. To have Oracle authenticate a user, specify a password for the user when you create or alter the user. Users can change their password at any time. Passwords are stored in an encrypted format. Each password must be made up of single-byte characters, even if your database uses a multi-byte character set.
To enhance security when using database authentication, Oracle recommends the use of password management, including account locking, password aging and expiration, password history, and password complexity verification.
The following statement creates a user who is identified and authenticated by Oracle:
CREATE USER scott IDENTIFIED BY tiger;
See Also: For more information about the CREATE USER and ALTER USER commands, see Oracle8 SQL Reference.
For more information about valid passwords, see Oracle8 SQL Reference.
For more information about Oracle password management, see Chapter 19, "Establishing Security Policies".
Following are advantages of database authentication:
When you choose external authentication for a user, the user account is maintained by Oracle, but password administration and user authentication is performed by an external service. This external service can be the operating system or a network service, such as the Oracle Advanced Networking Option (ANO).
With external authentication, your database relies on the underlying operating system or network authentication service to restrict access to database accounts. A database password is not used for this type of login. If your operating system or network service permits, you can have it authenticate users. If you do so, set the parameter OS_AUTHENT_PREFIX, and use this prefix in Oracle usernames. This parameter defines a prefix that Oracle adds to the beginning of every user's operating system account name. Oracle compares the prefixed username with the Oracle usernames in the database when a user attempts to connect.
For example, assume that OS_AUTHENT_PREFIX is set as follows:
OS_AUTHENT_PREFIX=OPS$
If a user with an operating system account named "TSMITH" is to connect to an Oracle database and be authenticated by the operating system, Oracle checks that there is a corresponding database user "OPS$TSMITH" and, if so, allows the user to connect. All references to a user authenticated by the operating system must include the prefix, as seen in "OPS$TSMITH".
The default value of this parameter is "OPS$" for backward compatibility with previous versions of Oracle. However, you might prefer to set the prefix value to some other string or a null string (an empty set of double quotes: ""). Using a null string eliminates the addition of any prefix to operating system account names, so that Oracle usernames exactly match operating system usernames.
After you set OS_AUTHENT_PREFIX, it should remain the same for the life of a database. If you change the prefix, any database username that includes the old prefix cannot be used to establish a connection, unless you alter the username to have it use password authentication.
The following command creates a user who is identified by Oracle and authenticated by the operating system or a network service:
CREATE USER scott IDENTIFIED EXTERNALLY;
Using CREATE USER IDENTIFIED EXTERNALLY, you can create database accounts that must be authenticated via the operating system or network service and cannot be authenticated using a password.
See Also: The text of the OS_AUTHENT_PREFIX parameter is case-sensitive on some operating systems. See your operating system-specific Oracle documentation for more information about this initialization parameter.
By default, Oracle only allows operating system authenticated logins over secure connections. Therefore, if you want the operating system to authenticate a user, by default that user cannot connect to the database over Net8. This means the user cannot connect using a multi-threaded server, since this connection uses Net8. This default restriction prevents a remote user from impersonating another operating system user over a network connection.
If you are not concerned about remote users impersonating another operating system user over a network connection, and you want to use operating system user authentication with network clients, set the parameter REMOTE_OS_AUTHENT (default is FALSE) to TRUE in the database's parameter file. Setting the initialization parameter REMOTE_OS_AUTHENT to TRUE allows the RDBMS to accept the client operating system username received over a non-secure connection and use it for account access. The change will take effect the next time you start the instance and mount the database.
Network authentication is performed via the Oracle Advanced Networking Option (ANO), which may be configured to use a third party service such as Kerberos. If you are using ANO as the only external authentication service, the setting of the parameter REMOTE_OS_AUTHENT is irrelevant, since ANO only allows secure connections.
See Also: For information about network authentication, see Oracle8 Distributed Database Systems, and Oracle Security Server Guide.
Following are advantages of external authentication:
If you choose enterprise authentication for a user, the user account is maintained by Oracle, but password administration and user authentication is performed by the Oracle Security Service (OSS). This authentication service can be shared among multiple Oracle database servers and allows user's authentication and authorization information to be managed centrally.
Use the following command to create a user (known as a global user) who is identified by Oracle and authenticated by the Oracle Security Service:
CREATE USER scott IDENTIFIED GLOBALLY as '<external name>';
See Also: For information about the contents of the <EXTERNAL NAME> string, see Oracle8 Distributed Database Systems, and Oracle Security Server Guide.
Following are advantages of enterprise authentication:
See Also: For information about enterprise authentication, see Oracle8 Distributed Database Systems, and Oracle Security Server Guide.
Each Oracle database has a list of valid database users. To access a database, a user must run a database application and connect to the database instance using a valid username defined in the database. This section explains how to manage users for a database, and includes the following topics:
To create a database user, you must have the CREATE USER system privilege. When creating a new user, tablespace quotas can be specified for any tablespace in the database, even if the creator does not have a quota on a specified tablespace. Due to such privileged power, a security administrator is normally the only type of user that has the CREATE USER system privilege.
You create a user with either the Create User property sheet of Enterprise Manager/GUI, or the SQL command CREATE USER. Using either option, you can also specify the new user's default and temporary segment tablespaces, tablespace quotas, and profile.
CREATE USER OPS$jward IDENTIFIED EXTERNALLY DEFAULT TABLESPACE data_ts TEMPORARY TABLESPACE temp_ts QUOTA 100M ON test_ts QUOTA 500K ON data_ts PROFILE clerk;
See Also: A newly-created user cannot connect to the database until granted the CREATE SESSION system privilege; see "Granting System Privileges and Roles".
Within each database a username must be unique with respect to other usernames and roles; a user and role cannot have the same name. Furthermore, each user has an associated schema. Within a schema, each schema object must have unique names.
In a database that uses a multi-byte character set, each username should contain at least one single-byte character. If a username contains only multi-byte characters, the encrypted username/password combination is considerably less secure.
In the previous CREATE USER statement, the new user is to be authenticated using the operating system. The username includes the default prefix "OPS$." If the OS_AUTHENT_PREFIX parameter is set differently (that is, if it specifies either no prefix or some other prefix), modify the username accordingly, by omitting the prefix or substituting the correct prefix.
Alternatively, you can create a user who is authenticated using the database and a password:
CREATE USER jward IDENTIFIED BY airplane . . . ;
In this case, the connecting user must supply the correct password to the database to connect successfully.
In a database that uses a multi-byte character set, passwords must include only single-byte characters. Multi-byte characters are not accepted in passwords.
See Also: For more information about valid passwords, see the Oracle8 SQL Reference.
Each user has a default tablespace. When a user creates a schema object and specifies no tablespace to contain it, Oracle stores the object in the user's default tablespace.
The default setting for every user's default tablespace is the SYSTEM tablespace. If a user does not create objects, this default setting is fine. However, if a user creates any type of object, consider specifically setting the user's default tablespace. You can set a user's default tablespace during user creation, and change it later. Changing the user's default tablespace affects only objects created after the setting is changed.
Consider the following issues when deciding which tablespace to specify:
In the previous CREATE USER statement, JWARD's default tablespace is DATA_TS.
Each user also has a temporary tablespace. When a user executes a SQL statement that requires a temporary segment, Oracle stores the segment in the user's temporary tablespace.
If a user's temporary tablespace is not explicitly set, the default is the SYSTEM tablespace. However, setting each user's temporary tablespace reduces file contention among temporary segments and other types of segments. You can set a user's temporary tablespace at user creation, and change it later.
In the previous CREATE USER statement, JWARD's temporary tablespace is TEMP_TS, a tablespace created explicitly to only contain temporary segments.
You can assign each user a tablespace quota for any tablespace. Assigning a quota does two things:
By default, a user has no quota on any tablespace in the database. If the user has the privilege to create a schema object, you must assign a quota to allow the user to create objects. Minimally, assign users a quota for the default tablespace, and additional quotas for other tablespaces in which they will create objects.
You can assign a user either individual quotas for a specific amount of disk space in each tablespace or an unlimited amount of disk space in all tablespaces. Specific quotas prevent a user's objects from consuming too much space in the database.
You can assign a user's tablespace quotas when you create the user, or add or change quotas later. If a new quota is less than the old one, then the following conditions hold true:
You can revoke a user's tablespace access by changing the user's current quota to zero. After a quota of zero is assigned, the user's objects in the revoked tablespace remain, but the objects cannot be allocated any new space.
To permit a user to use an unlimited amount of any tablespace in the database, grant the user the UNLIMITED TABLESPACE system privilege. This overrides all explicit tablespace quotas for the user. If you later revoke the privilege, explicit quotas again take effect. You can grant this privilege only to users, not to roles.
Before granting the UNLIMITED TABLESPACE system privilege, consider the consequences of doing so:
Advantage
Disadvantages
You cannot set a user's default roles in the CREATE USER statement. When you first create a user, the user's default role setting is ALL, which causes all roles subsequently granted to the user to be default roles. Use the ALTER USER command to change the user's default roles.
Users can change their own passwords. However, to change any other option of a user's security domain, you must have the ALTER USER system privilege. Security administrators are normally the only users that have this system privilege, as it allows a modification of any user's security domain. This privilege includes the ability to set tablespace quotas for a user on any tablespace in the database, even if the user performing the modification does not have a quota for a specified tablespace.
You can alter a user's security settings with either the Alter User property sheet of Enterprise Manager/GUI, or the SQL command ALTER USER. Changing a user's security settings affects the user's future sessions, not current sessions.
The following statement alters the security settings for user AVYRROS:
ALTER USER avyrros IDENTIFIED EXTERNALLY DEFAULT TABLESPACE data_ts TEMPORARY TABLESPACE temp_ts QUOTA 100M ON data_ts QUOTA 0 ON test_ts PROFILE clerk;
The ALTER USER statement here changes AVYRROS's security settings as follows:
While most non-DBA users do not use Enterprise Manager, they can still change their own passwords with the ALTER USER command, as follows:
ALTER USER andy IDENTIFIED BY swordfish;
Users can change their own passwords this way, without any special privileges (other than those to connect to the database). Users should be encouraged to change their passwords frequently.
Users must have the ALTER USER privilege to switch between Oracle database authentication, external authentication, and enterprise authentication; usually, only DBAs should have this privilege.
In a database that uses a multi-byte character set, passwords must include only single-byte characters. Multi-byte characters are not accepted in passwords.
See Also: For more information about valid passwords, see the Oracle8 SQL Reference.
A default role is one that is automatically enabled for a user when the user creates a session. You can assign a user zero or more default roles.
See Also: For more information on changing users' default roles, see Chapter 21, "Managing User Privileges and Roles".
When a user is dropped, the user and associated schema is removed from the data dictionary and all schema objects contained in the user's schema, if any, are immediately dropped.
|
Note: If a user's schema and associated objects must remain but the user must be revoked access to the database, revoke the CREATE SESSION privilege from the user. |
A user that is currently connected to a database cannot be dropped. To drop a connected user, you must first terminate the user's sessions using either Enterprise Manager/GUI, or the SQL command ALTER SYSTEM with the KILL SESSION clause.
To drop a user and all the user's schema objects (if any), you must have the DROP USER system privilege. Because the DROP USER system privilege is so powerful, a security administrator is typically the only type of user that has this privilege.
You can drop a user from a database using either the Drop menu item of Enterprise Manager/GUI, or the SQL command DROP USER.
If the user's schema contains any schema objects, use the CASCADE option to drop the user and all associated objects and foreign keys that depend on the tables of the user successfully. If you do not specify CASCADE and the user's schema contains objects, an error message is returned and the user is not dropped. Before dropping a user whose schema contains objects, thoroughly investigate which objects the user's schema contains and the implications of dropping them before the user is dropped. Pay attention to any unknown cascading effects. For example, if you intend to drop a user who owns a table, check whether any views or procedures depend on that particular table.
DROP USER jones CASCADE;
See Also: For more information about terminating sessions, see "Terminating Sessions".
A profile is a named set of resource limits. If resource limits are turned on, Oracle limits database usage and instance resources to whatever is defined in the user's profile. You can assign a profile to each user, and a default profile to all users who do not have specific profiles. For profiles to take effect, resource limits must be turned on for the database as a whole.
This section describes aspects of profile management, and includes the following topics:
To create a profile, you must have the CREATE PROFILE system privilege. You can create profiles using either the Create Profile property sheet of Enterprise Manager/GUI, or the SQL command CREATE PROFILE. At the same time, you can explicitly set particular resource limits.
The following statement creates the profile CLERK:
CREATE PROFILE clerk LIMIT SESSIONS_PER_USER 2 CPU_PER_SESSION unlimited CPU_PER_CALL 6000 LOGICAL_READS_PER_SESSION unlimited LOGICAL_READS_PER_CALL 100 IDLE_TIME 30 CONNECT_TIME 480;
All unspecified resource limits for a new profile take the limit set by the DEFAULT profile. You can also specify limits for the DEFAULT profile.
Each database has a DEFAULT profile, and its limits are used in two cases:
Initially, all limits of the DEFAULT profile are set to UNLIMITED. However, to prevent unlimited resource consumption by users of the DEFAULT profile, the security administrator should change the default limits using the Alter Profile dialog box of Enterprise Manager, or a typical ALTER PROFILE statement:
ALTER PROFILE default LIMIT . . . ;
Any user with the ALTER PROFILE system privilege can adjust the limits in the DEFAULT profile. The DEFAULT profile cannot be dropped.
After a profile has been created, you can assign it to database users. Each user can be assigned only one profile at any given time. If a profile is assigned to a user who already has a profile, the new profile assignment overrides the previously assigned profile. Profile assignments do not affect current sessions. Profiles can be assigned only to users and not to roles or other profiles.
Profiles can be assigned to users using the Assign Profile dialog box of Enterprise Manager/GUI, or the SQL commands CREATE USER or ALTER USER.
See Also: For more information about assigning a profile to a user, see "Creating Users" and "Altering Users".
You can alter the resource limit settings of any profile using either the Alter Profile property sheet of Enterprise Manager/GUI or the SQL command ALTER PROFILE. To alter a profile, you must have the ALTER PROFILE system privilege.
Any adjusted profile limit overrides the previous setting for that profile limit. By adjusting a limit with a value of DEFAULT, the resource limit reverts to the default limit set for the database. All profiles not adjusted when altering a profile retain the previous settings. Any changes to a profile do not affect current sessions. New profile settings are used only for sessions created after a profile is modified.
The following statement alters the CLERK profile:
ALTER PROFILE clerk LIMIT CPU_PER_CALL default LOGICAL_READS_PER_SESSION 20000;
See Also: For information about default profiles, see "Using the DEFAULT Profile".
You can limit the total resource cost for a session via composite limits. In addition to setting specific resource limits explicitly for a profile, you can set a single composite limit that accounts for all resource limits in a profile. You can set a profile's composite limit using the Composite Limit checkbox of the Create Profile and Alter Profile property sheets of Enterprise Manager/GUI, or the COMPOSITE_LIMIT parameter of the SQL commands CREATE PROFILE or ALTER PROFILE. A composite limit is set via a service unit, which is a weighted sum of all resources used.
The following CREATE PROFILE statement is defined using the COMPOSITE_LIMIT parameter:
CREATE PROFILE clerk LIMIT COMPOSITE_LIMIT 20000 SESSIONS_PER_USER 2 CPU_PER_CALL 1000;
Notice that both explicit resource limits and a composite limit can exist concurrently for a profile. The limit that is reached first stops the activity in a session. Composite limits allow additional flexibility when limiting the use of system resources.
The correct service unit setting for a composite limit depends on the total amount of resource used by an average profile user. As with each specific resource limit, historical information should be gathered to determine the normal range of composite resource usage for a typical profile user.
Each system has its own characteristics; some system resources may be more valuable than others. Oracle enables you to give each system resource a cost. Costs weight each system resource at the database level. Costs are only applied to the composite limit of a profile; costs do not apply to set individual resource limits explicitly.
To set resource costs, you must have the ALTER RESOURCE system privilege.
Only certain resources can be given a cost, including CPU_PER_ SESSION, LOGICAL_READS_PER_SESSION, CONNECT_TIME, and PRIVATE_SGA. Set costs for a database using the SQL command ALTER RESOURCE COST:
ALTER RESOURCE COST CPU_PER_SESSION 1 LOGICAL_READS_PER_SESSION 50;
A large cost means that the resource is very expensive, while a small cost means that the resource is not expensive. By default, each resource is initially given a cost of 0. A cost of 0 means that the resource should not be considered in the composite limit (that is, it does not cost anything to use this resource). No resource can be given a cost of NULL.
See Also: For additional information and recommendations on setting resource costs, see your operating system-specific Oracle documentation.
To drop a profile, you must have the DROP PROFILE system privilege. You can drop a profile using either Enterprise Manager/GUI, or the SQL command DROP PROFILE. To successfully drop a profile currently assigned to a user, use the CASCADE option.
The following statement drops the profile CLERK, even though it is assigned to a user:
DROP PROFILE clerk CASCADE;
Any user currently assigned to a profile that is dropped is automatically assigned to the DEFAULT profile. The DEFAULT profile cannot be dropped. Note that when a profile is dropped, the drop does not affect currently active sessions; only sessions created after a profile is dropped abide by any modified profile assignments.
A profile can be created, assigned to users, altered, and dropped at any time by any authorized database user, but the resource limits set for a profile are enforced only when you enable resource limitation for the associated database. Resource limitation enforcement can be enabled or disabled by two different methods, as described in the next two sections.
To alter the enforcement of resource limitation while the database remains open, you must have the ALTER SYSTEM system privilege.
If a database can be temporarily shut down, resource limitation can be enabled or disabled by the RESOURCE_LIMIT initialization parameter in the database's parameter file. Valid values for the parameter are TRUE (enables enforcement) and FALSE; by default, this parameter's value is set to FALSE. Once the parameter file has been edited, the database instance must be restarted to take effect. Every time an instance is started, the new parameter value enables or disables the enforcement of resource limitation.
If a database cannot be temporarily shut down or the resource limitation feature must be altered temporarily, you can enable or disable the enforcement of resource limitation using the SQL command ALTER SYSTEM. After an instance is started, an ALTER SYSTEM statement overrides the value set by the RESOURCE_LIMIT parameter. For example, the following statement enables the enforcement of resource limitation for a database:
ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;
An ALTER SYSTEM statement does not permanently determine the enforcement of resource limitation. If the database is shut down and restarted, the enforcement of resource limits is determined by the value set for the RESOURCE_LIMIT parameter.
The data dictionary stores information about every user and profile, including the following:
The following data dictionary views may be of interest when you work with database users and profiles:
See Also: See the Oracle8 Reference for detailed information about each view.
The examples in this section assume a database in which the following statements have been executed:
CREATE PROFILE clerk LIMIT SESSIONS_PER_USER 1 IDLE_TIME 30 CONNECT_TIME 600; CREATE USER jfee DENTIFIED BY wildcat DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp_ts QUOTA 500K ON users PROFILE clerk; CREATE USER dcranney IDENTIFIED BY bedrock DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp_ts QUOTA unlimited ON users; CREATE USER userscott IDENTIFIED BY "scott1" PASSWORD_LIFETIME 60 PASSWORD_GRACE_TIME 10;
The following query lists users and their associated information as defined in the database:
SELECT username, profile, account_status from dba_users; USERNAME PROFILE ACCOUNT_STATUS --------------- --------------- ---------------- SYS DEFAULT OPEN SYSTEM DEFAULT OPEN BLAKE DEFAULT OPEN SCOTT DEFAULT OPEN ADAMS DEFAULT OPEN JONES DEFAULT OPEN CLARK DEFAULT OPEN U DEFAULT LOCKED USERSCOTT PROF EXPIRED
All passwords are encrypted to preserve security.
The following query lists all tablespace quotas specifically assigned to each user:
SELECT * FROM sys.dba_ts_quotas; TABLESPACE USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS ---------- --------- -------- ---------- ------- ---------- SYSTEM SYSTEM 0 0 0 0 SYSTEM JFEE 0 512000 0 250 SYSTEM DCRANNEY 0 -1 0 -1
When specific quotas are assigned, the exact number is indicated in the MAX_BYTES column. Unlimited quotas are indicated by "-1".
The following query lists all profiles in the database and associated settings for each limit in each profile:
SELECT * FROM sys.dba_profiles ORDER BY profile; PROFILE RESOURCE_NAME RESOURCE LIMIT ------------------------- --------------- ---------- -------------- DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED DEFAULT SESSIONS_PER_USER KERNEL 1 DEFAULT CPU_PER_CALL KERNEL UNLIMITED DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED DEFAULT CONNECT_TIME KERNEL 30 DEFAULT IDLE_TIME KERNEL 600 DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED DEFAULT CPU_PER_SESSION KERNEL UNLIMITED DEFAULT PRIVATE_SGA KERNEL UNLIMITED DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED DEFAULT PASSWORD_LOCK_TIME PASSWORD UNLIMITED DEFAULT PASSWORD_GRACE_TIME PASSWORD UNLIMITED DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD UNLIMITED DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED PROF COMPOSITE_LIMIT KERNEL DEFAULT PROF PRIVATE_SGA KERNEL DEFAULT PROF CONNECT_TIME KERNEL DEFAULT PROF IDLE_TIME KERNEL DEFAULT PROF LOGICAL_READS_PER_CALL KERNEL DEFAULT PROF LOGICAL_READS_PER_SESSION KERNEL DEFAULT PROF SESSIONS_PER_USER KERNEL DEFAULT PROF CPU_PER_CALL KERNEL DEFAULT PROF CPU_PER_SESSION KERNEL DEFAULT PROF FAILED_LOGIN_ATTEMPTS PASSWORD 5 PROF PASSWORD_LIFE_TIME PASSWORD 60 PROF PASSWORD_REUSE_MAX PASSWORD UNLIMITED PROF PASSWORD_LOCK_TIME PASSWORD 1 PROF PASSWORD_GRACE_TIME PASSWORD 10 PROF PASSWORD_VERIFY_FUNCTION PASSWORD UNLIMITED PROF PASSWORD_REUSE_TIME PASSWORD 60 32 rows selected.
The following query lists all current sessions, showing the Oracle user and current memory use per session:
SELECT username, value || 'bytes' "Current session memory" FROM v$session sess, v$sesstat stat, v$statname name WHERE sess.sid = stat.sid AND stat.statistic# = name.statistic# AND name.name = 'session memory';
The amount of space indicated in "Current session memory" is allocated in the shared pool for each session connected through the multi-threaded server. You can limit the amount of memory allocated per user with the PRIVATE_SGA resource limit.
To see the maximum memory ever allocated to each session since the instance started, replace 'session memory' in the query above with 'max session memory'.
This section contains examples that use functions described throughout this chapter.
CREATE PROFILE prof limit FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LIFE_TIME 60 PASSWORD_REUSE_MAX 60 PASSWORD_REUSE_MAX UNLIMITED PASSWORD_VERIFY_FUNCTION verify_function PASSWORD_LOCK_TIME 1 PASSWORD_GRACE_TIME 10;
