Security and User Authorization in SQL 8.7 pp. 410

1. Security and User Authorization in SQL 8.7 pp. 410 • Authorization ID = user name • Special authorization ID: PUBLIC • Privileges for: SELECT, INSERT, UPDATE, DELETE, REFERENCE, USAGE, TRIGGER, EXECUTE, UNDER • For SELECT, INSERT, UPDATE, may also specify on attribute level • Privileges are needed for relations in the subqueries also. e.g. Fig. 8.25 pp 411

2. Creating privileges • Owner of schema or modules has all privileges • Establish ownership at: • When a schema is created. • When a session is initiated by a CONNECT statement. e.g. CONNECT TO ABC_server AS conn1 AUTHORIZATION smith; • When a module is created, use an optional AUTHORIZATION clause

3. Granting privileges • Owner of a relation has GRANT privilege. • If you have the "GRANT" privilege to a set of privileges, you may grant them to any user. GRANT <privilege list> ON <database element> TO <user list> [WITH GRANT OPTION] e.g. GRANT SELECT, INSERT ON Studio TO kirk, picard WITH GRANT OPTION; -- by Janeway GRANT SELECT, INSERT ON Studio TO sisko; -- by picard GRANT SELECT, INSERT(name) ON Studio TO sisko; -- by kirk • Grant diagram e.g. Fig. 8.26 pp. 417

4. Revoking Privileges • Privileges can be revoked: REVOKE [GRANT OPTION FOR] <privilege list> ON <database element> FROM <user list> {CASCADE | RESTRICT} e.g. REVOKE SELECT, INSERT ON Studio FROM picard CASCADE ; • If A has been given a privilege by several different people on the same element, then all of them have to revoke in order for A to lose the privilege • If A granted privilege P to B, who granted P to C, then A revokes P from B will also revoke P from C. e.g. Fig 8.29 pp 420

5. Object-Oriented Data Model • ODMG • Object Database Management Group • Deals with OO standard for database • Also deals with ORDBMS (Object Relational DBMS) • Major parts of ODMG standard: • ODL: Object Definition Language, how to specify the db schema • OQL: the SQL-like Object Query Language • Host language binding: how to use ODL and OQL from within procedural languages. The standard define bindings for C++, SmallTalk, and Java. In ODMG, the host language also serves as the object manipulation language.

6. ODMG database management system • Application is written in a host language e.g. C++, Java • In order to access the db, the application must be linked with the ODBMS libraries and with the code that implements its class methods. • Much of the code that manipulates objects is part of the database itself. • Each class has a set of methods. Method signatures are specified in the schema using ODL. • The code for these methods is stored on the database server. • ODBMS invokes the appropriate code whenever a method is called. • OODMG database data is modified directly in the host language e.g. Stud.Name = "Joe"; // Stud contains the oid of a // persistent Student object

7. Architecture of an ODMG database Schema Spec. in ODL(Embedded in C++, Java, etc) Source code for class methods in host language (C++, Java, …) Host language compiler ODBMS Software ODL Preprocessor Method Implementation Obj. code ODBMS Libraries Linker Information stored at the Server Metadata Method Implementation Binaries Stored in DBMS Data Access Object Data Ref. "Databases and Transaction Processing" – Lewis, Addison Wesley

8. Structure of ODMG Applications Application source code in host language ODBMS Host language compiler ODBMS library Application Object code Method implementation binaries stored in DBMS Linker Executable code Ref. "Databases and Transaction Processing" – Lewis, Addison Wesley

9. Object Definition Language (ODL) • Conceptual model to describe the attributes, methods, and relationships of each object type (class), including it's inheritance properties. • ODL classes describes 3 kinds of elements: • Attributes: values associated with the object • Relationship: connection between the object itself and other objects • Methods: functions that may be applied to objects of the class. Methods are specified by it's signature: name, arguments (names, order, and type), return value type, name of any exceptions it can raise. e.g. Fig. 4.2 pp137

10. Object Definition Language (ODL) (continued) • Class declaration • Class include: • Class Name • Key declaration(s). Optional. • Extent Declaration = name for the set of currently existing objects of a class (I.e. relation instance in relational model) • Element declarations: attributes, relationships, methods class <name> [(extent names)] { < list of elements> }

11. Object Definition Language (ODL) (continued 2) • Attribute declaration (non-objects): attribute <type> <name>; e.g. 1 attribute string name; e.g. 2 attribute Struct Addr{ string street, string city} address; • Relationship (and inverse relationship) declaration (objects): relationship [rangetype]<className> <name> inverse className::<relationship name>; e.g. relationship Set<Star>stars inverse Star::starredIn;

