Oracle7 Server Administrator's Guide | ![]() Library |
![]() Product |
![]() Contents |
![]() Index |
See Also: For guidelines on establishing security policies for users and profiles, see Chapter 18.
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 20.
For databases using Trusted Oracle, see the Trusted Oracle7 Server Administrator's Guide for additional information about user management in that environment.
This chapter contains several references to Oracle Server Manager. For more information about performing specific tasks using Server Manager/GUI or Server Manager/LineMode, see the Oracle Server Manager User's Guide.
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.
Note: In a few cases, a site might have an unlimited license, rather than concurrent usage or named user licensing. In these cases only, leave the licensing mechanism disabled, and omit LICENSE_MAX_SESSIONS, LICENSE_SESSIONS_WARNING, and LICENSE_MAX_USERS from the parameter file, or set the value of all three to 0.
See Also: For information about the initial installation procedure, see "Edit New Parameter Files" .
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" .
Warning: Sessions that connect to Oracle through multiplexing software or hardware (such as a TP monitor) each contribute individually to the concurrent usage limit. However, the Oracle licensing mechanism cannot distinguish the number of sessions connected this way. If your site uses multiplexing software or hardware, you must consider that and set the maximum concurrent usage limit lower to account for the multiplexed sessions.
See Also: For more information about setting and changing limits in a parallel server environment, see the Oracle7 Parallel Server Concepts & Administration guide.
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.
Set the session warning to a value lower than the concurrent usage maximum limit (LICENSE_MAX_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.
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 the Oracle7 Parallel Server Concepts & Administration guide for more information.
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, you should delete users, or upgrade your Oracle license.
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.
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 88 250
To see the current number of named users defined in the database, use the following query:
SELECT COUNT(*) FROM dba_users;
COUNT(*)
----------
174
Depending on the way you want user identities to be authenticated before they are allowed to create a database session, there are two ways to define users.
1. You can configure Oracle so that it performs both identification and authentication of users.
2. You can configure Oracle so that it performs only the identification of users (leaving authentication up to the operating system or network security service).
You can use the following statement to create a user who is identified and authenticated by Oracle:
CREATE USER scott IDENTIFIED BY tiger;
Use the following command to create a user who is identified by Oracle and authenticated by the operating system or a network service:
CREATE USER scott IDENTIFIED EXTERNALLY;
See Also: For information about network authentication, see Oracle7 Server Distributed Systems, Volume I.
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.
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.
See Also: For more information about valid passwords, see the Oracle7 Server SQL Reference.
You create a user with either the Create User property sheet of Server 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.
The following statement creates a new user named JWARD, identified externally:
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 page 20 - 12.
Usernames in Multi-Byte Character Sets 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.
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.
User Passwords in Multi-Byte Character Sets 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 Oracle7 Server SQL Reference.
Consider the following issues when deciding which tablespace to specify:
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.
Advantage
Warning: When you create a role, it is granted to you implicitly and added as a default role. You will get an error at login if you have more than MAX_ENABLED_ROLES. You can avoid this error by altering the user's default roles to be less than MAX_ENABLED_ROLES. Thus, you should change the DEFAULT ROLE settings of SYS and SYSTEM before creating user roles.
You can alter a user's security settings with either the Alter User property sheet of Server 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:
ALTER USER andy
IDENTIFIED BY swordfish;
A user must have the ALTER USER privilege to change between Oracle authorization and operating system authorization; usually only DBAs should have this privilege.
Passwords in Multi-Byte Character Sets 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 Oracle7 Server SQL Reference.
Note: Oracle automatically enables a user's default roles when the user creates a session. Placing a role in a user's list of default roles bypasses authentication for the role, whether the role is defined to be authorized using a password or the operating system.
If you specify a list of roles, all other roles granted to that user are removed from the user's default role list.
Suppose user AVYRROS has been granted the roles DEVELOPER and CLERK, and CLERK is his only default role. The following statement removes CLERK from his default role list and adds DEVELOPER:
ALTER USER avyrros
DEFAULT ROLE DEVELOPER;
In this case, any roles subsequently granted to AVYRROS will not be default roles, and will be disabled on connection.
If you specify ALL for the user's list of default roles, every role granted directly to the user is automatically added to the user's list of default roles. Subsequent modification of a user's default role list can remove newly granted roles from a user's list of default roles. The following example causes all roles currently granted to AVYRROS to be added to his list of default roles, as well as all roles granted in the future:
ALTER USER avyrros
DEFAULT ROLE ALL;
Furthermore, you can specify ALL EXCEPT with a list of roles, and those roles will be the only roles granted to the user not on the default role list. For example, the following statement adds all roles currently granted to AVYRROS (except the role PAYROLL) to the user's default role list. Any roles granted to AVYRROS in the future are also added to the default role list:
ALTER USER avyrros
DEFAULT ROLE ALL EXCEPT payroll;
To ensure a user has no default roles, specify NONE for the user's list of default roles:
ALTER USER avyrros
DEFAULT ROLE NONE;
Changing a user's default role list affects subsequent sessions; it does not affect any session in progress at the time.
Revoking a role from a user automatically removes the role from the user's default role list.
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 Server 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.
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.
The following statement drops the user JONES, all objects in JONES' schema, and any dependent foreign keys:
DROP USER jones CASCADE;
See Also: For more information about terminating sessions, see "Terminating Sessions" .
This section describes aspects of profile management, and includes the following topics:
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.
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.
Profiles can be assigned to users using the Assign Profile dialog box of Server Manager/GUI, or the SQL commands CREATE USER or ALTER USER.
See Also: For more information about assigning a profile to a user, see page 19 - 9 and page 19 - 12.
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" .
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.
To set resource costs, you must have the ALTER RESOURCE system privilege.
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.
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.
To alter the enforcement of resource limitation while the database remains open, you must have the ALTER SYSTEM system privilege.
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.
CREATE PROFILE clerk LIMIT
SESSIONS_PER_USER 1
IDLE_TIME 30
CONNECT_TIME 600;
CREATE USER jfee
IDENTIFIED BY wildcat
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp_ts
QUOTA 500K ON users
PROFILE clerk;
CREATE USER tsmith
IDENTIFIED BY bedrock
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp_ts
QUOTA unlimited ON users;
SELECT * FROM sys.dba_users;
USERNA USER_ID PASSWORD DEFAUL TEMPOR CREATED PROFILE
------ -------- ---------------- ------ ------ --------- --------
SYS % 522D06CDE017CF93 SYSTEM SYSTEM 31-JUL-90 PUBLIC...
SYSTEM % 9B30B3EB7A7EE46A SYSTEM SYSTEM 31-JUL-90 PUBLIC...
JFEE % DEE4F647381D62C4 USERS TEMP_TS 12-SEP-90 CLERK
TSMITH % 4791F162172E7834 USERS TEMP_TS 12-SEP-90 PUBLIC...
All passwords are encrypted to preserve security.
SELECT * FROM sys.dba_role_privs where grantee = 'JFEE';
GRANTEE GRANTED_ROLE ADM DEF
-------------------------- ------------------------- --- ---
JFEE CLERK YES YES
JFEE PAYROLL NO NO
JFEE WEEKLY_ADMIN NO NO
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 TSMITH 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".
SELECT * FROM sys.dba_profiles
ORDER BY profile;
PROFILE RESOURCE_NAME LIMIT
--------------- -------------------------------- --------------
CLERK COMPOSITE_LIMIT UNLIMITED
CLERK SESSIONS_PER_USER 1
CLERK CPU_PER_SESSION UNLIMITED
CLERK CPU_PER_CALL UNLIMITED
CLERK LOGICAL_READS_PER_SESSION UNLIMITED
CLERK LOGICAL_READS_PER_CALL UNLIMITED
CLERK IDLE_TIME 30
CLERK CONNECT_TIME 600
CLERK PRIVATE_SGA UNLIMITED
DEFAULT COMPOSITE_LIMIT UNLIMITED
DEFAULT SESSIONS_PER_USER UNLIMITED
DEFAULT CPU_PER_SESSION UNLIMITED
DEFAULT CPU_PER_CALL UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL UNLIMITED
DEFAULT IDLE_TIME UNLIMITED
DEFAULT CONNECT_TIME UNLIMITED
DEFAULT PRIVATE_SGA UNLIMITED
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'.
![]() ![]() Prev Next |
![]() Copyright © 1996 Oracle Corporation. All Rights Reserved. |
![]() Library |
![]() Product |
![]() Contents |
![]() Index |