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.

Obect in SYS

8 posts in General Discussion Last posting was on 2002-10-16 15:13:19.0Z
Vinod Posted on 2002-10-15 04:44:13.0Z
From: Vinod
Date: Tue, 15 Oct 2002 00:44:13 -0400
Newsgroups: ianywhere.public.general
Subject: Obect in SYS
Message-ID: <DE7D9DC3D4D3C682001A057385256C53.001A058385256C53@webforums>
Lines: 17
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub ianywhere.public.general:544
Article PK: 2361

Hi,

I am in process to upgrade/move my existing database from ASA 5504 to ASA 8
(W2K). I have created new database and did reload successfully. But later I
found that there are some objects created by application in SYS. Where I
tried to create these object in SYS id it gives me the following error:

ASA Error -121 Permission denied: you do not have permission to use the
"CREATE FUNCTION" statement

I have tried all the option to create in SYS, which I can't avoid. Since I
am not much aware about application so I've to create them in SYS.

Any clue how to create objects in SYS, any permission, option...

Thanx in advance...


Robert Waywell Posted on 2002-10-15 15:18:16.0Z
From: "Robert Waywell" <rwaywell@ianywhere.com>
References: <DE7D9DC3D4D3C682001A057385256C53.001A058385256C53@webforums>
Subject: Re: Obect in SYS
Date: Tue, 15 Oct 2002 11:18:16 -0400
Lines: 52
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4522.1200
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200
Message-ID: <ysmIsAGdCHA.196@forums.sybase.com>
Newsgroups: ianywhere.public.general
NNTP-Posting-Host: 172.31.143.74
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub ianywhere.public.general:542
Article PK: 2357

You are not currently allowed to create objects under the user ID "sys".

What specific objects are you seeing that are being created under "sys"?
What version was the database file originally created with?
- I don't think it could have been 5.5.04



--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer

<Vinod> wrote in message
news:DE7D9DC3D4D3C682001A057385256C53.001A058385256C53@webforums...
> Hi,
>
> I am in process to upgrade/move my existing database from ASA 5504 to ASA
8
> (W2K). I have created new database and did reload successfully. But later
I
> found that there are some objects created by application in SYS. Where I
> tried to create these object in SYS id it gives me the following error:
>
> ASA Error -121 Permission denied: you do not have permission to use the
> "CREATE FUNCTION" statement
>
> I have tried all the option to create in SYS, which I can't avoid. Since I
> am not much aware about application so I've to create them in SYS.
>
> Any clue how to create objects in SYS, any permission, option...
>
> Thanx in advance...
>


Vinod Posted on 2002-10-16 11:19:47.0Z
From: Vinod
Date: Wed, 16 Oct 2002 07:19:47 -0400
Newsgroups: ianywhere.public.general
Subject: Re: Obect in SYS
Message-ID: <0AF06505DC092E06003E3C8585256C54.0059EE7685256C53@webforums>
References: <DE7D9DC3D4D3C682001A057385256C53.001A058385256C53@webforums> <ysmIsAGdCHA.196@forums.sybase.com>
Lines: 24
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub ianywhere.public.general:537
Article PK: 2356

I heard that is stopped in version 6, If this thing stopped in version 6
then we will face problem in this upgrade. We put our functions in SYS bcoz
of global permission/security. Following is our requirement:

Our all the tables owned by ADMIN id
We have different groups having views based on admin tables, which will
filter data for group
We have id's belongs to above groups
Earlier we put all our functions/procedures in SYS so anybody can access
them.

We can't put our functions in ADMIN bcoz it will work on whole data instead
of group filtered one. If we create another group and put all the functions
in that group and then allow that group to end user ids or filter groups
then it gives us ambiguous error and may access another group data too.

DO WE HAVE SYNONYM/PUBLIC SYNONYM feature in ASA?

One another problem, often we get the following error in iSQL:

Could not execute statement.
Lexical error at line 2, column 2. Encountered: <EOF> after: ""

after getting this error we can't give even a simple statment or can't
disconnect


Robert Waywell Posted on 2002-10-16 15:13:19.0Z
From: "Robert Waywell" <rwaywell@ianywhere.com>
References: <DE7D9DC3D4D3C682001A057385256C53.001A058385256C53@webforums> <ysmIsAGdCHA.196@forums.sybase.com> <0AF06505DC092E06003E3C8585256C54.0059EE7685256C53@webforums>
Subject: Re: Obect in SYS
Date: Wed, 16 Oct 2002 11:13:19 -0400
Lines: 85
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4522.1200
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200
Message-ID: <JPHtmiSdCHA.287@forums.sybase.com>
Newsgroups: ianywhere.public.general
NNTP-Posting-Host: 172.31.143.74
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub ianywhere.public.general:535
Article PK: 2351


