Every now and then I need to create a new Oracle user to test the CodeSmith Generator OracleSchemaProvider. I wanted to outline the steps that I use to create a new user as well as change the password of an existing user.

The first step one needs to take is to create a new user but you may not remember your login information if it has been a while. At this point I always open up SQL Plus from the start menu and enter in the following user name when prompted.

/ as sysdba

This allows you to connect as the sysdba user without remembering any credentials. Please note that the passwords and/or steps below may not be the best practice for password security but all of my machines are behind a firewall and are on virtual machines for testing with no access to the world wide web. At this step you can change any of the user’s passwords in oracle like the sys account. Here is the command to reset the SYS users password.

alter user sys identified by NEW_PASSWORD;

Now you are ready to create a new user by entering in the following statement.

create user USER_NAME identified by NEW_PASSWORD;

Next we will unlock the user and grant connection access so they can login.

alter user USER_NAME account unlock;
grant connect to USER_NAME;

Finally, I grant some more rights to the user so we can import backups and not have to worry about permissions. Please note that I wouldn’t recommend doing this on a production database. Please see this documentation for setting the proper permissions.

grant resource to USER_NAME;
alter user USER_NAME quota unlimited on users;

Join the mailing list