Oracle Database Administration – Lab #7
Granting Privileges, Creating Synonyms, Sequences, and Views
Situation: You are continuing to develop the prototype for the BOCK Company. In this laboratory you will perform a variety of tasks required to maintain data and make the database easier for the system developer to use. You may elect to complete the laboratory individually, or you may team up with 1 to 2 laboratory partners. If you team up with partners, you will select your own partners and you will be graded as a group and you will submit a single document as evidence of your team's work.
STEPS
1. Granting privileges
a. Connect as the DBA (NOT SYS) of your database. Execute the SHOW USER command.
b. In an earlier lab you created three users - Al, Bill and Susan. Grant each of these users the privilege to select on the following tables: VENDOR, INVOICE, INVOICE_DETAILS, and PRODUCT (these are tables that you created in laboratory #5). Use the MINIMUM number of SQL statements that you can in granting the privilege.
c. Grant Al the privilege to insert new rows in each of the tables specified in 1b. Use the MINIMUM number of SQL statements that you can in granting the privilege.
d. While connected as the DBA select the rows from the DBA_TAB_PRIVS view to confirm that each user has the privilege needed to select rows from the tables.
2. Create synonyms
a. Connect as the DBA. Execute the SHOW USER command.
b. Create public synonyms named VENDOR, INVOICE, INVOICE_DETAILS, and PRODUCT for the tables that you created in your earlier laboratory.
3. Storing additional test data
a. Connect as the user Al. Execute the SHOW USER command.
b. Create data for one sample invoice. The invoice must be for two of the products stored in your PRODUCT table. Insert the new data rows into the INVOICE and INVOICE_DETAILS tables (your insert should use the public synonym created in #2 above).
4. Selecting from tables
a. Connect as the user named Susan. Execute the SHOW USER command.
b. Execute two SELECT statements to display the data stored in the INVOICE and INVOICE_DETAILS tables by using the public synonyms.
5. Creating Sequences
a. Connect as the DBA. Execute the SHOW USER command.
b. Create a sequence named VENDOR_SEQUENCE that starts with the value 1 and is incremented by 1. Set the values for MINVALUE = 1 and MAXVALUE = 99999. Allow the sequence to CYCLE automatically and allocate NOCACHE as a sequence option.
c. Create a public synonym for the VENDOR_SEQUENCE
d. Attempt to SELECT a value from the VENDOR_SEQUENCE by using the DUAL table in your SELECT command.
6. Using Sequences
a. You are still connected as the DBA. Alter the VENDOR table to add a new column named SequenceColumn. This new column should be defined as a NUMBER(5) datatype.
b. Grant the object privileges SELECT, INSERT, and UPDATE to Al on the VENDOR table object.
c. Grant the object privilege SELECT to Al on the VENDOR_SEQUENCE object.
d. Connect as the user Al. Execute the SHOW USER command. Edit all existing rows in the VENDOR table such that you replace each row’s SequenceColumn value with a unique value generated from the VENDOR_SEQUENCE.
e. Display the output of the query: SELECT * FROM Vendor;
7. Creating a View
a. Connect as the DBA. Execute the SHOW USER command.
b. Create a SALES_INVOICE view that combines the following columns from the files indicated. The view should also provide the computed "fields" indicated.
1) VENDOR: Vendor_Number, Vendor_Name.
2) INVOICE: Invoice_Number, Invoice_Date, Invoice_Amount.
3) INVOICE_DETAILS: Quantity_Ordered, Actual_Price.
4) PRODUCT: Product_Number, Description.
5) COMPUTED FIELDS: LineTotal (this is the Quantity_Ordered multiplied by Actual_Price).
c. Create a public synonym for the SALES_INVOICE view.
d. Grant permission to select from the view to all database users (use the minimum number of commands possible to grant this permission), then connect as the user named Susan and attempt to display the data for a single Customer Sales Invoice from your database.
8. Shut down the database