>>Earlier we put all our functions/procedures in SYS so anybody can access
>> them.

This is normally handled through the EXECUTE permission, not through the
owner of the procedure. In fact, permissions that rely on ownership of an
object are not inherited by the group.

>> We can't put our functions in ADMIN bcoz it will work on whole data
instead
>> of group filtered one.
The data set being manipulated in the function should be dependent on
the code in the function, not on who owns the function. For example,
regardless of who own's the function, if I select data from Table1 instead
of Group1_View then I'm going to get the data from Table1, not the data that
was specifically intended for Group1 through the Group1_View.

So, not being able to create objects under SYS is expected behaviour.
The fact that you were able to do it in 5.5.04 build 1867 is considered a
bug. From a migration standpoint, I understand the cost of having to rewrite
code. From an original design standpoint, there should not have been any
benefits to creating the functions under SYS in the first place, but you
have them now. It should be fairly straightforward (albeit time consuming)
to recreate the functions under a new group, but then you will still have to
find all the places that the original functions were referenced.
If you want to open up a case, then Support may be able to help you out
but I can't promise anything.

>> Could not execute statement.
>> Lexical error at line 2, column 2. Encountered: <EOF> after: ""

This sounds like something that was fixed in an ebf a while ago.

--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer

<Vinod> wrote in message
news:0AF06505DC092E06003E3C8585256C54.0059EE7685256C53@webforums...
> I heard that is stopped in version 6, If this thing stopped in version 6
> then we will face problem in this upgrade. We put our functions in SYS
bcoz
> of global permission/security. Following is our requirement:
>
> Our all the tables owned by ADMIN id
> We have different groups having views based on admin tables, which will
> filter data for group
> We have id's belongs to above groups
> Earlier we put all our functions/procedures in SYS so anybody can access
> them.
>
> We can't put our functions in ADMIN bcoz it will work on whole data
instead
> of group filtered one. If we create another group and put all the
functions
> in that group and then allow that group to end user ids or filter groups
> then it gives us ambiguous error and may access another group data too.
>
> DO WE HAVE SYNONYM/PUBLIC SYNONYM feature in ASA?
>
> One another problem, often we get the following error in iSQL:
>
> Could not execute statement.
> Lexical error at line 2, column 2. Encountered: <EOF> after: ""
>
> after getting this error we can't give even a simple statment or can't
> disconnect


Vinod Posted on 2002-10-16 02:43:39.0Z
From: Vinod
Date: Tue, 15 Oct 2002 22:43:39 -0400
Newsgroups: ianywhere.public.general
Subject: Re: Obect in SYS
Message-ID: <48B02EB26BC29D89000EFB9985256C54.0059EE7685256C53@webforums>
References: <DE7D9DC3D4D3C682001A057385256C53.001A058385256C53@webforums> <ysmIsAGdCHA.196@forums.sybase.com>
Lines: 21
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub ianywhere.public.general:539
Article PK: 2360

Hi,

I am trying to upgrade from ASA 5.5.04 (Build 1867) to ASA 8.0.1.2600 Long
back our programers have created lot of functions/procedures in SYS id bcoz
of some permission reason, now its very difficult to move these functions
to another id. Moving all these functions to another id means need program
change. Just now I try to create the follwoing function and was successful
in 5.5.04:

CREATE FUNCTION SYS.test ( aa numeric(2,0) )
RETURNS numeric(2,0)
BEGIN
DECLARE aa numeric(2,0);
set aa=1;
RETURN aa;
END

But in 8.0.1 it gives the following error:
com.sybase.jdbc2.jdbc.SybSQLException: ASA Error -121: Permission denied:
you do not have permission to use the "CREATE PROCEDURE" statement

The existing database engine I used is ASA 5.5.04 (Build 1867).


Robert Waywell Posted on 2002-10-16 15:08:35.0Z
From: "Robert Waywell" <rwaywell@ianywhere.com>
References: <DE7D9DC3D4D3C682001A057385256C53.001A058385256C53@webforums> <ysmIsAGdCHA.196@forums.sybase.com> <48B02EB26BC29D89000EFB9985256C54.0059EE7685256C53@webforums>
Subject: Re: Obect in SYS
Date: Wed, 16 Oct 2002 11:08:35 -0400
Lines: 53
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4522.1200
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200
Message-ID: <4#hD9fSdCHA.297@forums.sybase.com>
Newsgroups: ianywhere.public.general
NNTP-Posting-Host: 172.31.143.74
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub ianywhere.public.general:536
Article PK: 2354

