Path: text.usenetserver.com!out03b.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!d55g2000hsg.googlegroups.com!not-for-mail From: "fitzjarrell@cox.net" Newsgroups: comp.databases.oracle.misc Subject: Re: User Security Question Date: Fri, 02 Nov 2007 12:14:49 -0700 Organization: http://groups.google.com Lines: 66 Message-ID: <1194030889.119367.37830@d55g2000hsg.googlegroups.com> References: <1194024748.352618.214830@y42g2000hsy.googlegroups.com> NNTP-Posting-Host: 138.32.32.166 Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" X-Trace: posting.google.com 1194030889 17942 127.0.0.1 (2 Nov 2007 19:14:49 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Fri, 2 Nov 2007 19:14:49 +0000 (UTC) In-Reply-To: <1194024748.352618.214830@y42g2000hsy.googlegroups.com> User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1) ; Embedded Web Browser from: http://bsalsa.com/; .NET CLR 1.1.4322; .NET CLR 2.0.50727),gzip(gfe),gzip(gfe) Complaints-To: groups-abuse@google.com Injection-Info: d55g2000hsg.googlegroups.com; posting-host=138.32.32.166; posting-account=ps2QrAMAAAA6_jCuRt2JEIpn5Otqf_w0 Xref: usenetserver.com comp.databases.oracle.misc:250471 X-Received-Date: Fri, 02 Nov 2007 14:14:49 EST (text.usenetserver.com) Comments embedded. On Nov 2, 12:32 pm, HectorTYC wrote: > 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. Is this a 'home-grown' application or one supplied by an outside vendor? If the latter are the vendor representatives also making these changes? > > 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. 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. > 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. SYS_CONTEXT() can be your friend, as it can return IP Address, Host, and other useful information about the connected session. > > 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. > 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. > 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. It isn't, really. Look into the DDL trigger option and see if that will meet your needs. David Fitzjarrell