Home

How to create an implicit SQL user that does not need a password

How to create an implicit SQL user that does not need a password
P125338
000144257
Product: Progress
Version: 9.x
Product: OpenEdge
Version: All supported versions
OS: all supported platforms
Other: SQL
How to create an implicit SQL user in the database, which will not need a password.
How to implement SQL-92 user access while still being able to see user names in Progress Monitor (promon)?
Can I avoid the need to create individual SQL users for each of the 4GL users?
Is it possible to add a SQL user to the database without demanding login for other users?
The OpenEdge database allows ABL users to connect without any authentication as long as there are no records in the _user table at all. Once a single record has been added to the _user table, database security has been enabled; every user must have a a record in the _user table and log in with that user id in order to connect to the database.
 
OpenEdge SQL requires all users to connect with a user id. However, since the _user table is used to store records for both ABL and SQL users, adding even a few SQL users makes it necessary to add _user records for all ABL users as well. Once the records exist in the _user table, the database enforces login for all ABL users. This can be burdensome if there are many ABL users who are not SQL users, or if the ABL users are used to being able to access the database without logging in.
The need to create records in the _user table for SQL users (and therefore for ABL users as well) can be avoided in the following ways:
  • The database creator will always have DBA permissions, so this user can be used for SQL connections without needing to add to the _user table. Any password can be used when connecting as the database creator in this way; the password is not checked. See article "Basic Guide to Defining Progress SQL-92 Database Permissions & Security".
  • An implicit SQL user can be created in the database, which does not need a password. To create a SQL user implicitly, assign permissions to a user that does not exist in the _user table. For example:
GRANT DBA TO user1
  
The above essentially creates a new SQL user "user1" without creating a record in _user.  The database can now be accessed by user1 in the same way as the database creator account, with the "user1" user name and any password.
 
Implicit users can be granted restricted access by granting or revoking specific privileges to specific tables, rather than full DBA rights. For example:
 
GRANT SELECT ON pub.Customer to user2;
 
user2 can connect to the database but can only query the Customer table. 
 
The above techniques will only work if there are no users defined in the database security (no _user records).

References to Other Documentation:

Progress Articles:

"Basic Guide to Defining Progress SQL-92 Database Permissions & Security"
12/29/2020 8:10 PM