Question

3. Two new users created in Question 2 should have the following access rights:

a) Both users should be able to login to the database.
b) Both users should have Read/Only access to the oe tables ORDERS and CUSTOMERS. This Read/Only access should be implemented using a role read_access, created for this purpose only.
c) The user cust_user should demonstrate the use of their access rights to the oe table CUSTOMERS. These access rights are defined in the previous items 3(a, b). For the demonstration purpose, this user should login with their changed password from Question 2(c).
d) The user invent_user should have Read/Write access to oe tables WAREHOUSES and INVENTORIES.
e) The user invent_user should also be allowed to create new tables.
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 created users.

Answer 1

To assign the access rights to the users created in Question 2, you can use SQL commands in an Oracle database. Here's an example of how you can achieve this:

1. Create the read_access role and grant Read/Only access to the oe tables ORDERS and CUSTOMERS:

```sql

-- Create the read_access role

CREATE ROLE read_access;

-- Grant Read/Only access to ORDERS and CUSTOMERS tables to the read_access role

GRANT SELECT ON oe.ORDERS TO read_access;

GRANT SELECT ON oe.CUSTOMERS TO read_access;

```

In the above commands, we create the `read_access` role using the `CREATE ROLE` statement. Then, we grant the `SELECT` privilege on the `oe.ORDERS` and `oe.CUSTOMERS` tables to the `read_access` role using the `GRANT SELECT ON` statement.

2. Grant the read_access role to the cust_user user:

```sql

-- Grant the read_access role to cust_user

GRANT read_access TO cust_user;

```

The `GRANT` statement is used to assign the `read_access` role to the `cust_user` user.

3. Grant Read/Write access to the WAREHOUSES and INVENTORIES tables to the invent_user user:

```sql

-- Grant Read/Write access to WAREHOUSES and INVENTORIES tables to invent_user

GRANT SELECT, INSERT, UPDATE, DELETE ON oe.WAREHOUSES TO invent_user;

GRANT SELECT, INSERT, UPDATE, DELETE ON oe.INVENTORIES TO invent_user;

```

In the above commands, we grant the `SELECT`, `INSERT`, `UPDATE`, and `DELETE` privileges on the `oe.WAREHOUSES` and `oe.INVENTORIES` tables to the `invent_user` user using the `GRANT` statement.

4. Allow invent_user to create new tables:

```sql

-- Allow invent_user to create new tables

GRANT CREATE TABLE TO invent_user;

```

The `GRANT CREATE TABLE` statement allows the `invent_user` user to create new tables.

After executing these SQL commands, the users will have the following access rights:

- cust_user: Read/Only access to the ORDERS and CUSTOMERS tables through the read_access role.

- invent_user: Read/Write access to the WAREHOUSES and INVENTORIES tables, as well as the ability to create new tables.

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.

The passwords for the created users are "password" (existing). The cust_user user should log in with their changed password from Question 2(c).

Answer 2

To meet the requirements, you need to create two new users with login access, grant them Read/Only access to specific tables using a role, and assign Read/Write access and table creation privileges to one of the users. Additionally, the user "cust_user" should demonstrate their access rights by logging in with their changed password.

To fulfill the requirements mentioned in the question, you can use the following SQL commands:

1. Create two new users:
```
CREATE USER user1 IDENTIFIED BY password1;
CREATE USER user2 IDENTIFIED BY password2;
```
Replace "user1" and "user2" with the desired usernames, and "password1" and "password2" with the desired passwords for each user.

2. Grant login access to both users:
```
GRANT CONNECT, RESOURCE TO user1;
GRANT CONNECT, RESOURCE TO user2;
```
These commands allow the users to log in to the database.

3. Create a role named "read_access":
```
CREATE ROLE read_access;
```

4. Grant Read/Only access on ORDERS and CUSTOMERS tables to the "read_access" role:
```
GRANT SELECT ON oe.ORDERS TO read_access;
GRANT SELECT ON oe.CUSTOMERS TO read_access;
```

5. Assign the "read_access" role to both users:
```
GRANT read_access TO user1;
GRANT read_access TO user2;
```

6. Grant Read/Write access on WAREHOUSES and INVENTORIES tables to the user "invent_user":
```
GRANT SELECT, INSERT, UPDATE, DELETE ON oe.WAREHOUSES TO invent_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON oe.INVENTORIES TO invent_user;
```

7. Grant the privilege to create new tables to the user "invent_user":
```
GRANT CREATE ANY TABLE TO invent_user;
```

To demonstrate the access rights of the user "cust_user" to the CUSTOMERS table, the user can log in with their changed password. You will need to retrieve the password for "cust_user" from Question 2(c) and use it to log in. Here is an example of how you can log in:

```
CONNECT cust_user at the rate<database_name> ENTER NEW PASSWORD: <new_password>
```

Replace "<database_name>" with the name of your database, and "<new_password>" with the changed password for "cust_user".

By following these steps, you will have created two new users with the required access rights, implemented a role for Read/Only access, and granted the necessary privileges for Read/Write access and table creation.

Please note that the provided SQL commands are just one way to achieve the desired requirements. There might be alternative approaches depending on the specific database management system being used.