Skip to Main Content
  • Questions

  • Allow DDL for some developers and not others

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Daniel.

Asked: September 29, 2007 - 10:55 am UTC

Last updated: October 03, 2007 - 4:33 pm UTC

Version: 10.2.0

Viewed 1000+ times

You Asked

Hi Tom - looking for ideas here....

I have a business unit which would like to have their Data Modellers have access to a schema to create/maintain schema objects, and also want their developers to be able to build an app on top of these objects. A requierment is that the Developer group CANNOT issue DDL (if they want a new column, it has to go back to a Data Modeler to implement). Presumably Developers could create/alter triggers/functions/procedures/packages. So they kind of want a technological division between the data modelers and those responsible for code (which could be whatever outside the database - in which case this is easy - or it could also be PL/SQL in procedures/triggers etc.)

Some of the applications in question are APEX based, some are more generic in nature (whatever frontend hits a schema) some are older Forms apps. In general I don't think that matters although APEX may offer some different devlopment workflow.

This request has come up because the development manager feels there is a lack of controls around development, particularly at the Data Model Level.

I'd appreciate comments as to workablity, maintaniablity, and how sensible an environment like this would be. And also suggestions on how to implement - I've thought of a few, none of which strike me as great. Consider that the database would be expected to host several applications, some of which the developers and data modelers would have no access to, so simply granting CREATE ANY XXX to some users is not an acceptable option.

We do maintain Dev/Test/Prod kind of environments for most of these apps but I guess the development manager would like to take things a step further.

Thanks for any ideas - Environment will be at least 10R2.

and Tom said...

The development manager is not complaining about LACK OF CONTROLS

The development manager is complaining about the fact that (thankfully) there are controls in place.

DDL is code, code is code, code needs to be configuration managed. In a multi-user/developer environment making changes "whenever you feel like it" to the physical schema is a horrible idea.

You'll lose changes.
You'll have no clue why those changes where made.
You'll end up 12 months from now with tables that have 500 indexes, and 20 columns.
You'll end up with column names that follow no convention, they just appear - and never get used

One would think a development manager would appreciate the need for some level of control, for some configuration management, for change management - more than anyone else.


Code - that comes and goes, yet we control it using source code control.

Data - data models, the physical structures - they live for decades, and yet we just "throw them out there"


I would be against development have the ability to effect schema changes in any environment outside of their own individual testing areas. Any schema change affects the entire company, not just them, and must be coordinated and thought about at a more global level - NOT at the application level.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Excellent !!!

A reader, October 01, 2007 - 7:13 am UTC


Use database controls

Bill B, October 01, 2007 - 9:49 am UTC

The way to fix your problem is to have the database objects in one schema and the developers logging in to another schema that ONLY has insert/delete/update privileges on the tables. Use a login trigger to change their default schema and they will never be able to issue any schema.

don't quite understand

Sokrates, October 01, 2007 - 3:31 pm UTC

Tom,

you say

a. "DDL is code, code is code, code needs to be configuration managed. " (I agree)
b. "Code - that comes and goes ... data models, the physical structures - they live for decades"

I don't understand. Don't a. and b. contradict a bit each other ?
(by the way, because of a., why isn't there a configuration management tool built into Oracle - or are there plans to do so ? - or is there already s.th. in 11g ?)

We have (in ideal)
I. Code that is in the database (packages that deliver interfaces clients can use to access/modify the data)
II. "Client code" accessing I.
ok ? (at least I think I learnt that from you)

"I would be against development have the ability to effect schema changes in any environment outside of their own individual testing areas." - I don't see it like that.

My opinion:
- don't think "schema"
- rather think "interface accessing/modifying data"

So, I would't separate data modellers and developpers at all - data modelling *IS* developping
Of course they have to separate different applications probably accessig the same data, but that's there responsibility
Am I completely wrong ?
Tom Kyte
October 03, 2007 - 4:33 pm UTC

no, they do not contradict eachother - I was pointing out that we configuration manage stuff that is so transitory, but we don't do that for the really important stuff - the stuff we'll be living with for years.

configuration management is done outside of the database by various tools.


I did not understand the last bits there.


changes to a shared environment - be it placing new code in or modifying a structure - has to be coordinated and controlled.


thing of a database table like a C struct.
and a schema like a ".h" file.


DDL Configuration Management

MartinT, October 04, 2007 - 9:36 pm UTC

If the DDL is being managed properly by the data modellers their configuration management tool should be Designer 2000 or something similar. The developers can do what they want in test but the only DDL that gets implemented into production is that generated from the physical model. Isn't it?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library