Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Stored procedure "execute" permission

4 posts in General Discussion Last posting was on 2010-01-19 16:34:36.0Z
John Flynn Posted on 2010-01-18 20:34:30.0Z
From: "John Flynn" <jflynn@miqs.com>
Newsgroups: sybase.public.ase.general
Subject: Stored procedure "execute" permission
Lines: 29
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b54c5d6$1@forums-1-dub>
Date: 18 Jan 2010 12:34:30 -0800
X-Trace: forums-1-dub 1263846870 10.22.241.152 (18 Jan 2010 12:34:30 -0800)
X-Original-Trace: 18 Jan 2010 12:34:30 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28889
Article PK: 78131

Hi.

I understand that if you deny direct write access to a table, but then allow
execute access to a stored procedure that itself writes the table, then the
execute access overrides the direct table permission. The documentation
explains that this can be a good way to control access to a table, to
encapsulate all the permission logic in the stored procedure.

My question is, what if you don't want the direct table permission to be
overridden? What if you have a stored procedure that sometimes writes the
table and sometimes doesn't, and you want to allow any user to run it, but
you want to disallow direct writes via a role-based mechanism? In that case
I'd want the direct table permission to be honored first no matter what the
stored procedure says. I can't see any way to do that in ASE. Is that true?

I think I could maybe let the stored procedure check whether the current
user owns a specified role before it proceeds with the table write. Even if
that can be made to work, what if I have 20 different roles that all allow
access, my stored procedure would have to know what they all are and check
for each one of them. So that doesn't seem too clean.

If only ASE had two flavors of "execute" permission: execute with override
(which I think is currently the only option), and execute without override
(meaning direct table permissions take priority).

Thanks.
- John.


Luc Van der Veurst Posted on 2010-01-19 12:21:15.0Z
From: "Luc Van der Veurst" <dba_azvub@hotmail.com>
Newsgroups: sybase.public.ase.general
References: <4b54c5d6$1@forums-1-dub>
Subject: Re: Stored procedure "execute" permission
Lines: 56
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Response
X-Forwarded: by - (DeleGate/5.8.7)
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b55a3bb$1@forums-1-dub>
Date: 19 Jan 2010 04:21:15 -0800
X-Trace: forums-1-dub 1263903675 10.22.241.152 (19 Jan 2010 04:21:15 -0800)
X-Original-Trace: 19 Jan 2010 04:21:15 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28894
Article PK: 78136

Hi John,

You can give users insert/update/delete permissions on tables
and execute permissions on procedures. These are separate
permissions on separate objects. So I don't understand what
you mean with "to be honored first".

The execute access of a procedure that updates a table doesn't
override the direct table access. If you have a procedure
that updates a table, users who can update the table directly
will still be able to do that, it doesn't matter if they are allowed
to execute the procedure or not.

If this is the way you understand permissions, then please explain what
you mean with "override the direct table access".

If no one else replies who understands your question better than me,
then please give a more specific example.

Luc.

"John Flynn" <jflynn@miqs.com> wrote in message
news:4b54c5d6$1@forums-1-dub...
> Hi.
>
> I understand that if you deny direct write access to a table, but then
> allow execute access to a stored procedure that itself writes the table,
> then the execute access overrides the direct table permission. The
> documentation explains that this can be a good way to control access to a
> table, to encapsulate all the permission logic in the stored procedure.
>
> My question is, what if you don't want the direct table permission to be
> overridden? What if you have a stored procedure that sometimes writes the
> table and sometimes doesn't, and you want to allow any user to run it, but
> you want to disallow direct writes via a role-based mechanism? In that
> case I'd want the direct table permission to be honored first no matter
> what the stored procedure says. I can't see any way to do that in ASE. Is
> that true?
>
> I think I could maybe let the stored procedure check whether the current
> user owns a specified role before it proceeds with the table write. Even
> if that can be made to work, what if I have 20 different roles that all
> allow access, my stored procedure would have to know what they all are and
> check for each one of them. So that doesn't seem too clean.
>
> If only ASE had two flavors of "execute" permission: execute with override
> (which I think is currently the only option), and execute without override
> (meaning direct table permissions take priority).
>
> Thanks.
> - John.
>
>


