Effect of AuthIDs on Object Names and Usage

Restriction: This topic applies to Windows environments only.

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

Primary AuthID	Secondary ID	Group Name	Creates Table...¹	Owner	Authorized Users
TECHPUBS	--	--	Created as a Primary AuthID. Privileges are assigned. Used as a SecondaryID for one or more Primary AuthIDs.
--	--	WRITER	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, also ARLENE.
ARLENE	--	WRITERS	ARLENE.TABLE1	ARLENE	ARLENE and, if access is granted to WRITERS, also DAVID.
ROCHELLE	TECHPUBS	--	TECHPUBS.TABLE1	TECHPUBS	Users with TECHPUBS as SecondaryID (ROCHELLE, STEVE, PABLO, BRUCE) and, if access is granted to WRITERS, also DAVID, ARLENE.
STEVE	TECHPUBS	--	TECHPUBS.TABLE2	TECHPUBS	Users with TECHPUBS as SecondaryID (ROCHELLE, STEVE, PABLO, BRUCE) and, if access is granted to WRITERS, also DAVID, ARLENE.
PABLO	TECHPUBS	WRITERS (ignored)²	TECHPUBS.TABLE3	TECHPUBS	Users with TECHPUBS as SecondaryID (ROCHELLE, STEVE, PABLO, BRUCE)
BRUCE	TECHPUBS	WRITERS (ignored)²	TECHPUBS.TABLE4	TECHPUBS	Users with TECHPUBS as SecondaryID (ROCHELLE, STEVE, PABLO, BRUCE)

¹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 table above, 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, 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.
- 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 privileges that have been granted to all the groups.
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.)