Managing User

1.Security Domain
Authentication Mechanism
. Data Dictionary
. Operating system
. Network

Syntax
Use the following command to create a new user:

1
2
3
4
5
6
7
8
9
10

CREATE USER user
IDENTIFIED {BY password | EXTERNALLY}
[ DEFAULT TABLESPACE tablespace ]
[ TEMPORARY TABLESPACE tablespace ]
[ QUOTA {integer [K | M ] | UNLIMITED } ON tablespace
[ QUOTA {integer [K | M ] | UNLIMITED } ON tablespace
]...]
[ PASSWORD EXPIRE ]
[ ACCOUNT { LOCK | UNLOCK }]
[ PROFILE { profile | DEFAULT }]

EXTERNALLY:specifies that the user is authenicated by the operating system
GLOBALLY AS:specifies that the user is authenicated globally
PASSWORD EXPIRE:forces the user to reset the password when the user logs on to the database using SQL Plus
(this option is valid only if the user is authenicated by the database)
PROFILE:is used to control resource usage and to specify the password control mechanism to be userd for the user

2.Creating a New User:Operating System Authentication

OS_AUTHENT_PREFIX initialization parameter
The OS_AUTHENT_PREFIX initialization parameter is used to specify the format of the
usernames for operating system authentication. This value defaults to OPS$ to make it
backward compatible with earlier releases of the Oracle server.You can set the value to NULL.

REMOTE_OS_AUTHENT initialization parameter
Setting another initialization parameter, REMOTE_OS_AUTHENT=TRUE, specifies that
a user can be authenticated by a remote operating system. The default value of FALSE
indicates that a user can be authenticated only by the machine running the Oracle
server. Use this parameter with care because there is a potential security problem

3.Changing User Quota on Tablespace

1
2
3
4
5
6

ALTER USER user
[ DEFAULT TABLESPACE tablespace]
[ TEMPORARY TABLESPACE tablespace]
[ QUOTA {integer [K | M] | UNLIMITED } ON tablespace
[ QUOTA {integer [K | M] | UNLIMITED } ON tablespace ]
...]

The space assigned will not be revoked.But the coming space can not be allocated to new object.

4.Dropping a User
Syntax

1

DROP USER user [CASCADE]

.The CASCADE option drops all objects in the schema before dropping the user. This
must be specified if the schema contains any objects.
. A user who is currently connected to the Oracle server cannot be dropped.

5.Obtaining User information
.DBA_USERS
.DBA_TS_QUOTAS