Feedback
Did this article resolve your question/issue?

   

Article

SQL Multi-Tenant User Needs RESOURCE Privileges

Information

 
TitleSQL Multi-Tenant User Needs RESOURCE Privileges
URL Namesql-multi-tenant-user-needs-resource-privileges
Article Number000138443
EnvironmentProduct: OpenEdge
Version: 10.x, 11.x
OS: All supported platforms
Other: SQL-92, JDBC, ODBC
Question/Problem Description
When logged in as a regular tenant user and trying to create a temp-table via SQL-92 in a multi-tenant database, the following error is displayed:

DataException: Unknown errorCode 100
[DataDirect][OpenEdge JDBC Driver][OpenEdge] No RESOURCE privileges (7574)


When trying to grant RESOURCE to the regular tenant user, for example with this statement:

grant resource to user@domain;

then the following error is displayed:

=== SQL Exception 1 ===
SQLState=HY000
ErrorCode=-20368
[DataDirect][OpenEdge JDBC Driver][OpenEdge] Cannot GRANT DBA privileges or RESOURCE privileges to ordinary tenant user. (16112)


If logged in as a non-tenant user, then grant resource statement is successful, so the user can create temp-tables. However afterward, drop table statement fails with the following error:

Failure to acquire exclusive schema lock for DDL operation. (7872)
Steps to Reproduce
Clarifying Information
The full details of the Message (16112) read as follows:

DBA privileges and RESOURCE privileges can only be granted to users who either have no tenancy, or who are bound to default tenancy, or who are super-tenants. The term "bound to default tenancy" means a user who is defined in a domain which is associated with the tenant named "default".
 
Error MessageDataException: Unknown errorCode 100
[DataDirect][OpenEdge JDBC Driver][OpenEdge] No RESOURCE privileges (7574)
Cannot GRANT DBA privileges or RESOURCE privileges to ordinary tenant user. (16112)
Defect/Enhancement Number
Cause
This is expected behavior. As per Progress design, regular tenants are not supposed to have resource privileges (to create tables) and resource privileges are limited to only DBA or super-tenant users.

OpenEdge SQL does not support the notion of temp-tables, so once a table is created, it will be treated as a regular table, irrespective of whether it is used for a temporary purpose or not. For this reason, the locking error (7872) is displayed.
Resolution
This enhancement has not been implemented in the product.  As an alternative, please use the workaround listed in the next section below.

An enhancement can be submitted to Progress with a request for temp-table support in OpenEdge SQL, so that regular tenants could create temp-tables.

For more information, please see Progress Article 000010839: How to submit an idea for a Progress product enhancement
Workaround
Have tables created in a non-pub schema by a super-tenant or DBA, so that those tables could be dropped while other users are connected to the database and this way avoid the locking error 7872.

A pool of temp-tables could be created, for each tenant or for all tenants, which would be consumed by regular tenants and then those tables could be dropped from the pool when they aren't needed anymore.
Notes
Last Modified Date12/14/2018 11:56 PM
Attachment 
Files
Disclaimer The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.