AuthID Ownership and Usage Rules

Primary AuthIDs, GroupIDs and SecondaryIDs can be used in combination when setting up system security. These IDs are defined and used as follows:

The table and the descriptions following it illustrate how various GroupID and SecondaryID assignments can affect the naming of and access to database objects.

When security is on, the Primary AuthID and Password are used when each user (or process) logs in to the XDB Server. The system then checks the SYSXDB.SYSACFUSERS table for the existence of a SecondaryID. If present, the user (or process) assumes the identity of the SecondaryID and is accorded all privileges that have been defined for the SecondaryID. If no SecondaryID exists for the user, then privileges are based on what has been granted for the Primary AuthID and the groups to which it belongs.

To grant a particular set of privileges to a group of users, without having to define those privileges for each user individually, assign multiple users to the same SecondaryID. Grant the privileges to the SecondaryID (essentially just another AuthID). However, the preferred method for assigning the same privilege set to many users is to use the XDB Group, which is similar to the DB2 concept of assigning multiple secondary authorization IDs to a primary authorization in IBM's RACF (Resource Access Control Facility).

Note: If several users have the same SecondaryID, all the objects they create will be owned by the SecondaryID and not by the individual users. All users having that same SecondaryID will have all privileges on those objects. The GRANT and REVOKE commands have no effect on privileges of an object's creator/owner, which in this case includes all users with the SecondaryID that is also the AuthID portion of the object's name. All of these users are seen by the system as being the owner of the object.
Primary AuthID Secondary ID Group Name Creates Table... (see note 1) Owner Authorized Users
TECHPUBS -- -- Created as a Primary AuthID. Privileges are assigned. Used as a SecondaryID for one or more Primary AuthIDs.
-- -- WRITERS Created as a group. Privileges are assigned to the group. GroupIDs are assigned to Primary AuthIDs.
ERIC -- -- ERIC.

TABLE1

ERIC ERIC
DEBBIE -- -- DEBBIE

TABLE1

DEBBIE DEBBIE
DAVID -- WRITERS DAVID.

TABLE1

DAVID DAVID and

if access is granted to WRITERS, then also ARLENE.

ARLENE -- WRITERS ARLENE.

TABLE1

ARLENE ARLENE and

if access is granted to WRITERS, then also DAVID.

ROCHELLE TECHPUBS -- TECHPUBS.

TABLE1

TECHPUBS Users with TECHPUBS as SecondaryID (ROCHELLE, STEVE, PABLO, BRUCE) and

if access is granted to WRITERS, then also DAVID, ARLENE.

STEVE TECHPUBS -- TECHPUBS.

TABLE2

TECHPUBS Users with TECHPUBS as SecondaryID (ROCHELLE, STEVE, PABLO, BRUCE) and

if access is granted to WRITERS, then also DAVID, ARLENE.

PABLO TECHPUBS WRITERS

(ignored, see note 2)

TECHPUBS.

TABLE3

TECHPUBS Users with TECHPUBS as SecondaryID (ROCHELLE, STEVE, PABLO, BRUCE)
BRUCE TECHPUBS WRITERS

(ignored, see note 2)

TECHPUBS.

TABLE4

TECHPUBS Users with TECHPUBS as SecondaryID (ROCHELLE, STEVE, PABLO, BRUCE)

Table Notes

  1. All scenarios described here assume that the user does not specify an AuthID when creating the object and the user has not changed his Current SQLID.
  2. Pablo and Bruce do not have privileges defined for the group writers because as soon as they log in, they both become techpubs, and techpubs is not assigned to the group writers in this example.

As shown in the above table, the AuthID portion of the name assigned to a database object when it is created, and the users who have access to that object depend on how AuthIDs, GroupIDs and SecondaryIDs are defined. The object naming and usage rules are as follows:

  1. If a user belongs to a group and creates an object, the AuthID portion of the object name will be the user's own Current SQLID, and not the GroupID. This allows more than one user to create objects of the same name within the same location.

    For example, if user david (who is in the writers group) creates a table called staff in the location called books, the table's three-part name would be books.david.staff. If user arlene (who is also assigned to the writers group) attempts to create a table called staff, in the location called books, the table's three-part name would be books.arlene.staff. Since the three-part names of the two tables are unique, each user can have a table of the same one-part name within a location.

    Note: If a user has no Secondary ID and has not otherwise changed his Current SQLID, the Current SQLID is the user's Primary AuthID.
    Note: If you specify a GroupID as the second part of an object's three-part name, all users who are assigned to that group will automatically have all privileges on the object. Privileges cannot be revoked from a user whose GroupID is the same as the object creator's AuthID. The GRANT and REVOKE commands have no effect on these privileges.
  2. If a GRANT command gives privileges to a GroupID, all users who are members of that group will have those privileges.
  3. If a user belongs to multiple-groups, the user has all privileges that have been granted to each group.
  4. If a user has a SecondaryID and creates an object, the AuthID portion of the object name will be the SecondaryID (assuming that the user has not changed his Current SQLID, which by default takes the value of the SecondaryID). Two users with the same SecondaryID cannot create objects of the same name within a location.

    For example, if user rochelle (who has a SecondaryID of techpubs) creates a table called staff in the location called books, the table's three-part name would be books.techpubs.staff. If user steve (who also has techpubs as his SecondaryID) attempts to create a table called staff, the table's three-part name would also be books.techpubs.staff. This is not allowed.

  5. Any object whose name has a SecondaryID as the AuthID qualifier can be accessed by any user who has that same SecondaryID as his Current SQLID. GRANT and REVOKE commands have no effect on these users' access to the objects because these users are all seen by the system as being the owner of the object. All users who have the SecondaryID that is the owner of the object will have all privileges on the object.

    Users rochelle, steve, pablo, and bruce all have all privileges on techpubs.table1, techpubs.table2, techpubs.table3, and techpubs.table4. GRANT and REVOKE commands for these users and tables will have no effect.

    If an object has a different owner, then granting privileges to the SecondaryID will give those privileges to all users who have that SecondaryID as their Current SQLIDs.

  6. If a user has a SecondaryID (as his Current SQLID) and belongs to a group and creates an object, the AuthID portion of the object name will be the SecondaryID. Other users in the group do not have access to the object, because privileges on the object have not been granted to the group. The object is owned by the SecondaryID. Only users with the same SecondaryID (as their Current SQLIDs) can access the object. (However, the original user who created the object of this example can access other objects to which the group has been granted privileges.)