125k views
2 votes
2. You are required to create the following two new users with login requirements specified below:

a) One user called cust_user for the Customer Service department and one user called invent_user for the Inventory department. Default tablespace with unlimited quota for both users should be set to tbs_ext (created in Question 1). Temporary tablespace for both users should be set to temp.
b) Both users should have unique usernames and passwords. A password should be at least 8 characters long: the users are not allowed to have passwords shorter than 8 characters. There should not be any other enforcement on the password.
c) The user cust_user should be forced to change their password on the first login. The user invent_user does not have this requirement.
d) Both users should be forced to change their passwords every three months.
e) Both users are not allowed to reuse the same password.
You must provide SQL commands and screenshots of successful execution of these SQL commands. Make sure you include an explanation on how all the requirements are implemented, possible alternatives, their benefits and drawbacks. Please provide passwords (existing and new if changed) for the created users.

2. You are required to create the following two new users with login requirements-example-1
User Gosha A
by
8.2k points

2 Answers

7 votes

Answer:

To create the required users with the specified login requirements, you can use SQL commands in an Oracle database. Here's an example of how you can achieve this:

1. Create the users with the necessary privileges and settings:

```sql

-- Create the cust_user user

CREATE USER cust_user IDENTIFIED BY password DEFAULT TABLESPACE tbs_ext TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON tbs_ext;

-- Create the invent_user user

CREATE USER invent_user IDENTIFIED BY password DEFAULT TABLESPACE tbs_ext TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON tbs_ext;

```

In the above commands, we create two users: `cust_user` and `invent_user`. They are both identified by the password "password". The default tablespace is set to `tbs_ext`, and the temporary tablespace is set to `temp`. The `QUOTA UNLIMITED ON tbs_ext` clause ensures that both users have unlimited quota on the `tbs_ext` tablespace.

2. Set the password requirements:

```sql

-- Set the password requirements for cust_user

ALTER USER cust_user PROFILE DEFAULT;

-- Set the password requirements for invent_user

ALTER USER invent_user PROFILE DEFAULT;

```

By using the `ALTER USER` statement with the `PROFILE DEFAULT` clause, we assign the default profile to both users. The default profile includes password policies such as minimum length, password expiration, and password reuse.

3. Force cust_user to change their password on first login:

```sql

-- Force cust_user to change their password on first login

ALTER USER cust_user PASSWORD EXPIRE;

```

The `ALTER USER` statement with the `PASSWORD EXPIRE` clause forces the user `cust_user` to change their password on the next login.

4. Set password expiration and password reuse policies:

```sql

-- Set password expiration and password reuse policies for cust_user

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 90 PASSWORD_REUSE_TIME 90 PASSWORD_REUSE_MAX 1;

-- Set password expiration and password reuse policies for invent_user

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 90 PASSWORD_REUSE_TIME 90 PASSWORD_REUSE_MAX 1;

```

In the above commands, we set the password expiration to 90 days (`PASSWORD_LIFE_TIME 90`) and the password reuse time to 90 days (`PASSWORD_REUSE_TIME 90`). The `PASSWORD_REUSE_MAX 1` clause ensures that the users are not allowed to reuse the same password.

After executing these SQL commands, you will have created the `cust_user` and `invent_user` users with the specified login requirements. The passwords for the created users are "password" (existing). The users will be prompted to change their passwords on the first login and will be required to change their passwords every three months. They will not be allowed to reuse the same password.

Please note that the screenshots of the successful execution of these SQL commands cannot be provided as this platform only supports text-based responses. However, you can execute these commands in an Oracle database environment to see the successful execution and verify the results.

User Jmenezes
by
7.5k points
1 vote

Final answer:

To create the two new users with the specified login requirements, use SQL commands. One user is forced to change their password on the first login, while both users need to change their passwords every three months and are not allowed to reuse the same password.

Step-by-step explanation:

To create the two new users with the specified login requirements, you can use the following SQL commands:

a) Create the cust_user:
CREATE USER cust_user IDENTIFIED BY <password> DEFAULT TABLESPACE tbs_ext TEMPORARY TABLESPACE temp;
ALTER USER cust_user QUOTA UNLIMITED ON tbs_ext;
ALTER PROFILE default LIMIT PASSWORD_LIFE_TIME 90;
ALTER USER cust_user PASSWORD EXPIRE;

b) Create the invent_user:
CREATE USER invent_user IDENTIFIED BY <password> DEFAULT TABLESPACE tbs_ext TEMPORARY TABLESPACE temp;
ALTER USER invent_user QUOTA UNLIMITED ON tbs_ext;
ALTER PROFILE default LIMIT PASSWORD_LIFE_TIME 90;

These commands create the two users with the specified names and passwords and set the appropriate tablespaces. The ALTER command is used to update the user profile settings, including the password expiration.

c) The cust_user is forced to change their password on the first login by setting the PASSWORD EXPIRE option. This will prompt the user to change their password when they first log in.

d) Both users are forced to change their passwords every three months by setting the PASSWORD_LIFE_TIME option to 90 in the DEFAULT profile. This ensures that the passwords expire every 90 days.

e) The requirement for users not to reuse the same password is automatically enforced by Oracle's password management functionality. Once a password is changed, it is added to the password history and cannot be reused.

The benefit of using the ALTER PROFILE command is that it allows you to set profile limits for multiple users, ensuring consistent password policies across the database. The drawback is that you need the appropriate privileges to execute the ALTER commands. Another alternative is to manually update the PASSWORD_LIFE_TIME option for each user individually, but this can be time-consuming and error-prone.

User Dhg
by
8.0k points