creating a database users with parmeters in a table and a custome table with database objects to implement security
Required tasks:
- Create the following database users with the parameters specified in the table. (10 points)
- Create a CUSTOMER table as a sample database object to implement security. (10 points) The DBSEC user is the owner of the CUSTOMER table, which has the following columns.
- Create password complexity policy function (named “complexity _function”). (10 points) The password policy will enforce the following complexity so that the password:
- Is at least six (6) characters long
- Differs from the user name
- Has at least one alpha, one numeric, and one punctuation mark character
- Is not simple or obvious, such as welcome, account , oracle, database , or user.
- Differs from the previous password by at least 3 characters
- Create profiles and manage all database users using the profiles including enforcing the password complexity. (20 points)
You will create the following profiles:
- Implement view on CUSTOMER table. (10 points) You will create a VIEW named as CUSTOMER_F_VIEW to display only rows that belong to the logged on user.
You will enable VPD_CLERK1 to access the CUSTOMER data through the view.
- Implement virtual private database (VPD) on CUSTOMER table so that only the owner of data can access their own row. (15 points)
First, you will create a policy function, named “DBSEC_ROW_OWNER” so that only the data that belong to the current user will be accessed. Then, you will add the policy using DBMS_RLS.ADD_POLICY function.
- Audit the activities on CUSTOMER table. (15 points)
You will design and implement the following auditing functions on the CUSTOMER table.
- Track all “SELECT” activities on the CUSTOMER table, including database user, operating system user, and time when the operation is performed.
- Track the changes when CREDIT_LIMIT is set to a value above $50,000.
- Track the CUSTOMER table when a customer record was deleted.
- Audit the user activities of two users. You will set up an audit mechanism to monitor all activities by two (2) database users: VPD_CLERK1, VPD_CLERK2. (10 points
User |
Password |
Other Parameters |
DBSEC_ADMIN |
Is481admin |
Tablespace: USERS |
DBSEC_CLERK |
Is481clerk |
Same as above |
DBSEC_DEV |
Is481dev |
Same as above |
DBSEC |
Is481#1 |
Same as above |
VPD_CLERK1 |
john$22 |
Same as above |
VPD_CLERK2 |
nancy$46 |
Same as above |
Column Name |
Data Type |
SALES_REP_ID |
NUMBER(4) |
CUSTOMER_ID |
NUMBER(8) NOT NULL |
CUSTOMER_SSN |
VARCHAR2(9) |
FIRST_NAME |
VARCHAR2(20) |
LAST_NAME |
VARCHAR2(20) |
ADDR_LINE |
VARCHAR2(60) |
CITY |
VARCHAR2(30) |
STATE |
VARCHAR2(30) |
ZIP_CODE |
VARCHAR2(9) |
PHONE |
VARCHAR2(15) |
|
VARCHAR2(80) |
CC_NUMBER |
VARCHAR2(20) |
CREDIT_LIMIT |
NUMBER |
GENDER |
CHAR(1) |
STATUS |
CHAR(1) |
COMMENTS |
VARCHAR2(1024) |
CTL_UPD_DTTM |
DATE |
CTL_UPD_USER |
VARCHAR2(30) |
CTL_REC_STAT |
CHAR(1) |
Populate data using the file provided.
Profile |
Resources |
Password |
DBSEC_ADMIN_PROF |
SESSIONS_PER_USER=5 |
PASSWORD_LIFE_TIME= 1 month |
DBSEC_DEV_PROF |
CONNECT_TIME=12 hours |
PASSWORD_LIFE_TIME= 1 month |
DBSEC_CLERK_PROF |
SESSIONS_PER_USER=1 |
FAILED_LOGIN_ATTEMPTS=3 |
You will create the following roles:
Role Name |
Privileges |
DBSEC_ADMIN_ROLE |
SELECT and ALTER on all DBSEC tables |
DBSEC_CLERK_ROLE |
SELECT, INSERT, and UPDATE on all DBSEC tables |
DBSEC_SUPERVISOR_ROLE |
SELECT, INSERT, UPDATE and DELETE on all DBSEC tables |
DBSEC_QUERY_ROLE |
SELECT only on CUSTOMER table owned by DBSEC |
You will assign roles and profiles to database users as follows:
User Name |
Role |
Profile |
DBSEC_ADMIN |
DBSEC_ADMIN_ROLE |
DBSEC_ADMIN_PROF |
DBSEC_CLERK, VPD_CLERK1, VPD_CLERK2 |
DBSEC_CLERK_ROLE |
DBSEC_CLERK_PROF |
DBSEC_DEV |
DBSEC_ADMIN_ROLE plus DBSEC_SUPERVISOR_ROLE |
DBSEC_DEV_PROF |