/* This file sets up authorities for the PurchDB and RecDB databases.*/
/* The DBA for the sampledb DBEnvironment is the DBEUserID John. */
REVOKE ALL ON ManufDB.SupplyBatches FROM PUBLIC;
REVOKE ALL ON ManufDB.TestData FROM PUBLIC;
REVOKE ALL ON PurchDB.Parts FROM PUBLIC;
REVOKE ALL ON PurchDB.Inventory FROM PUBLIC;
REVOKE ALL ON PurchDB.SupplyPrice FROM PUBLIC;
REVOKE ALL ON PurchDB.Vendors FROM PUBLIC;
REVOKE ALL ON PurchDB.Orders FROM PUBLIC;
REVOKE ALL ON PurchDB.OrderItems FROM PUBLIC;
REVOKE ALL ON PurchDB.Reports FROM PUBLIC;
REVOKE ALL ON RecDB.Members FROM PUBLIC;
REVOKE ALL ON RecDB.Clubs FROM PUBLIC;
REVOKE ALL ON RecDB.Events FROM PUBLIC;
GRANT DBA TO John;
/* The following commands create the group for the Purchasing */
/* Department. This group has SELECT authority on all tables */
/* and views of the PurchDB database. It also has INSERT */
/* and UPDATE authority for reports. */
CREATE GROUP PurchManagers;
ADD Margy TO GROUP PurchManagers;
ADD Ron TO GROUP PurchManagers;
ADD Sharon TO GROUP PurchManagers;
GRANT SELECT ON PurchDB.Parts TO PurchManagers;
GRANT SELECT ON PurchDB.Inventory TO PurchManagers;
GRANT SELECT ON PurchDB.SupplyPrice TO PurchManagers;
GRANT SELECT ON PurchDB.Vendors TO PurchManagers;
GRANT SELECT ON PurchDB.Orders TO PurchManagers;
GRANT SELECT ON PurchDB.OrderItems TO PurchManagers;
GRANT SELECT ON PurchDB.VendorStatistics TO PurchManagers;
GRANT SELECT ON PurchDB.PartInfo TO PurchManagers;
GRANT SELECT, INSERT, UPDATE ON PurchDB.Reports TO PurchManagers;
/* The following commands create the group that will maintain */
/* the database. This group has RESOURCE authority, and all */
/* table and view authorities for the tables and views of the */
/* PurchDB database. */
CREATE GROUP PurchDBMaint;
ADD Annie TO GROUP PurchDBMaint;
ADD Doug TO GROUP PurchDBMaint;
ADD David TO GROUP PurchDBMaint;
GRANT RESOURCE TO PurchDBMaint;
GRANT ALL ON PurchDB.Parts TO PurchDBMaint;
GRANT ALL ON PurchDB.Inventory TO PurchDBMaint;
GRANT ALL ON PurchDB.SupplyPrice TO PurchDBMaint;
GRANT ALL ON PurchDB.Vendors TO PurchDBMaint;
GRANT ALL ON PurchDB.Orders TO PurchDBMaint;
GRANT ALL ON PurchDB.Reports TO PurchDBMaint;
GRANT ALL ON PurchDB.OrderItems TO PurchDBMaint;
GRANT SELECT ON PurchDB.VendorStatistics TO PurchDBMaint;
GRANT SELECT ON PurchDB.PartInfo TO PurchDBMaint;
/* The following commands create the Purchasing Department's */
/* group. This group has SELECT, INSERT, DELETE, and UPDATE */
/* authority for the Inventory, SupplyPrice, Vendors, Orders,*/
/* and OrderItems tables of the PurchDB database. */
CREATE GROUP Purchasing;
ADD AJ TO GROUP Purchasing;
ADD Jorge TO GROUP Purchasing;
ADD Ragaa TO GROUP Purchasing;
ADD Greg TO GROUP Purchasing;
ADD Karen TO GROUP Purchasing;
GRANT SELECT,
INSERT,
DELETE,
UPDATE
ON PurchDB.Inventory
TO Purchasing;
GRANT SELECT,
INSERT,
DELETE,
UPDATE
ON PurchDB.SupplyPrice
TO Purchasing;
GRANT SELECT,
INSERT,
DELETE,
UPDATE
ON PurchDB.Vendors
TO Purchasing;
GRANT SELECT,
INSERT,
DELETE,
UPDATE
ON PurchDB.Orders
TO Purchasing;
GRANT SELECT,
INSERT,
DELETE,
UPDATE
ON PurchDB.OrderItems
TO Purchasing;
/* The following commands create the Receiving Department's */
/* group. This group has SELECT, INSERT, DELETE, and UPDATE*/
/* authority for the Orders and OrderItems tables of the */
/* PurchDB database. */
CREATE GROUP Receiving;
ADD Al TO GROUP Receiving;
ADD Sue TO GROUP Receiving;
ADD Martha TO GROUP Receiving;
GRANT SELECT,
INSERT,
DELETE,
UPDATE
ON PurchDB.Orders
TO Receiving;
GRANT SELECT,
INSERT,
DELETE,
UPDATE
ON PurchDB.OrderItems
TO Receiving;
/* The following commands create the Warehouse Department's */
/* group. This group has SELECT, INSERT, DELETE, and UPDATE */
/* authority for the Parts and Inventory tables of the */
/* PurchDB database. */
CREATE GROUP Warehouse;
ADD Kelly TO GROUP Warehouse;
ADD Al TO GROUP Warehouse;
ADD Peter TO GROUP Warehouse;
GRANT SELECT,
INSERT,
DELETE,
UPDATE
ON PurchDB.Parts
TO Warehouse;
GRANT SELECT,
INSERT,
DELETE,
UPDATE
ON PurchDB.Inventory
TO Warehouse;
/* The following commands create the Accounts Payable Department's */
/* group. This group has SELECT, INSERT, DELETE, and UPDATE */
/* authority for the SupplyPrice, Vendors, Orders, and OrderItems */
/* tables of the PurchDB database. */
CREATE GROUP AccountsPayable;
ADD Michele TO GROUP AccountsPayable;
ADD Jim TO GROUP AccountsPayable;
ADD Karen TO GROUP AccountsPayable;
ADD Stacey TO GROUP AccountsPayable;
GRANT SELECT,
INSERT,
DELETE,
UPDATE
ON PurchDB.SupplyPrice
TO AccountsPayable;
GRANT SELECT,
INSERT,
DELETE,
UPDATE
ON PurchDB.Vendors
TO AccountsPayable;
GRANT SELECT,
INSERT,
DELETE,
UPDATE
ON PurchDB.Orders
TO AccountsPayable;
GRANT SELECT,
INSERT,
DELETE,
UPDATE
ON PurchDB.OrderItems
TO AccountsPayable;
/* The following commands create the group called Purch. All DBEUserIDs
or the groups to which they belong are made members of this group.
This group has CONNECT authority only to the PartsDBE DBEnvironment. */
CREATE GROUP Purch;
ADD PurchManagers TO GROUP Purch;
ADD PurchDBMaint TO GROUP Purch;
ADD Purchasing TO GROUP Purch;
ADD Receiving TO GROUP Purch;
ADD Warehouse TO GROUP Purch;
ADD AccountsPayable TO GROUP Purch;
ADD Tom@Wilkens TO GROUP Purch;
GRANT CONNECT TO Purch;
/* The following commands create the Manufacturing Department's */
/* group. This group has SELECT, INSERT, DELETE, and UPDATE */
/* authority for the TestData and SupplyBatches tables of the */
/* ManufDB database. */
CREATE GROUP Manuf;
ADD Henry TO GROUP Manuf;
ADD Peter TO GROUP Manuf;
GRANT SELECT,
INSERT,
DELETE,
UPDATE
ON ManufDB.SupplyBatches
TO Manuf;
GRANT SELECT,
INSERT,
DELETE,
UPDATE
ON ManufDB.TestData
TO Manuf;
GRANT CONNECT TO Manuf;
/* The following commands GRANT specific authorities to */
/* specific DBEUserIDs. */
GRANT SELECT ON PurchDB.Vendors TO Tom;
GRANT SELECT ON PurchDB.VendorStatistics TO Tom;
GRANT SELECT ON PurchDB.PartInfo TO Tom;
GRANT UPDATE (BinNumber,QtyOnHand,LastCountDate)
ON PurchDB.Inventory TO Kelly;
GRANT UPDATE (BinNumber,QtyOnHand,LastCountDate)
ON PurchDB.Inventory TO Peter;
GRANT UPDATE (PhoneNumber,VendorStreet,VendorCity,
VendorState,VendorZipCode)
ON PurchDB.Vendors TO Karen;
GRANT UPDATE (PhoneNumber,VendorStreet,VendorCity,
VendorState,VendorZipCode)
ON PurchDB.Vendors TO Jim;
/* The following commands create a group called DBEUser */
/* for all other DBEUserIDs, and GRANTS specific */
/* authorities to this group. */
CREATE GROUP DBEUsers;
GRANT CONNECT TO DBEUsers;
GRANT RESOURCE TO DBEUsers;
GRANT SELECT,
INSERT,
DELETE,
UPDATE
ON PurchDB.Parts
TO DBEUsers;
GRANT SELECT,
INSERT,
DELETE,
UPDATE
ON PurchDB.Inventory
TO DBEUsers;
GRANT SELECT,
INSERT,
DELETE,
UPDATE
ON PurchDB.Vendors
TO DBEUsers;
GRANT SELECT,
INSERT,
DELETE,
UPDATE
ON PurchDB.Orders
TO DBEUsers;
GRANT SELECT,
INSERT,
DELETE,
UPDATE
ON PurchDB.OrderItems
TO DBEUsers;
GRANT SELECT,
INSERT,
DELETE,
UPDATE
ON PurchDB.VendorStatistics
TO DBEUsers;
GRANT SELECT,
INSERT,
DELETE,
UPDATE
ON RecDB.Members
TO DBEUsers;
GRANT SELECT,
INSERT,
DELETE,
UPDATE
ON RecDB.Clubs
TO DBEUsers;
GRANT SELECT,
INSERT,
DELETE,
UPDATE
ON RecDB.Events
TO DBEUsers;
