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

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

Table 4-8 Roles and privileges configuration requirements

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

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 4-11 Customer_Service

Table 4-12 Account_Managers

Table 4-13 Sales

Table 4-14 Marketing

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