Database version: 9.2.0.6.0
OS = Solaris 8

I currently work for an organisation that continually shoots itself in
the foot with the large amount of unauthorised changes it makes to the
production database of the main business application. These changes
are being made by a motley bunch of devlopers, support analysts ...
pretty much anyone who feels like it really.

To make matters worse they are doing this using the main schema
account (please excuse me if I mangle terminology, I'm not a DBA) used
by the application itself, thus leaving no audit trail of who has made
these changes. All show as being the application that has made the
change, rather than some nut with Toad and a cavalier attitude as is
more often the case. Worse; tracing (?) is not turned on so that info
is even more limited than it could be.

I am aware of how bad this is m'kay (on any number of levels) but
until I can persuade/force the organisation to plough resource into
what is quite a large undertaking, simply changing the password is an
appealing but not viable option.

As an interim measure I am resigning myself to attempting to force
people not to use this account using policy and a mixture of begging
and loud tutting, however I'd be interested to see if anyone out there
has any clever suggestions as to how I could stop people using the
application user account without making changes to the account or
incurring any DB downtime. I did wonder if it was possible to allow
connections by this user from only a list of trusted sources but our
DBA thinks this isn't possible.

On Nov 2, 1:32 pm, HectorTYC <rufus_mi... (AT) btinternet (DOT) com> wrote:
Too bad you are not US based. I say this because the environment you
describe is not SOX complaint and almost any audit would have written
this up as a violation.

As a general rule developers should have update access to production
data nor should customers except via a provided application screen.
Unfortunately there are more exceptions than sites that follow this
basic policy.

Try documenting mistakes that affect production and how much time it
took to fix the error. Time is money. Most managers at least
partially understand money and the desirability of not letting others
waste it.

IMHO -- Mark D Powell --

Comments embedded.
On Nov 2, 12:32 pm, HectorTYC <rufus_mi... (AT) btinternet (DOT) com> wrote:
Is this a 'home-grown' application or one supplied by an outside
vendor? If the latter are the vendor representatives also making
these changes?

Unfortunately it's necessary to use the owner account to modify
objects (in the absence of DBA or SYSDBA access), but I understand
your problem. There are possible workarounds, however, such as DDL
triggers which could populate a table with the 'identifying'
information for the session, such as IP address, program used, etc.
You should look at:

http://www.psoug.org/reference/ddl_trigger.html

for examples of such triggers.

SYS_CONTEXT() can be your friend, as it can return IP Address, Host,
and other useful information about the connected session.

That shouldn't be necessary; a robust DDL trigger just might be all
you need to track down who is doing what and when. Then it's up to
you to log how much trouble this change created.

It isn't, really. Look into the DDL trigger option and see if that
will meet your needs.


David Fitzjarrell

On 2 Nov, 17:32, HectorTYC <rufus_mi... (AT) btinternet (DOT) com> wrote:
An all too common problem, I'm afraid. I'm in the process of
attempting to introduce best practice at the organisation I work for
at the moment too. I feel your pain

Generally speaking, only the application written for the database
should be logging into a production system. I would get your DBA to
look into database logon triggers. Your DBA could write a PL/SQL
procedure that checks what application is logging in to the database
(e.g. TOAD.EXE, as in your case) and print an appropriate error
message before closing the connection. This method isn't foolproof -
a determined hacker could simply rename the TOAD executable, for
example, but it could well be a start for you.

Good luck.

HTH

-g

On 2 Nov, 18:57, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote:
Hi Mark,

As it happens we're in serious breach (12 counts) of the security
standards (BS27001) we already (astoundingly) have certification for.
I've already flagged these breaches some time ago.

I appreciate your comments but believe me it's an approach I've
already undertaken. I'm an ITIL qualified practitioner of about 6
years experience in change and release and about 15 years in IT in
general and agree with all you say, it's not for a lack of trying.
Oddly this is an organisation that doesn't place the usual values on
things, even money. I'm currently (at last!) in a place where I can
maybe force through a couple of policies around this as a result of a
couple of recent cock-ups, if not get the budget for the complete
overhaul it really requires. I was looking for some sneaky ways to bar
access that I could ask our overstretched DBA to implement while the
iron was hot so to speak.

Hi David,

It's an application that was developed bespoke by a 3rd party 2 years
ago, we're currently entering our 3rd cycle of use (annual) and it's
entirely internally supported. We are completely responsible for
breaking it ourselves nowadays.

Thanks for the pointers about triggers. I'll mention it to the DBA and
see what he thinks.

GazzaG,

That sounds the most useful. Until we can actually get the password
changed and do all the work that really needs to be done then just
putting in some sort of control that would actually take effort to
circumvent would have immense value as to do so would demonstrate an
attempt to subvert policy. As it stands it's all so uncontrolled and
vague I have sympathy for the folks who're being driven to make these
unauthorised changes.