Transcript PPTX, 1.51Mb - UK Oracle User Group

1
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
SQL Developer Data Modeler
Overview and New Features
in version 4.0
David Last
Data Modeler development team
2
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Legal
The following is intended to outline our general product direction. It is intended for
information purposes only, and may not be incorporated into any contract. It is not a
commitment to deliver any material, code, or functionality, and should not be relied
upon in making purchasing decisions. The development, release, and timing of any
features or functionality described for Oracle’s products remains at the sole
discretion of Oracle.
3
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Agenda
4

Brief Overview

New diagram display options

Improved support for External Tables

Improved Search functionality

Oracle 12c database – Identity columns

Oracle 12c database – Masking/Redaction
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Oracle SQL Developer Data Modeler Overview
 Technology and Architecture
– Java based
– Common IDE Framework
– Metadata stored in XML Files
 Database support
– Oracle 9i, 10g, 11g and 12c
– Third-party databases:
 Platform support
– Windows
– Linux
– Mac OSX
5
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Microsoft SQL Server, DB2, UDB
Oracle SQL Developer Data Modeler Overview
Strategy and Analysis
Import Models
Data Type
Domains
DFD
ERD
Logical
Multidimensional
Reporting
Database Design
Relational
DDL Script
6
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Star Schema
Physical
Data Dictionary
SQL Developer Data Modeler Releases
7
Initial release (version 2.0)
July 2009
version 3.1
February 2012
version 3.3
March 2013
version 4.0
December 2013
version 4.0.2
May 2014
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
New diagram display options
 Can include Table/Column comments
 Can include Classification Types
 Can mark Table or Column as “deprecated”
 Can include attributes of a Column that is defined as a
Structured Type (new in DM v 4.0.2)
8
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Organization EXTERNAL for Relational Tables
 Tables on the Relational Model diagram can be
generated as External tables (by setting the Table’s
Organization property to “EXTERNAL”)
 This is new in Data Modeler v 4.0.2 - previously External
Tables were defined only in the Physical Model, and did
not appear on the diagram.
9
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Search functionality – simple search
Simple and Advanced search :
Simple search:
10
•
Just type the word you are looking for and all searchable
properties of all objects are checked for match; dynamic
properties are also checked
•
Content of the property is checked, if property is another
object (Domain, Structured type, …) then the name of that
object is checked
•
The name of the property is checked if content is “true”,
“yes”, “y” – using “partit” as search pattern on physical model
will return all partitioned tables
•
Search could be triggered in two ways – set in preferences;
could be slow for large and complete designs
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Search functionality – advanced search
Advanced search:
•
Narrowed to specific object type and properties
•
AND/OR expressions can be constructed
•
Available at model level
Regular expressions can be used in both modes
11
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Search functionality – global search
Global search:
12
•
Spans all open designs and models
•
Only simple search is available
•
Located at top right corner in standalone version
•
Available through menu “View>Data Modeler>Global
Search” in SQL Developer (with DM 3.3)
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Search functionality – global search
13
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Search functionality – model level
Model level search:
14

Searches through the whole model not just current
diagram

For relational model – searches through all open physical
models

Activated using “Find” icon on tool bar (or Ctrl-F on
diagram) or “Find” in context menu for the model in
browser

Advanced mode can be used to search on specific
properties
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Search functionality – result
15

Search result (model level) can be pinned, another window will appear on next search

Search conditions can be saved and reused later

Search result can be filtered by model (in global search) and object type

Double click on a row in search result will bring up properties editor for that object

Selecting a row in result list will show diagrams where that object (or its container) is
used – double click on diagram will focus on object on that diagram

Filtering on model and object type will unlock another functionality – “Properties”
(setting common properties on objects) and “Reports”
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Search functionality – result
16
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Search functionality – setting common
properties
17

“Old Value” shows if there are common
properties set, empty otherwise

Three types of data – String (text), boolean
(true/false) and object (domain,
schema/user, tablespace …) – additional
dialog appear to select related object

More than one properties can be set at once

Data type can be only one of domain, logical
type, distinct type, structured type or
collection type
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Search functionality – setting common
properties
18
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Search functionality – custom reports
19

Custom reports can contain only one type of objects –
columns, tables, entities, sequences… ; this is the
reason they are available only when search result is
filtered by model and object type

Report templates can be created for each type of
object

Optimization on boolean values – true and false can
be replaced with provided constants

Supported formats – HTML, PDF, RTF and Excel
(XLS, XLSX)

Standard report can be generated on search result if
there is a standard report for objects in search result
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Search functionality – custom reports,
templates
20

Up to 30 properties can be
included, properties in red are
read-only, those in blue could be
read-only when edited in Excel

Order of columns and custom
names can be set

Sort order can be defined – by
Entity and Name in example

Column width is for HTML
reports
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Search functionality – custom reports
21
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Search functionality – custom reports, Excel
file

Reports in XLS and XLSX format can be
edited in Excel
 Read-only properties will remain read-only
in Excel
 Properties that represent object (in blue in
template – domain, schema/user,
tablespace …) will be presented with look
up field
22
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Search functionality – custom reports, Excel
file
23

