Creating DB User and Grant Option - Oracle

When the new user has been created in Oracle database, the new user cannot do anything with the database. The database admin has to grant privilege to that user. First privilege is

SQL> grant connect to Tommy;

Grant succeed.

The privilege allow the user Tommy to be able to connect to the database. In order for user Tommy to use the resource, granting resource must be follow:

SQL> grant resource to TOMMY;

Grant succeeded.

Now, the user Tommy can use resource like table space. There are other options in privilege to be granted. From my practice, after I create the new user, I also grant EXP_FULL_DATABASE to the user.

SQL> grant EXP_FULL_DATABASE to TOMMY;

This option allows the user to create and drop tables and other objects.

Oracle Password Management

Each user account is protected by the their password. When the user is created, the password is assigned to the user. By default, user password is set to expired by 180 days. After the password expires, the user can no longer access his or her account. To see the expiry date for each open user, user following commands.

SQL> Select username, Account_Status, Expiry_Date from DBA_USERS;

The result shall display after query statement execution. The database administrator can switch off password expiry option by issuing the following command

SQL> Alter Profile Default limit PASSWORD_LIFE_TIME unlimited;

Profile altered.

Now running the same query below, the expiry date value shall be null

SQL> Select username, Account_Status, Expiry_Date from DBA_USERS;

However, this is dangerous for security reason because all users under PROFILE default have non-expiry date for password. To limit the password expiry again,

SQL> alter profile default limit PASSWORD_LIFE_TIME 180;

Profile altered.

180 means password expire after 180 days

Oracle Password Restriction & Solution

What are valid characters in password for Oracle? The Oracle recommends using the combination of letters and numbers. Symbols of "#", "_", and "$" sign are also valid. There are rules that you should follow when creating the password.

First Rule: The first letter of the password must start with the letter, Example

SQL> alter user tommy identified by tommy1234;

User altered.

SQL> alter user tommy identified by 1234tommy;

ERROR at line 1:

ORA-00988 missing or invalid password(s)

As you can see, the password cannot start with the number. However if you want to start password with the number, you can use the double quotation mark on the password;

SQL> alter user tommy identified by "1234tommy";

User altered.

My note: some blogs say password is not case sensitive. I do some testing on the testing database. The result is password is case sensitive. If your user password is Tommy1234, typing password as tommy1234 gives the error message while logging in.

Good luck........

Alter User Password - Reset User Password in Oracle

When the database administrator creates a new user in the Oracle database, password for the new user is set to expire. Password expiry occurs, for it is the default in setting inside Oracle. You can query the Oracle database to see the detail of the database profile as:

Select Profile, Resource_Name, Resource_Type, Limit

from dba_Profiles

where Resource_Name like 'PASS%';

Then, look for highlight line. The password expire after 180 days.

Then, you can query the DBA_USERS table to see the expiry date for the user. Using the follow SQL statement:

Select UserName, Account_Status, Expiry_Date

from dba_Users

Where UserName = 'TEST';

The above query will show the account status and password expiry date for user TEST.

The password for the user TEST shall expire on Sep 03, 2011. What happen when your user password expires. The user cannot log in. Let's try by altering the user TEST account status to expire. Log in the Oracle database as SYSDBA in SQL Plus, and issue alter statement as follow:

Alter User TEST Password Expire;

Now the user TEST shall not be able to connect to the database. Here is the result after altering password to expired

You can unlock the account for user TEST by resetting the new password. At the SQL Plus command prompt, issue the following command to reset password

Alter User TEST identified by NewPassword;

The user TEST now can access the database again.....