Oracle 9i Database Installation



Granting/Revoking Rights, Creating Synonyms & Creating Indexes

IS 475

Granting/Revoking Rights

Here are some example commands for granting or revoking rights to object.

Grant the SELECT right to everyone for a particular table.

Example: grant select on STUDENT to public;

Grant the SELECT right to one user for a particular table.

Example: grant select on FACULTY to kreie;

Revoke the right granted earlier.

Example: revoke select on student from public;

Example: revoke select on faculty from agassi.

Create some views then create synonyms for those views.

You’ve created the tables and views in one account’s schema. Other accounts that want to query these must precede the table or view name with the owner’s name. For example:

select distinct facfirstname || ‘ ‘ || faclastname, course.courseno, crsdesc

from jcry.faculty, jcry.offering, jcry.course

where faculty.facNo = offering.facNo

and offering.courseno = course.courseno;

This can be time-consuming for the person creating queries. Creating a view is helpful. Below is an example view based on the SQL example shown above.

Create or replace view Faculty_Courses as

select distinct facfirstname || ‘ ‘ || faclastname as faculty, course.courseno, crsdesc

from jcry.faculty, jcry.offering, jcry.course

where faculty.facNo = offering.facNo

and offering.courseno = course.courseno;

Create or replace view Faculty_Info

(faculty_member, supervisor) as

Select sub.facfirstname || ‘ ‘ || sub.faclastname subordinate,

sup.facfirstname || ‘ ‘ || sup.faclastname supervisor

From jcry.faculty sub left join jcry.faculty sup on sub.facsupervisor = sup.facNo;

1. In your regular user account create the two views shown above using the tables in the JCRY schema.

2. Even if you give other users access to these view, the fully-qualified name of the database object (.) would have to be entered. This can be lengthy and error-prone. The account that owns the database objects can simplify access to the database objects by using synonyms. You can create synonyms for your database objects to simplify some of your own SQL statements.

To create a synonym use the following syntax:

Create synonym fac_crs for faculty_courses;

You can view your schema synonyms using the user_synonyms view.

Select synonym_name, table_name from user_synonyms;

There are regular (private) synonyms and there are public synonyms which all DBMS accounts can use. A person with a regular Oracle account probably does not have the system privilege to create public synonyms but a request can be made to the database administrator to make public synonyms.

The syntax for creating a public synonym is shown here. This is an example. Use a different synonym name than the one shown in the example. Use your first initial plus the first 3 letters of your last name. (If this matches someone else in class modify the name so that it is unique.)

Create public synonym faclist for jcry.faculty_courses;

Then, grant select privilege on the synonym to public;

Grant select on faclist to public;

NOTE: The syntax to drop a synonym is:

drop public synonym faclist;

What to turn in.

Log on with your DBA account. Create public synonyms for the views you created above in your regular user account. We can’t all use the same synonym name so put your last name on the end of the synonym. Example: faclist_kreie

Write a script to do the following steps 1 through 5.

1. Log on with your regular user account in the DBA database (not your DBA account). Show the user and the system date.

2. Grant SELECT to everyone for your user account’s COURSE table.

3. Grant SELECT to one other user for your FACULTY table.

4. Use one of the metadata views to show these grants have been made. Show only the pertinent output (don’t show all columns).

5. Revoke the rights you granted. Use the same metadata view to show these grants have been revoked. Save this output to a file.

6. Disconnect and reconnect to the DBA server using your DBA account. Use a metadata view to show that you created a public synonym for the faculty-course view, as described earlier in the handout. Save the output to a file. (You can copy/paste it into the output for 1 through 5, if you want or submit #6 as a separate file.)

Indexes

Indexes are used to expedite retrieval of data. There is a trade-off between using indexes for faster data retrieval and slower updates because every index belonging to a table must be updated when the table is updated. There are some fairly complex decisions for the DBA to make about what indexes to create and what type of indexes to create to maximize overall database performance.

In general, an index is similar to a table of contents. You can scan an simple list of contents to find the topic you want then jump to the page where that topic begins rather than flipping through each page until the topic is found (flipping through one page at a time is an example of a sequential search). Once an index is created for a table, the DBMS uses that index whenever a query on that table is performed. The user doesn’t have to reference the index in the query.

Guidelines for creating indexes:

1. Index the primary key (this is done automatically by the DBMS).

Example: PKSTDID (index for STUDENTS in the peer evaluations database)

2. Consider using indexes on foreign keys to support faster join operations in queries.

Example: Index ARTLNAME in the ARTIST table.

3. Index a column with many values (the column is almost unique).

4. Consider using an index for a column frequently used in the WHERE clause of an SQL statement.

5. Do not index a column that is frequently updated and keep indexes to a minimum on volatile tables (tables with a lot of insertions and deletions).

There are different types of indexes—Btree, hash, bitmap, etc.—which designate how the index is created. The type of index to use depends on the characteristics of the column and table. For example, a bitmap index is appropriate for stable columns with few values and is often used in data warehouses (data warehouses are non-volatile). For example, there might be an index on a column called “division” in a sales data warehouse.

Btree is the most common type of index (and the default if another type is not specified). This index is stored in sorted order based on the sorted indexed column. There is a hierarchy of sorted groups, as shown below. A quick search of the root level of this Btree for the value 61 (a value higher than 50 but lower than 70) points to the level 1 group that contains 61 which in turn points to the associated record in the table being queried.

[pic]

The DBMS automatically creates an index on the primary key for a table. This type of index is a “unique index” which enforces the uniqueness requirement for the primary key. Before a record can be inserted into a table, the primary key index is searched to make sure the primary key value in the new record doesn’t already exist (Remember the uniqueness constraint error we’ve seen?). The syntax for creating a unique index is …

Create unique index ssn on student(stdssn);

You might occasionally create a unique index when you determine that a column (not the primary key column) is a candidate key, which should only contain unique values.

You create a non-unique index on a column(s) with the following syntax …

Create index on ();

Example: create index Custlname on customer(custlastname)

To see some of these indexes you can access a view for user indexes:

select index_name from user_indexes;

You can drop an index using the following syntax:

Drop index ; Example: drop index custlname

What to turn in:

1. Log on under your regular user account in the “DBA” database.

2. List the indexes that currently exist in this schema. Show the index name, table_owner, table_type.

3. Create an index .

4. List the indexes that now exist in this schema. Show the index name, table_owner, table_type.

5. Drop the index.

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

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

Google Online Preview   Download