Some conversations are a recurring experience of DBA life.
DBA [to software vendor] – ‘So why does the application schema owner need to have the DBA role privilege?’
Vendor – ‘The installation procedure requires it’
DBA – ‘So can we revoke it after the installation?’
Vendor – ‘It hasn’t been tested so we couldn’t support it’
DBA – ‘Could you test it to prove that it works?’
Vendor – ‘That would be a change request and we’ll need time.’
Closely followed by :-
DBA – ‘We like to lock application schema owner accounts’
Vendor – ‘The application servers need to connect to that account’
DBA – ‘Couldn’t we create a separate login account and grant the privileges that are actually needed to that? That way there’s no risk of anyone dropping or modifying the application objects, except when the schema owner account is unlocked briefly under change control’
Vendor – ‘It hasn’t been tested so we couldn’t support it’
DBA – ‘Could you test it to prove that it works?’
Vendor – ‘That would be a change request’
DBA – ‘How about if we implement it in one of our test environment and prove to you that it will work.’
Vendor – ‘We haven’t tested it so we couldn’t support it’
You’re on your own, basically.
Of course, a poor security model; database independent (for which read, lowest common denominator) code; cobbled-together data model, etc. etc. are never used as show-stoppers for business applications. The people who purchase them like the front-end interface, the functionality and the neat salesman with the neat demo. However, a word for the software vendors out there – when these applications turn up on a DBA’s lap, we sigh, maybe chuckle a bit, and then wonder how we’re going to make the best of a bad job while you laugh your way to the bank. Maybe it would be worth employing someone who has moved beyond the stage of knowing that Oracle is a database so you can use JDBC or ODBC to access it? You could even just employ them for a month or two at the start of the application design and development. It would save so much pain later on. For you, for your customers and for DBAs 😉
… and people wonder why DBAs get angry sometimes?
It’s not just 3rd party products. There are plenty of crimes hidden in the Oracle Application Suite.
Also, when support comes via a consultancy firm who don’t understand the finer points of security, you’ll make your ropey application even ropier. Nice!
Cheers
Tim…
I would laugh along with you, but I’m on the vendor side in one of those cases. I am guilty of uttering those very phrases – within the past week, even.
However, I am working with them to address their needs, and I will accelerate any required change requests.
I’ve seen some crazy designs over the years. Here’s how one product was designed:
– Support for 3 major DB vendors
– No foreign keys, constraints, at all
– Very little direct SQL in the code, everything through stored procedures
– Stored procedures written in “neutral” language and “compiled” into PL/SQL or T-SQL as appropriate
The worst of both worlds!
That’s not so bad…this is worse
DBA [to software vendor] – ‘So why can’t we reset the application owner’s password?’
Vendor – ‘The application uses it throughout the code.’
DBA – ‘So can we change the code to take a variable instead?’
Vendor – ‘That would be a change request and it would take a lot of effort.’
DBA – ‘Will this be fixed in the next release?’
Vendor – ‘We don’t have that scheduled but will submit it in the queue for nice-to-have enhancements.’
Rob,
You can only do what you can 😉
Peter,
Yeah, I’ve had that one a couple of times too 😉
It’s not quite in the same league, but this same application requires two different schemas that share table names (one is a cut-down data mart version of the other). Unfortunately they both have the same schema name too – XXXXXX.
Vendor – ‘We need a new database instance for the Data Mart’
DBA – ‘Can’t we just use two different schemas in the same database? We’re trying to consolidate when we can.’ (Note – these schema are *tiny*)
Vendor – ‘We don’t support that and haven’t tested it.’
DBA [based on experience of earlier conversations] – ‘Can we test it for you?’
Vendor – ‘We’ll get back to you’
[Days pass …]
Vendor – ‘We think it’ll be okay, but the Data Mart schema has to be called XXXXXX because it’s hard-coded in the application’
DBA – ‘So they share table names and they have to have the same schema name too? That’s why they need to be in different databases?’
Vendor – ‘Yes, but you can change the name of the other one’
It looks like it’s all resolved, but why wouldn’t you think of making object names unique in the namespace of your entire application?!?
Sigh ….