Actually, this CREATE FUNCTION statement isn't allowed in 5.5.05 build
2778, so the bug that allowed you to create objects owned by SYS was also
fixed in the 5.5.x codeline.


--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer

<Vinod> wrote in message
news:48B02EB26BC29D89000EFB9985256C54.0059EE7685256C53@webforums...
> Hi,
>
> I am trying to upgrade from ASA 5.5.04 (Build 1867) to ASA 8.0.1.2600 Long
> back our programers have created lot of functions/procedures in SYS id
bcoz
> of some permission reason, now its very difficult to move these functions
> to another id. Moving all these functions to another id means need program
> change. Just now I try to create the follwoing function and was successful
> in 5.5.04:
>
> CREATE FUNCTION SYS.test ( aa numeric(2,0) )
> RETURNS numeric(2,0)
> BEGIN
> DECLARE aa numeric(2,0);
> set aa=1;
> RETURN aa;
> END
>
> But in 8.0.1 it gives the following error:
> com.sybase.jdbc2.jdbc.SybSQLException: ASA Error -121: Permission denied:
> you do not have permission to use the "CREATE PROCEDURE" statement
>
> The existing database engine I used is ASA 5.5.04 (Build 1867).


Stephen Rice Posted on 2002-10-15 15:36:06.0Z
Subject: Re: Obect in SYS
From: Stephen Rice <srice_nospam@ianywhere.com>
References: <DE7D9DC3D4D3C682001A057385256C53.001A058385256C53@webforums>
Organization: iAnywhere Solutions
Message-ID: <Xns92A8737D5CC5Csriceianywherecom@199.93.177.77>
User-Agent: Xnews/L5
Newsgroups: ianywhere.public.general
Date: Tue, 15 Oct 2002 11:36:06 -0400
Lines: 35
NNTP-Posting-Host: 172.31.141.72
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub ianywhere.public.general:541
Article PK: 2358

Vinod wrote in
news:DE7D9DC3D4D3C682001A057385256C53.001A058385256C53@webforums:

> Hi,
>
> I am in process to upgrade/move my existing database from ASA 5504 to
> ASA 8 (W2K). I have created new database and did reload successfully.
> But later I found that there are some objects created by application
> in SYS. Where I tried to create these object in SYS id it gives me the
> following error:
>
> ASA Error -121 Permission denied: you do not have permission to use
> the "CREATE FUNCTION" statement
>
> I have tried all the option to create in SYS, which I can't avoid.
> Since I am not much aware about application so I've to create them in
> SYS.
>
> Any clue how to create objects in SYS, any permission, option...
>
> Thanx in advance...
>
>

There is no way to directly create objects as SYS and this AFAIK
functionality has never existed so I'm at a bit confused as to what is
going on.

What objects are you trying to create?

The "proper" solution is to create the functions under an appropriate ID
and grant execute permissions to the ids that are using it. (just like
tables)

/steve

--
Stephen Rice
Technical Services Manager
iAnywhere Solutions

Whitepapers, TechDocs, bug fixes are all available through the
iAnywhere Developer Community at http://www.ianywhere.com/developer
------ Please Post ------


Vinod Posted on 2002-10-16 02:40:55.0Z
From: Vinod
Date: Tue, 15 Oct 2002 22:40:55 -0400
Newsgroups: ianywhere.public.general
Subject: Re: Obect in SYS
Message-ID: <066F39B85D97BF94000EBB7685256C54.0059EE7385256C53@webforums>
References: <DE7D9DC3D4D3C682001A057385256C53.001A058385256C53@webforums> <Xns92A8737D5CC5Csriceianywherecom@199.93.177.77>
Lines: 19
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub ianywhere.public.general:540
Article PK: 2359

Hi Stephen,

I am trying to upgrade from ASA 5.5.04 (Build 1867) to ASA 8.0.1.2600 Long
back our programers have created lot of functions/procedures in SYS id bcoz
of some permission reason, now its very difficult to move these functions
to another id. Moving all these functions to another id means need program
change. Just now I try to create the follwoing function and was successful
in 5.5.04:

CREATE FUNCTION SYS.test ( aa numeric(2,0) )
RETURNS numeric(2,0)
BEGIN
DECLARE aa numeric(2,0);
set aa=1;
RETURN aa;
END

But in 8.0.1 it gives the following error:
com.sybase.jdbc2.jdbc.SybSQLException: ASA Error -121: Permission denied:
you do not have permission to use the "CREATE PROCEDURE" statement