The grant users option has been around since release 2 (was on installation table back then). I had not tested turning it off. Our practice is create the userid then change the default tablespaces then notify clients of new userid. It works for us, because the DBA's happen to be the security administrators.
If you are creating the Oracle side of the user account, how are you doing password synchronisation?
PeopleSoft 8 has password aging done within the application, but to use pure database password aging prior to PeopleSoft 8 you can use Braintree's SQLSecure product. The peopleSoft software has DLL/database integration with the product if you have it installed. It also does cross database password synchronisation.
I thought the lowest common denominator was DB2 guess its been lowered. The do Windows development on SQLServer, but Unix is done on UDB. I wonder if PeopleSoft will move away from SQLServer as a source development platform now the Microsoft is trying to get into the CRM business. Direct competition.
David Davis
From: [EMAIL PROTECTED]
To: "david davis" <[EMAIL PROTECTED]>, Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Subject: Re[2]: Peoplesoft & Oracle
Date: Wed, 29 Jan 2003 09:44:23 -0500
David & Lisa,
1st point is that were still on PeopleTools 7.53 so this may be somewhat
dated. Anyway, go to Peopletools, utilities, use Peopletools options. Uncheck
the "Grant Access" option & low & behold, PeopleTools will not try to create a
user via the "grant connect to " syntax that is so out dated as to be atrocious.
The down side is that you now have to create the user manually, but we did put
together a trigger/dbms_job system that does it automatically as we wanted. Now
why in tarnation would one want to do that, Well as it turns out if you use that
syntax, as Lisa noted, everyone is assigned SYSTEM as their default and temp
tablespace. Well that's not so bad since their there only for a very brief
period of time before changing over to SYSADMIN or whatever else you happen to
use. The problem is that PeopleSoft in their VAST wisdom coded the first 2
select statements with a "group by" in them, just incase there was a duplication
of data in ps.psdbowner and/or psoprdefn. Now I do not want to bad mouth them
because that was not a bad idea. The bad point is that you end up with temp
segments in system and a fragmented system tablespace. YUCK!! OH for 9i and a
system wide default temp tablespace!!
OH, also one item of extreme note. DON'T setup password aging for your users.
PeopleTools doesn't appreciate it at all.
BTW: I would not say that PeopleSoft does not know databases, they just minored
in them instead. The real problem is that they code to the least common
denominator in the dbms market, namely SqlServer. So be prepared for lots of
fun, and do watch Customer Connection. They every once in a while come out with
a note or patch that is Oracle specific and really does help. One would think
that there'd be more of them since something like 75% of PeopleSoft's installs
are on Oracle. Of course their development is on Sql Server. Go figure!!
Dick Goulet
____________________Reply Separator____________________
Subject: Re: Peoplesoft & Oracle
Author: "david davis" <[EMAIL PROTECTED]>
Date: 1/28/2003 8:13 PM
There is a resolution 19368 on customer connection which discusses which
rights the SYSADM account requires and which ones can be revoked. Though it
was somewhat better formatted when I retrieved it in 1999. It was last
modified in 2002.
I am curious how you turn off the create user? I didn't think you could do
that, only control the grants that take place.
A little gotcha I ran into a couple of years ago when I revoked access was
that had errors in app designer. The DLL referenced DBA_TABLES (oops) I
think that was in V7.0.
David Davis
>From: [EMAIL PROTECTED]
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: Re: Peoplesoft & Oracle
>Date: Tue, 28 Jan 2003 14:39:21 -0800
>
>Lisa,
> Sounds like a default install of PeopleSoft. Step 1 that you can do is
>re-assign everyone to use a real temp tablespace. Step 2 is break up that
>db
>onto seperate spindles. Step 3 is to turn off autoextend exept for those
>tablespaces that are nearing say 90% full, then set autoextend to something
>sensible. As far as the user accounts are concerned that's normal. I for
>one
>would revoke DBA from sysadmin & grant "alter any user" instead.
>Afterwards, I'd
>get into PeopleTools & turn off the "creat user" function. I could write
>more,
>but this should keep you VERY busy.
>
>Dick Goulet
>
>"Koivu; Lisa" <[EMAIL PROTECTED]> wrote on 1/28/03 1:08 pm:
>
>Hello everyone,
>
>My apologies for bombarding the list with more questions than answers as of
>late. This one is pretty desparate.
>
>For those of you who support Peoplesoft databases - Are there any websites
>that
>you frequent to check for quirks, weird anomalies, etc. related to your
>specific
>Peoplesoft/Oracle combination?
>
>I've just inherited this environment. It's Oracle 8.1.7 on AIX. I don't
>know
>the version of AIX and I don't know the version(s) of the Peoplesoft
>modules
>being used. However I am seeing some very weird things - not the least of
>which
>is every tablespace is dictionary managed, all are on autoextend, there are
>600
>open database accounts but it looks like the app only connects as SYSADM,
>all
>users have SYSTEM for default/temp ts, the entire freaking database is on
>one
>disk, all redologs (one in each group) are on one disk, etc., etc., etc.
>The
>list goes on. I'm afraid to turn this environment upside down and fix it
>because management is only interested in keeping the system alive, not
>improving
>it. The previous DBA is elusive at best, probably because I yelled at him
>last
>year after catching him creating objects on the fly in my databases :)
>
>I'll do the obvious things like add redologs and check backup and recovery,
>etc.
> User error is extraordinarily high in this environment. Everything else
>I am
>afraid to touch without doing my homework first. Any websites,
>suggestions,
>comments, etc. would be greatly appreciated.
>
>The only good thing to come of all this is I'M BAAAAAAAAAAAAAACK in Unix
>again
>!!! Woo Hoo!!!!!!!
>
>Lisa Koivu
>Oracle Database Administrator
>Fairfield Resorts, Inc.
>5259 Coconut Creek Parkway
>Ft. Lauderdale, FL, USA 33063
>
>
>
><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML>
><HEAD>
><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
>
>
>
>
><META NAME="Generator" CONTENT="MS Exchange Server version 6.0.6249.1">
><TITLE>Peoplesoft & Oracle</TITLE>
></HEAD>
><BODY>
><!-- Converted from text/rtf format -->
>
><P><FONT SIZE=2 FACE="Arial">Hello everyone, </FONT>
></P>
>
><P><FONT SIZE=2 FACE="Arial">My apologies for bombarding the list with more
>questions than answers as of late. This one is pretty desparate.
></FONT>
></P>
>
><P><FONT SIZE=2 FACE="Arial">For those of you who support Peoplesoft
>databases -
>Are there any websites that you frequent to check for quirks, weird
>anomalies,
>etc. related to your specific Peoplesoft/Oracle combination?
></FONT></P>
>
><P><FONT SIZE=2 FACE="Arial">I've just inherited this environment.
>It's
>Oracle 8.1.7 on AIX. I don't know the version of AIX and I don't know
>the
>version(s) of the Peoplesoft modules being used. However I am seeing
>some
>very weird things - not the least of which is every tablespace is
>dictionary
>managed, all are on autoextend, there are 600 open database accounts but it
>looks like the app only connects as SYSADM, all users have SYSTEM for
>default/temp ts, the entire freaking database is on one disk, all redologs
>(one
>in each group) are on one disk, etc., etc., etc. The list goes
>on.
>I'm afraid to turn this environment upside down and fix it because
>management is
>only interested in keeping the system alive, not improving it. The
>previous DBA is elusive at best, probably because I yelled at him last year
>after catching him creating objects on the fly in my databases
>:)</FONT></P>
>
><P><FONT SIZE=2 FACE="Arial">I'll do the obvious things like add redologs
>and
>check backup and recovery, etc. User error is extraordinarily high in
>this
>environment. Everything else I am afraid to touch without doing my
>homework first. Any websites, suggestions, comments, etc. would be
>greatly
>appreciated. </FONT></P>
>
><P><FONT SIZE=2 FACE="Arial">The only good thing to come of all this is I'M
>BAAAAAAAAAAAAAACK in Unix again !!! Woo Hoo!!!!!!!</FONT>
></P>
>
><P><FONT SIZE=2 FACE="Tahoma">Lisa Koivu</FONT>
>
><BR><FONT SIZE=2
>
>*************************
>This message has been truncated. The entire message is available on your
>desktop
>e-mail client.
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author:
> INET: [EMAIL PROTECTED]
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
_________________________________________________________________
MSN 8 with e-mail virus protection service: 2 months FREE*
http://join.msn.com/?page=features/virus
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: david davis
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
_________________________________________________________________
MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*. http://join.msn.com/?page=features/virus
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: david davis
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).