Question

Scenario:
In your newly assigned Oracle DBA position, the security audit team wants you to be involved in a new project to make sure Users are created with Least Privileges, the database uses encryption for all data-at-rest, use separate table spaces for users and applications, user passwords expire on first login, quotas should be used for tablespaces as opposed to unlimited tablespaces, temporary tablespaces should be used to help speed performance, no default user passwords exist in the database, Roles are used for privileges as opposed to being assigned directly to the user, and the password complexity should meet or exceed some requirements for DoD STIGs.
In additional the following business guidelines are provided to help in your design:
Number of Database Users:
a. 10 Total
b. 2 with DBA privileges
c. 4 with Privileges to Select, Update, Insert and Delete from the students, employees and finances tables
d. 2 with Privileges to Select from the students, employees and finances tables
e. 2 with Privileges to select, Update, Insert and Delete from the products, sales and payroll application tables.
It is estimated no more 500K tablespace is needed for each user.
Big tablespaces are not required.
AES 256 encryption algorithm is needed to meet the contract requirements.
Note: You will need to create the tables to test your permissions and roles.

Answer 1

Creating a User
Once connected as SYSTEM, simply issue the CREATE USER command to generate a new account.

CREATE USER first_admin IDENTIFIED BY MyPassword;
Here we’re simply creating a books_admin account that is IDENTIFIED or authenticated by the specified password.

Providing Roles

GRANT CONNECT TO first_admin;

Assigning Privileges

GRANT DBA to second_admin;
----------------------
Table Privileges for 4

Grants the SELECT UPDATE, INSERT, DELETE object privileges for all columns of the Students, Employees, Finances table to the users A_user, B_user, C_user and D_user.

GRANT SELECT, UPDATE, INSERT, DELETE ON Students, Employees, Finances TO A_user, B_user, C_user, D_user;

------------------------------------

Table Privileges for 2
Grants the SELECT object privileges for all columns of the Students, Employees, Finances table to the users E_user, F_user.

GRANT SELECT ON Students, Employees, Finances TO E_user, F_user;

-----------------------------
Table Privileges for 2
Grants the SELECT , UPDATE, INSERT, DELETE object privileges for all columns of the products, Sales and Payroll application table to the users G_user and H_user.

GRANT SELECT, UPDATE, INSERT, DELETE ON Products, Sales and Payroll TO G_user, H_user;
----------------------------------