oracle-databaseoracle-ords

ORDS ORA-01031: insufficient privileges


During installation, ORDS created a TEST user and granted rights, according to the instructions

1.3.4.1 ORDS Installer Privileges Script.

Issued the appropriate rights. But when trying to enable AUTOREST with (under user TEST):

BEGIN

     ORDS.ENABLE_OBJECT(p_enabled => TRUE,
                        p_schema => 'MAIN',
                        p_object => 'SOME_TABLE',
                        p_object_type => 'TABLE',
                        p_object_alias => 'some_table',
                        p_auto_rest_auth => FALSE);
     commit;
END;

I get:

[Error] Execution (49:1): ORA-01031: insufficient privileges
ORA-06512: at "ORDS_METADATA.ORDS", line 310
ORA-06512: at line 3

Oracle 11.2.0.4

ORDS latest

APEX 20.02


Solution

  • You have to use the ORDS_ADMIN package to enable a schema or object other than your own.

    To use this package, your user needs the ORDS Administrator role.

    So, do this

    GRANT ORDS_ADMINISTRATOR_ROLE TO HR_ADMIN;
    

    Then login as TEST and run this

    BEGIN
    
         ORDS_ADMIN.ENABLE_OBJECT(p_enabled => TRUE,
                            p_schema => 'MAIN',
                            p_object => 'SOME_TABLE',
                            p_object_type => 'TABLE',
                            p_object_alias => 'some_table',
                            p_auto_rest_auth => FALSE);
         commit;
    END;
    

    Disclaimer: I work for Oracle and am a product manager on the database team and cover ORDS.