只问耕耘
分类： Oracle
2007-08-20 11:09:20
|
See Also:
For more information on the SQL statements for managing users, as presented in succeeding sections, see the . |
|
|
To create a database user, you must have the CREATE USER system privilege. When creating a new user, tablespace quotas can be specified for tablespaces in the database, even if the creator does not have a quota on a specified tablespace. Because it is a powerful privilege, a security administrator is normally the only user who has the CREATE USER system privilege.
You create a user with the SQL statement CREATE USER. The following example creates a user and specifies that user's password, default tablespace, temporary tablespace where temporary segments are created, tablespace quota, and profile.
CREATE USER jward
IDENTIFIED BY AIRPLANE
DEFAULT TABLESPACE data_ts
TEMPORARY TABLESPACE temp_ts
QUOTA 100M ON test_ts
QUOTA 500K ON data_ts
PROFILE clerk;
GRANT connect TO jward;
A newly created user cannot connect to the database until granted the CREATE SESSION system privilege. Usually, a newly created user is granted a role, similar to the predefined roll CONNECT, as shown in this example, that specifies the CREATE SESSION and other basic privileges required to access a database. See for specific information.
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 a unique name.
In the previous CREATE USER statement, the new user is to be authenticated using the database. In this case, the connecting user must supply the correct password to the database to connect successfully.
The methods of specifying the type of user authentication were discussed earlier in .
Each user should have 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 should be assigned 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. You do not set a quota for temporary tablespaces. These temporary segments are created by the system when doing sorts or joins and are owned by SYS, which has resource privileges in all tablespaces.
If a user's temporary tablespace is not explicitly set, the default is the SYSTEM tablespace. Setting each user's temporary tablespace reduces file contention among temporary segments and other types of segments, as the temporary tablespace is used exclusively for temporary 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, but quotas are not necessary for a temporary 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 also specify a profile when you create a user. A profile is a set of limits on database resources. If no profile is specified, the user is assigned a default profile. For information on profiles see and "Password Management Policy".
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 statement to change the user's default roles. See .
|
WARNING: When you create a role (other than a user 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. |
|
|
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 the SQL statement 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:
Most non-DBA users can still change their own passwords with the ALTER USER statement, 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 or a form of external authentication. Usually, only DBAs should have this privilege.
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. For more information on changing users' default roles, see .
When a user is dropped, the user and associated schema are 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 denied 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 the SQL statement ALTER SYSTEM with the KILL SESSION clause. For more information about terminating sessions, see .
You can drop a user from a database using the DROP USER statement. 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 user JONES and all associated objects and foreign keys that depend on the tables owned by JONES.
DROP USER jones CASCADE;
