ORDS, Database Vault and ORA-20031

Picture this…you’ve been working happily with ORDS for ages and realise that it’s time to upgrade but in your upgrade test environment you start seeing issues with ORDS not being able to create connections at runtime and nothing works!

No matter what you try to do, it’s ORA-20031: Management of Schema enablement has been restricted to ORDS_ADMINISTRATOR_ROLE privilege error messages everywhere!

It is not immediately obvious but the root cause is likely to be the Oracle Database Vault feature that has been doing such a good job in protecting data in the database. Database Vault is a security feature designed to provide additional layers of protection for sensitive data, and can enhance the security of data-driven applications. The sort of data-driven applications that one would use with ORDS. Users can encounter this hurdle while working with this combination of ORDS and Database Vault when upgrading ORDS. This happens especially when ORDS was initially installed first and then Database Vault enabled afterwards.

SQL> BEGIN

 ORDS.ENABLE_SCHEMA(p_enabled => TRUE,
    p_schema => ‘AVSYS’,
    p_url_mapping_type => ‘BASE_PATH’,
    p_url_mapping_pattern => ‘auditvault’,
    p_auto_rest_auth => FALSE);

 commit;
end;
/ 2  3  4  5  6  7  8  9  10  11
’BEGIN
*
ERROR at line 1:
ORA-20031: Management of Schema enablement has been restricted to
ORDS_ADMINISTRATOR_ROLE privilege.
ORA-06512: at “ORDS_METADATA.ORDS”, line 183
ORA-06512: at “ORDS_METADATA.ORDS_INTERNAL”, line 1048
ORA-01031: insufficient privileges
ORA-06512: at “ORDS_METADATA.ORDS_INTERNAL”, line 456
ORA-06512: at “ORDS_METADATA.ORDS_INTERNAL”, line 468
ORA-06512: at “ORDS_METADATA.ORDS_INTERNAL”, line 468
ORA-06512: at “ORDS_METADATA.ORDS_INTERNAL”, line 1034
ORA-06512: at “ORDS_METADATA.ORDS_INTERNAL”, line 922
ORA-06512: at “ORDS_METADATA.ORDS_INTERNAL”, line 1063
ORA-06512: at “ORDS_METADATA.ORDS”, line 167
ORA-06512: at line 3

When you see this error it’s because the database user is protected by Database Vault. The ORDS documentation does have a section about Authorizing Oracle REST Data Services to Access Protected Users but it does not specifically call out the error message you get when you don’t perform this additional authorisation step using DBMS_MACADM.AUTHORIZE_PROXY_USER

DBMS_MACADM.AUTHORIZE_PROXY_USER(
  proxy_user   IN VARCHAR2,
  user_name    IN VARCHAR2);

Conclusion

The upshot is that for every database user that you REST Enable, when using Oracle Database Vault, there’s an extra step:

BEGIN
  ORDS_ADMIN.ENABLE_SCHEMA(
    p_schema => 'tickets'
  );
  DBMS_MACADM.AUTHORIZE_PROXY_USER('ORDS_PUBLIC_USER','TICKETS');
END;
/

Note: In the majority of cases ORDS_PUBLIC_USER is the configured ORDS Runtime User specified by the db.username configuration setting.