The system catalog generally contains a table that identifies the users who are authorized to access the database. The DBMS may use this system table to validate the user name and password when a user first attempts to connect to the database. The table may also store other data about the user.
SQL Server stores user information in its SYSUSERS system table, shown in Table 16-10. Each row of this table describes a single user or user group in the SQL Server security scheme. Informix takes a similar approach, with a system table that is also called SYSUSERS. The corresponding Oracle table is called ALL_USERS. Following are two equivalent queries that list the authorized users for SQL Server and Oracle:
List all the user-ids known to SQL Server.
SELECT NAME
FROM SYSUSERS
WHERE UID <> GID
List all the user-ids known to Oracle.
SELECT USERNAME
FROM ALL_USERS
The DB2 system catalog table that contains user names also contains the information about their roles and privileges within the database (that is, whether they are a database administrator, whether they can create tables, whether they can create programs that access the database). Here is the equivalent query to the preceding queries for retrieving user names from the DB2 catalog:
List all the user-ids known to DB2.
SELECT DISTINCT GRANTEE
FROM SYSCAT.DBAUTH
WHERE GRANTEETYPE = ‘U’
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.