Oracle – How to Grant Permissions for User Defined Type in Oracle

oracleoracle-11g-r2oracle-sql-developerpermissions

This is probably easy but i can't seem to figure it out. I created my user-defined type as CO_ADMIN. However, the application using the type runs using GS_USER database account.

I have granted permissions to GS_USER as CO_ADMIN but yet anytime my application attempts to use the user defined type, i always get:

: type ""."DOCPARAMETERTYPE_TABLE" not found    at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck)
   at Oracle.DataAccess.Types.OracleUdtDescriptor..ctor(OracleConnection con, String schemaName, String typeName)

Here are the statements i have tried so far. All say "Grant succeeded" after running them but it's not true or perhaps i am using the wrong statements. Please help:

GRANT all on "CO_ADMIN"."DOCPARAMETERTYPE" to "GS_USER" with grant option;
GRANT all on "CO_ADMIN"."DOCPARAMETERTYPE_TABLE" to "GS_USER" with grant option;

GRANT EXECUTE ON "CO_ADMIN"."DOCPARAMETERTYPE" TO "GS_USER";
GRANT EXECUTE ON "CO_ADMIN"."DOCPARAMETERTYPE_TABLE" TO "GS_USER";

Here are the definitions for the types:

create or replace type DOCPARAMETERTYPE as object (
docID varchar2(20),
system varchar(50)
);

create or replace type DOCPARAMETERTYPE_TABLE AS TABLE of DOCPARAMETERTYPE;

Best Answer

I think you need to either qualify type with schema when using client account (CO_ADMIN.DOCPARAMETERTYPE_TABLE, not just DOCPARAMETERTYPE_TABLE ) or create a public synonym for the type.

Related Question