Users and permissions in the system tables

Chapter 24. Managing User IDs and Permissions
Users and permissions in the system tables

Information about the current users of a database and about their permissions is stored in the database system tables and system views.

For a description of each of these tables, see "SQL Anywhere System Tables".

The system tables are owned by the special user ID SYS. It is not possible to connect to the SYS user ID.

The DBA has SELECT access to all system tables, just as to any other tables in the database. The access of other users to some of the tables is limited. For example, only the DBA has access to the SYS.SYSUSERPERM table, which contains all information about the permissions of users of the database, as well as the 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. All permissions and group memberships set up in a new database for SYS, PUBLIC, and DBA can be fully modified.

The following table summarizes the system tables containing information about user IDs, groups, and permissions. All tables and views are owned by user ID SYS, and so their qualified names are SYS.SYSUSERPERM and so on.

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

Table Default Contents

SYSUSERPERM DBA only Database-level permissions and password for each user ID

SYSGROUP PUBLIC One row for each member of each group

SYSTABLEPERM PUBLIC All permissions on table given by the GRANT command s

SYSCOLPERM PUBLIC All columns with UPDATE permission given by the GRANT command

SYSDUMMY PUBLIC Dummy table, can be used to find the current user ID

SYSPROCPERM PUBLIC Each row holds one user granted permission to use one procedure

Table	Default	Contents
SYSUSERPERM	DBA only	Database-level permissions and password for each user ID
SYSGROUP	PUBLIC	One row for each member of each group
SYSTABLEPERM	PUBLIC	All permissions on table given by the GRANT command s
SYSCOLPERM	PUBLIC	All columns with UPDATE permission given by the GRANT command
SYSDUMMY	PUBLIC	Dummy table, can be used to find the current user ID
SYSPROCPERM	PUBLIC	Each row holds one user granted permission to use one procedure

The following table summarizes the system views containing information about user IDs, groups, and permissions

Views Default Contents

SYSUSERAUTH DBA only All information in SYSUSERPERM except for user numbers

SYSYUSERPERMS PUBLIC All information in SYSUSERPERM except for passwords

SYSUSERLIST PUBLIC All information in SYSUSERAUTH except for passwords

SYSGROUPS PUBLIC Information from SYSGROUP in a more readable format

SYSTABAUTH PUBLIC Information from SYSTABLEPERM in a more readable format

SYSCOLAUTH PUBLIC Information from SYSCOLPERM in a more readable format

SYSPROCAUTH PUBLIC Information from SYSPROCPERM in a more readable format

Views	Default	Contents
SYSUSERAUTH	DBA only	All information in SYSUSERPERM except for user numbers
SYSYUSERPERMS	PUBLIC	All information in SYSUSERPERM except for passwords
SYSUSERLIST	PUBLIC	All information in SYSUSERAUTH except for passwords
SYSGROUPS	PUBLIC	Information from SYSGROUP in a more readable format
SYSTABAUTH	PUBLIC	Information from SYSTABLEPERM in a more readable format
SYSCOLAUTH	PUBLIC	Information from SYSCOLPERM in a more readable format
SYSPROCAUTH	PUBLIC	Information from SYSPROCPERM in a more readable format

In addition to these tables and views, there are tables and views containing information about each of the objects of the database that include the owner of each object.