IS481 Grantham Week 3 Single SQL Script to Perform Several Tasks Paper Can you help me with Part B? I’ll research Part A myself. There are 4 phases to thi

IS481 Grantham Week 3 Single SQL Script to Perform Several Tasks Paper Can you help me with Part B? I’ll research Part A myself. There are 4 phases to this assignment: Phase 1 – Create 3 users (schema).Phase 2-Create 3 profilesPhase 3-Create roles with privilegesPhase 4-Assign roles and profiles to the users Week 3 Written Assignment Encrypting in the Database
In our readings we have learned the importance of encryption of sensitive data across database
environments. Let’s consider the data stored in the database. What about the three approaches to
remedy clear text data being stored in the database’s data files and in subsequent copies used for
redundancy or copied to backup media? This assignment looks at encryption methods related to
securing sensitive data in the database.
For this assignment you are required to do BOTH Part A and Part B
Part A
Note: Don’t forget about the Oracle documentation.
http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r1/prod/security/tde/tde.htm.
Submit short answers that discuss the following questions:
1. What is Transparent Data Encryption used in Oracle?
2. What are the datatypes for columns that can be encrypted? How would you create an
encrypted column in a new or existing table? What are the pros and cons of column
encryption? Hint: look at primary keys and applications such as CRM.
3. When might you use the tablespace encryption?
4. What laws or regulation considerations should you consider when looking at encryption
methods?
Part B Lab Create Profiles and Roles
In addition to your text and notes, links to tutorials are provided in the left navigation panel
under Oracle and SQL/PL Resources
Your mission is to develop a single SQL script that will perform all the following tasks:
Phase 1. Create three (3) users (schema) as follows:
User
Password
Other Parameters
DBSEC_ADMIN is481admin
Tablespace: USERS
Temporary tablespace: TEMP
DBSEC_CLERK is481clerk
Same as above
DBSEC_DEV
Same as above
is481$dev
Phase 2. Create three profiles as specified in the following table:
Profile
Resources
Password
PASSWORD_LIFE_TIME= 1
month
PASSWORD_GRACE_TIME=7
days
SESSIONS_PER_USER=5
DBSEC_ADMIN_PROF CONNECT_TIME=8 hours
IDLE_TIME=1 hour
DBSEC_DEV_PROF
PASSWORD_LIFE_TIME= 1
month
PASSWORD_GRACE_TIME=14
days
CONNECT_TIME=2 hours
IDLE_TIME=2 hours
CPU_PER_CALL=1 minute
SESSIONS_PER_USER=1
CPU_PER_CALL=5 seconds
CONNECT_TIME=8 hours
DBSEC_CLERK_PROF
IDLE_TIME=30 minutes
LOGICAL_READS_PER_CALL=10 0
Database Blocks
FAILED_LOGIN_ATTEMPTS=3
PASSWORD_LIFE_TIME= 1
month
PASSWORD_LOCK_TIME=3
days
PASSWORD_GRACE_TIME=14
days
Phase 3. Create roles with the following privileges:
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
Phase 4. Assign roles and profile to the users as in the following table:
User Name
Role
Profile
DBSEC_ADMIN DBSEC_ADMIN_ROLE
DBSEC_ADMIN_PROF
DBSEC_CLERK DBSEC_CLERK_ROLE
DBSEC_CLERK_PROF
DBSEC_DEV
DBSEC_ADMIN_ROLE plus
DBSEC_SUPERVISOR_ROLE
DBSEC_DEV_PROF
5. Connect as DBSEC_CLERK/is481clerk.
6. Perform a simple query on SUPPLIER table owned by DBSEC.
Tips:
1. DBSEC tables refer to the tables you created under previous project. DBSEC is the
owner of those data.
2. When you need to connect to SYSTEM user, please use a syntax so that it will ask the
user to type the password. A typical example will be: “connect system;”
3. For step 6 above, you may have to specify the table name as “dbsec.supplier.”
Submission Requirement:
The SQL script files will be required and the screen capture of the creation.
Grading Rubric
Criteria
Excellent
Competent
Needs
Improvement
Overview of
TDE
(10-9 points)
Meets
“Proficient”
criteria and
substantiated
with evidence
to support use
of TDE
(10-9 points)
Discusses the
data types that
should be
encrypted with
the pros and
cons. Meets
“Proficient”
criteria and
substantiated
with evidence
to support
selection.
(10-9 points)
Meets
“Proficient”
criteria and
substantiated
with evidence
to support
reasoning for
the pros and
cons of
tablespace
( 8-7 points)
Provides
overview of
TDE and
usage
(6-0 points)
Insufficiently
provides
explanation of
TDE and usage
( 8-7 points)
Discusses the
data types that
should be
encrypted with
the pros and
cons but does
not supply
reasons behind
selection.
(6-0 points)
Insufficiently
describes the
data types that
should be
encrypted.
10
( 8-7 points)
Provides
reasoning for
the pros and
cons of
tablespace
encryption
(6-0 points)
Insufficiently
provides
reasoning for
the pros and
cons tablespace
encryption.
10
Column
Encryption
Tablespace
Encryption
Total
Points
Possible
10
Laws &
Regulation
Considerations
Creation of
Profiles
encryption
(10-9 points)
Meets
“Proficient”
criteria and
substantiated
with evidence
to explain the
relationship of
encryption
with law and
regulation.
(20-18 points)
Submission
includes both
script file and
screen capture.
( 8-7 points)
Provides an
explanation of
the
relationship of
encryption
with law and
regulation
(6-0 points)
Insufficiently
provides an
explanation of
the relationship
of encryption
with law and
regulation
10
(17-15 points)
Submission
includes both
script file and
screen capture
with errors.
(14-0 points)
Submission
does not
include both
script file and
screen capture
with errors.
(14-0 points)
Submission
does not
include both
script file and
screen capture
with errors.
(14-0 points)
Submission
does not
include both
script file and
screen capture
with errors.
20
Creation of
Roles
(20-18 points)
Submission
includes both
script file and
screen capture.
(17-15 points)
Submission
includes both
script file and
screen capture
with errors.
Assigned
profiles and
roles to
accounts
(20-18 points)
Submission
includes both
script file and
screen capture.
(17-15 points)
Submission
includes both
script file and
screen capture
with errors.
20
20

Purchase answer to see full
attachment