A New Series: Three Schema Architecture, APIs and Updatable Views

I'm starting a new series of posts today to talk about some ideas in database schema design. It is especially for Oracle RDBMS, but you could probably implement some of these ideas in other RDBMSs. Today's post is about some of the ways we've done it in the past and how we do it now.

The Way it used to be Done

I’ve been working with Oracle and applications that use the Oracle database for a long time. The tradition in the past was that there would be one schema to hold all the tables, views and stored procedures for one application, or sometimes, if the application was large, one schema per sub-system. In an environment like Oracle Forms, where the users each had his/her own account in our database, we would grant each user a database role, grant object privileges to the roles as needed, and have synonyms for the objects in the application schema. And yes, they were public synonyms – we didn’t know any better. This was okay for back then, because Forms was client/server, and all our users were on our local network. Or our users were on a terminal connected to our central computer.

This doesn’t work anymore. Our applications are now web-based applications, and they connect to a single shared schema in our database. At first, we connected to the application schema itself, which is great for ease of management, because there are no synonyms, no roles, no grants, and everybody can do everything, unless the application itself restricted it. But it isn’t very secure, and certainly doesn’t follow the rule of “least privilege”. If someone were to hack our web application server, and gain access to the data source through which the application talks to the database, that person would have free reign over the database – could change tables, alter code in the stored procedures, even drop everything. They might not even need to hack the server. I’m afraid that I’ve caught some developers (and that includes me) writing applications that are subject to SQL Injection.

The Way we do it Now

So we quickly realized that we should still have separate schemas for accessing the application objects. And so, I have for the HR application, the schema named HR to own all the objects, and the schema named HR_PROXY through which the application accesses the objects. And we can even get away from public synonyms, and simply give HR_PROXY private synonyms for all of the objects to which the application needs direct access. I can even have roles, password protect those roles and have the application only activate the roles that are appropriate for the currently connected user. Now SQL injection can’t do DDL and change the objects. But it can still see anything that HR_PROXY can see, and do anything that HR_PROXY can do, including delete all the data in the database.

What I have is:

One or more schemas that I call the Application Schemas, which contain all the database objects, tables, views, sequences, triggers, and stored procedures for an application.

One (maybe more) schema that I call the Proxy Schema, which is granted privileges on the objects of the Application Schemas as needed for the application to work. It also has private synonyms for those objects. Applications connect to the Proxy schema, NEVER the Application Schema.

Tomorrow, I'll talk about Oracle's proposal for a Three-Schema Architecture to replace my current Two-Schema version.

I'm an Oracle programmer and administrator experienced in all phases of software design, system implementation, and database conversion. Visit my website here.

Disclaimer: Blog contents express the viewpoints of their independent authors and
are not reviewed for correctness or accuracy by
Toolbox for IT. Any opinions, comments, solutions or other commentary
expressed by blog authors are not endorsed or recommended by
Toolbox for IT
or any vendor. If you feel a blog entry is inappropriate,
click here to notify
Toolbox for IT.

This is my favorite Oracle conference. It is completely devoted to people who make their livings developing applications with Oracle tools and Oracle databases, not just the original Oracle RDBMS, but also APEX, OBIEE, Essbase, Hyperion, My SQL, TimesTen etc. Very technical, little hype. This year, I'm presenting "Updatable Views to Secure Your Database and Make Your Developers Happy" - which my readers may recognize from an earlier series of posts.

This is Oracle's big show. If you want to know what Oracle is up to, go. Oracle Develop is no longer a formal track at OpenWorld. That doesn't mean that you can't find good technical content - you can, especially on User Group Sunday. But I am reconsidering whether there is enough to justify my attendance.

I haven't been to Collaborate for a few years, because the best content in the IOUG part of the conference is very DBA-oriented, and I've surrendered my DBA hat. But if you are primarily a DBA or database developer, Collaborate is well worth your time.