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.

EXEC IMMEDIATE and Implicit Grants

2 posts in Product Futures Discussion Last posting was on 2003-12-18 08:49:08.0Z
cruiz Posted on 2003-11-28 09:43:44.0Z
Message-ID: <3FC71614.1060700@sybase.com>
From: cruiz <cruiz@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.0.2) Gecko/20030208 Netscape/7.02 (ax)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: sybase.public.ase.product_futures_discussion
CC: cruiz@sybase.com
Subject: EXEC IMMEDIATE and Implicit Grants
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: cruiz-c800.sybase.com
X-Original-Trace: 28 Nov 2003 01:50:10 -0800, cruiz-c800.sybase.com
Lines: 50
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 28 Nov 2003 01:33:12 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 28 Nov 2003 01:43:44 -0800
X-Trace: forums-1-dub 1070012624 10.22.108.75 (28 Nov 2003 01:43:44 -0800)
X-Original-Trace: 28 Nov 2003 01:43:44 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1460
Article PK: 96465

Hi

The current way EXECUTE IMMEDIATE deals with permissions is, as stated
in the Reference Manual:

"The permission to execute Transact-SQL commands defined with the string
or char_variable options is checked against the user executing the
command. This is true even when execute() is defined within a procedure
or trigger that belongs to another user."

This means that the Implicit Grant functionality that you have with
stored procedures does not apply for EXEC IMMEDIATE statements.

Having the following procedures, owned by syslogins' owner:

create proc p as select password from master..syslogins

create proc p as exec("select password from master..syslogins")

The first one gives read access to the protected attribute 'password.
but, granting exec on the second one to public has no effect at all,
because the protection tree for the statement inside the EXECUTE
IMMEDIATE does not get any information about the calling procedure,
and the permissions are checked directly against the user that
executes it.

There are different opinions about this functionality, and whether
execute immediate should do implicit grants as well.

The options are:

- Leave it as it is now
- Do implicit grant
- Do implicit grants under a trace flag
- Do implicit grants it when the procedure is a SA owned system stored
procedure.
- Any other ?

Observe that there are quite a few system stored procedures that are
using exec immediate internally. Not doing implicit grants means that
we have to have permissions granted to public on the system catalog.

Any opinion is welcome.

Thanks

Carlos Ruiz

Sybase IMET/asesecure engineering


"Ilya Zvyagin" <ziv{ Posted on 2003-12-18 08:49:08.0Z
Reply-To: "Ilya Zvyagin" <ziv{@}fct{.}ru>
From: "Ilya Zvyagin" <ziv{@}fct{.}ru>
Newsgroups: sybase.public.ase.product_futures_discussion
References: <3FC71614.1060700@sybase.com>
Subject: Re: EXEC IMMEDIATE and Implicit Grants
Lines: 26
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
X-Comment-To: cruiz
FL-Build: Fidolook Express 2001 UIExt. BuildID: 3BC00FAD (7/10/2001 12:17:49).
Organization: ZAO FCT
Message-ID: <1071736596.268652@gatekeeper.fct.ru>
Cache-Post-Path: gatekeeper.fct.ru!unknown@dream.int.fct.ru
X-Cache: nntpcache 3.0.1 (see http://www.nntpcache.org/)
X-Original-NNTP-Posting-Host: gatekeeper.fct.ru
X-Original-Trace: 18 Dec 2003 00:56:45 -0800, gatekeeper.fct.ru
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 18 Dec 2003 00:36:38 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 18 Dec 2003 00:49:08 -0800
X-Trace: forums-1-dub 1071737348 10.22.108.75 (18 Dec 2003 00:49:08 -0800)
X-Original-Trace: 18 Dec 2003 00:49:08 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1467
Article PK: 96470

Hello, cruiz!
You wrote on 28 Nov 2003 01:43:44 -0800:

c> There are different opinions about this functionality, and whether execute immediate should do implicit grants as
c> well.

c> The options are:

c> - Leave it as it is now
c> - Do implicit grant
c> - Do implicit grants under a trace flag
c> - Do implicit grants it when the procedure is a SA owned system stored procedure.
c> - Any other ?

What I whould propose to do is to add an option to EXEC('...') statement to execute
the statement using permissions of the owner of the stored procedure this EXEC statement
is in. This option should be allowed only when EXEC is in a stored procedure.
This approach will not harm anything and will be very usefull for implementation of buisness
logic in a stored procedures when only EXECUTE IMMIDIATE is an option.

------------------------------------
Ilya Zvyagin
E-mail: ziv[AT]fct[dot]ru
ICQ# 29427861