At 06:34 PM 15/11/2007, you wrote:

>--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> >
>> There is no "correct SQL syntax" for passing object references as
>arguments to stored procedures.
>
>I was afraid of that.
>
>>
>> > I'm trying to get my stord procedure to return results from one of
>2 views based > on a condiiton. SourceView should have one of two view
>names.
>>
>> There is a way to do this. Study the documentation in the v.1.5
>release notes for EXECUTE STATEMENT. It was implemented for
>situations like this. Essentially, you pass the object name as a
>VARCHAR(31) variable and construct your statement by concatenation at
>run-time.
>
>It seems to me, and hopefully this is a fair analogy, is that this is
>like writing JavaScript code that creates other JavaScript code. Not
>exactly a very pure or clean solution.

I'm not fond of it...gad, I PROTESTED when it was proposed. But it's there, and sobeit..

>Let me get back to my end-goal to determine if I am taking the right
>approach. My end goal is to have just one stored procedure that all
>clients call. It will pass back different results based on who is
>logged in (CURRENT_USER). In reality the only real difference is the
>WHERE clause. For the FULL access - there is no where clause. For the
>limited access I strip things down to a simple condition.

No, I don't think you're taking the right approach. Since you can create views to provide restricted permissions on both fields and records, you're making life unnecessarily complicated for yourself here. I don't like anything in the database (other than permissions, of course) that hard-wire an object to a user. Why don't I like it? 'Cos, one day, we're going to have it so that SYSDBA can't get inside a database unless it has permission to. So, for consistency reasons, I don't like little tricks with no-exit signs.

>As your book points out, the limitations of using EXECUTE STATEMENT
>seems to outstrip its usefulness.

Hmmm, I don't think I ever meant to convey that. I was, however, very anxious to dispel the idea that EXECUTE STATEMENT is an acceptable workaround for not being able to wreak havoc on metadata through the medium of PSQL. If you are determined to go the route you describe, EXECUTE STATEMENT is OK for DML.

>But if I don't use this approach, my
>only other alternative (that I am aware of) would be to start writing
>business logic on the client end.

Use permissions. The way SQL permissions are implemented is execrable (they were designed by a committee) but at least, if you design them carefully, they will give you the best of both worlds: the intended protection resident on the server, without hard-wiring any objects to conditions that might change without notice. Then the client side doesn't need to know the logic, only the user and role variables.

./heLen