Cannot Invoke Procedure - Privileges Error

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Cannot Invoke Procedure - Privileges Error

Simon Carroll
Hi,

I am experiencing an issue with version 2.2.9 of HSQLDB when attempting
to invoke a procedure.  I create the procedure using the following DDL
script:

/*u0*/CREATE SCHEMA "udm_fgac" authorization dba;
SET DATABASE DEFAULT INITIAL SCHEMA "udm_fgac";
SET SCHEMA "udm_fgac";
CREATE table "udm_fgac"."living_user_external_credential_view" (
"living_user_external_credential_id"     INTEGER        PRIMARY KEY,
        "living_user_id"             INTEGER        NOT NULL,
        "customer_id"                varchar(255)  NOT NULL,
        "customer_number"            varchar(255)  NOT NULL,
        "company_id"                 INTEGER           NOT NULL
);
GRANT ALL ON "udm_fgac"."living_user_external_credential_view" TO
PUBLIC;

CREATE PROCEDURE "udm_fgac"."get_living_user_id" (IN
"p_identity_provider" varchar(255),
IN "p_identity_provider_id"         varchar(255),
IN "p_pin"                 varchar(255),
OUT "living_user_id" bigint
)
READS SQL DATA
    SELECT 1 INTO "living_user_id" FROM
"udm_fgac"."living_user_external_credential_view";
.;

GRANT ALL ON ROUTINE "udm_fgac"."get_living_user_id" TO PUBLIC;

The script executes correctly but when I try and call the procedure
using the following command:

call "udm_fgac"."get_living_user_id"(?,?,?)

I get the following error:

Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or
object not found
        at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
        at org.hsqldb.jdbc.Util.sqlException(Unknown Source)

Can you help with this as its driving me mad!

Thanks

Simon



------------------------------------------------------------------------------
Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer
Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS Reports
Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White paper
Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog Analyzer
http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/ostg.clktrk
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Cannot Invoke Procedure - Privileges Error

Fred Toussi-2
The procedure has 4 parameters but it is called with three.

Fred

On Fri, Oct 10, 2014, at 14:12, Simon Carroll wrote:

> Hi,
>
> I am experiencing an issue with version 2.2.9 of HSQLDB when attempting
> to invoke a procedure.  I create the procedure using the following DDL
> script:
>
> /*u0*/CREATE SCHEMA "udm_fgac" authorization dba;
> SET DATABASE DEFAULT INITIAL SCHEMA "udm_fgac";
> SET SCHEMA "udm_fgac";
> CREATE table "udm_fgac"."living_user_external_credential_view" (
> "living_user_external_credential_id"     INTEGER        PRIMARY KEY,
> "living_user_id"             INTEGER        NOT NULL,
> "customer_id"                varchar(255)  NOT NULL,
> "customer_number"            varchar(255)  NOT NULL,
> "company_id"                 INTEGER           NOT NULL
> );
> GRANT ALL ON "udm_fgac"."living_user_external_credential_view" TO
> PUBLIC;
>
> CREATE PROCEDURE "udm_fgac"."get_living_user_id" (IN
> "p_identity_provider" varchar(255),
> IN "p_identity_provider_id"         varchar(255),
> IN "p_pin"                                       varchar(255),
> OUT "living_user_id"                            bigint
> )
> READS SQL DATA
>     SELECT 1 INTO "living_user_id" FROM
> "udm_fgac"."living_user_external_credential_view";
> .;
>
> GRANT ALL ON ROUTINE "udm_fgac"."get_living_user_id" TO PUBLIC;
>
> The script executes correctly but when I try and call the procedure
> using the following command:
>
> call "udm_fgac"."get_living_user_id"(?,?,?)
>
> I get the following error:
>
> Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or
> object not found
> at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
> at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
>
> Can you help with this as its driving me mad!
>
> Thanks
>
> Simon
>
>
>
> ------------------------------------------------------------------------------
> Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer
> Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS Reports
> Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White paper
> Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog Analyzer
> http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/ostg.clktrk
> _______________________________________________
> Hsqldb-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer
Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS Reports
Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White paper
Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog Analyzer
http://p.sf.net/sfu/Zoho
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user