creating a database users with parmeters in a table and a custome table with database objects to implement security

/in /by

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
Temporary tablespace: TEMP

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)

EMAIL

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
CONNECT_TIME=8 hours
IDLE_TIME=1 hour

PASSWORD_LIFE_TIME= 1 month
PASSWORD_GRACE_TIME=7 days
PASSWORD_VERIFY_FUNCTION=complexity _function

DBSEC_DEV_PROF

CONNECT_TIME=12 hours
IDLE_TIME=2 hours
CPU_PER_CALL=1 minute

PASSWORD_LIFE_TIME= 1 month
PASSWORD_GRACE_TIME=14 days
PASSWORD_VERIFY_FUNCTION=complexity _function

DBSEC_CLERK_PROF

SESSIONS_PER_USER=1
CPU_PER_CALL=5 seconds
CONNECT_TIME=8 hours
IDLE_TIME=30 minutes
LOGICAL_READS_PER_CALL=10 KB

FAILED_LOGIN_ATTEMPTS=3
PASSWORD_LIFE_TIME= 1 month
PASSWORD_LOCK_TIME=3 days
PASSWORD_GRACE_TIME=14 days
PASSWORD_VERIFY_FUNCTION=complexity _function

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