In an OpenEdge database, there are separate security models for ABL-client users, who can only see data in the PUB schema, and SQL-client users, who can see PUB and other schemas, e.g. sysprogress. Read this article for an overview:
Basic Guide to Defining Progress SQL-92 Database Permissions & Security
A user with the DBA role has all permissions on all tables, including inheriting all permissions on all newly-added tables. These DBA permissions are stored in sysprogress.sysdbauth. To see them:
sqlexp -db dbname -H hostname -S SQLbrokerport -user username -pass password
SQLExplorer>select * from sysprogress.sysdbauth;
Non-DBA users only have table-level permissions that are explicitly granted to them by a user with permission to do so. As the security model is default-deny, those users inherit no permissions on new tables. When tables are added, a DBA needs to give them the desired permissions. These table-level user permissions are stored in sysprogress.systabauth.
If you use SQL, I recommend that you use the sysprogress user to create a SQL-only DBA user and then delete sysprogress. Then use that DBA user to grant new permissions as needed.
I do the following after schema changes that add tables, to grant SELECT permission to a BI user so it can read data in application tables. Not pretty, but it works.
Code:
/* GrantSelect.p
* This program generates a SQL script in the current directory
* for the current database.
*
* This script can then be run from SQL Explorer to grant
* table select privileges to a user; it prompts for the user name.
*
* SQL explorer must be run with the credentials of an existing user
* that has the privileges required to grant table SELECT privileges
* to another user.
*
* To run the output of this program:
* sqlexp -db <dbname> -S <SQL broker port>
* -infile grantselect.sql
* -outfile grantselect.out
* -user <username> -password <userpassword>
*
* Rob Fitzpatrick
* 08/21/2013
*/
define variable v-username as character no-undo.
define variable v-dbname as character no-undo format "x(11)".
define variable v-filename as character no-undo format "x(26)".
define frame a with no-labels no-box.
define frame b with no-labels no-box.
assign
v-dbname = ldbname( "dictdb" )
v-filename = "grantselect_" + v-dbname + ".sql".
display "Name of the DB user to receive SELECT " skip
"privilege on " + v-dbname + " tables: " format "x(37)"
with frame a.
update v-username
format "x(20)"
with frame a.
output to value( v-filename ).
for each _file no-lock where _tbl-type = "T":
put unformatted "grant select on pub." + quoter( _file._file-name ) + " to " + v-username + ";" skip.
end.
put unformatted "commit work;" skip.
output close.
Is the "Administrator" "pseudo-user" an old legacy feature from older versions of Progress, that has somehow survived in the old database files?
From the linked article:
"The user who creates the database becomes the default DBA. The default DBA can be used to set additional database Administrator accounts."
If the Administrator account in Windows was used to create the existing DB, that would explain why it is also a SQL DBA.