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. • 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").

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.

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'

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".

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;

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.

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

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';

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.

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>;

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 ?

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.

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;

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.

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.

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;

Views, Security, and SQL Facilities