12. Method declaration <returnType> <methodName> (arguments) raises (<exception>); e.g. 1: void lengthInhours() raises (noLengthFound); e.g. 2: void starName(out Set<String>) ; • Arguments: in : read-only out: for returning values inout: for both

13. ODL Relationships • Only binary relationships supported • Use a connecting class to represent multiway relationships Fig. 2.9 pp. 34. • Relationships are defined in inverse pairs. Fig. 4.3 pp 140 • Many-many: have a set type of class in each direction • Many-one: a set type for the one, and a simple class name for the many • One-one: simple class name in both

14. Subclass (S is a subclass of D) Class C extends D { class C's declarations } e.g. class Cartoon extends Movie { relationship Set<Star> voices; } • Multiple inheritance (separate the super classes by : in the extend declaration) e.g. class CartoonMurderMystery extends MurderMystery : Cartoon • Name conflict resolutions with Multiple inheritance pp. 151

15. ODL data types • Basis: • Atomic type: integer, float, characters, string, boolean, enum • Class names • Structured types: • Set: Set<T> // finite sets of elements of type T • Bag: Bag<T> // finite bags of element type T • List: List<T> // finite lists of 0 or more elements T • Array: Array <T, i> // T = type, i = no. of elements • Dictionary: Dictionary <T, S>, T is key type, S is range type. Each pair has unique key value. • Structures : Struct N {<type1> field1, …}

16. Keys declaration in ODL • Optional because each object is identified by an internal OID • May declare one or more keys in the extent declaration e.g. class Movie (extent Movies key (title, year)) { attribute string title; attribute integer year; … }

17. ODL to Relational Design • Invent a new attribute to serve as key when there is no key in the ODL design • ODL attributes that are not atomic are converted into relation attributes that usually are redesigned with normalization • Methods are not converted to relational design. But can have methods in Object Relational design

18. Object-Relational DB (ORDB) • SQL-99 adopted a limited subset of the object relational model • ORDBMS is a conservative extension to the existing RDBMS. • In general, ORDB consists of: • A set of relations (which can be viewed as classes) • Each relation consists of a set of tuples (which can be viewed as instances of the class that represents the relation) • Each tuple is of the form (oid, val) where oid is an object id and val is a tuple value whose components can be arbitrary values (e.g. primitive values, sets of tuples, and references to other objects)

19. ORDB, ODB, RDB • Difference between ORDB and ODB • In ORDB, the top-level structure of each object instance is always a tuple. In ODB, top-level structure can be an arbitrary value. • Difference between ORDB and RDB: • RDB tuple components must be primitive values • ORDB tuple components can be arbitrary values

20. Oracle Object example create type ADDRESS_TY as object (Street VARCHAR2(50), City VARCHAR2(25), State CHAR(2), Zip NUMBER); create type PERSON_TY as object (Name VARCHAR2(25), BirthDate DATE; Address ADDRESS_TY member function AGE_DAYS (BirthDate IN DATE) return NUMBER);

21. Oracle Object example (continued) • Defining methods for user defined types using PL/SQL: Create type body PERSON_TY as Member function AGE_DAYS (BirthDate DATE) return NUMBER is begin RETURN ROUND(SysDate – BirthDate); end; -- if there are more methods to the data type, may define here end; /

22. Oracle Object example (continued 2) • Create table with user defined abstract data types: create table CUSTOMER (Customer_ID NUMBER, Person PERSON_TY); • Use constructors for inserting data: insert into CUSTOMER values (1, PERSON_TY('Joe Smith', '01-JAN-90',ADDRESS_TY('10 Spring ST', 'BHM', 'AL', 35110))); • Use path names to access the attributes: SELECT Person.Address.Street FROM CUSTOMER; SELECT Person.AGE_DAYS(Person.BirthDate) FROM CUSTOMER; UPDATE CUSTOMER SET Person.Address.City = 'Birmingham' WHERE Person.Address.City = 'BHM';

23. Object-Orient Analysis and Design • Normalization in relational model relates each attribute to its primary key e.g. The following is in 3NF: create table CUSTOMER (Customer_ID NUMBER, Name VARCHAR2(25), BirthDate DATE; Street VARCHAR2(50), City VARCHAR2(25), State CHAR(2), Zip NUMBER); • For OO, further group related columns into abstract data types (ADT) (e.g. ADDRESS_TY) for reuse. • Then look for relationships among ADTs to determine if nesting is appropriate (e.g. PERSON_TY);