Welcome to Oracle Database Administrator Home

24x7 oracle database support and solutions
Oracle DBA Home     Routine Maintenance     PostgreSQL     Unix Commands     CVS     Oracle FAQ     Oracle Concepts     SQLPlus     OEM     ASM     Data Guard     RAC     RMAN     Networking     OAS     Partitioning      
Database User Maintenance
Database Space Management
Manage Listener Log
Self managing oracle DB
Manage Alert Log
Cron Jobs
Database Jobs
Oracle Installation
Oracle Database Refresh
Oracle DB COPY
Oracle RMAN backup
Oracle User Profiles
Oracle Unix Scripts
Oracle Materialized View
Oracle troubleshooting SQ
Oracle data Files
oracle Control Files
Sessions and Locks
Managing temp tablespace
Shrink tables in Oracle
Managing Oracle Users
Each Oracle database has a list of valid database users. To access a database, a user must run a database application and connect to the database instance using a valid user name defined in the database. We will learn here how to manage users for a database.
 

Create User : To create a user, you must have the CREATE USER system privilege. A newly created user cannot connect to the database until granted the CREATE SESSION system privilege.
CREATE USER sachi
    IDENTIFIED BY password
    DEFAULT TABLESPACE data_ts
    QUOTA 100M ON test_ts
    QUOTA 500K ON data_ts
    TEMPORARY TABLESPACE temp_ts
    PROFILE auditor;
GRANT create session TO sachi;

Alter User : Users can change their own passwords. However, to change any other option of a user security domain, you must have the ALTER USER system privilege.
 
ALTER USER sachi DENTIFIED BY newpassword;
Security administrators are typically the only users that have this system privilege, as it allows a modification of any user 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.
ALTER USER sachi
    IDENTIFIED EXTERNALLY
    DEFAULT TABLESPACE data_ts
    TEMPORARY TABLESPACE temp_ts
    QUOTA 100M ON data_ts
    QUOTA 0 ON test_ts
    PROFILE operator;