ASA SQL Reference
System Tables
| Column name | Column type | Column constraint | Table constraints |
|---|---|---|---|
| user_id | UNSIGNED INT | NOT NULL | Primary key |
| user_name | CHAR(128) | NOT NULL | Unique index |
| password | BINARY(36) | ||
| resourceauth | CHAR(1) | NOT NULL | |
| dbaauth | CHAR(1) | NOT NULL | |
| scheduleauth | CHAR(1) | NOT NULL | |
| publishauth | CHAR(1) | NOT NULL | |
| remotedbaauth | CHAR(1) | NOT NULL | |
| user_group | CHAR(1) | NOT NULL | |
| remarks | LONG VARCHAR |
DBA permissions requiredSYSUSERPERM contains passwords, so DBA permissions are required to SELECT from it. |
Each row of SYSUSERPERM describes one user ID.
user_id Each new user ID is assigned a unique number (the user number), which is the primary key for SYSUSERPERM.
user_name A string containing a unique name for the user ID.
password The password for the user ID. The password contains the NULL value for the special user IDs SYS and PUBLIC. This prevents anyone from connecting to these user IDs.
resourceauth (Y/N) Indicates whether the user has RESOURCE authority. Resource authority is required to create tables.
dbaauth (Y/N) Indicates whether the user has DBA (database administrator) authority. DBA authority is very powerful, and should be restricted to as few user IDs as possible for security purposes.
scheduleauth (Y/N) Indicates whether the user has SCHEDULE authority. This is currently not used.
publishauth (Y/N) Indicates whether the user has the SQL Remote publisher authority.
remotedbaauth (Y/N) Indicates whether the user has the SQL Remote remote DBA authority.
user_group (Y/N) Indicates whether the user is a group.
remarks A comment string.
When a database is initialized, the following user IDs are created:
SYS The creator of all the system tables.
PUBLIC A special user ID used to record PUBLIC permissions.
DBA The database administrator user ID is the only usable user ID in an initialized system. The initial password is SQL.
There is no way to connect to the SYS or PUBLIC user IDs.