DONE IN ORACLE 11G This project requires you to use all previous knowledge to cr

Question

DONE IN ORACLE 11G

This project requires you to use all previous knowledge to create database objects and populate data into tables. It will prepare you for database objects to practice various database security techniques in this course.

You may copy the script from the downloaded files from the course web site. You need to create one (the only one) SQL script that will complete the following tasks:

Connect to SYSTEM user. (5 points) When you need to connect to SYSTEM users, please use a syntax so that it will ask the user to type the password. A typical example will be: "connect system;"

Change the password of the user "DBSEC" to "secc$1new." Please note that the period "." is not a part of the password specification. (10 points)

Grant the DBSEC user with "CREATE SESSION" and "CREATE TABLE" privileges. (5 points)

Connect to DBSEC with the new password. (5 points)

Create all 17 tables (SUPPLIER, SHIPMENT_METHOD, DEPARTMENT, JOB, EMPLOYEE, SALES_COMMISSION , PROMOTION, CATEGORY, PRODUCT, PRODUCT_SUPPLIER, PRODUCT_PRICE, PRODUCT_INVENTORY, PAYMENT_METHOD, CUSTOMER, ORDER_TAB, ORDER_LINE, EMPLOYEE_RANK) under DBSEC schema. (40 points)

Populate SUPPLIER table with the data. (35 points)

Tips:

You may copy from files provided in the course download into Notepad or Wordpad.

Please note the changes in table names from the scripts provided by this course. It is required to follow the exactly spelling from on this page.

To be able to repeatedly test your script, you may add "DROP TABLE table-name;" at the beginning of your file to drop all tables.

Throughout the semester, exact spelling is required to conform to the specifications.

Explanation / Answer

Connecting system

SQL> connect system

Creating new user

SQL> create user DBSEC identified by secc$1new;

Grant the DBSEC user

SQL> grant CREATE SESSION to DBSEC;

Connecting DBSEC

SQL> connect DBSEC/secc$1new;

SQL> grant resource to DBSEC;

Dropping the table previously exists

SQL> drop table supplier;

Creating supplier table in the DBSEC schema

SQL> create table supplier(supplier_no number(6), supplier_name varchar(30), address varchar(30), city varchar(25), state(25), pincode(6), remarks(100), bal_due number(10,2));

Inserting data to the supplier table

SQL> insert into supplier values(1000, ‘John’, ‘Joy Cottage’, ‘1/6 Jones st’, ‘Canada’, ‘8760348’, ‘now in’, 120.20);

Creating privileges to others to access supplier table

SQL> grant select,update on supplier to scott;

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.