Procedure Permission - Issue

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

permissions

asked 14 Jun '12, 07:41

J Diaz
1.2k●40●49●68
accept rate: 10%

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

(14 Jun '12, 08:11) Volker Barth

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

Jim

(14 Jun '12, 09:41) J Diaz

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

(14 Jun '12, 15:32) Glenn Paulley

Replies hidden

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.

(14 Jun '12, 17:28) Elmi Eflov

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

(14 Jun '12, 18:57) J Diaz

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

(15 Jun '12, 03:19) Volker Barth

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

(15 Jun '12, 06:55) Glenn Paulley

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;

(15 Jun '12, 08:23) J Diaz

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.

(15 Jun '12, 15:21) Elmi Eflov

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

Thanks

Jim

(18 Jun '12, 21:48) J Diaz

More comments hidden

showing 5 of 10 show all flat view

Be the first one to answer this question!

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

permissions ×28

question asked: 14 Jun '12, 07:41

question was seen: 1,786 times

last updated: 18 Jun '12, 21:48

SAP SQL Anywhere

Procedure Permission - Issue

Follow this question

Related questions