Updated Excel file can be returned back to the
source model – context menu for the model in
browser

Verification that file is for the same model

Log will be generated for updates done during
import

Be aware that changes in data type of FK
column/attribute won’t be applied because
they inherit data type from referred
column/attribute
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Oracle Database 12c – identity column
24

Data Modeler supports identity (auto increment) columns in Oracle Database 11g through usage of
sequence and trigger. Native support for identity column in Oracle Database 12c just brings another
option to consider.

Data Modeler 4.0 provides options to use the new database feature or stay with sequence trigger
approach
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Oracle Database 12c – identity column:
Trigger – the traditional approach
CREATE TABLE Course
( Course_ID NUMBER NOT NULL ) ;
ALTER TABLE Course
ADD CONSTRAINT Course_PK PRIMARY KEY ( Course_ID ) ;
CREATE SEQUENCE Course_Course_ID_SEQ
START WITH 1 NOCACHE ORDER ;
CREATE OR REPLACE TRIGGER Course_Course_ID_TRG
BEFORE INSERT ON Course
FOR EACH ROW
WHEN (NEW.Course_ID IS NULL)
BEGIN
:NEW.Course_ID := Course_Course_ID_SEQ.NEXTVAL;
END;
/
25
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Oracle Database 12c – identity column:
new IDENTITY clause
CREATE TABLE Course
(
Course_ID NUMBER GENERATED BY DEFAULT AS IDENTITY
( START WITH 1 NOCACHE ORDER ) NOT NULL
)
;
ALTER TABLE Course
ADD CONSTRAINT Course_PK PRIMARY KEY ( Course_ID ) ;
26
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Oracle Database 12c – identity column:
using DEFAULT clause
CREATE SEQUENCE Course_Course_ID_SEQ
START WITH 1
NOCACHE
ORDER ;
CREATE TABLE Course
(
Course_ID NUMBER DEFAULT Course_Course_ID_SEQ.NEXTVAL NOT NULL
);
ALTER TABLE Course
ADD CONSTRAINT Course_PK PRIMARY KEY ( Course_ID ) ;
27
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Who’s looking at my sensitive data?
28
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Redaction

Enables to mask (redact) data that is returned from the queries issued by low privileged
users or application


Part of Oracle data security
Real-time

works well in dynamic production environment

All referential-integrity of back-end data is preserved during the redaction process

Different masking styles through different policies

To comply with industry regulations

Payment Card Industry Data Security Standard (PCI-DSS)
 Sarbanes-Oxley Act
29
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Oracle Data Redaction
Partial
Before
After
30
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Oracle Database 12c - masking
Data Modeler 4.0 supports 2 ways of masking, extending current support for defining and
visualizing of sensitive data
31

REDACTION policies – a redaction policy can operate only for a single table, providing
masking definition(s) and condition when it's applied, for one or more columns
belonging to that table.

TSDP (Transparent Sensitive Data Protection) policies - TSDP policy can protect
multiple columns belonging to multiple tables, and more than one TSDP policy can be
used to protect columns belonging to one table. However it's important to note that
TSDP policies are implemented using Redaction policies and therefore it's not
possible to have Redaction policy and TSDP policy protecting one and the same
table.

Implementation for both approaches starts with masking templates
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Masking Templates
32

Masking templates for Character, Numeric and Date columns can be defined

“Tools>Masking Templates Administration”

Used in Redaction and TSDP policies
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Redaction policy
33

Definition of masking type and masking template at column level

Only masking template related to column data type (Character, Numeric and
Date) are available for particular column
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Redaction policy
34
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
TSDP policy
Following steps are required
1.
Templates
2.
To create sensitive type - sensitive types appear as kind of classification for associated with them
columns. Sensitive types are not "sensitive" to data type of columns associated to them.
3.
Associate columns to sensitive type – directly from sensitive type dialog or through domain defined
as “sensitive”
4.
To create TSDP policy
5.
Associate sensitive types to TSDP policy
Templates, Sensitive types and TSDP policies can be used in all designs
35
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
TSDP policy, sensitive type
 Can be created in the browser
36
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
TSDP policy, sensitive type
37
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
TSDP policy, sensitive domain

38
Domain can be associated with “sensitive type” and later assigned to columns/attributes
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
TSDP policy, create
39
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
TSDP policy, sub-policies

40
It’s a collection of sub-policies – can be restricted to specific schema, table, data type
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
TSDP policy, sensitive types
41
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Learn More..
 SQL Developer Data Modeler on OTN -
http://www.oracle.com/technetwork/developer-tools/datamodeler
 SQL Developer Data Modeler community forum
https://forums.oracle.com/forums/forum.jspa?forumID=1317
 Oracle Learning Library
http://apex.oracle.com/pls/apex/f?p=44785:2:0:FORCE_QUERY::2,RIR
,CIR:P2_TAGS:Data%20Modeler
 Jeff Smith http://www.thatjeffsmith.com/
 Kris Rice http://krisrice.blogspot.co.uk/
42
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
43
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
44
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.