John Flynn Posted on 2010-01-19 16:34:36.0Z
From: "John Flynn" <jflynn@miqs.com>
Newsgroups: sybase.public.ase.general
References: <4b54c5d6$1@forums-1-dub> <4b55a3bb$1@forums-1-dub>
Subject: Re: Stored procedure "execute" permission
Lines: 39
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b55df1c$1@forums-1-dub>
Date: 19 Jan 2010 08:34:36 -0800
X-Trace: forums-1-dub 1263918876 10.22.241.152 (19 Jan 2010 08:34:36 -0800)
X-Original-Trace: 19 Jan 2010 08:34:36 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28896
Article PK: 78138


Luc Van der Veurst wrote:
> You can give users insert/update/delete permissions on tables
> and execute permissions on procedures. These are separate
> permissions on separate objects. So I don't understand what
> you mean with "to be honored first".
>
> The execute access of a procedure that updates a table doesn't
> override the direct table access. If you have a procedure
> that updates a table, users who can update the table directly
> will still be able to do that, it doesn't matter if they are allowed
> to execute the procedure or not.
>
> If this is the way you understand permissions, then please explain
> what you mean with "override the direct table access".
>
> If no one else replies who understands your question better than me,
> then please give a more specific example.

You're right, I didn't explain myself well at all. When I used the term
"direct table access" I was speaking nonsense, sorry. My mistake was in
speaking as if a lack of permission to write the table is the same as an
explicit denial of access. I guess Sybase doesn't have the concept of
"explicit denial of access".

I think that's what I want, a way to positively "deny" access, like some
other systems have. In Sybase, the closest you can get is to omit (or
revoke) the granting. What I want is a new command analogous to GRANT,
called DENY. This is different from REVOKE. Denying write access to a table
could not be overriden by executing a stored procedure.

I think another way to say it is: What I'm really after is a permission mode
for stored procedures that requires TWO things: execute access to the stored
procedure, AND write access to the table being updated. That would be a
permission mode that is stricter than the one currently supported by Sybase.

Thanks.
- John.


Carl Kayser Posted on 2010-01-19 14:31:40.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
Newsgroups: sybase.public.ase.general
References: <4b54c5d6$1@forums-1-dub>
Subject: Re: Stored procedure "execute" permission
Lines: 68
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3598
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3350
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b55c24c@forums-1-dub>
Date: 19 Jan 2010 06:31:40 -0800
X-Trace: forums-1-dub 1263911500 10.22.241.152 (19 Jan 2010 06:31:40 -0800)
X-Original-Trace: 19 Jan 2010 06:31:40 -0800, vip152.sybase.com
X-Authenticated-User: ase1251
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28895
Article PK: 78137


"John Flynn" <jflynn@miqs.com> wrote in message
news:4b54c5d6$1@forums-1-dub...
> Hi.
>
> I understand that if you deny direct write access to a table, but then
> allow execute access to a stored procedure that itself writes the table,
> then the execute access overrides the direct table permission. The
> documentation explains that this can be a good way to control access to a
> table, to encapsulate all the permission logic in the stored procedure.
>
> My question is, what if you don't want the direct table permission to be
> overridden? What if you have a stored procedure that sometimes writes the
> table and sometimes doesn't, and you want to allow any user to run it, but
> you want to disallow direct writes via a role-based mechanism? In that
> case I'd want the direct table permission to be honored first no matter
> what the stored procedure says. I can't see any way to do that in ASE. Is
> that true?
>
> I think I could maybe let the stored procedure check whether the current
> user owns a specified role before it proceeds with the table write. Even
> if that can be made to work, what if I have 20 different roles that all
> allow access, my stored procedure would have to know what they all are and
> check for each one of them. So that doesn't seem too clean.
>
> If only ASE had two flavors of "execute" permission: execute with override
> (which I think is currently the only option), and execute without override
> (meaning direct table permissions take priority).
>
> Thanks.
> - John.
>
>

OK, let's work with something specific, say, sp_john which does an update
and a delete (and we'll skip the prologue and epilogue portions):

update table1
...

delete table2
....

and protections for the above are "execute with override". If "execute
without override" is desired then the code could be:

declare @ cmdstring varchar (NNN)

set @cmdstring = 'update table1 ....'

execute (@cmdstring)

set @cmdstring = 'delete table2 ...'

execute( @cmdstring)


And one could code a mix-and-match of the two. Complexities could occur
with conditional testing (I believe that the execute immediate does not
provide a return value but I haven't checked this possibility with 15.x. I
think that it is possible; Rob V would be the resident expert on this.)
Also, it would not be easy to switch between the two modes, especially in
these days of configuration management to the Nth degree.