PeopleSoft SQR Development Question -Reply

[Jim Hardesty <jhardest@LMBERRY.COM>]

Mike,

I'm sure you will get many responses advocating strong control of the databases.
And there are many good reasons to have strong control.

But let me give another side.

We are a small PeopleSoft HRMS shop that has been live with PeopleSoft for
almost 3 years and we have been through 3 release upgrades.  We have only two
PeopleSoft developers and PeopleSoft shares one DBA with Oracle Financials, a
completely custom Sales pay system and a completely custom A/R system.
That is one DBA with about 15-20 databases on 4 machines.  So you can
imagine how thinly our DBA resource is stretched.

Both of the PeopleSoft developers know the sysadm password in all of the test
and production databases.  The developers build, drop and alter tables and
indexes in both test and production.  The developers run sqr, sql, etc as sysadm.

Obviously, sqr has the access and ability to dynamically create and drop tables.
But we don't do it. Only because PeopleSoft doesn't.  We tend to model our SQR
after PoepleSoft's and they always use an existing temporary table.  One thing I
would change would be to truncate the table at the end of the sqr instead of
leaving a bunch of temporary data in a table.  Maybe it doesn't matter, but it
seems messy.

The reason we allow developers to have the sysadm password is pure practicality.
 The DBA simply doesn't have the time or expertise to add any value to the
process.  We can show the DBA our alter scripts and he will hem and haw and
whine about the inefficiency.  But when it comes right down to it, the things he
wants to change are the very things that PeopleSoft does on purpose.  The DBA
has a tendency to do much more harm than good when he looks at the
PeopleSoft scripts.

Are there risks?  Of course.  The developers can make mistakes.  Production
tables can be dropped.  Does it happen? Yes.  We once accidently dropped the
production security tables.  That's a bad thing, things got pretty exciting that day.
But guess what, DBAs make mistakes too.  I can guarantee that if I gave a script
to the DBA that dropped the production security tables, and told him to run it, he
would.  Because he doesn't understand our tables and he would have no clue.
Where is the control and what value is added?

And that is what tends to happen when you control access.  The people who are
responsible for the changes don't have the access to make them.   The people
with the access don't have the same intimate understanding of what they are
changing and aren't responsible for the changes.   You end up with expertise and
responsibility in one group, access and control in another.  What value is added if
I stand over someone's shoulder and tell them "ok, go to sqlplus and run
psalter.sql" simply because they have access and I don't?

Honestly, we should probably have more control, primarily in production.  But if we
did, less work would get done and more mistakes would be made.  The changes
need to be made.  The big question you have to answer is who is best able to
make the changes, not whose job description should give them control of the
ability to make changes.

On the question of synonyms:  You've hit the nail on the head.  How do
developers access the tables if they aren't sysadm?  You can't force them to code
the schema name.  You might have different schema names in different
databases. Also, sqr should be written platform independent, let alone schema
independent.  You could create public synonyms for all of the tables.  But then
the developers need access to the tables, so you grant access to the tables.
What kind of access?  Well, they need read.  Ooops, they need update.  Ooops,
they need delete.  On which tables?  PS_%.  Ooops, they need some system
PS% (no underscore).  Ooops, they need more.  Shoot.  They need everything, or
just about.  So you create scripts that grant this access and create these
synonyms.  And these scripts need to be run every time tables are altered.  And
you probably created a role with this access and granted the role to the
developers.  So, when all is said and done, the developers can now select, update
and delete from every table owned by sysadm.  That is a lot of work, virtually for
nothing.  Ok, so they developers can't create or drop tables.  Big deal.  Once
someone can write "truncate ps_job;" then you should probably trust them a little.

Note: all of this only makes sense in a small shop with limited, trusted resources.
 You wouldn't be able to do this if you had 20 developers and 3 DBA's. I'm not
sure what you'd do, but it would take more resources to control than we have
total.

Just my two cents, I hope everyone isn't adding my name to the list of resume's
to be thrown away immediately upon receipt...

jim


>>> Mike Fulce <m.fulce@USM.EDU> 10/02/98 04:32pm >>>
Maybe this is more for DBA's (specifically ORACLE) than developers.
(However, I'm not looking for philosophy, just practicality)

I'd just like to hear how some of your shops manage the SQR development
environment with PeopleSoft, ie:

Do you allow developer's to run SQR's outside of PeopleSoft as user sysadm?

If not, then how do you handle table reference coding in SQR's? Do you
create synonymns for all of the PS tables or do you force the developers to
code the schema name?

What privileges do you give your developer's in the development database?
Full or limited? If limited, please specify.

Do you allow custom SQR's to build/drop temporary tables dynamically or do
they use truncate on existing tables that the DBA created.

I would like to have a loose development environment by giving SQR
developers privileges to be able to connect to the development database
outside of PeopleSoft to do queries or to run SQR's directly and to be able
to do some DDL. However, since we are new to PeopleSoft and Oracle/SQR, our
developer's are inexperienced. So I really don't want them connecting with
the privileges of user sysadm outside of PeopleSoft.

I'd appreciate any responses.
______________________________________________________________________________
Mike Fulce                    __   Oracle Database Administrator I
OTR-Admin. Resources      ___/ /__ University of Southern Mississippi
m.fulce@usm.edu          /__  ___/ Voice:(601) 266-6102 Fax:(601) 266-6881
207 Forrest County Hall    / /     Box 5171, Hattiesburg, MS 39406
                          / /      http://ocean.otr.usm.edu/~rmfulce
                          ^^