Loading ...
Sorry, an error occurred while loading the content.

RE: PeopleSoft DBA Forum Re: PeopleSoft database setup with different ownerid and accessid

Expand Messages
  • David Kurtz
    As mentioned earlier, Oracle Resource profiler allocates profiles to users, so you would need multiple Access IDs. You will need to grant SELECT, INSERT,
    Message 1 of 5 , Apr 24, 2008
    • 0 Attachment
      As mentioned earlier, Oracle Resource profiler allocates profiles to users,
      so you would need multiple Access IDs.

      You will need to grant SELECT, INSERT, UPDATE, DELETE on every table to the
      additional Access IDs.
      You will need to regrant the privileges when the object is dropped and
      recreated by PS alter scripts.

      Either, you will have to create synonyms for every table and view, or
      alternatively, you might be able to use an AFTER LOGON trigger on the new
      ACCESSID to alter the CURRENT_SCHEMA for those sessions to the OWNERID. I
      haven't tested which profile you access. If know that you use the temporary
      and default tablespaces for the CURRENT_SCHEMA, rather than the one as which
      you connected.

      Before you do this make sure you really need to go down this path. Most of
      the resource consumption for on-line users will be in the web and app
      servers layers rather than the database.

      regards
      _________________________
      David Kurtz
      Go-Faster Consultancy Ltd.
      tel: +44 (0)7771 760660
      fax: +44 (0)7092 348865
      mailto:david.kurtz@...
      web: www.go-faster.co.uk
      Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
      DBA Blogs: PeopleSoft: http://blog.psftdba.com, Oracle:
      http://blog.go-faster.co.uk
      PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba

      >-----Original Message-----
      >From: psftdba@... [mailto:psftdba@yahoogroups.com]
      >On Behalf Of shajivps
      >Sent: Thursday, April 24, 2008 3:09 AM
      >To: psftdba@yahoogroups.com
      >Subject: PeopleSoft DBA Forum Re: PeopleSoft database setup
      >with different ownerid and accessid
      >
      >David,
      >
      > I have the same question and you sort of answered that
      >partially. I have a requirement to separate connections
      >coming from app servers using different accessid pointing to
      >the same owner (SYSADM). This is to use oracle's resource
      >manager to limit resources for different types of users
      >(internal corporate, external
      >vendor accounts coming thru portal etc.,). I need to guarantee a
      >certain amount of cpu to each category of users. Any help on this
      >will be much appreciated.
      >
      >Regards,
      >
      >Shaji.
      >
      >--- In psftdba@yahoogroups.com, "David Kurtz" <david.kurtz@...> wrote:
      >>
      >> The owner ID is always an access ID, and App Designer must use that
      >> owner/access ID - otherwise you would be creating and dropping
      >objects in
      >> the wrong schema. You don't have to call the Owner ID sysadm. But
      >the
      >> owner ID must still be the access ID.
      >>
      >> Theoretically, you can set up additional access IDs for use by
      >specific
      >> oprids, so that you could use database resource profiling or
      >security
      >> techniques that are dependent on the database user name.
      >PeopleBooks
      >> suggests this is a possible use, but gives you no help on how to
      >implement
      >> it. However, you are then responsible for creating all the
      >necessary grants
      >> and synonyms. This is a considerable administrative overhead.
      >>
      >> I have to ask why you would want to do this? I can only imagine
      >doing this
      >> in the most extreme and contrived of circumstances, and I have
      >never done it
      >> other than the course of research.
      >>
      >> regards
      >> _________________________
      >> David Kurtz
      >> Go-Faster Consultancy Ltd.
      >> tel: +44 (0)7771 760660
      >> fax: +44 (0)7092 348865
      >> mailto:david.kurtz@...
      >> web: www.go-faster.co.uk
      >> Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
      >DBA Blogs:
      >> PeopleSoft: http://blog.psftdba.com, Oracle:
      >> http://blog.go-faster.co.uk
      >> PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
      >>
      >> >-----Original Message-----
      >> >From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On
      >> >Behalf Of Pattabhiram Marupudi
      >> >Sent: Wednesday, April 23, 2008 4:57 PM
      >> >To: psftdba@yahoogroups.com
      >> >Subject: PeopleSoft DBA Forum PeopleSoft database setup with
      >> >different ownerid and accessid
      >> >
      >> >
      >> >
      >> >Hello PSDBAs,
      >> >
      >> >Seeking assistance with the below question:
      >> >------------------------------------------
      >> >
      >> >Is it possible for a PS database to have a different ownerid and
      >> >accessid. (Currently we have both of them set to sysadm) If
      >yes, can
      >> >you please let me know the procedure how to accomplish this.
      >> >
      >> >
      >> >Thank you
      >> >
      >> >Regards,
      >> >Ram.
      >> >
      >> >
      >> >------------------------------------
      >> >
      >> >PeopleSoft for the Oracle DBA is published by Apress - see
      >> >http://www.psftdba.com.
      >> >The PeopleSoft DBA Forum is managed by http://www.go-faster.co.uk.
      >> >Yahoo! Groups Links
      >> >
      >> >
      >> >
      >>
      >
      >
      >
      >------------------------------------
      >
      >PeopleSoft for the Oracle DBA is published by Apress - see
      >http://www.psftdba.com.
      >The PeopleSoft DBA Forum is managed by http://www.go-faster.co.uk.
      >Yahoo! Groups Links
      >
      >
      >
    • Pattabhiram Marupudi
      David, The main business reason fo why we want to segragate the ownerid and ... We have PeopleSoft AR/Billing application running on DB2 database. As we have
      Message 2 of 5 , Apr 24, 2008
      • 0 Attachment
        David,

        The main business reason fo why we want to segragate the ownerid and
        accessid is as below:
        --------------------------------------------------------------------

        We have PeopleSoft AR/Billing application running on DB2 database.

        As we have the below instances with ownerid (same as acccessid)

        DEV -- devadm
        TEST -- tstadm
        PROD -- prdadm

        When ever we request for Database refresh from PROD to test, it
        takes up to 7 days as there is no easy way to change the schema of
        an object during a refresh rather than drop and recreate all of the
        tables and indexes and reload all the data.

        Unlike in Oracle is restore from backup and change the password
        in the database copy. If we can split the schema and login account,
        we might be able to do that with UDB.

        So, what I'm proposing is, if we can make the ownerid same for all
        databases - something like "udbadm" and accessids different for each
        database, Then it makes the refresh process seamless and efficient.

        Can you please let me know your thoughts / suggestions on this...

        Regards,
        Ram.
      Your message has been successfully submitted and would be delivered to recipients shortly.