Oracle Database 10g: SQL Fundamentals II

[Pages:307]Oracle Database 10g: SQL Fundamentals II

Student Guide ? Volume 1

D17111GC11 Edition 1.1 August 2004 Applied

Introduction

Copyright ? 2004, Oracle. All rights reserved.

Course Overview

In this course, you will use advanced SQL data retrieval techniques such as: ? Datetime functions ? ROLLUP, CUBE operators, and GROUPING SETS ? Hierarchical queries ? Correlated subqueries ? Multitable inserts ? Merge operation ? External tables ? Regular expression usage

I-2

Copyright ? 2004, Oracle. All rights reserved.

Oracle Database 10g: SQL Fundamentals II I-2

Course Application

EMPLOYEES

DEPARTMENTS

LOCATIONS

REGIONS

COUNTRIES

I-3

Copyright ? 2004, Oracle. All rights reserved.

Tables Used in the Course

The following tables are used in this course:

EMPLOYEES: The EMPLOYEES table contains information about all the employees such as their first and last names, job IDs, salaries, hire dates, department IDs, and manager IDs. This table is a child of the DEPARTMENTS table.

DEPARTMENTS: The DEPARTMENTS table contains information such as the department ID, department name, manager ID, and location ID. This table is the primary key table to the EMPLOYEES table.

LOCATIONS: This table contains department location information. It contains location ID, street address, city, state province, postal code, and country ID information. It is the primary key table to DEPARTMENTS table and is a child of the COUNTRIES table.

COUNTRIES: This table contains the country names, country IDs, and region IDs. It is a child of the REGIONS table. This table is the primary key table to the LOCATIONS table.

REGIONS: This table contains region IDs and region names of the various countries. It is a primary key table to the COUNTRIES table.

Oracle Database 10g: SQL Fundamentals II I-3

Summary

In this lesson, you should have learned the following: ? The course objectives ? The sample tables used in the course

I-4

Copyright ? 2004, Oracle. All rights reserved.

Oracle Database 10g: SQL Fundamentals II I-4

Controlling User Access

Copyright ? 2004, Oracle. All rights reserved.

Objectives

After completing this lesson, you should be able to do the following: ? Differentiate system privileges from object

privileges ? Grant privileges on tables ? View privileges in the data dictionary ? Grant roles ? Distinguish between privileges and roles

1-2

Copyright ? 2004, Oracle. All rights reserved.

Objectives

In this lesson, you learn how to control database access to specific objects and add new users with different levels of access privileges.

Oracle Database 10g: SQL Fundamentals II 1-2

Controlling User Access

Database administrator

Username and password Privileges

Users

1-3

Copyright ? 2004, Oracle. All rights reserved.

Controlling User Access

In a multiple-user environment, you want to maintain security of the database access and use. With Oracle server database security, you can do the following:

? Control database access ? Give access to specific objects in the database ? Confirm given and received privileges with the Oracle data dictionary ? Create synonyms for database objects

Database security can be classified into two categories: system security and data security. System security covers access and use of the database at the system level such as the username and password, the disk space allocated to users, and the system operations that users can perform. Database security covers access and use of the database objects and the actions that those users can have on the objects.

Oracle Database 10g: SQL Fundamentals II 1-3

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download