FPMSq8FT

1 # mysql -u root -p -h localhost 
2
3DROP database IF EXISTS SecureStore;
4CREATE DATABASE SecureStore;
5USE SecureStore;
6
7CREATE TABLE ProductSubCategory (
8	ProdSubId INT NOT NULL AUTO_INCREMENT,
9	ProdSubCat VARCHAR(64) NOT NULL,
10	ProdSubName VARCHAR(64) NOT NULL,
11	PRIMARY KEY (ProdSubId, ProdSubCat)
12);
13
14CREATE TABLE City ( 
15	CityId INT NOT NULL AUTO_INCREMENT,
16	CityName VARCHAR(64) NOT NULL UNIQUE,
17	PostalCode INT(10) UNSIGNED NOT NULL UNIQUE,
18	PRIMARY KEY (CityId)
19);
20
21CREATE TABLE Product ( 
22	ProductId INT NOT NULL AUTO_INCREMENT,
23	ProductSubCategory VARCHAR(64) NOT NULL,
24	Cost FLOAT UNSIGNED NOT NULL, 
25	Size VARCHAR(64) NOT NULL,
26	Color VARCHAR(64) NOT NULL,
27	Price FLOAT UNSIGNED NOT NULL,
28	PRIMARY KEY (ProductId),
29	CONSTRAINT fk_product_id_cat
30	FOREIGN KEY (ProductId, ProductSubCategory) REFERENCES ProductSubCategory(ProdSubId, ProdSubCat) 
31	ON DELETE RESTRICT
32	ON UPDATE CASCADE
33);
34
35CREATE TABLE Customer (
36	CustId INT NOT NULL AUTO_INCREMENT,
37	CustCityId INT NOT NULL,
38	FirstName VARCHAR(64) NOT NULL,
39	LastName VARCHAR(64) NOT NULL,
40	Phone VARCHAR(64) NOT NULL,
41	Address VARCHAR(128) NOT NULL,
42	PRIMARY KEY (CustId),
43	CONSTRAINT fk_custcity_id
44	FOREIGN KEY (CustCityId) REFERENCES City (CityId)
45	ON DELETE RESTRICT
46	ON UPDATE CASCADE
47);
48
49CREATE TABLE Store (
50	StoreId INT NOT NULL AUTO_INCREMENT,
51	StoreCityId INT NOT NULL,
52	StoreName VARCHAR(64) NOT NULL,
53	Phone VARCHAR(64) NOT NULL,
54	PRIMARY KEY (StoreId),
55	CONSTRAINT fk_storecity_id
56	FOREIGN KEY (StoreCityId) REFERENCES City (CityId)
57	ON DELETE RESTRICT
58	ON UPDATE CASCADE
59);
60
61CREATE TABLE SalesPerson (
62	SalesPersonId INT NOT NULL,
63	SalesPersonStoreId INT NOT NULL,
64	SalesFirstName VARCHAR(64) NOT NULL,
65	SalesLastName VARCHAR(64) NOT NULL,
66	PRIMARY KEY (SalesPersonId),
67	CONSTRAINT fk_salespersonstore_id
68	FOREIGN KEY (SalesPersonStoreId) REFERENCES Store (StoreId)
69	ON DELETE RESTRICT
70	ON UPDATE CASCADE
71);
72
73CREATE TABLE FactSales (
74	SalesPersonId INT NOT NULL,
75	ProductId INT NOT NULL,
76	Quantity INT NOT NULL,
77	
78	OrderDateDay INT NOT NULL,
79	OrderDateMonth INT NOT NULL,
80	OrderDateYear INT NOT NULL,
81	
82	PRIMARY KEY (SalesPersonId, ProductId),
83	CONSTRAINT fk_salesperson_id
84	FOREIGN KEY (SalesPersonId) REFERENCES SalesPerson (SalesPersonId)
85	ON DELETE RESTRICT
86	ON UPDATE CASCADE,
87	CONSTRAINT fk_product_id2
88	FOREIGN KEY (ProductId) REFERENCES Product (ProductId)
89	ON DELETE RESTRICT
90	ON UPDATE CASCADE,
91	CHECK (OrderDateDay BETWEEN 1 and 31),
92	CHECK (OrderDateMonth BETWEEN 1 and 12),
93	CHECK (OrderDateYear >= 1970)
94);
95
96CREATE TABLE Date ( 
97	DayOfWeek VARCHAR(64) NOT NULL,
98	DayOfMonth INT NOT NULL,
99	Month INT NOT NULL,
100	Year INT NOT NULL,
101	CHECK (DayOfWeek in ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday')),
102	CHECK (DayOfMonth BETWEEN 1 AND 31)	,
103	CHECK (Month BETWEEN 1 and 12),
104	Check (Year >= 1970)
105);
106
107-- Following the principle of least principle, every account is given write access only to specific tables of the database.
108-- They are also given read (select) access on all other tables, since a more detailed access control policy was not taken into account.
109
110-- The Sales manager needs to have read access the Customer, SalesPerson and FactSales table.
111CREATE ROLE SalesManager;
112GRANT SELECT ON SecureStore.* to SalesManager;
113GRANT ALL ON SecureStore.Customer to SalesManage;
114GRANT ALL ON SecureStore.SalesPerson to SalesManager; 
115GRANT ALL ON SecureStore.FactSales to SalesManager;
116
117-- The Product Manager needs to have access on the Product related tables.
118CREATE ROLE ProductManager;
119GRANT SELECT ON SecureStore.* to ProductManager;
120GRANT ALL ON SecureStore.Product to ProductManager ;
121GRANT ALL ON SecureStore.ProductSubCategory to ProductManager;
122
123CREATE user nick;
124GRANT SalesManager to nick;
125CREATE user john;
126GRANT ProductManager to john;
127
128-- Login as nick:
129mysql -u nick
130show databases;  -- should show only information_schema
131SELECT current_role; -- should be null
132SET role ProductManager; -- should fail
133SET role SalesManager; -- successful
134show databases; -- SecureStore database appears
135use SecureStore; 
136-- Nick has write access to Customer/SalesPerson/FactSales and read access to everything
137-- Similar scenario for john as a product manager.