Strategy for system privileges

You work for a large company called Ginger Bread working throughout the UK who provide baking equipment to large bakeries.  Ginger Bread comprises of several traditional departments including; Research and development, Production, Finance, HR, Sales and Marketing and IT.  Each department has roughly 85 members of staff.  Each department implements a standard hierarchical management structure.

The company is undergoing database and IT system redevelopment.  The new database and supporting software have already been design and created using the industry standard DBMS Oracle, version 10g.  In large database systems there is a requirement for varying levels of access for different grades of users.  Lower level users are restricted in what they can access. You have been asked by the IT director to establish a strategy to control user access to the system.  You will need to design a hierarchy for access, bearing in mind the various operations used in database design, input, output etc.  This should take the form of a managerial report considering the following privileges:

Tables

CLIENTS (CID, CompanyName, Address1, Address2, City, County, Postcode, Region, Phone, Fax, Email, WebSite, Notes)

STAFF (ID, CID, Firstname, Surname, Department, Gender, Grade, Trade, ContactNumber, LastPDR, Salary, Notes, Recommendations)

Views

Consider appropriate use of the following example views:

• Views with column projections as below and row restrictions based on the following regions; ENGLAND,SCOTLAND, N.IRELAND WALES:

sales_views(staff.firstname, staff.surname, departments.department_id, staff.contactnumber, notes, recommendations, clients.CompanyName, clients.allAddreessDetails,clients.allContactDetails)

• View with column projections as below:

admin_view(staff_id,did, firstname, surname, specialism, HomeContactNumber, notes)

• View with column projections as below:

postal_view(CID, company_name, address1, address2, city, county, postcode)

• View with column projections as below:

hr_view(contactid, cid, firstname, surname, department, grade, contact_number, salaries)

Note: there maybe errors or bad practice in the above tables. 

Requirements:

• The assignment can be completed individually or in pairs or small groups

1.  A brief synopsis of your implementation plan including, a list of users and their usernames and initial passwords, role names etc
   • Adopt an appropriate username and password strategy 

1.  According to the strategy (appendix a), write the SQL script files to create all:

• Tables, including some test data
• Views
• Users, logins and passwords 

- You need only implement 1 rep group (e.g. WALES)

• Roles 

- You need only implement 1 rep group (e.g. WALSE)

• Grants
• Drops to remove everything you have created

You will need to consider the use of synonyms 

1.  Identify a test schedule to test the access levels of your users

1. Automating 
   • Apply PL/SQL to create loop constructs in procedures or triggers to automate as many as the processes as possible e.g. generating users and granting roles

Notes

Ensure that all objects you create are suffixed with your login number e.g.

Companies_12, NIreps_12

Appendix a - List of Priviledges   represents a subset of the strategy for Ginger Bread.  It reflects a possible approach to Ginger Bread security planning, suitable for implementation.

Appendix b - List of Users                     provides an outline of the number of users that will be assigned to each role.  You will have to provide dummy data relating to users’ names

Security Strategy for the Sales and Marketing team, The Finance  and the IT department

Appendix a - List of Priviledges 

Note the preexisting roles in oracle: sysoper

, sysdba on the diagram

Appendix b - List of Users