Implement the authorization table using any two suitable

Question

Part -1:

Case: Oracle Security

You are hired as a database administrator for a small reseller company to implement and administer the database component of their main application.The data model of this application is presented inFigure 4-20. The schema owner is called DBSEC.

a. Use the scripts provided to create a schema.

b. Create three different profiles based on the criteria listed in Table 4-7.

Table 4-7 Profile configuration requirements

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
DBSEC_DEVELOPER_PROF	CONNECT_TIME = 12 hours IDLE_TIME = 2 hours CPU_PER_CALL = 1 minute	PASSWORD_LIFE_TIME = 1 month PASSWORD_GRACE_TIME = 14 days
DBSEC_DEVELOPER_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 = 1 PASSWORD_LIFE_TIME = 1 month PASSWORD_LOCK_TIME = 3 days PASSWORD_GRACE_TIME = 14 days

c. Create four different roles that have privileges according to the specification outlined inTable 4-8.

Table 4-8 Roles and privileges configuration requirements

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 on all DBSEC tables

d. After creating these profiles and roles,create users according to the specifications in Table 4-9.

User Name	Role	Profile
DBSEC_ADMIN	DBSEC_ADMIN_ROLE	DBSEC_ADMIN_PROF
DBSEC_CLERK	DBSEC_CLERK_ROLE	DBSEC_CLERK_PROF
DBSEC_SUPER	DBSEC_SUPERVISOR_ROLE	DBSEC_CLERK_PROF
DBSEC_QUERY1	DBSEC_QUERY_ROLE	DBSEC_CLERK_PROF
DBSEC_QUERY2	DBSEC_QUERY_ROLE	DBSEC_CLERK_PROF
DBSEC_DEVELOPER	DBSEC_ADMIN_ROLE + DBSEC_SUPERVISOR_ROLE	DBSEC_DEVELOPER_PROF

e. Verify your implementation by viewing the data dictionary.

Case SQL Server Security

You are the DBA for Acme Corporation.You're asked to implement a new database server usingMicrosoft SQL Server.In any implementation,security needs to be a theme woven throughouttheproject,not an afterthought. Design an implementation that allows you to implement policies. The design should also include a role-based security structure. The business requirements for the database are as follows:

• No user should have delete access to any object.
• Human Resources needs to be able to fully manage employee data.
• Customer Service needs to be able to make changes to existing customers and vieworder data.
• Customer account managers need to be able to fully manage customer data and orders.
• Sales needs to be able to view customer data and fully manage orders.
• Marketing needs to able to pull quarterly sales numbers to analyze.

Follow these steps to produce a solution:

1. Install SQL Server using the Windows Integrated security mode.

2. Use a Window user for the service accounts.

3. Demote built-in\administrators to security and create a DBA windows group for systemadministrators.

4. Set up password policies based on best practices.

a. Use a minimum of eight characters

b. Use complex passwords

c. Set account lock-out for after three tries

d. Keep five passwords in history

e. Expire passwords after 30 days

5. Set up the roles listed in Tables 4-10 through 4-14.

Table 4-10 Human_Resources

Table	Select	Insert	Update	Delete
Employees	√	√	√	X

Table 4-11 Customer_Service

Table	Select	Insert	Update	Delete
Customers	√		√	X
Orders	√			X
Order Details	√			X

Table 4-12 Account_Managers

Table	Select	Insert	Update	Delete
Customers	√	√	√	X
Orders	√	√	√	X
Orders Details	√	√	√	X

Table 4-13 Sales

Table	Select	Insert	Update	Delete
Customers	√			X
Orders	√	√	√	X
Order Details	√	√	√	X

Table 4-14 Marketing

Table	Select	Insert	Update	Delete
Territory	√			X
Orders	√			X
Order Details	√			X

Part -2:

1 Case 5-1 Secure Your Application with SQL Server IAcme Manufacturing is implementing a new Web-based ordering system for preferred customers and Pats an internal client/server application so Acme sales people can manage the orders. Implement the authorization table using any two suitable application models presented in this chapter.

Role Permissions

clerk select, update, and insert on all tables

super clerk and delete

admin super and all permissions on the authentication table

Case: Application Security with Oracle

You were hired by Acme Telecommunication Systems (ATM to implement a number of security
rain requirements. Provide the architecture of the application security model you select and the steps to
implement it. The application has the following modules:
• Account maintenance
• Call records
• Transactions
• Call monitoring
• Backup and restore
• Security
The following ATS users will be accessing the system:
• Sammy Bright
• Tom Lord
• Linda Glass
• loan Knight
• James Howell
• Dennis Wright
• Ray Stevenson
• Phil Magnet
• Kate Davis

ATS requested the following security implementation: Roles
Role Rank
Service 0
Operator 1
Supervisor 2
Administrator 3
CEO 4
Privileges
Privilege Rank
query 0
update 1
delete 2
grant 3

Module-Role
Module	Service	Operator	Supervisor	Administrator	CEO
Account maintenance	query	update	delete	grant	query
Call records		update	delete	grant	query
Transactions		update	delete	grant	query
Call monitoring Backup and restore	delete	query	query	grant grant	query
Security		query		grant	query
User-Role
User	Service	Operator	Supervisor	Administrator	CEO
Sammy Bright	X		X
Toni Lord	X	X
Linda Glass				X
Joan Knight	X	X	X
James Howell	X
Dennis Wright			X
Ray Stevenson		X
Phil Magnet		X
Kate Davis					X