Invariant Properties

  • rss
  • Home

Database and Webapp Security, part 4: Schema Ownership

Bear Giles | December 18, 2011

What are DDL, DML, DCL and TCL?

SQL contains four distinct types of statements.

Data Definition Language

Data Definition Language (DDL) statements define the database structure. Think of this as the landlord that builds the warehouse but turns over the keys to the renter.

Statements:

  • create – create tables, views, indexes, etc.
  • alter – alter tables, views, indexes, columns, etc.
  • drop – delete tables, views, indexes, etc.
  • truncate – remove all of the records from a table
  • comment – add comments to tables, columns, views, etc.
  • rename – rename a table, view, etc.

Data Manipulation Language

Data Manipulation Language (DML) statements manage the data within the structure created by the DDL. Think of this as the tenant of the warehouse – it can use the warehouse but can’t knock down walls.

Statements:

  • select – retrieve data
  • insert – insert new data into a table
  • update – update existing data within a table
  • delete – delete data from a table
  • call – call a PL/SQL or other stored procedure
  • explain plan – explain how a querywill be executed
  • lock table – lock a table to limit concurrency

Data Control Language

Data Control Language (CDL) statements control access rights to the data and schema. Think of these as locks on the doors, permission to move walls within the warehouse, etc.

Statements:

  • grant – give the user additional privileges
  • revoke – remove user privileges

Transaction Control Language

Transaction Control Language (TCL) statement are used to control transactions.

Statements:

  • commit – save completed work
  • rollback – undo completed work
  • savepoint – mark a point that we can rollback to later without necessarily rolling back the entire transaction
  • set transaction – set transaction options

Use Different Database User for Schema And Data Ownership

The schema should be owned by one database user, e.g., app_owner and the data should be owned by a different database user, e.g., app_user.

The owner should:

  • have the ability to run DDL and DCL statements
  • arguably not have the ability to run DML statements
  • never be accessed via the webapp

The user should

  • have the ability to run DML and TCL statements
  • not have the ability to run DDL or DCL statements
  • be accessible via the webapp

Cost/Benefit Analysis

There is a very favorable cost/benefit ratio for separating the ownership of schema and database. There is a slightly higher cost when creating and maintaining the database but it essentially eliminates the ability of a web intruder to destroy the database schema itself. The data, on the other hand, can still be nuked.

Categories
java, linux, security
Comments rss
Comments rss
Trackback
Trackback

« Database and Webapp Security, part 3: SQL Injection in Stored Procedures Revisiting Defending from XEE Attacks With Security Managers »

Leave a Reply

Click here to cancel reply.

You must be logged in to post a comment.

Archives

  • May 2020 (1)
  • March 2019 (1)
  • August 2018 (1)
  • May 2018 (1)
  • February 2018 (1)
  • November 2017 (4)
  • January 2017 (3)
  • June 2016 (1)
  • May 2016 (1)
  • April 2016 (2)
  • March 2016 (1)
  • February 2016 (3)
  • January 2016 (6)
  • December 2015 (2)
  • November 2015 (3)
  • October 2015 (2)
  • August 2015 (4)
  • July 2015 (2)
  • June 2015 (2)
  • January 2015 (1)
  • December 2014 (6)
  • October 2014 (1)
  • September 2014 (2)
  • August 2014 (1)
  • July 2014 (1)
  • June 2014 (2)
  • May 2014 (2)
  • April 2014 (1)
  • March 2014 (1)
  • February 2014 (3)
  • January 2014 (6)
  • December 2013 (13)
  • November 2013 (6)
  • October 2013 (3)
  • September 2013 (2)
  • August 2013 (5)
  • June 2013 (1)
  • May 2013 (2)
  • March 2013 (1)
  • November 2012 (1)
  • October 2012 (3)
  • September 2012 (2)
  • May 2012 (6)
  • January 2012 (2)
  • December 2011 (12)
  • July 2011 (1)
  • June 2011 (2)
  • May 2011 (5)
  • April 2011 (6)
  • March 2011 (4)
  • February 2011 (3)
  • October 2010 (6)
  • September 2010 (8)

Recent Posts

  • 8-bit Breadboard Computer: Good Encapsulation!
  • Where are all the posts?
  • Better Ad Blocking Through Pi-Hole and Local Caching
  • The difference between APIs and SPIs
  • Hadoop: User Impersonation with Kerberos Authentication

Meta

  • Log in
  • Entries RSS
  • Comments RSS
  • WordPress.org

Pages

  • About Me
  • Notebook: Common XML Tasks
  • Notebook: Database/Webapp Security
  • Notebook: Development Tips

Syndication

Java Code Geeks

Know Your Rights

Support Bloggers' Rights
Demand Your dotRIGHTS

Security

  • Dark Reading
  • Krebs On Security Krebs On Security
  • Naked Security Naked Security
  • Schneier on Security Schneier on Security
  • TaoSecurity TaoSecurity

Politics

  • ACLU ACLU
  • EFF EFF

News

  • Ars technica Ars technica
  • Kevin Drum at Mother Jones Kevin Drum at Mother Jones
  • Raw Story Raw Story
  • Tech Dirt Tech Dirt
  • Vice Vice

Spam Blocked

53,793 spam blocked by Akismet
rss Comments rss valid xhtml 1.1 design by jide powered by Wordpress get firefox