Oracle Users

Privileged Access

There are two vital account, SYS and SYSTEM both of which MUST NOT be left with the default passwords, ever! The SYS is the owner of all the internal objects, so it is better to use SYSTEM. However the recommended option is to create users for people and give those that need it the ability to use the SYSDBA role, see Administering User Accounts and Security for further details.

Read Only

There are times when having a read-only user is very useful, it protects you from yourself! To create such a user run the following script:
create user ro_user identified by readonly;
grant create session, select any table, select any dictionary to ro_user;

Then you can login with the user names ro_user and password of readonly.

Users and Profiles

You can get a list of all the users and their settings, including which profile they are on with the following:
select * from sys.dba_users
From this you can see which profile a user has, assuming they have a profile called custom_profile then the following will show how this profile is configured:
select * from sys.dba_profiles where profile = 'custom_profile'
Then if you need to change something, for example the password expiry period then execute this:
alter profile custom_profile limit password_life_time UNLIMITED
Of course there is more to all this but that's a nice starter.

Reset a user's password

This is quite simple, so to set the password for the user BOB to badpassword then execute this:
alter user BOB identified by "badpassword"

Locking or Unlocking a user

Working with our friend BOB, we can lock and unlock BOB's Oracle account as follows:
alter user BOB account lock
alter user BOB account unlock

This way you can easily lock accounts that are no longer needed or unlock accounts that are needed again.