Abc Assignment Help

Application of Oracle

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 

ABCD
create session
select ON all   tables and views
create session
select, update ON
sales north   views

create session
select, update on
sales south   views

create session
select, update on 
sales east views

EFGH
create session
select, update on
sales west views

create session
select ON postal_view
create session
select, update, insert on admin_view,
companies

create session
select, update, on company,   hr_view,
IJK
create session
select, update, insert delete on company, contacts

create session
create procedure
create sequence
create table
create trigger
create type
create view
create synonym
drop table
drop procedure
drop sequence
drop trigger
drop type
drop view
drop synonym
create session
create user
create role
create index
create tablespace
alter user
backup table
drop index
drop user
drop role
with admin






Note the preexisting roles in oracle: sysoper

I
 


I
 

, sysdba on the diagram


Appendix b - List of Users

User   GroupNo   of users
England Reps5
Scotland Reps5
N.Ireland Reps5
Wales Reps5
Sales Administers2
HR Staff2
Head of Sales1
Head of Finance1
Head of IT1
Directors2
ITDev – Developers3
ITAdmin – DBA x 3 + IT Director3+1
Total   No of Users36



Answer

Ginger Bread (company name) working throughout the UK who provides 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.

Customer Testimonials