0

I am using Oracle and SQL Developer and here is a problem I ran into. I have created a table called T_TEST and I am trying to create a read only transaction to it from another user. This is my procedure:

CREATE PROCEDURE reader
AS
BEGIN
    SET TRANSACTION READ ONLY;
    SELECT SUM(value) FROM SYSTEM.T_TEST;
    COMMIT;
END;

And this is the result that I get:

ORA-01031: insufficient privileges

The user itself has this privileges:

GRANT CONNECT TO admin1;
GRANT CREATE SESSION TO admin1;
GRANT SELECT ON T_TEST TO admin1;

Which is enough for this query to work:

SELECT SUM(value) FROM SYSTEM.T_TEST;

However, when I wrap it up in a transaction, as I showed, it fails. So what privileges am I lacking here?

1
  • I can't reproduce this issue, even after fixing the PL/SQL syntax and granting create procedure to admin1. Also, SELECT privilege allows select for update, which READ privilege does not. Oct 30 '21 at 9:42
0

You're missing the REFERENCES constraint.

grant references on system.t_test to admin1

Also, you shouldn't create tables under SYSTEM

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.