Managing Database Users

posted Sep 8, 2010, 1:22 PM by Sachchida Ojha   [ updated Sep 8, 2010, 1:24 PM ]
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;
Drop User: When a user is dropped, the user and associated schema are removed from the data dictionary and all schema objects contained in the user schema, if any, are immediately dropped.
 

The following statement drops the user, sachi and all associated objects and foreign keys that depend on the tables owned by sachi.


DROP USER sachi CASCADE;

Lock and unlock Oracle database user

Here's how to lock or unlock Oracle database user accounts.

SQL> ALTER USER username ACCOUNT LOCK;

SQL> ALTER USER username ACCOUNT UNLOCK;
Comments