I put together this blog post for those interested in learning more about the SmartDB (also or formerly known as "ThickDB") architecture and how to apply it in your applications. I will update it as more resources become available.
Large software systems must be built from modules. A module hides its implementation behind an interface that exposes its functionality. This is computer science’s most famous principle. For applications that use an Oracle Database, the database is, of course, one of the modules. The implementation details are the tables and the SQL statements that manipulate them. These are hidden behind a PL/SQL interface.
This is the Smart Database paradigm: select, insert, update, delete, merge, commit, and rollback are issued only from database PL/SQL. Developers and end-users of applications built this way are happy with their correctness, maintainability, security, and performance. But when developers follow the NoPlsql paradigm, their applications have problems in each of these areas and end-users suffer.
Toon Koppelaars, Real World Performance Team
Toon has been part of the Oracle eco-system since 1987. He is currently a member of Oracle's Real World Performance team. RWP troubleshoots application performance issues in and around the DBMS. The way applications currently use (or, rather, abuse) the DBMS is often at the root of these performance issues. Prior to joining the RWP team, Toon was mainly involved in database application development. He is the co-author of "Applied Mathematics for Database Professionals" (Apress 2016), a member of the OakTable network (http://www.oaktable.net/) and alumni Oracle ACE-Director. His special interests are: architecting applications for performance and scalability, database design, and business rules / constraints modeling. He is a long-time champion of the Smart Database paradigm, as witnessed by his authorship of the Helsinki Declaration (IT Version) in 2009.
Follow Toon on Twitter.
Which one do you think requires a bigger database server?
Toon Koppelaars describes an experiment to measure the work done by Oracle Database to complete a specific task using different approaches. The NoPlsql approach treats the database as no more than a persistence layer, using only naive single-row SQL statements; it implements all business logic outside of it. The Thick Database approach treats the database as a processing engine; it uses a combination of sophisticated set-based SQL statements and PL/SQL to implement all business logic inside it. “No business logic in the database” advocates take note: the Thick Database approach gets the task done with far less database work than the NoPlsql approach.
Guarding Your Data Behind a Hard Shell PL/SQL API
This session examines in practical detail how to ensure that the hard shell of a database’s PL/SQL API is impenetrable. It advocates strict adherence to the principle of least privilege by using a four-schema model (data, code implementation, API, and connect) and invokers rights units together with code-based access control. Scrupulous care is taken to ensure that the privileges needed for installation and patching are not available at runtime, and the approach is reinforced by secure error-handling.
The Database: Persistence Layer (NoPlsql) or Processing Engine (SmartDB)?
Slide deck from Toon's presentations at ODTUG's Kscope17 conference. Toon goes deep into the question of where business logic should reside, and the benefits you get from putting that logic into the database.
Also: Why SmartDB?
How to install a #SmartDB application back-end
The PL/SQL Product Manager offers a "sketch" of how developers and DBAs should set up their application in the database to follow a SmartDB architecture.
Why Use PL/SQL?
A definitive white paper on the key advantages accrued when you use the PL/SQL language, to build secure, maintainable, high performance applications that guarantee data integrity and consistency.
Doing SQL from PL/SQL: Best and Worst Practices
Assuming you buy into the SmartDB paradigm and will enclose your SQL statements inside PL/SQL "hard shell", this white paper will help you do it properly.
Moovit: A View From the Trenches
Millions of people develop applications on top of Oracle Database. The most secure and optimized of those applications take full advantage of SQL and PL/SQL. In this CodeTalk webcast, Steven Feuerstein interviews Oren Nakdimon of Moovit (http://moovitapp.com, lead developer for the backend of this popular transit app, to find out just how he and his small team have made the most of PL/SQL, and how they manage their PL/SQL code base.
How to Prove That Your SmartDB App Is Secure
If you are guarding your data behind a hard shell PL/SQL API, then it should be quite easy to prove, that your PL/SQL application is secured against SQL injection attacks. The basic idea is 1) that you do not expose data via tables nor views to Oracle users used in the middle-tier, by end-users and in the GUI; and 2) that you use only static SQL within PL/SQL packages. By following these two rules, you ensure that only SQL statements with bind variables are used in your application, making the injection of unwanted SQL fragments impossible. In this blog post, Philipp Salvisberg shows how to check if an application is complying to these two rules.
Is Your Application SmartDB?
Philipp Salvisberg offers another post and utility for analyzing compliance with SmartDB. He writes:
"I had recently a few discussions regarding the Smart Database Paradigm (SmartDB) with long-standing customers, new customers, partners, competitors and colleagues. Some people think that using APEX and PL/SQL in their database application is SmartDB. But it is not that simple. Bryn Llewelyn [former PL/SQL product manager] defined the term “Smart Database Paradigm” (SmartDB) in his talk Guarding your data behind a hard shell PL/SQL API. Based on his definition a SmartDB application must have the following five properties:
From Peter Koletzke (who prefers the term "ThickDB")
Using Thick Database Principles to Leverage Oracle SQL and PL/SQL
Part 1 - Save Cloud Costs and Simplify User Interface Development
Part 2 - Design, Create, and Maintain a Business Rules Repository
Part 3 - Implementation Techniques
From Philipp Salvisberg
Philipp's blog contains a number of posts relating to SmartDB. He has also offered up a variation on SmartDB, which he has named "PinkDB".
The Pink Database Paradigm
What is SmartDB?
Here's a summary:Large software systems must be built from modules. A module hides its implementation behind an interface that exposes its functionality. This is computer science’s most famous principle. For applications that use an Oracle Database, the database is, of course, one of the modules. The implementation details are the tables and the SQL statements that manipulate them. These are hidden behind a PL/SQL interface.
This is the Smart Database paradigm: select, insert, update, delete, merge, commit, and rollback are issued only from database PL/SQL. Developers and end-users of applications built this way are happy with their correctness, maintainability, security, and performance. But when developers follow the NoPlsql paradigm, their applications have problems in each of these areas and end-users suffer.
The Voice (and Face) of SmartDB
Toon has been part of the Oracle eco-system since 1987. He is currently a member of Oracle's Real World Performance team. RWP troubleshoots application performance issues in and around the DBMS. The way applications currently use (or, rather, abuse) the DBMS is often at the root of these performance issues. Prior to joining the RWP team, Toon was mainly involved in database application development. He is the co-author of "Applied Mathematics for Database Professionals" (Apress 2016), a member of the OakTable network (http://www.oaktable.net/) and alumni Oracle ACE-Director. His special interests are: architecting applications for performance and scalability, database design, and business rules / constraints modeling. He is a long-time champion of the Smart Database paradigm, as witnessed by his authorship of the Helsinki Declaration (IT Version) in 2009.
Follow Toon on Twitter.
AskTOM Office Hours on SmartDB
AskTOM, famous for its exhaustive Q&A on Oracle Database, has added free, monthly trainings and Q&A, in the guise of Office Hours. Subscribe here for reminders to stay up on the very latest with SmartDB!Resources
NoPLSql and Thick Database Approaches with Toon KoppelaarsWhich one do you think requires a bigger database server?
Toon Koppelaars describes an experiment to measure the work done by Oracle Database to complete a specific task using different approaches. The NoPlsql approach treats the database as no more than a persistence layer, using only naive single-row SQL statements; it implements all business logic outside of it. The Thick Database approach treats the database as a processing engine; it uses a combination of sophisticated set-based SQL statements and PL/SQL to implement all business logic inside it. “No business logic in the database” advocates take note: the Thick Database approach gets the task done with far less database work than the NoPlsql approach.
Guarding Your Data Behind a Hard Shell PL/SQL API
This session examines in practical detail how to ensure that the hard shell of a database’s PL/SQL API is impenetrable. It advocates strict adherence to the principle of least privilege by using a four-schema model (data, code implementation, API, and connect) and invokers rights units together with code-based access control. Scrupulous care is taken to ensure that the privileges needed for installation and patching are not available at runtime, and the approach is reinforced by secure error-handling.
The Database: Persistence Layer (NoPlsql) or Processing Engine (SmartDB)?
Slide deck from Toon's presentations at ODTUG's Kscope17 conference. Toon goes deep into the question of where business logic should reside, and the benefits you get from putting that logic into the database.
Also: Why SmartDB?
How to install a #SmartDB application back-end
The PL/SQL Product Manager offers a "sketch" of how developers and DBAs should set up their application in the database to follow a SmartDB architecture.
Why Use PL/SQL?
A definitive white paper on the key advantages accrued when you use the PL/SQL language, to build secure, maintainable, high performance applications that guarantee data integrity and consistency.
Doing SQL from PL/SQL: Best and Worst Practices
Assuming you buy into the SmartDB paradigm and will enclose your SQL statements inside PL/SQL "hard shell", this white paper will help you do it properly.
Moovit: A View From the Trenches
Millions of people develop applications on top of Oracle Database. The most secure and optimized of those applications take full advantage of SQL and PL/SQL. In this CodeTalk webcast, Steven Feuerstein interviews Oren Nakdimon of Moovit (http://moovitapp.com, lead developer for the backend of this popular transit app, to find out just how he and his small team have made the most of PL/SQL, and how they manage their PL/SQL code base.
How to Prove That Your SmartDB App Is Secure
If you are guarding your data behind a hard shell PL/SQL API, then it should be quite easy to prove, that your PL/SQL application is secured against SQL injection attacks. The basic idea is 1) that you do not expose data via tables nor views to Oracle users used in the middle-tier, by end-users and in the GUI; and 2) that you use only static SQL within PL/SQL packages. By following these two rules, you ensure that only SQL statements with bind variables are used in your application, making the injection of unwanted SQL fragments impossible. In this blog post, Philipp Salvisberg shows how to check if an application is complying to these two rules.
Is Your Application SmartDB?
Philipp Salvisberg offers another post and utility for analyzing compliance with SmartDB. He writes:
"I had recently a few discussions regarding the Smart Database Paradigm (SmartDB) with long-standing customers, new customers, partners, competitors and colleagues. Some people think that using APEX and PL/SQL in their database application is SmartDB. But it is not that simple. Bryn Llewelyn [former PL/SQL product manager] defined the term “Smart Database Paradigm” (SmartDB) in his talk Guarding your data behind a hard shell PL/SQL API. Based on his definition a SmartDB application must have the following five properties:
- The connect user does not own database objects
- The connect user can execute PL/SQL API units only
- PL/SQL API units handle transactions
- SQL statements are written by human hand
- SQL statements exploit the full power of set-based SQL
From the Community
Developers independent of Oracle also promote and teach about SmartDB and related approaches to database-centric development.From Peter Koletzke (who prefers the term "ThickDB")
Using Thick Database Principles to Leverage Oracle SQL and PL/SQL
Part 1 - Save Cloud Costs and Simplify User Interface Development
Part 2 - Design, Create, and Maintain a Business Rules Repository
Part 3 - Implementation Techniques
From Philipp Salvisberg
Philipp's blog contains a number of posts relating to SmartDB. He has also offered up a variation on SmartDB, which he has named "PinkDB".
The Pink Database Paradigm
A big thank you for this overview and bundling all these resources. It's something I'll definitely use as a reference in the future.
ReplyDeleteHi,
ReplyDeleteDo the table API’s for insert, update & delete need to be defined in the data layer of the Smart DB? where the tables are defined.
This would keep it abstracted from the business logic layer.
Please confirm your views. Thanks.
That layer of code should definitely be kept separate from the business logic layer.
DeleteThey should be inside the database. But you also need to decide WHERE in the database. You could compile all that code into the same schema as owns the data, but we'd recommend that you have a separate schema just for the code with explicit grants of access to the underlying data.