Transcript week15 - Arms-A

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
1
© D. Wong 2003
Creating privileges

Owner of schema or modules has all privileges

Establish ownership at:
1. When a schema is created.
2. When a session is initiated by a CONNECT
statement.
e.g. CONNECT TO ABC_server AS conn1
AUTHORIZATION smith;
3. When a module is created, use an optional
AUTHORIZATION clause
2
© D. Wong 2003
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
3
© D. Wong 2003
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
4
© D. Wong 2003
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.
5
© D. Wong 2003
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
6
© D. Wong 2003
Architecture of an ODMG database
Schema Spec. in
ODL(Embedded in
C++, Java, etc)
ODL
Preprocessor
Source code for class
methods in host language
(C++, Java, …)
Host language
compiler
ODBMS
Software
Method
Implementation
Obj. code
ODBMS
Libraries
Linker
Information stored at the Server
Metadata
Method Implementation
Binaries Stored in DBMS
Data Access
Object Data
7
Ref. "Databases and Transaction Processing" – Lewis, Addison Wesley
© D. Wong 2003
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
8
Ref. "Databases and Transaction Processing" – Lewis, Addison Wesley
© D. Wong 2003
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:
1. Attributes: values associated with the object
2. Relationship: connection between the object itself and
other objects
3. 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
9
© D. Wong 2003
Object Definition Language (ODL) (continued)

Class declaration

Class include:
1. Class Name
2. Key declaration(s). Optional.
3. Extent Declaration = name for the set of currently
existing objects of a class (I.e. relation instance in
relational model)
4. Element declarations: attributes, relationships,
methods
class <name> [(extent names)]
{ < list of elements> }
10
© D. Wong 2003
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;
11
© D. Wong 2003

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
12
© D. Wong 2003
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
1. Many-many: have a set type of class in each
direction
2. Many-one: a set type for the one, and a simple
class name for the many
3. One-one: simple class name in both
13
© D. Wong 2003

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
14
© D. Wong 2003
ODL data types


Basis:
1. Atomic type: integer, float, characters, string,
boolean, enum
2. Class names
Structured types:
1. Set: Set<T> // finite sets of elements of type T
2.
3.
4.
5.
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.
6. Structures : Struct N {<type1> field1, …}
15
© D. Wong 2003
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;
…
}
16
© D. Wong 2003
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
17
© D. Wong 2003
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)
18
© D. Wong 2003
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
19
© D. Wong 2003
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);
20
© D. Wong 2003
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;
/
21
© D. Wong 2003
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', '01JAN-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';
22
© D. Wong 2003
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),
ZipNUMBER);


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);
23
© D. Wong 2003