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.

Problem with permissions on SPs and tables

4 posts in General Discussion Last posting was on 2004-04-18 12:11:12.0Z
Louise Hadley Posted on 2004-04-17 18:23:35.0Z
Sender: 6906.408165d8.1804289383@sybase.com
From: Louise Hadley
Newsgroups: ianywhere.public.general
Subject: Problem with permissions on SPs and tables
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <40816817.690a.846930886@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 17 Apr 2004 10:23:35 -0800
X-Trace: forums-1-dub 1082222615 10.22.241.41 (17 Apr 2004 10:23:35 -0800)
X-Original-Trace: 17 Apr 2004 10:23:35 -0800, 10.22.241.41
Lines: 26
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2967
Article PK: 6434

I'm trying to get an ASP.Net application to call stored
procedures and have a permissions problem. I'm using the
userID ASPNET, with a password. The error message I got was
something along the lines of "stored procedure doesn't
exist".

So I went to work in Sybase Central and I-Sql and - when
connected to the database as ASPNET - I cannot execute
stored procedures that are owned by DBA, even though I have
granted ASPNET permission to run them.

If I run a stored procedure that is owned by ASPNET that
selects from a table that is owned by dba, the procedure
runs but then I get an error Table <tablename> not found.
Again, I have given ASPNET permission to select (and
everything else) from this table.

I'm sure I'm just doing something stupid - but what??

I'm running ASA 9.0.1.1751

Thanks,
Louise


If I call a stored procedure that ASPNET owns


Greg Fenton Posted on 2004-04-18 00:44:50.0Z
From: Greg Fenton <greg.fenton_NOSPAM_@ianywhere.com>
Organization: iAnywhere Solutions Inc.
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.6) Gecko/20040113 MultiZilla/1.6.2.0c
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Problem with permissions on SPs and tables
References: <40816817.690a.846930886@sybase.com>
In-Reply-To: <40816817.690a.846930886@sybase.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: cpe000625d8278c-cm.cpe.net.cable.rogers.com
Message-ID: <4081c16d$1@forums-2-dub>
X-Original-Trace: 17 Apr 2004 16:44:45 -0800, cpe000625d8278c-cm.cpe.net.cable.rogers.com
Lines: 39
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 17 Apr 2004 16:44:46 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 17 Apr 2004 16:44:50 -0800
X-Trace: forums-1-dub 1082245490 10.22.108.75 (17 Apr 2004 16:44:50 -0800)
X-Original-Trace: 17 Apr 2004 16:44:50 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2968
Article PK: 6436


Louise wrote:

> I'm trying to get an ASP.Net application to call stored
> procedures and have a permissions problem. I'm using the
> userID ASPNET, with a password. The error message I got was
> something along the lines of "stored procedure doesn't
> exist".
>

A couple of points about stored procedures in ASA:

1. If the stored procedure is owned by another user, you must qualify
its name for any other user:

call "dba".stored_proc_owned_by_dba( .... );

2. A stored procedure runs with the permissions of the owner of the
procedure *regardless* of who is actually running the stored proc.
The benefit of this is that I can have a table owned by DBA that no
one else has DML (SELECT, INSERT, UPDATE or DELETE) permissions, but
I can create a stored procedure that manipulates that table and give
EXECUTE permissions to whatever users you want. You can build it
whatever business logic you want and not have to give them blanket
access to using regular DML.

All of this is documented in the online docs:

ASA Database Administration Guide
Managing User IDs and Permissions

Hope this helps,
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/


Chris Keating (iAnywhere Solutions) Posted on 2004-04-18 03:03:51.0Z
From: "Chris Keating \(iAnywhere Solutions\)" <FightSpam_keating@iAnywhere.com>
Newsgroups: ianywhere.public.general
References: <40816817.690a.846930886@sybase.com> <4081c16d$1@forums-2-dub>
Subject: Re: Problem with permissions on SPs and tables
Lines: 75
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
NNTP-Posting-Host: vpn-concord-045.sybase.com
X-Original-NNTP-Posting-Host: vpn-concord-045.sybase.com
Message-ID: <4081e207@forums-1-dub>
Date: 17 Apr 2004 19:03:51 -0800
X-Trace: forums-1-dub 1082253831 158.159.8.45 (17 Apr 2004 19:03:51 -0800)
X-Original-Trace: 17 Apr 2004 19:03:51 -0800, vpn-concord-045.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2969
Article PK: 6438

For the procedure to be visible to ASPNET, you must full qualify the
procedure with the owner name or convert DBA to a group and make ASPNET a
member of the group DBA. Users who are members of the group DBA do not
inherit any of the special 'dba' authorities.

--

Chris Keating
Sybase Adaptive Server Anywhere Professional Version 8

****************************************************************************
*
Sign up today for your copy of the SQL Anywhere Studio 9 Developer Edition
and try out the market-leading database for mobile, embedded and small to
medium sized business environments for free!

http://www.ianywhere.com/promos/deved/index.html

****************************************************************************
*

iAnywhere Solutions http://www.iAnywhere.com

** Please only post to the newsgroup

** Whitepapers can be found at http://www.iAnywhere.com/developer
** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
** Use CaseXpress to report bugs http://casexpress.sybase.com

****************************************************************************
*

"Greg Fenton" <greg.fenton_NOSPAM_@ianywhere.com> wrote in message
news:4081c16d$1@forums-2-dub...
> Louise wrote:
>
> > I'm trying to get an ASP.Net application to call stored
> > procedures and have a permissions problem. I'm using the
> > userID ASPNET, with a password. The error message I got was
> > something along the lines of "stored procedure doesn't
> > exist".
> >
>
> A couple of points about stored procedures in ASA:
>
> 1. If the stored procedure is owned by another user, you must qualify
> its name for any other user:
>
> call "dba".stored_proc_owned_by_dba( .... );
>
> 2. A stored procedure runs with the permissions of the owner of the
> procedure *regardless* of who is actually running the stored proc.
> The benefit of this is that I can have a table owned by DBA that no
> one else has DML (SELECT, INSERT, UPDATE or DELETE) permissions, but
> I can create a stored procedure that manipulates that table and give
> EXECUTE permissions to whatever users you want. You can build it
> whatever business logic you want and not have to give them blanket
> access to using regular DML.
>
> All of this is documented in the online docs:
>
> ASA Database Administration Guide
> Managing User IDs and Permissions
>
> Hope this helps,
> greg.fenton
> --
> Greg Fenton
> Consultant, Solution Services, iAnywhere Solutions
> --------
> Visit the iAnywhere Solutions Developer Community
> Whitepapers, TechDocs, Downloads
> http://www.ianywhere.com/developer/


Louise Hadley Posted on 2004-04-18 12:11:12.0Z
Sender: 11bb.4082576a.1804289383@sybase.com
From: Louise Hadley
Newsgroups: ianywhere.public.general
Subject: Re: Problem with permissions on SPs and tables
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <40826247.1328.846930886@sybase.com>
References: <40816817.690a.846930886@sybase.com> <4081c16d$1@forums-2-dub><4081e207@forums-1-dub>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 18 Apr 2004 04:11:03 -0800, 10.22.241.42
Lines: 4
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 18 Apr 2004 04:11:04 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 18 Apr 2004 04:11:12 -0800
X-Trace: forums-1-dub 1082286672 10.22.108.75 (18 Apr 2004 04:11:12 -0800)
X-Original-Trace: 18 Apr 2004 04:11:12 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2970
Article PK: 6437

>Thanks Greg and Chris - I went with the "change DBA to a
group" option and everything's working perfectly!
Am just printing off the ASA Database Admin Guide for a bit
of bedtime reading :)