Procedure Permission - Issue

[JimDiaz]

In ASA 12.0.1.3152 a procedure owned by "SMH" was selecting on a view owned by "SMH". A User belonging to group "_SMH" which has permission to execute the procedure owned by "SMH" executes the procedure and receives the error "you do not have permission to select from the view owned by "SMH". I issue the statement grant select on "SMH".ViewName to "SMH" and the problem goes away.

I don't believe this is expected behavior.

Thanks

Jim

[VolkerBarth]

Are you using the SQL SECURITY clause (introduced in v11) with value INVOKER? That would explain the behaviour you see...

I.e. something like

CREATE PROCEDURE SMH.myProcedure()
   RESULT (myResultCol INT)
   SQL SECURITY INVOKER
   BEGIN
   ...
   END

[JimDiaz]

No, but one item I never mentioned is the "SMH" procedure does return a result set. Thanks,

Jim

[Former Member]

How are you invoking the procedure, Jim? With a CALL statement, or a SELECT, perhaps?

[Former Member]

Could you please provide some more details about the procedure? With the most recent SA 12 build, the following setup

CREATE TABLE u1.tab( pk INTEGER, c1 INTEGER );
GRANT SELECT ON u1.tab TO u2;
CREATE VIEW u2.t_view AS SELECT pk, c1 + pk as val FROM u1.tab;
CREATE PROCEDURE u2.proc1()
BEGIN
    DECLARE @p INTEGER;
    SET @p = 1;
    SELECT pk, val + @p FROM u2.t_view;
END;
GRANT EXECUTE ON u2.proc1 TO u3;

allows the user u3 to SELECT/CALL the procedure, but does not allow the user to select from the view/table.

[JimDiaz]

We invoke using call. I will compile more details and post.

[VolkerBarth]

Would this make a difference w.r.t. permissions? (As of Elmi's answer, I would think not - and that would meet my expectation!)

[Former Member]

No. Just trying to narrow things down, since Jim clearly has a problem.

[JimDiaz]

The procedure is straight forward (shown below) however their are many functions and sub-functions called all with various owners. In the procedure below the call "SWA.FiltersAllValues" evaluated to 0 which invokes the SELECT WHERE EXISTS. The error received was no permission to select from ViewFilteredAttributeValues which is when I granted select on SMH.ViewFilteredAttributeValues to SMH. The next call returned the VesselId's without error.

The database is a development database with significant activity. Review of permissions on the view does show the grant select statement was issued, which in and of itself is strange.

This by the way is repeatable, if I drop the grant select the issue returns.

I would be happy to host a GOTO meeting so that you can see this for yourself.

Jim

CREATE PROCEDURE SMH.FilteredVesselIds()
RESULT( VesselId INTEGER )
BEGIN
  DECLARE @FilterId INTEGER;

SET @FilterId = SMH.UsersActiveFilter();

IF SWA.FiltersAllValues(@FilterId, 'VesselId') = 1 THEN
    SELECT VesselId
      FROM SWA.UsersVesselList(SMH.ApplicationId())
  ELSE
    SELECT VesselId
      FROM SWA.UsersVesselList(SMH.ApplicationId())
     WHERE EXISTS ( SELECT * FROM SMH.ViewFilteredAttributeValues
                     WHERE AttributeCode = 'VesselId'
                       AND AttributeValue = VesselId )
  END IF;
END;

[Former Member]

Jim, could you please try the simple example above to see if the problem reproduces without your specific procedures/views? There have been changes made to the procedure execution gear that may affect this behavior.

[JimDiaz]

I've tried the above example and it works as expected.

Thanks

Jim