Grant on constant value in Oracle-CodePudding

On schema test package pkg_test I have c_status CONSTANT NUMBER := 1;

How add grant on this value to other scheme test2?

Some like this:

Grant select pkg_test.c_status to test2

On schema test2 I need constant value in update statements

CodePudding user response：

You cannot grant single variables, only packages, procedures and functions. Would be

Grant execute on test.pkg_test to test2;

If you need to give access only to this single constant, then I would suggest

CREATE OR REPLACE FUNCTION get_status RETURN NUMBER DETERMINISTIC IS
BEGIN
    return test.pkg_test.c_status;
END get_status;

GRANT EXECUTE ON get_status TO test2;

Or a bit more generic:

CREATE OR REPLACE FUNCTION get_constant(const_name IN VARCHAR2) RETURN NUMBER DETERMINISTIC IS

    INVALID_IDENTIFIER EXCEPTION;
    PRAGMA EXCEPTION_INIT(INVALID_IDENTIFIER, -904);

    res NUMBER;
BEGIN
   IF UPPER(const_name) IN ('C_STATUS') THEN
      EXECUTE IMMEDIATE 'BEGIN :ret := test.pkg_test.'||const_name||'; END;' USING OUT res; 
      RETURN res;
   ELSE
      RAISE INVALID_IDENTIFIER;
   END IF;
END get_constant;

GRANT EXECUTE ON get_constant TO test2;