Users and permissions in the catalog

The database system views contain information about the current users of a database and about their permissions.

The special user ID SYS owns the system views. You cannot connect using the SYS user ID.

Users with DBA authority have SELECT access to all system views, but not the underlying system tables. The access of other users to some tables and views is also limited. For example, only a user with DBA authority has access to the SYS.SYSUSERPERM view, which contains all information about the permissions of users of the database, and the encrypted passwords of each user ID. However, SYS.SYSUSERPERMS is a view containing all information in SYS.SYSUSERPERM except for the password, and by default all users have SELECT access to this view. You can fully modify all permissions and group memberships set up in a new database for SYS, PUBLIC, DBA, and dbo.

The following table summarizes the system views containing information about user IDs, groups, and permissions. The user ID SYS owns all the listed views, and so their qualified names are SYS.SYSUSERPERM and so on.

Appropriate SELECT queries on these views generate all the user ID and permission information stored in the database.

View	Default	Contents
SYSCOLAUTH	PUBLIC	Information from SYSCOLPERM in a more readable format. See SYSCOLAUTH consolidated view.
SYSCOLPERM	PUBLIC	All columns with SELECT or UPDATE permission given by the GRANT command. See SYSCOLPERM system view.
DUMMY	PUBLIC	Dummy table that can be used to find the current user ID. See DUMMY system table.
SYSGROUP	PUBLIC	One row for each member of each group. See SYSGROUP system view.
SYSGROUPS	PUBLIC	Information from SYSGROUP in a more readable format. See SYSGROUPS consolidated view.
SYSPROCAUTH	PUBLIC	Information from SYSPROCPERM in a more readable format. See SYSPROCAUTH consolidated view.
SYSPROCPERM	PUBLIC	Each row holds one user granted permission to use one procedure. See SYSPROCPERM system view.
SYSTABAUTH	PUBLIC	Information from SYSTABLEPERM in a more readable format. See SYSTABAUTH consolidated view.
SYSTABLEPERM	PUBLIC	All permissions on table given by the GRANT commands. See SYSTABLEPERM system view.
SYSUSER	DBA only	Information on all users in the database. See SYSUSER system view.
SYSUSERAUTH	DBA only	All information in SYSUSERPERM except for user numbers. See SYSUSERAUTH compatibility view (deprecated).
SYSUSERAUTHORITY	PUBLIC	Authority granted for each user ID. See SYSUSERAUTHORITY system view.
SYSUSERLIST	PUBLIC	All information in SYSUSERAUTH except for passwords. See SYSUSERLIST compatibility view (deprecated).
SYSUSERPERM	DBA only	Database-level permissions and password for each user ID. See SYSUSERPERM compatibility view (deprecated).
SYSUSERPERMS	PUBLIC	All information in SYSUSERPERM except for passwords. See SYSUSERPERMS compatibility view (deprecated).

Discuss this page in DocCommentXchange.
Send feedback about this page using email.