Setting up Oracle Database on Oracle VM VirtualBox is a great way to practice database management in a controlled environment. In this guide, we’ll walk through how to create a new user, assign roles, and log in with the new credentials inside your Oracle Database.
Step 1: Checking for Version Number
Step 2: Create a New User
Start by connecting to your Oracle Database as SYSDBA. Once connected, use the following SQL command to create a new user:
Explanation:
-
C##free→ the username (the prefixC##is required for common users in a multitenant database). -
IDENTIFIED BY free→ sets the initial password. -
DEFAULT TABLESPACE users→ assigns the user to theuserstablespace. -
QUOTA UNLIMITED ON users→ allows unlimited space in that tablespace. -
PASSWORD EXPIRE→ forces the user to change the password upon first login.
Step 3: Create and Grant a Role
1. Next, create a role for the new user and assign the required privileges.
2. Then grant permissions to perform essential database operations:
3. Finally, assign this role to your new user:
4. If everything is correct, you’ll see: Grant succeeded.
Step 4: Allow the User to Connect
While still logged in as SYSDBA, grant the CONNECT privilege to the new user:
This step ensures the new user has permission to log in to the database.
Step 5: Log In as the New User
Now, log out of your SYSDBA session and log in using the newly created user:
-
Username:
C##free -
Password:
free
Since the password was set to expire, you will be prompted to create a new password upon login. Enter a secure password of your choice.
Once the password is updated, reconnect using your new credentials.
Step 6: Verification
To verify your user setup, try creating a simple table:
CREATE TABLE sample_table (
id NUMBER PRIMARY KEY,
name VARCHAR(50)
);
If this executes successfully, your user setup and configuration are complete.
Summary
In this setup, you:
-
Verified the Oracle Database version.
-
Created a new common user and role.
-
Granted essential privileges and connection access.
-
Logged in and set a new password successfully.
You’re now ready to explore Oracle SQL under your new user account in VirtualBox!
