SQL Trigger issue
Hal
I have a file PEIS301 in EMSFILES on our system, it is the main
employee file. Over the weekend I created an update SQL trigger that
runs against this file, any time some select fields in this file are
updated, the trigger writes the updated values into another table for
our badge software. That way if someone changes their name in HR, the
badge computer updates.
It worked great for me. However, everyone else is having trouble now,
specifically, all of the folks in HR who regularly use a program that
has been in place for years to update employee information. They can't
even get in to the program, what happens is they try to run it and it
bombs out with a CPF4236. Looking at the job log, it says "Not
authorized to open member GPS4100", "You do not have sufficient
authority to open member GPS4100 file PEIS301 in library EMSFILES".
I did a test, and gave one user *ALLOBJ special authority, and the
problem went away for that user. But the kicker is, if I do an
edtobjaut against file PEIS301 in library EMSFILES, it has *PUBLIC
*ALL authority. I don't know how to grant it anything more that would
allow the users to get in and use this object when the only thing I
changed is create a SQL trigger against this file.
Anyone have any ideas?
Chris
Hal
> authorized to open member GPS4100", "You do not have sufficient
> authority to open member GPS4100 file PEIS301 in library EMSFILES".
>
> I did a test, and gave one user *ALLOBJ special authority, and the
> problem went away for that user. But the kicker is, if I do an
> edtobjaut against file PEIS301 in library EMSFILES, it has *PUBLIC
> *ALL authority. I don't know how to grant it anything more that would
> allow the users to get in and use this object when the only thing I
> changed is create a SQL trigger against this file.
To any who might be stuck in a similar situation, I found the solution
to my problem. The database schema permission where the trigger is
stored was set to public exclude. The file that the error was
reporting against was set to public all, as was the trigger itself.
IBM sent me an interesting snippet on trigger permissions that might
be helpful, it is posted below.
Trigger authority: The trigger program object authorities are: v When
SQL naming is in effect, the trigger program will be created with the
public authority of *EXCLUDE, and adopt authority from the schema
qualifier of the trigger-name if a user profile with that name exists.
If a user profile for the schema qualifier does exist, then the owner
of the trigger program will be the user profile for the schema
qualifier. Note that the special authorities *ALLOBJ and *SECADM are
required to create the trigger program object in the schema qualifier
library if a user profile exists that has the same name as the schema
qualifier, and the name is different from the authorization ID of the
statement. If a user profile for the schema qualifier does not exist,
then the owner of the trigger program will be the user profile or
group user profile of the job executing the SQL CREATE TRIGGER
statement. The group user profile will be the owner of the trigger
program object, only if OWNER(*GRPPRF) was specified on the user's
profile who is executing the statement. If the owner of the trigger
program is a member of a group profile, and if OWNER(*GRPPRF) was
specified on the user's profile, the program will run with the adopted
authority of the group profile. v When System naming is in effect, the
trigger program will be created with public authority of *EXCLUDE, and
adopt authority from the user or group user profile of the job
executing the SQL CREATE TRIGGER statement.
Chris