SQL Naming convention

["Graap, Ken" <keg@xxxxxxxxxxxxx>]

I had asked this group for some information explaining why a new object
created via SQL was owned by the user profile that just happened to have the
same name as the library it was created in. Since that posting I have come
across a great explanation from IBM on this exact subject. I though I would
share it with everyone:

Document Title:SQL versus SYS Naming Affects Authorities for New SQL
             Objects

Document Description:

This document explains how object authorities are linked to the naming
convention used with DB2/400 SQL and Client Access ODBC and points out
differences you may see when using *SQL versus *SYS naming.  Since many
ODBC applications require *SQL naming, the differences may become evident
when using ODBC.  DB2/400 SQL and the Client Access ODBC driver allow you
to specify the naming convention used for the job, either system (*SYS) or
SQL (*SQL).

System Naming

If system naming is used, standard OS/400 security is used to determine
object ownership and authorities to the object.  With system naming, the
owner of the object becomes either the user who created the object or the
group profile, if the member user profile has specified that the group
should be the owner of the objects.  This is specified on the OWNER
parameter of the user profile.  The owner of an object is automatically
given all object and data authorities to the object.  If the user owning
the object is a member of a group profile, the group's authority to the
object is determined by the GRPAUT value on the user profile.  If the group
owns the object, the user creating the object is not given any specific
authority to the object.

The public authority on the object (*PUBLIC) is acquired from the authority
of the library into which the object is created.  This is determined by the
CRTAUT parameter on the library.  The default value of CRTAUT is *SYSVAL
which points to the system value QCRTAUT.  See the SQL Reference, available
in the iSeries Information Center, for further information.

SQL Naming

If SQL naming is used, the following differences should be noted.

First, under Create Table in the DB2 for OS/400 SQL Reference, SC41-4611-0,
it states that "the qualifier (library name) is the owner of the table if a
user profile with that name exists.  Otherwise, the owner of the table is
the user or group profile of the job invoking the statement".  This means
that if user JOHN creates a file in a library called MARY and user ID MARY
exists on the system, MARY becomes the owner of the table.  If user profile
MARY does not exist, standard OS/400 security is followed, and the owner
becomes the user or group associated with the job.  In this case, it is
JOHN.

The SQL reference also states that when *SQL naming is used, public
authority on the object (*PUBLIC) acquires *EXCLUDE authority.  This
implies that any value specified on the library's CRTAUT property,
including use of an authorization list, is ignored.
Group authorities to the new object are the same for both naming
conventions:

"If the owner of the table is a member of a group profile (GRPPRF keyword)
and group authority is specified (GRPAUT keyword), that group profile will
also have authority to the table."

Kenneth

****************************************
Kenneth E. Graap
IBM Certified Specialist
AS/400e Professional System Administrator
NW Natural (Gas Services)
keg@nwnatural.com
Phone: 503-226-4211 x5537
FAX:    603-849-0591
****************************************