Question: How do I decide on which attributes to select for a new user when creating a new user using Oracle SQL?
When creating a new user in Oracle SQL, selecting the right attributes is critical for ensuring both security and functionality. Follow the guidelines below to decide which attributes to select:
Username and Default Tablespace:
Username: Choose a unique and descriptive username for the new user.
Default Tablespace: Allocate the appropriate tablespace where the user will store their data objects by default. Evaluate the expected data load and usage patterns to assign the correct tablespace.
Temporary Tablespace: Assign a temporary tablespace for sorting operations. This allocation helps in managing space used for tasks like sorting large queries.
Profile: Assign a profile to the new user. Profiles are used to impose limits on database resources and password management to improve security and manageability.
Quotas: Set specific quotas on tablespaces to control the amount of space the user can consume, preventing unauthorized use of excessive database storage.
Roles and Privileges:
Assign only the necessary roles and privileges to the new user.
Roles: Provide the user with predefined roles that grant a set of privileges required for their tasks.
System Privileges: Assign specific system privileges if the user needs to perform administrative tasks.
Object Privileges: Grant privileges on specific database objects as per the user's job responsibilities.
Password: Assign a strong, unique password to the new user. Follow the organization’s password policy, ensuring it is robust to prevent unauthorized access.
Account Status: Decide the initial status of the account – Open or Locked. Open the account only when the user is ready to start operations.
Expire Password: Optionally, set the password to expire. This setting forces the user to change the password upon the first login, enhancing security.
Instructions to Create a New User:
Below is a basic syntax for creating a new user in Oracle SQL:
CREATE USER username IDENTIFIED BY password
DEFAULT TABLESPACE tablespace_name
TEMPORARY TABLESPACE temp_tablespace_name
PROFILE profile_name
QUOTA quota_size ON tablespace_name;
Then, grant necessary roles and privileges:
GRANT role_name TO username;
GRANT privilege_name ON object_name TO username;
Ensuring each attribute is meticulously chosen and configured when creating a new user is paramount to maintaining the security, efficiency, and effectiveness of Oracle database operations. Tailor each setting to the user's expected roles and responsibilities within the database environment to uphold these standards.
Prerequisites for CREATE USER
Before you create a user, you must consider the following:
The tablespace you want to use as the default for objects created by the user.
The temporary tablespace used when the user executes queries that require sorting large amounts of data.
The user's initial password and whether or not you want that password to be pre-expired. Pre-expiring a password forces a user to change it to some value other than the one you chose initially.
The profile assignment, if any, that you want to make. Profiles specify how often a user must change his or her password, and also can be used to limit the amount of database resources available to the user.
The amount of disk space available to each user. This is referred to as the user's quota.
Example: Creating a User Account
Suppose you want to create a user account for a database application developer named Nick. Because Nick is a developer, you want to grant him the database privileges and roles that he requires to build and test his applications. You also want to give Nick a 11 MB quota on his default tablespace so that he can create schema objects in that tablespace.
To create the user Nick:
Go to the Users page
On the Users page, click Create.
The Create User page appears, displaying the General subpage.
Before Creating User
In the Name field, enter NICK.
In the Profile list, accept the value DEFAULT. This setting assigns the default password policy to user Nick.
Accept the default value Password in the Authentication list.
In the Enter Password and Confirm Password fields, enter a password that is secure.
Create a password that is secure.
Do not select Expire password now. If the account status is set to expired, then the user or the database administrator must change the password before the user can log in to the database.
(Optional) Next to the Default Tablespace field, click the flashlight icon, select the USERS tablespace, and then click Select.
All schema objects that Nick creates will then be created in the USERS tablespace unless he specifies otherwise.
If you leave the Default Tablespace field blank, then Nick is assigned the default tablespace for the database, which is USERS in a newly installed database.
(Optional) Next to the Temporary Tablespace field, click the flashlight icon, select the TEMP tablespace, and then click Select.
If you leave the Temporary Tablespace field blank, then Nick is assigned the default temporary tablespace for the database, which is TEMP in a newly installed database.
For the Status option, accept the default selection of Unlocked. You can later lock the user account to prevent users from logging in with it.
To temporarily deny access to a user account, locking the user account is preferable to deleting it, because deleting it also deletes all schema objects owned by the user.
Grant roles, system privileges, and object privileges to the user
Assign a 10 MB quota on the USERS tablespace
If you did not click OK while assigning the tablespace quota (previous step), then click OK now to create the user.
