Database-wide privileges grant the user DBA, RESOURCE, AUDIT_ADMIN, AUDIT_ARCHIVE, or AUDIT_INSERT privileges. Users with DBA privileges have the ability to access, modify, or delete a database object and to grant privileges to other users. RESOURCE privileges allow a user to create database objects. Users with AUDIT_ADMIN privileges can read the data from audit tables in audit-enabled databases. Users with AUDIT_ARCHIVE privileges can read, archive, and delete audit data. Users with AUDIT_INSERT privileges can insert application audit events into audit tables. For general information about implementing auditing, see Learn about Security and Auditing.

Syntax

The GRANT statement syntax for granting RESOURCE or DBA privileges is:

GRANT { RESOURCE, DBA, AUDIT_ADMIN, AUDIT_ARCHIVE, AUDIT_INSERT } 
    TO user_identifier[ , user_identifier] , ... 
    [ WITH GRANT OPTION ];
user_identifier

Identifies a username. For a tenant user, you must mention the fully qualified user name, username@domain_name, to grant access to a user.

username | username@domain_name
Note: By employing the WITH GRANT OPTION clause, you enable a user to grant the same privilege he or she has been granted to others. This clause should be used carefully due to its ability to affect database security. Example

Examples: GRANT RESOURCE statement

The following example demonstrates the use of the GRANT RESOURCE statement. 

GRANT RESOURCE TO 'GSP';

In this case, GSP is granted the privilege to issue CREATE statements, and can therefore add objects, such as tables, to the database.

The following example demonstrates how a DBA grants DELETE and SELECT privileges to the user dbuser1 associated with domuser1 of a multi-tenant table:

GRANT DELETE ON cust_view TO dbuser1@domuser1 ;
GRANT SELECT ON newcustomers TO dbuser2@domuser1 ;

The user dbuser1, dbuser2 must be associated with the domain name domuser1 in the multi-tenant table.

OpenEdge SQL compliance with the audit authorization model

As of OpenEdge 12.1, OpenEdge SQL is fully compliant to correctly support the OpenEdge audit authorization model.

If you are using OpenEdge SQL to perform audit authorization, consider adapting its usage to work with the enhanced SQL behavior.

Here's an outline of the OpenEdge SQL enhancements:

  • When an Audit Admin is created, any SQL DBA loses the ability to Grant or Revoke audit roles.
  • An Audit Admin has the inherent ability to Grant and Revoke all audit roles.
  • If exactly one Audit Admin exists, then a SQL DBA can revoke the Audit Admin role.
  • In the context of audit authorization, if no Audit Admin exists, the SQL DBAs continue to function as in the earlier OpenEdge releases.

SQL change details for audit authorization

The following changes have been made to OpenEdge SQL to comply with the audit authorization model.

  • If an Audit Admin exists, any SQL DBA loses the ability to GRANT, or REVOKE, AUDIT_ADMIN , or AUDIT_ARCHIVE, or AUDIT_INSERT, or any other audit role to any user.
    • The SQL DBA no longer has implicitly granted Audit roles and the associated capabilities.
      Note: An Audit Admin user may, however, grant these audit roles explicitly back to the DBA, assuming that such an action conforms to the user's company policy.
  • A user who has the Audit Admin role is automatically able to GRANT, and REVOKE, AUDIT_ADMIN , or AUDIT_ARCHIVE, or AUDIT_INSERT, or any other audit role to any user.
    • An Audit Admin user no longer needs a SQL DBA to assign the privileges to perform GRANT and REVOKE.
    • In earlier versions of the OpenEdge, an Audit Admin user had no privileges except those explicitly granted by a SQL DBA. For instance, granting an AUDIT_ADMIN role to user U1 would not enable U1 to grant AUDIT_ARCHIVE role to a user.
  • A SQL DBA has the ability to REVOKE the Audit Admin role from a user UAA who has this role, if and only if the user UAA is the only Audit Admin. In doing so, the SQL DBA implicitly regains the Audit Admin and Audit Read roles.

Audit authorization - adaptations for OpenEdge SQL behavior

If you are using OpenEdge SQL for audit authorization management, consider the following adaptations to resolve any problems caused by these changes:

  • If the SQL users never create an Audit Admin role, no adaptation is needed.
  • If an Audit Admin is created, then use these adaptations:
    • Use a SQL DBA only to create the first Audit Admin.
    • For any other audit authorization SQL work, which was previously done by a SQL DBA, transfer that work to be done under the userid of a user who has been granted the Audit Admin role (that is, transfer the audit authorization work to the Audit Admin).