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.

    Save your time - order a paper!

    Get your paper written from scratch within the tight deadline. Our service is a reliable solution to all your troubles. Place an order on any task and we will take care of it. You won’t have to worry about the quality and deadlines

    Order Paper Now
    • 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