AuthID Ownership and Usage Rules

Restriction: This topic applies to Windows environments only.

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

Primary AuthID. This is the primary identifier for all users of an XDB Server. A Primary AuthID can be assigned to a process or program that requires access to XDB Server data.
- Used for logging in to an XDB Server.
- Appears as an identifier on server screens that are used to monitor activity of users.
- Used as the default for the AuthID portion of a database object's three-part name when the user creates the object. A different ID will become the default if the SET CURRENT SQLID command is issued, if a SecondaryID is defined, or if a security user exit modifies the Current SQLID (applies to Windows environments only). See the Security User Exit topic for information.
- Used to determine privileges when user attempts to access a database object. If a different ID is in effect (from issuance of a SET CURRENT SQLID command), that ID's privileges are available also. If the Primary AuthID belongs to a group, the privileges granted to the GroupID also are available to the user of the Primary AuthID. If a SecondaryID is defined, the Primary AuthID's privileges are not valid, but rather the SecondaryID's privileges take effect (along with its groups, if any).
Group or GroupID. This is a special identifier that can be assigned to one or more Primary AuthIDs. The privileges granted to the GroupID are available to every Primary AuthID to which the GroupID is assigned. In addition, each Primary AuthID can have multiple GroupIDs assigned to it, thus gaining the privileges of all of those groups combined. Other privileges can be granted to the Primary AuthID, providing the user with the assigned group privileges plus the privileges assigned specifically to the 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.
SecondaryID (optional). If defined, this AuthID replaces the Primary AuthID as soon as the user logs in to the XDB Server. The user (or process) is given the privileges that have been granted to the SecondaryID. If the Primary AuthID had privileges granted to it, those privileges would be ignored. When a user with a SecondaryID creates an object, the AuthID portion of the object's name is the SecondaryID. All other users who use the same SecondaryID will have all privileges on that object.
Note: The XDB Server SecondaryID is not the same as the DB2 Secondary Authorization ID. The IBM method of defining multiple Secondary Authorization IDs and assigning them to Primary AuthIDs in RACF (Resource Access Control Facility) is similar to Micro Focus' group concept.

Note: If you use an object creator's AuthID as a SecondaryID for other users, those other users will all have all privileges on the object. A user with a SecondaryID is seen by the system as being the SecondaryID, and therefore has all privileges on all objects that carry that AuthID as the second part of the object name. Privileges cannot be revoked from the creator of an object, or from a user who's Current SQLID is the same as the object creator's AuthID. The GRANT and REVOKE commands have no effect on the privileges of an object's creator/owner.
Current SQLID. The Current SQLID is a special register that keeps track of which AuthID is in effect for a given client session. A user with SYSADM or super user authority can issue the SET CURRENT SQLID command to change the SQLID to any valid AuthID. If a user has a SecondaryID, the Current SQLID changes from the Primary AuthID to the SecondaryID at the start of the client session.

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

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.
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:

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.
If a GRANT command gives privileges to a GroupID, all users who are members of that group will have those privileges.
If a user belongs to multiple-groups, the user has all privileges that have been granted to each group.
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.
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.
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.)