User without any privileges on the package schema can declare and use the package's record types

SET sql_mode=ORACLE;

CREATE DATABASE secdb;

CREATE PACKAGE secdb.pkg AS

TYPE rec_t IS RECORD (a INT, secret VARCHAR(30));

FUNCTION f RETURN INT;

END;

$$

CREATE PACKAGE BODY secdb.pkg AS FUNCTION f RETURN INT AS BEGIN RETURN 42; END; END;

$$

CREATE DATABASE appdb;

CREATE USER u_none@localhost;

GRANT ALL ON appdb.* TO u_none@localhost;

connect  cnone, localhost, u_none,, appdb;

SET sql_mode=ORACLE;

CREATE PROCEDURE appdb.p AS r secdb.pkg.rec_t;

BEGIN r.a:=1; r.secret:='x'; SELECT r.a; END;

$$

CALL appdb.p;

r.a

1

SELECT secdb.pkg.f();

ERROR 42000: execute command denied to user 'u_none'@'localhost' for routine 'secdb.pkg'

connection default;

disconnect cnone;

DROP USER u_none@localhost;

DROP DATABASE appdb;

DROP DATABASE secdb;

SET sql_mode=ORACLE;

CREATE DATABASE secdb;

DELIMITER $$;

CREATE PACKAGE secdb.pkg AS

  TYPE rec_t IS RECORD (a INT, secret VARCHAR(30));

  FUNCTION f RETURN INT;

END;

$$

CREATE PACKAGE BODY secdb.pkg AS FUNCTION f RETURN INT AS BEGIN RETURN 42; END; END;

$$

DELIMITER ;$$

CREATE DATABASE appdb;

CREATE USER u_none@localhost;

# No privilege on secdb

GRANT ALL ON appdb.* TO u_none@localhost;          

connect (cnone, localhost, u_none,, appdb);

SET sql_mode=ORACLE;

# The server allows a package to be created using a record type defined in a package owned by another schema, even when the user has no privileges on the secdb schema

DELIMITER $$;

CREATE PROCEDURE appdb.p AS r secdb.pkg.rec_t;

BEGIN r.a:=1; r.secret:='x'; SELECT r.a; END;

$$

DELIMITER ;$$

CALL appdb.p;

# Here the package routine is correctly denied

--error ER_PROCACCESS_DENIED_ERROR

SELECT secdb.pkg.f();

connection default;

disconnect cnone;

DROP USER u_none@localhost;

DROP DATABASE appdb;

DROP DATABASE secdb;