Saturday, August 12, 2006

Managing Users

Syntax

Example

CREATE USER user


IDENTIFIED {BY password | EXTERNALLY}
[DEFAULT TABLESPACE
tablespace]
[TEMPORARY TABLESPACE
tablespace]
[QUOTA
{integer [K|M] ON
tablespace
[QUOTA
{integer [K|M] ON tablespace
]...]

[PROFILE {profile | DEFAULT }]
[PASSWORD
EXPIRE]
ACCOUNT
{LOCK
| UNLOCK }];

CREATE USER user1


IDENTIFIED BY usr1
DEFAULT TABLESPACE
usrtab
TEMPORARY TABLESPACE
tmp1
QUOTA
10M ON usr1
PROFILE app_developr
PASSWORD
EXPIRE
ACCOUNT
UNLOCK;


Create user

  • For operating system authentication user name should by default be preceded by OPS$.

  • Name should start with a letter.

  • _ & # are permitted but preferable no alphanumerical characters should be used.

  • Name must be limited to 30 bytes ( i.e. 30 characters for a 1-byte character-set).

  • Name should contain one single-byte character as per Oracle recommendation.

  • Name is not case-sensitive.

  • Can't be a reserved word.

Identified by

  • Password should contain at least 3 characters, preferably 6 or more.

  • Should not be a word or user name and preferably contain a numeric character in between.

  • Maximum length 30 bytes

  • To force password change, use EXPIRE.

  • For operating system authentication use identified externally.

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:

Here we cover only the first two methods. Network authentication we will cover at a latter stage. The means of authentication can be specified at user creation and can be modified latter.

Database Schema :

    • A schema is a collection of objects

    • A corresponding schema is created for each user created.

    • A user can be associated with only one schema.

    • Username & schema are often used interchangeably


List of Schema Objects:

  1. Tables
  2. Triggers
  3. Constraints
  4. Indexes
  5. Sequences
  6. Stored program units
  7. Synonyms
  8. User-defined data types
  9. Database Links
Creating Operating System Authenticated User :

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:

Altering User:


ALTER USER user


IDENTIFIED {BY password | EXTERNALLY}
[DEFAULT TABLESPACE
tablespace]
[TEMPORARY TABLESPACE
tablespace]
[QUOTA
{integer [K|M] ON
tablespace
[QUOTA
{integer [K|M] ON tablespace
]...]

[PROFILE {profile | DEFAULT }]
[PASSWORD
EXPIRE]
ACCOUNT
{LOCK
| UNLOCK }];

Note : The only aspect of user's ID that can be set by ALTER USER command but can't be set by CREATE USER command is the default role clause.

Setting new password:

Unlocking locked user a/c:

Locking a user a/c:

ALTER USER usr1
IDENTIFIED BY newpwd
PASSWORD EXPIRE;

ALTER USER usr1
IDENTIFIED BY newpwd
ACCOUNT UNLOCK
PASSWORD EXPIRE;

ALTER USER usr1
ACCOUNT LOCK;

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;

Changing temporary tablespace :

ALTER USER usr1
TEMPORARY TABLESPACE tmp2;

Dropping a User account:

DROP USER usr1 CASCADE;

Views:

Comments: Post a Comment

Links to this post:

Create a Link



<< Home
Advertisements

eBay.in - The World's Online Marketplace

ARCHIVES
  • March 2006
  • April 2006
  • May 2006
  • June 2006
  • July 2006
  • August 2006
  • September 2006

This page is powered by Blogger. Isn't yours?

Recent Postings

Google