1 / 17

Views, Security, and SQL Facilities

Views, Security, and SQL Facilities . View. A derived (and virtual) relvar, whose value at any time is the relation that would result if the view-defining expression were in fact evaluated at that time. A view is essentially just a named expression of the relational algebra.

Download Presentation

Views, Security, and SQL Facilities

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Views, Security, and SQL Facilities

  2. View • A derived (and virtual) relvar, • whose value at any time is the relation that would result if the view-defining expression were in fact evaluated at that time. • A view is essentially just a named expression of the relational algebra. • No data is actually stored in a view. • Users can operate on a view just as if it were a base relvar. • Updates on some views are prohibited (The "view update problem").

  3. View Example VAR GOOD_SUPPLIER VIEW (S WHRE STATUS > 15) {S#, STATUS, CITY} • This statement defines a view named GOOD_SUPPLIER. • No data will be actually stored in it. • Data are stored in the base relvar S.

  4. Manipulating a View • Query: "Get the good suppliers in London". GOOD_SUPPLIER WHERE CITY <> 'London' • This query looks just like a regular query on a regular "real" relvar. • The system converts it into an equivalent query on the underlying base relvar. ((S WHRE STATUS > 15) {S#, STATUS,CITY}) WHERE CITY <> 'London'

  5. What are Views For? • To provide automatic security for hidden data. • Users can not access SNAME via GOOD_SUPPLIER. • To provide a shorthand or "macro" capability • The previous query on the view GOOD_SUPPLIER is simpler than the query on the base relvar S. • To allow the same data to be seen by different users in different ways at the same time. • Users are aware of just that portion of database that is of concern of them and ignore the rest. • To provide logical data independence: • "The immunity of users and user programs to changes in the logical structure of the database".

  6. Logical Data Independence • Database Growth • As the database grows to incorporate new information (adding a new relvar or a new attribute), the changes should not affect existing users or user programs at all. • Database Restructuring • Sometimes it might become necessary to restructure the database. • E.g., replace the base relvar S by two base relvars. SNC {S#, SNAME, CITY} ST {S#, STATUS} • We can create a view to get the original relvar S. VAR S VIEW SNC JOIN ST;

  7. SQL Facilities • Data Definition Language (DDL) • Define the database. • CREATE, ALTER, or DROP a base TABLE or VIEW. • Data Manipulation Language (DML) • Manipulate the data in an existing database. • SELECT, INSERT, UPDATE, or DELETE data. • Data Control Language (DCL) • Control user access to an existing database. • GRANT or REVOKE user privileges.

  8. CREATE, ALTER, DROP VIEW CREATE VIEW GOOD_SUPPLIER AS SELECT S#, STATUS, CITY FROM MIN.S WHERE STATUS > 15; DROP VIEW GOOD_SUPPLIER;

  9. SELECT FROM a View • Query: "Get the good suppliers in London". SELECT * FROM MIN.GOOD_SUPPLIER WHERE CITY <> 'London'; SELECT S#, STATUS, CITY FROM MIN.S WHERE STATUS > 15 AND CITY <> 'London';

  10. Security • SQL supports security via • The view mechanism • which can be used to hide sensitive data from unauthorized users. • Users can not access SNAME via GOOD_SUPPLIER. • The authorization subsystem (DCL) • which allows users having specific privileges selectively and dynamically to GRANT those privileges to other users, • and subsequently REVOKE those privileges.

  11. SQL DCL • Specify the operations that authorized users are allowed to execute against objects (TABLEs or VIEWs). GRANT <privilege list> ON <object> TO <user ID list> [with grant option]; REVOKE [GRANT OPTION FOR] <privilege list> ON <object> FROM <user ID list> <option>;

  12. SQL DCL Example GRANT SELECT, INSERT ON MIN.S TO MIS331S1; REVOKE INSERT ON MIN.S FROM MIS331S1; • Can MIS331S1 SELECT, UPDATE, INSERT, or DELETE data in MIN.S ?

  13. Some Oracle Facilities • (Digression) These are not related to view and security. • The DUAL Table • A special table with one dummy column and one row. • SEQUENCE • Automatic sequence number generator.

  14. Oracle DUAL Table • DUAL is a table automatically created by Oracle along with the data dictionary. • DUAL is in the schema of the user SYS, but is accessible by the name DUAL to all users. • It has one column, DUMMY and contains one row. • Selecting from the DUAL table is useful for computing a constant expression with the SELECT command. • The following statement returns the current date: SELECT SYSDATE FROM DUAL;

  15. Oracle SEQUENCE • You can use sequence numbers to automatically generate unique primary key values for your data. • When a sequence number is generated, the sequence is incremented. • Create a sequence • CREATE SEQUENCE • Once a sequence is created, you can access its values in SQL statements with the following pseudo columns: • CURRVAL - returns the current value of the sequence. • NEXTVAL - increments the sequence and returns the new value.

  16. CREATE SEQUENCE CREATE SEQUENCE [schema.]sequence [INCREMENT BY integer] [START WITH integer] [MAXVALUE integer | NOMAXVALUE] [MINVALUE integer | NOMINVALUE] [CYCLE | NOCYCLE] [CACHE integer | NOCACHE] [ORDER | NOORDER] • Please use the SQL*Plus on-line help or the Oracle online documentation for a detailed description.

  17. SEQUENCE Example CREATE SEQUENCE SEQ_S# START WITH 6; INSERT INTO S VALUES ('S' || TO_CHAR(SEQ_S#.NEXTVAL), 'Swan', 20, 'Tucson'); INSERT INTO S VALUES ('S' || TO_CHAR(SEQ_S#.NEXTVAL), 'Direct', 10, 'Tucson'); SELECT SEQ_S#.CURRVAL FROM DUAL;

More Related