Establish user accounts and assign privileges

Using authentication

Logon validation is a mechanism that checks user identification and password at connection time using reference data stored in the OpenEdge RDBMS.

In the OpenEdge RDBMS, the reference table storing user identifications and passwords serves both OpenEdge SQL and ABL interfaces. Use the CREATE USER statement to enable authentication from the SQL interface. Use the OpenEdge Data Administration tool to enable authentication from the ABL interface. Authentication can be disabled from either interface.

In addition, ABL and SQL both support authentication to the operating system (OS) user accounts (in Windows or UNIX, wherever the OpenEdge process runs). If no users are defined in the _User table accounts, ABL can either authenticate to the OS user accounts or connect (without authentication) using the OpenEdge default user ID; however, SQL must authenticate a user to one or the other set of user accounts in order to access a database.

The accounts used depend on the domain in which the user authenticates. A supports authentication to the OS user accounts. For more information, see _oeusertable authentication system supports authentication to the The accounts used depend on the domain in which the user authenticates. A domain configured with the _User table accounts; a domain configured with the _oslocalOpenEdge Getting Started: Identity Management.

authentication systemCreating, altering, or dropping a user via SQL is equivalent to creating, maintaining, or deleting a user with the OpenEdge Data Administration tool. The _User table accounts updated for the OpenEdge SQL interface are updated for the ABL interface and vice versa.

In an environment where an OpenEdge RDBMS is accessed by applications using both ABL and SQL, the following conditions exist:

• If no users have been created in the database — All SQL users will be required to enter a username and password before they will be permitted access to the database.
• If users have been created in the database — Users accessing the database through the ABL interface are required to provide an identification and password, unless the default blank user ID access is allowed. Furthermore, users working from the interface by default have unlimited privileges. The DBA who controls access from the interface must place limitations on user privileges or restrict access to certain database objects. SQL users, by default, have no privileges and must be explicitly assigned.

Assigning privileges

Database administrators also control access to a database by assigning user privileges. SQL DBAs use the GRANT and REVOKE statements to authorize privileges for users who are working with the OpenEdge SQL interface. Privileges granted to users of the ABL interface are maintained separately using the OpenEdge Data Administration tool.

A DBA can grant specific privileges—such as selecting, updating, or deleting records—to individual users or to all users. When working through the SQL interface, all user actions against a database are prohibited unless explicitly authorized by the DBA. When users attempt to perform an action for which they do not have privileges, the OpenEdge SQL server generates an error message.

Conversely, all users working through the ABL interface have unlimited database privileges, unless those privileges are explicitly restricted by the DBA. Database privileges are defined for the interface by using the OpenEdge Data Administration tool.

DBAs must exercise caution when deciding which privileges should be assigned and to whom. For example, if a table or view is selectable by all users, the DBA can grant the ability to select data to the public. Otherwise, the privilege to select data should be granted to those individuals who have a need to do so. The same principle applies to other privileges, such as updating records or executing stored procedures.

For more information on using SQL GRANT and REVOKE statements and controlling user privileges, see Data control language and transaction behavior. For more information on database security, see Manage OpenEdge Databases.