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.

A long winded question

2 posts in General Discussion (old) Last posting was on 2009-12-16 19:04:51.0Z
Jim Diaz Posted on 2009-12-05 01:22:55.0Z
From: "Jim Diaz" <nospam@emprisecorporation.com>
Newsgroups: sybase.public.sqlanywhere
Subject: A long winded question
Lines: 94
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: <4b19b5ef$1@forums-1-dub>
Date: 4 Dec 2009 17:22:55 -0800
X-Trace: forums-1-dub 1259976175 10.22.241.152 (4 Dec 2009 17:22:55 -0800)
X-Original-Trace: 4 Dec 2009 17:22:55 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:326
Article PK: 866753

SQL Anywhere 9.0.2.3804



When designing a database we normally create two users and one group for
each area of functionality. The first user actually owns the core database
objects, the second user owns the objects which are executed from the
application and the group is given permissions to execute the procedures
owned by the application user. So for example if I were writing an SQL
Anywhere Tools application the owner of the core objects might be "SQL", the
owner of the objects executed from the application would be "_SQL" and the
group designed for application users would be __SQL or SQLUsers. Why go
through all this trouble? Because when applications mature and databases
are upgraded it is easy to tell what changes will affect the applications.



Example schema details might be:




CREATE TABLE SQL.Projects (
ProjectId INTEGER NOT NULL DEFAULT AUTOINCREMENT,
ParentProjectId INTEGER NULL,
ProjectName CHAR(128) NOT NULL UNIQUE,
PRIMARY KEY ( ProjectId )
);



The corresponding insert function might be



CREATE FUNCTION SQL.ProjectsInsert(
IN @ProjectName CHAR(128),
IN @ParentProjectId INTEGER DEFAULT NULL
)
RETURNS INTEGER
BEGIN

DECLARE @ProjectId INTEGER;
INSERT

INTO SQL.Projects(
ParentProjectId,
ProjectName
)
VALUES(@ParentProjectId,
@ProjectName
);
SELECT @@Identity INTO @ProjectId FROM DUMMY;
RETURN (@ProjectId)
END;

GRANT EXECUTE ON SQL.ProjectsInsert TO _SQL;

The corresponding application function would be



CREATE FUNCTION _SQL.ProjectsInsert(
IN @ProjectName CHAR(128),
IN @ParentProjectId INTEGER DEFAULT NULL
)
RETURNS INTEGER
BEGIN

DECLARE @ProjectId INTEGER;
SET @ProjectId = SQL.ProjectsInsert(@ProjectName, @ParentProjectId);

RETURN (@ProjectId)
END;

GRANT EXECUTE ON _SQL.ProjectsInsert TO SQLUsers;

One thing we have discovered is that if a function is called from inside a
view. For instance SQL.View1 calls a function SQL.Function1 granting the
user _SQL select permissions on view SQL.View1 is not sufficient you must
also grant execute permissions on SQL.Function1 to user _SQL. It would be
nice if Views executed under the permissions of their owners as do
procedures and functions. Ok so my first question, Is there a way to do
this that I'm missing?

My second question, are we paying a performance penalty by designing in this
way and if so how much of one?

Thanks and Happy Holidays!


Jim


"Nick Elson [Sybase iAnywhere]" < Posted on 2009-12-16 19:04:51.0Z
From: "Nick Elson [Sybase iAnywhere]" <@nick@dot@elson@at@sybase@dot@com@>
Newsgroups: sybase.public.sqlanywhere
References: <4b19b5ef$1@forums-1-dub>
In-Reply-To: <4b19b5ef$1@forums-1-dub>
Subject: Re: A long winded question
Lines: 115
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6002.18005
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18005
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b292f53$1@forums-1-dub>
Date: 16 Dec 2009 11:04:51 -0800
X-Trace: forums-1-dub 1260990291 10.22.241.152 (16 Dec 2009 11:04:51 -0800)
X-Original-Trace: 16 Dec 2009 11:04:51 -0800, vip152.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:331
Article PK: 866758

Seasons greeting 2u2 ...

If it helps you can grant the execute to a group that SQLUsers
are a member of.

The problem with the view is that it is more of a macro definition
and less of a real database object (like a table or procedure) and
permissions on functions (and procedures) seem not to come through
when the query is rewritten.

The rewritten query is what matters here and you can see that
for youself (or a close approximation of it) when you use
the rewrite function

select rewrite( '<select * from view in question/>' )

"Jim Diaz" <nospam@emprisecorporation.com> wrote in message
news:4b19b5ef$1@forums-1-dub...
> SQL Anywhere 9.0.2.3804
>
>
>
> When designing a database we normally create two users and one group for
> each area of functionality. The first user actually owns the core database
> objects, the second user owns the objects which are executed from the
> application and the group is given permissions to execute the procedures
> owned by the application user. So for example if I were writing an SQL
> Anywhere Tools application the owner of the core objects might be "SQL",
> the owner of the objects executed from the application would be "_SQL" and
> the group designed for application users would be __SQL or SQLUsers. Why
> go through all this trouble? Because when applications mature and
> databases are upgraded it is easy to tell what changes will affect the
> applications.
>
>
>
> Example schema details might be:
>
>
>
>
> CREATE TABLE SQL.Projects (
> ProjectId INTEGER NOT NULL DEFAULT AUTOINCREMENT,
> ParentProjectId INTEGER NULL,
> ProjectName CHAR(128) NOT NULL UNIQUE,
> PRIMARY KEY ( ProjectId )
> );
>
>
>
> The corresponding insert function might be
>
>
>
> CREATE FUNCTION SQL.ProjectsInsert(
> IN @ProjectName CHAR(128),
> IN @ParentProjectId INTEGER DEFAULT NULL
> )
> RETURNS INTEGER
> BEGIN
>
> DECLARE @ProjectId INTEGER;
> INSERT
>
> INTO SQL.Projects(
> ParentProjectId,
> ProjectName
> )
> VALUES(@ParentProjectId,
> @ProjectName
> );
> SELECT @@Identity INTO @ProjectId FROM DUMMY;
> RETURN (@ProjectId)
> END;
>
> GRANT EXECUTE ON SQL.ProjectsInsert TO _SQL;
>
> The corresponding application function would be
>
>
>
> CREATE FUNCTION _SQL.ProjectsInsert(
> IN @ProjectName CHAR(128),
> IN @ParentProjectId INTEGER DEFAULT NULL
> )
> RETURNS INTEGER
> BEGIN
>
> DECLARE @ProjectId INTEGER;
> SET @ProjectId = SQL.ProjectsInsert(@ProjectName, @ParentProjectId);
>
> RETURN (@ProjectId)
> END;
>
> GRANT EXECUTE ON _SQL.ProjectsInsert TO SQLUsers;
>
> One thing we have discovered is that if a function is called from inside a
> view. For instance SQL.View1 calls a function SQL.Function1 granting the
> user _SQL select permissions on view SQL.View1 is not sufficient you must
> also grant execute permissions on SQL.Function1 to user _SQL. It would be
> nice if Views executed under the permissions of their owners as do
> procedures and functions. Ok so my first question, Is there a way to do
> this that I'm missing?
>
> My second question, are we paying a performance penalty by designing in
> this way and if so how much of one?
>
> Thanks and Happy Holidays!
>
>
> Jim
>
>