ASA Database Administration Guide
Managing User IDs and Permissions
The database system tables and system views stores information about the current users of a database and about their permissions.
For more information about each of these tables, see System Tables.
The special user ID SYS owns the system tables. You cannot 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 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, and DBA.
The following table summarizes the system tables containing information about user IDs, groups, and permissions. The user ID SYS owns all tables and views, 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 |
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 |
| SYSUSERPERMS | 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, there are tables and views that contain information about each object in the database.