HomeOracle Page 2 - Oracle 10G XE User Management 101
Creating Users - Oracle
If you're a DBA, one of your basic tasks involves managing users: dropping, adding, and changing permissions. Even if you aren't a DBA, if you use a database, you may find it helpful to know the tasks that a DBA performs. This article explains the basics for how to perform these tasks in Oracle 10G XE.
You can create a new user using the same interface by accessing the create database user. Users have privileges that they carry with their Username and Password. Their password may have to be set to expire in some cases and/or they may have limited roles. These have to be pinned down before you create a user. For Oracle 10G XE the following points need to be defined before you contemplate creating a new user.
Are you permitting the user to create database objects in his own schema? In this case you grant him the RESOURCE role, or grant system privileges to the individual objects he can create.
Are you allowing him to take up the DBA role? For this case you have to grant him the DBA role. The DBA is much stronger than the RESOURCE role which gives permissions to create objects in only one schema. This is a serious permission not to be taken lightly as he can alter objects by accessing any schema.
Are you going to set a password for the user to expire? In this case the password assigned to the user can be used for the first time login only and he will be reminded to select a new password. This is to cover cases when the password is compromised or forgotten.
Creating a User Account
One of your users, Jay, accesses the database and creates some tables, queries, and so forth in his own schema. This means he needs access to the resources and he has a password that you have assigned, "xfiles," set to expire. The next screen shown how this can be set. At first, you get to one of the screens we have seen earlier shown next.
Now click on the Create button to open the window shown in the next picture.
This window shows two main areas, Create Database User and User Privileges. The user has RESOURCE and CONNECT roles. Enter "jay" for the Username, "xfiles" for the Password, and confirm it (both are case sensitive). You can also check the Expire Password checkbox and grant privileges to all objects by clicking the hyperlink Check All at the bottom right. You leave the Account StatusUnlocked so that Jay can get in by logging in. Now you click on the create button on this page which opens the next window as shown.
Now you see that Jay is added to the Database Users with an already expired password. It also says the user was created about a second ago. Now log out of the database by clicking on the Logout hyperlink at the top right of this window. This will bring you to a screen that shows you logged out. This screen also has a hyper link Login to login with some credentials. Click on the hyperlink to open the login screen again.
Now let's pretend you are Jay. Remember that you were assigned the pair "jay" and "xfiles" for Username/Password. Enter these and click on the login button. You will see the following screen where you need to choose a new password.
Use "xfiles" for your Old Password and choose another for your new password. Click on Apply Changes. You will see that you succeeded in changing the password.
Now "jay" can login with the new password he chose and gets to see his schema objects as shown (which happens to be empty for now).