Read this blog to learn how to manage database user accounts. Learn how to create a new user, alter an existing user, close a user and drop an existing user in Toad® for Oracle.

As a DBA there is a lot of activity that you are responsible for.

In this blog I want to take you through the process of managing database user accounts. I will cover: 

  • How to create a new user
  • How to alter an existing user
  • How to clone a user
  • How to drop an existing user

These activities may come at random times and Toad makes it easy for you to quickly manage users without having to remember how to do this from the command line.

Since you are already using Toad for all of your other activities User Management is just a click away.


User Management

In the Schema Browser you will see Users on the menu:

Using the schema browser dropdown to show the users menu item.


Choosing the Users menu item will show you all of the users that currently exist and provide an overview of their status, default tablespace, etc., etc.  Everything that you see on the right-side of the screen below.

The schema browser showing all of the database users.


Create a new user

The first feature I would like to show you is how you can create a new user

While inSchema Browser and looking at the Users dropdown the second icon from the left is the Create icon.

The create new user icon is indicated by the red arrow.


Click on the Create icon and the Create User dialog window opens. In this window you enter the new username that you want to create and choose all of the options and grants for this user.  In this example we will create a user named “markdemo” and by clicking on the Show SQL button we can view the script that will be run.  When you are happy with the script close the script viewer window and click on the OK button in the lower right and the new user will be created.

The create user script shows all of the parameters for the new user account.


Refresh the left-side of the Schema Browser and you will see the new markdemo user that was just created.

Showing the new user account has been successfully created.


Alter an existing user

The second feature I want to show you is how to Alter an existing user.

To alter a user you select the user account you want to make changes to and then choose the "pencil" icon as shown below. We will alter the account MATHEW.

The MATHEW account has been selected for alteration.


Once you choose alter you will see the window below.  From here you choose what you would like to alter about the user.

The first option is ‘Authentication’. From here you can lock the account, expire their password and change their password.

You can alter the other features of the user account as shown on the left-hand menu.

This is the change/alter dialog window.


Below is an example of viewing the tablespace for a user, and if you need to make a change you can choose the tablespace you want to use from the drop down menu.

This is the dialog box used to alter user tablespaces.


Once you have chosen the changes you wnat to make you can view the sql statement that is generated.  Below you can see that I am changing the default tablespace and granting some additional roles to the user MATHEW. You can close this window.

This is the alter user script that is generated.


Once you have made all of your alterations you can click on the OK button in the lower right and the changes will be applied.

Press the 'OK' button to apply the changes.


Now you can see that the default tablespace for the user MATHEW has been changed to ‘TOOLS’.

Here you can see your changes-applied.


How to clone a user

The third feature I would like to show you is how to clone a user.  This is handy when you have a new app or release coming out and you need to create several users.  You simply create the first user and then click on the Clone icon (we will clone the markdemo user as markdemo1), enter the new username and password and click the ‘OK’ button and the new cloned user will be created.

Choose the indicated icon to clone a user account.


New cloned user confirmation.


Now simply refresh the Schema Browser window and you will see the newly cloned account.

The new account markdemo1 has been created as a clone of the markdemo account.



How to drop an existing user

The last feature I want to show you ishow to drop an existing user.  You may have to do this when a user leaves the company or project.

We are going to go back to the Schema Bowser window, highlight the account you want to drop and click on the Drop User icon.

Choose the account you want to drop.


A confirmation window will open. 

Choose either the “Do Not Cascade Option” or the “Use Cascade Option” and click the OK button and the user account will be dropped.

Drop user account confirmation window.


Refresh the Schema Browser and you will see that the user account has been dropped from the database.

The markdemo user account has been dropped.


And now you know how you can quickly and easily manage your database users while not disrupting your other duties as these user management requests pop up.

I hope you find this blog useful and can now manage database user accounts. If you have any questions just leave a comment on the blog page.

-Mark Kurtz

More blogs from Mark.


Try Toad for Oracle now

Try Toad for Oracle free for 30 days.

Already in a trial? Talk to sales or buy now online.

Already a loyal fan of Toad for Oracle? Renew now.


Useful resources

Toad for Oracle general information

Technical brief: Top Five Reasons to Choose Toad Over SQL Developer

Case study: Opening doors and creating opportunities with data insights


Have questions, comments?

Head over to the Toad for Oracle forum on Toad World®!  Chat with the me, the other Toad developers, and lots of experienced users. 


Help your colleagues

If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post. Thanks!

About the Author

Mark Kurtz

Mark Kurtz has been with Quest Software since April 2000 and has held the position of Software Sales Engineer in the PreSales Organization since then. Mark started his career as a programmer/analyst for NASA supporting the Space Shuttle program. He became a DBA after moving into the health care industry and has managed several teams of DBA's in his career. Outside of work, Mark enjoys spending time with his family, playing poker, and working on old cars and woodworking.

Start the discussion at