Saturday, August 12, 2006
Managing Users
| Syntax | Example | ||||
|---|---|---|---|---|---|
| CREATE USER user
| CREATE USER user1
|
Create user |
|
| Identified by |
|
| Default tablespace | If not defined then the objects created by the user may be placed in the SYSTEM tablespace, which should not be allowed. |
| Temporary tablespace | By creating default temporary tablespace, it is not necessary to specify a temporary tablespace to individual users, unless specifically required. This can be done using the create user or alter user commands. If neither default temporary nor temporary tablespace is defined that SYSTEM tablespace is used for temporary segments, so beware. |
| Quota | Specify in KB or MB on the each tablespace separately (other than the temporary tablespace). If the user attempts to create an object which exceeds the quota, then the object creation script will fail. For unlimited quota use Quota unlimited on tablespcname. Note : Quota is needed only for creating database objects. Users don't need quota on tablespace to update/insert/delete data in existing object as long as they have appropriate privileges on those objects. |
| profile | Bundled set of resource usage parameters to limit the user's overall host machine utilization. |
| Password expire | Forces the user to change the password on first logon. |
| Account unlock | This is the default setting meaning the account is available for use immediately. To prevent usage use account lock. |
Users can be authenticated by any of the following three ways:
- Data Dictionary
- Operating System
- Network
Database Schema :
| |
|
CREATE USER usr2
IDENTIFIED EXTERNALLY
DEFAULT TABLESPACE usrtab;
The user usr1 must have an operating system account on the machine Oracle is running on. He should use the following command to log into Oracle :
SQLPLUS /
Before creating an OS authenticated user, the initialization parameter OS_AUTHENT_PREFIX has to be defined. This specifies the format of the username of OS authenticated users. It defaults to OPS$ for backward compatibility but it can now be defined a NULL as follows:
OS_AUTHENT_PREFIX=””
If there are users in the database who are being authenticated by the operating System, then changing OS_AUTHENT_PREFIX may prevent these users from logging on the database.
Using OS_AUTHENT_PREFIX = OPS$ :
Provides the flexibility of being authenticated by either the OS or the DB.
CREATE USER OPS$usr2 IDENTIFIED usr2_pwd;
Such a user when logging on to the machine running the Oracle server need not supply the password. But while connecting from a remote client he can connect by supplying a password.
Setting REMOTE_OS_AUTHENT=TRUE specifies that a user can be authenticated by a remote operating system. The default is FALSE. However it will open up a security hole as users in rogue systems may be able to gain control over your database.
Guideline for User-account management:
- Use a standard password for user creation and use password expire.
- Avoid OS authentication unless all users will use Oracle while connected directly to the machine hosting the database.
- Always create default temporary tablespace so u don't have to worry about assigning one during user creation.
- Give unlimited quota to only few users. Better to have them ask for more, then to reorganise carelessly filled up tablespaces.
- Use profiles.
Altering User:
ALTER USER user
|
| IDENTIFIED {BY password | EXTERNALLY} |
| Setting new password: | Unlocking locked user a/c: | Locking a user a/c: |
| ALTER USER usr1 | ALTER USER usr1 | ALTER USER usr1 |
Note : changes to passwords, account lock status, or password expiration are applied only to subsequent user sessions, and not current one. Any unchanged options remains unchanged.
Changing User Tablespace allocation:
ALTER USER usr1
DEFAULT TABLESPACE overflow_usrtab01;
Only new created objects will be effected by this statement. Exiting objects created in other tablespaces by that user will continue to reside there until dropped.
If the user specifies a tablespace in which to place a database object, that specification will override the default tablespace.
Changing temporary tablespace :
ALTER USER usr1
TEMPORARY TABLESPACE tmp2;
Note : Only the DBAs can make these changes.
Users can only change their password :
ALTER USER usr1
IDENTIFIED BY newpwd;Exception to this is when the user is granted ALTER ANY USER privilege.
Dropping a User account:
DROP USER usr1 CASCADE;
A user can't be dropped until all his created database objects are dropped as well. CASCADE is used to drop the user and all related database objects in one fell swoop.
Note : To remove a user, but assign his/her tables to another user, use EXPORT tool to dump the tables, and then use IMPORT with the FROMUSER and TOUSER parameters to import the tables as that other user.
- DBA_USERS
- DBA_TS_QUOTAS
