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.

select

14 posts in General Discussion Last posting was on 2012-03-22 16:38:32.0Z
gen Posted on 2012-03-02 03:09:22.0Z
Sender: 4490.4f5037f2.1804289383@sybase.com
From: gen
Newsgroups: sybase.public.ase.general
Subject: select
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f5039e2.44ff.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 1 Mar 2012 19:09:22 -0800
X-Trace: forums-1-dub 1330657762 172.20.134.41 (1 Mar 2012 19:09:22 -0800)
X-Original-Trace: 1 Mar 2012 19:09:22 -0800, 172.20.134.41
Lines: 17
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30962
Article PK: 73852

i have a genuine requirement and may be some of u good folks
can shed some light how this could be done.

following command is issued against table with 100K rows:
select * from tbl_01

can we have the select return 0 rows? at the same time any
insert/update/delete performed on tbl_01 should go through
successfully.

No revoking permission as that will error the select. None
of the statments select/insert/update/delete should error
out.

it sounds a bit crazy to me, but this is my current
requirement and my only option... why i will justify later.
can this be done?


Manish Negandhi [TeamSybase] Posted on 2012-03-02 03:16:58.0Z
From: "Manish Negandhi [TeamSybase]" <nospam_negandhi.manish@gmail.com>
Newsgroups: sybase.public.ase.general
References: <4f5039e2.44ff.1681692777@sybase.com>
Subject: Re: select
Lines: 37
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3664
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3664
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4f503baa$1@forums-1-dub>
Date: 1 Mar 2012 19:16:58 -0800
X-Trace: forums-1-dub 1330658218 10.22.241.152 (1 Mar 2012 19:16:58 -0800)
X-Original-Trace: 1 Mar 2012 19:16:58 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30963
Article PK: 73856

How about using RLAC i.e Row Level Access Control ?. If you use RLAC you
can control what kind of rows users can see. In your case you can simply
restrict user so that no rows are returned for the particular user but in
reality you still have other DML going through for those users who have been
granted permissions for RLAC



-HTH

Manish Negandhi

[TeamSybase]

<gen> wrote in message news:4f5039e2.44ff.1681692777@sybase.com...
>i have a genuine requirement and may be some of u good folks
> can shed some light how this could be done.
>
> following command is issued against table with 100K rows:
> select * from tbl_01
>
> can we have the select return 0 rows? at the same time any
> insert/update/delete performed on tbl_01 should go through
> successfully.
>
> No revoking permission as that will error the select. None
> of the statments select/insert/update/delete should error
> out.
>
> it sounds a bit crazy to me, but this is my current
> requirement and my only option... why i will justify later.
> can this be done?


"Mark A. Parsons" <iron_horse Posted on 2012-03-03 02:39:02.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: select
References: <4f503baa$1@forums-1-dub> <4f503d36.45c8.1681692777@sybase.com>
In-Reply-To: <4f503d36.45c8.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4f518446@forums-1-dub>
Date: 2 Mar 2012 18:39:02 -0800
X-Trace: forums-1-dub 1330742342 10.22.241.152 (2 Mar 2012 18:39:02 -0800)
X-Original-Trace: 2 Mar 2012 18:39:02 -0800, vip152.sybase.com
Lines: 134
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30970
Article PK: 73857

How many rows can this app modify at a time with an INSERT/UPDATE/DELETE? And are they simple INSERT/UPDATE/DELETE
commands, eg, they do not perform joins with any other tables? Does tbl_01 have any triggers?

Does this app access other tables where it does need to have rows returned by a SELECT?

Would it be acceptable to limit the SELECT to pulling back 1 row (as opposed to 0 rows)?

At some point you might want to explain why a) the app runs the SELECT, b) why you don't want it to receive any rows and
c) does hobbling the SELECT cause any problems for the app?

Oh, and how big is tbl_01?

=====================

If ...

- the application only affects 1 row with INSERT/UPDATE/DELETE commands and ...
- the application does not need to join with multiple rows from any other tables and ...
- and you're ok with sending 1 row back as a response to a SELECT then ...

- create a login trigger that issues 'set rowcount 1' and ...
- assign said login trigger to this application's ASE login

The downside is that the 'set rowcount 1' will apply to all queries and commands issued by the application, unless of
course the application overrides this be issuing a new 'set rowcount' command.

If ...

- the application never issues another SELECT and ...
- you need to allow the option of performing a multi-row INSERT/UPDATE/DELETE ...

- create a user-defined function (no input parameters are required) that issues a 'set rowcount 0' and 'set
export_options on' (assume it's named 'clear_rowcount') and ...
- alter tbl_01 and add a computed column like:

alter tbl_01 add clear_rowcount as dbo.clear_rowcount() not materialized

When the application logs in the login trigger will limit the first SELECT from tbl_01 to 1 row, the SELECT will fire
the clear_rowcount function which will 'set rowcount 0', so all follow on SQL statements will not be limited to a single
row.

====================

A variation on the login trigger/user-defined function idea ...

- same design but have the login trigger issue a 'set nodata on' instead of the 'set rowcount 1' and ...
- create a user-defined function (no input parameters required) that issues 'set nodata off' and 'set export_options on'
and (assume it's named 'disable_nodata') and ...
- add a non-materialized computed column to tbl_01 that calls dbo.disable_nodata()

While the initial SELECT will table scan tbl_01 it will not send any data back to the application. The initial SELECT
will trigger a call to dbo.disable_nodata() which will disable the trigger's 'set nodata on' thus allowing follow on SQL
statements to function as normal.

NOTE: 'set nodata on' is new with ASE 15, ie, it won't work in pre-15.

====================

Obviously (?) a variation of the previous ideas would combine the 'set rowcount 1' and 'set nodata on'.

For the initial SELECT this would eliminate the table scan in favor of pulling one row from the table, but said row
won't be returned to the application.

A compound 'set rowcount 0/set nodata off' user-defined function, added to tbl_01 as a non-materialized column, would
then allow follow on SQL statement to function as normal.

====================

If you can send an error back to the application without causing it to crash or run amok ...

- grant insert/update/delete on tbl_01 to the application's ASE login
- revoke select on tbl_01 from the application's ASE login

====================

On 03/01/2012 20:23, gen wrote:
> thanks for you reply. I thought about rlac but it dosent
> work in my case as there is just one user. moreover it is
> just one binary and tracing packets for this binary, the
> connection is made just once, followed by select and then
> update/insert/deletes.
>
> Let me make it more clear:
>
> 1) User01 connects
> 2) User01 runs select * from tbl_01
> 3) user01 runs other dms on tbl_01
> 4) user01 disconnects
>
> i want 2) to return 0 rows withour error, 3) should go
> through successfully updating/deleting qualified rows in
> where and insert should insert new rows and error only on
> duplicate keys.
>
>
>
>> How about using RLAC i.e Row Level Access Control ?. If
>> you use RLAC you can control what kind of rows users can
>> see. In your case you can simply restrict user so that no
>> rows are returned for the particular user but in reality
>> you still have other DML going through for those users who
>> have been granted permissions for RLAC
>>
>>
>>
>> -HTH
>>
>> Manish Negandhi
>>
>> [TeamSybase]
>>
>>
>>
>>
>> <gen> wrote in message
>> news:4f5039e2.44ff.1681692777@sybase.com...>i have a
>>> genuine requirement and may be some of u good folks can
>> shed some light how this could be done.>
>>> following command is issued against table with 100K
>>> rows: select * from tbl_01
>>>
>>> can we have the select return 0 rows? at the same time
>>> any insert/update/delete performed on tbl_01 should go
>>> through successfully.
>>>
>>> No revoking permission as that will error the select.
>>> None of the statments select/insert/update/delete should
>>> error out.
>>>
>>> it sounds a bit crazy to me, but this is my current
>>> requirement and my only option... why i will justify
>>> later. can this be done?
>>
>>


gen Posted on 2012-03-03 20:24:42.0Z
Sender: 3628.4f527794.1804289383@sybase.com
From: gen
Newsgroups: sybase.public.ase.general
Subject: Re: select
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f527e0a.370f.1681692777@sybase.com>
References: <4f518446@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 3 Mar 2012 12:24:42 -0800
X-Trace: forums-1-dub 1330806282 172.20.134.41 (3 Mar 2012 12:24:42 -0800)
X-Original-Trace: 3 Mar 2012 12:24:42 -0800, 172.20.134.41
Lines: 207
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30972
Article PK: 73862

I guess i should state the issue rather than ask how
something can be done based on my understanding.

The app is Delphi binary. We have 14 binaries for different
modules which gets called by start.exe binary. vendor is not
available, sold/banckrupt/vanished. App source code is not
available. It is "check" scanning and mainting system(check
are getting obsolete)

All module works fine all throughout the year except Scan
module.

Check-Scan module or .exe scans images (inserts image into
table tbl_01), Monitoring all SQLs for this .exe/module
using montables we see 'this module run select * from
tbl_01" at start - based on sql's, we see no need for app to
do this. It just scans, other modules picks up the entry and
processes the scanned checks. We tested by 1) truncating the
tbl_01 table and 2) running the Scan module, and everything
scanned fine(we cant truncate the table in prod though).

If we had code, or access to it, it is a simple solution -
but we dont.

so I was looking for some way to see if anything could be
done on DB side, do avoid the select returning all rows from
tbl_01 when this module runs.

Not an option: app upgrade, code change (mgmt is strictly
against investing in system which will be obsolete in future
a.k.a checks). mgmt is fine with the agony it cause for IT
and scan operators.

We know the issue, we know the solution, but no simple way
to implement it i guess. Some might suggest to truncate/trim
the table, but the post date check volumne is large i.e.
these cannot be deleted.

> How many rows can this app modify at a time with an
> INSERT/UPDATE/DELETE? And are they simple
> INSERT/UPDATE/DELETE commands, eg, they do not perform
> joins with any other tables? Does tbl_01 have any
> triggers?
>
> Does this app access other tables where it does need to
> have rows returned by a SELECT?
>
> Would it be acceptable to limit the SELECT to pulling back
> 1 row (as opposed to 0 rows)?
>
> At some point you might want to explain why a) the app
> runs the SELECT, b) why you don't want it to receive any
> rows and c) does hobbling the SELECT cause any problems
> for the app?
>
> Oh, and how big is tbl_01?
>
> =====================
>
> If ...
>
> - the application only affects 1 row with
> INSERT/UPDATE/DELETE commands and ... - the application
> does not need to join with multiple rows from any other
> tables and ... - and you're ok with sending 1 row back as
> a response to a SELECT then ...
>
> - create a login trigger that issues 'set rowcount 1' and
> .. - assign said login trigger to this application's ASE
> login
>
> The downside is that the 'set rowcount 1' will apply to
> all queries and commands issued by the application, unless
> of course the application overrides this be issuing a new
> 'set rowcount' command.
>
> If ...
>
> - the application never issues another SELECT and ...
> - you need to allow the option of performing a multi-row
> INSERT/UPDATE/DELETE ...
>
> - create a user-defined function (no input parameters are
> required) that issues a 'set rowcount 0' and 'set
> export_options on' (assume it's named 'clear_rowcount')
> and ... - alter tbl_01 and add a computed column like:
>
> alter tbl_01 add clear_rowcount as
> dbo.clear_rowcount() not materialized
>
> When the application logs in the login trigger will limit
> the first SELECT from tbl_01 to 1 row, the SELECT will
> fire the clear_rowcount function which will 'set rowcount
> 0', so all follow on SQL statements will not be limited to
> a single row.
>
> ====================
>
> A variation on the login trigger/user-defined function
> idea ...
>
> - same design but have the login trigger issue a 'set
> nodata on' instead of the 'set rowcount 1' and ... -
> create a user-defined function (no input parameters
> required) that issues 'set nodata off' and 'set
> export_options on' and (assume it's named
> 'disable_nodata') and ... - add a non-materialized
> computed column to tbl_01 that calls dbo.disable_nodata()
>
> While the initial SELECT will table scan tbl_01 it will
> not send any data back to the application. The initial
> SELECT will trigger a call to dbo.disable_nodata() which
> will disable the trigger's 'set nodata on' thus allowing
> follow on SQL statements to function as normal.
>
> NOTE: 'set nodata on' is new with ASE 15, ie, it won't
> work in pre-15.
>
> ====================
>
> Obviously (?) a variation of the previous ideas would
> combine the 'set rowcount 1' and 'set nodata on'.
>
> For the initial SELECT this would eliminate the table scan
> in favor of pulling one row from the table, but said row
> won't be returned to the application.
>
> A compound 'set rowcount 0/set nodata off' user-defined
> function, added to tbl_01 as a non-materialized column,
> would then allow follow on SQL statement to function as
> normal.
>
> ====================
>
> If you can send an error back to the application without
> causing it to crash or run amok ...
>
> - grant insert/update/delete on tbl_01 to the
> application's ASE login - revoke select on tbl_01 from the
> application's ASE login
>
> ====================
>
> On 03/01/2012 20:23, gen wrote:
> > thanks for you reply. I thought about rlac but it dosent
> > work in my case as there is just one user. moreover it
> > is just one binary and tracing packets for this binary,
> > the connection is made just once, followed by select and
> > then update/insert/deletes.
> >
> > Let me make it more clear:
> >
> > 1) User01 connects
> > 2) User01 runs select * from tbl_01
> > 3) user01 runs other dms on tbl_01
> > 4) user01 disconnects
> >
> > i want 2) to return 0 rows withour error, 3) should go
> > through successfully updating/deleting qualified rows in
> > where and insert should insert new rows and error only
> > on duplicate keys.
> >
> >
> >
> >> How about using RLAC i.e Row Level Access Control ?.
> If >> you use RLAC you can control what kind of rows
> users can >> see. In your case you can simply restrict
> user so that no >> rows are returned for the particular
> user but in reality >> you still have other DML going
> through for those users who >> have been granted
> permissions for RLAC >>
> >>
> >>
> >> -HTH
> >>
> >> Manish Negandhi
> >>
> >> [TeamSybase]
> >>
> >>
> >>
> >>
> >> <gen> wrote in message
> >> news:4f5039e2.44ff.1681692777@sybase.com...>i have a
> >>> genuine requirement and may be some of u good folks
> can >> shed some light how this could be done.>
> >>> following command is issued against table with 100K
> >>> rows: select * from tbl_01
> >>>
> >>> can we have the select return 0 rows? at the same time
> >>> any insert/update/delete performed on tbl_01 should go
> >>> through successfully.
> >>>
> >>> No revoking permission as that will error the select.
> >>> None of the statments select/insert/update/delete
> should >>> error out.
> >>>
> >>> it sounds a bit crazy to me, but this is my current
> >>> requirement and my only option... why i will justify
> >>> later. can this be done?
> >>
> >>


Manish Negandhi [TeamSybase] Posted on 2012-03-04 18:38:01.0Z
From: "Manish Negandhi [TeamSybase]" <nospam_negandhi.manish@gmail.com>
Newsgroups: sybase.public.ase.general
References: <4f518446@forums-1-dub> <4f527e0a.370f.1681692777@sybase.com>
Subject: Re: select
Lines: 245
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3664
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3664
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4f53b689$1@forums-1-dub>
Date: 4 Mar 2012 10:38:01 -0800
X-Trace: forums-1-dub 1330886281 10.22.241.152 (4 Mar 2012 10:38:01 -0800)
X-Original-Trace: 4 Mar 2012 10:38:01 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30975
Article PK: 73861

In the below mentioned case, is it possible for you to delay execution of
"other module" which picks up the entry and processes the scanned checks ?

If so you can create a dummy table using the same user id which executes all
these commands against given db and drop the table before any DMLs take
place



For example create table user1.tbl_01 using same user which executes select
( i.e. other than dbo user ) and hence you will have 2 tables in your db
(1) dbo.tbl_01 (2) user1.tbl_01

In this case when user1 executes select , the select will return row from
user1.tbl1_01 and hence no rows are returned and before user1 can execute
any DMLs you just drop the table user1.tbl_01 hence all subsequent DMLs
should go through against real table..





-HTH

Manish Negandhi

[TeamSybase]

<gen> wrote in message news:4f527e0a.370f.1681692777@sybase.com...
>I guess i should state the issue rather than ask how
> something can be done based on my understanding.
>
> The app is Delphi binary. We have 14 binaries for different
> modules which gets called by start.exe binary. vendor is not
> available, sold/banckrupt/vanished. App source code is not
> available. It is "check" scanning and mainting system(check
> are getting obsolete)
>
> All module works fine all throughout the year except Scan
> module.
>
> Check-Scan module or .exe scans images (inserts image into
> table tbl_01), Monitoring all SQLs for this .exe/module
> using montables we see 'this module run select * from
> tbl_01" at start - based on sql's, we see no need for app to
> do this. It just scans, other modules picks up the entry and
> processes the scanned checks. We tested by 1) truncating the
> tbl_01 table and 2) running the Scan module, and everything
> scanned fine(we cant truncate the table in prod though).
>
> If we had code, or access to it, it is a simple solution -
> but we dont.
>
> so I was looking for some way to see if anything could be
> done on DB side, do avoid the select returning all rows from
> tbl_01 when this module runs.
>
> Not an option: app upgrade, code change (mgmt is strictly
> against investing in system which will be obsolete in future
> a.k.a checks). mgmt is fine with the agony it cause for IT
> and scan operators.
>
> We know the issue, we know the solution, but no simple way
> to implement it i guess. Some might suggest to truncate/trim
> the table, but the post date check volumne is large i.e.
> these cannot be deleted.
>
>
>
>
>
>
>> How many rows can this app modify at a time with an
>> INSERT/UPDATE/DELETE? And are they simple
>> INSERT/UPDATE/DELETE commands, eg, they do not perform
>> joins with any other tables? Does tbl_01 have any
>> triggers?
>>
>> Does this app access other tables where it does need to
>> have rows returned by a SELECT?
>>
>> Would it be acceptable to limit the SELECT to pulling back
>> 1 row (as opposed to 0 rows)?
>>
>> At some point you might want to explain why a) the app
>> runs the SELECT, b) why you don't want it to receive any
>> rows and c) does hobbling the SELECT cause any problems
>> for the app?
>>
>> Oh, and how big is tbl_01?
>>
>> =====================
>>
>> If ...
>>
>> - the application only affects 1 row with
>> INSERT/UPDATE/DELETE commands and ... - the application
>> does not need to join with multiple rows from any other
>> tables and ... - and you're ok with sending 1 row back as
>> a response to a SELECT then ...
>>
>> - create a login trigger that issues 'set rowcount 1' and
>> .. - assign said login trigger to this application's ASE
>> login
>>
>> The downside is that the 'set rowcount 1' will apply to
>> all queries and commands issued by the application, unless
>> of course the application overrides this be issuing a new
>> 'set rowcount' command.
>>
>> If ...
>>
>> - the application never issues another SELECT and ...
>> - you need to allow the option of performing a multi-row
>> INSERT/UPDATE/DELETE ...
>>
>> - create a user-defined function (no input parameters are
>> required) that issues a 'set rowcount 0' and 'set
>> export_options on' (assume it's named 'clear_rowcount')
>> and ... - alter tbl_01 and add a computed column like:
>>
>> alter tbl_01 add clear_rowcount as
>> dbo.clear_rowcount() not materialized
>>
>> When the application logs in the login trigger will limit
>> the first SELECT from tbl_01 to 1 row, the SELECT will
>> fire the clear_rowcount function which will 'set rowcount
>> 0', so all follow on SQL statements will not be limited to
>> a single row.
>>
>> ====================
>>
>> A variation on the login trigger/user-defined function
>> idea ...
>>
>> - same design but have the login trigger issue a 'set
>> nodata on' instead of the 'set rowcount 1' and ... -
>> create a user-defined function (no input parameters
>> required) that issues 'set nodata off' and 'set
>> export_options on' and (assume it's named
>> 'disable_nodata') and ... - add a non-materialized
>> computed column to tbl_01 that calls dbo.disable_nodata()
>>
>> While the initial SELECT will table scan tbl_01 it will
>> not send any data back to the application. The initial
>> SELECT will trigger a call to dbo.disable_nodata() which
>> will disable the trigger's 'set nodata on' thus allowing
>> follow on SQL statements to function as normal.
>>
>> NOTE: 'set nodata on' is new with ASE 15, ie, it won't
>> work in pre-15.
>>
>> ====================
>>
>> Obviously (?) a variation of the previous ideas would
>> combine the 'set rowcount 1' and 'set nodata on'.
>>
>> For the initial SELECT this would eliminate the table scan
>> in favor of pulling one row from the table, but said row
>> won't be returned to the application.
>>
>> A compound 'set rowcount 0/set nodata off' user-defined
>> function, added to tbl_01 as a non-materialized column,
>> would then allow follow on SQL statement to function as
>> normal.
>>
>> ====================
>>
>> If you can send an error back to the application without
>> causing it to crash or run amok ...
>>
>> - grant insert/update/delete on tbl_01 to the
>> application's ASE login - revoke select on tbl_01 from the
>> application's ASE login
>>
>> ====================
>>
>> On 03/01/2012 20:23, gen wrote:
>> > thanks for you reply. I thought about rlac but it dosent
>> > work in my case as there is just one user. moreover it
>> > is just one binary and tracing packets for this binary,
>> > the connection is made just once, followed by select and
>> > then update/insert/deletes.
>> >
>> > Let me make it more clear:
>> >
>> > 1) User01 connects
>> > 2) User01 runs select * from tbl_01
>> > 3) user01 runs other dms on tbl_01
>> > 4) user01 disconnects
>> >
>> > i want 2) to return 0 rows withour error, 3) should go
>> > through successfully updating/deleting qualified rows in
>> > where and insert should insert new rows and error only
>> > on duplicate keys.
>> >
>> >
>> >
>> >> How about using RLAC i.e Row Level Access Control ?.
>> If >> you use RLAC you can control what kind of rows
>> users can >> see. In your case you can simply restrict
>> user so that no >> rows are returned for the particular
>> user but in reality >> you still have other DML going
>> through for those users who >> have been granted
>> permissions for RLAC >>
>> >>
>> >>
>> >> -HTH
>> >>
>> >> Manish Negandhi
>> >>
>> >> [TeamSybase]
>> >>
>> >>
>> >>
>> >>
>> >> <gen> wrote in message
>> >> news:4f5039e2.44ff.1681692777@sybase.com...>i have a
>> >>> genuine requirement and may be some of u good folks
>> can >> shed some light how this could be done.>
>> >>> following command is issued against table with 100K
>> >>> rows: select * from tbl_01
>> >>>
>> >>> can we have the select return 0 rows? at the same time
>> >>> any insert/update/delete performed on tbl_01 should go
>> >>> through successfully.
>> >>>
>> >>> No revoking permission as that will error the select.
>> >>> None of the statments select/insert/update/delete
>> should >>> error out.
>> >>>
>> >>> it sounds a bit crazy to me, but this is my current
>> >>> requirement and my only option... why i will justify
>> >>> later. can this be done?
>> >>
>> >>


"Mark A. Parsons" <iron_horse Posted on 2012-03-03 21:15:06.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: select
References: <4f518446@forums-1-dub> <4f527e0a.370f.1681692777@sybase.com>
In-Reply-To: <4f527e0a.370f.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4f5289da@forums-1-dub>
Date: 3 Mar 2012 13:15:06 -0800
X-Trace: forums-1-dub 1330809306 10.22.241.152 (3 Mar 2012 13:15:06 -0800)
X-Original-Trace: 3 Mar 2012 13:15:06 -0800, vip152.sybase.com
Lines: 215
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30973
Article PK: 73863

While not ideal, I did provide a few workarounds that would reduce/eliminate the overhead of the SELECT ... but even
though workarounds require some modifications (albeit no changes to the application).

If you're saying that no changes can be made to the database ... then you'll pretty much have to live with what you've
got as there isn't any magic button that says 'do not return any rows for a SELECT by user XXXX'.

On 03/03/2012 13:24, gen wrote:
> I guess i should state the issue rather than ask how
> something can be done based on my understanding.
>
> The app is Delphi binary. We have 14 binaries for different
> modules which gets called by start.exe binary. vendor is not
> available, sold/banckrupt/vanished. App source code is not
> available. It is "check" scanning and mainting system(check
> are getting obsolete)
>
> All module works fine all throughout the year except Scan
> module.
>
> Check-Scan module or .exe scans images (inserts image into
> table tbl_01), Monitoring all SQLs for this .exe/module
> using montables we see 'this module run select * from
> tbl_01" at start - based on sql's, we see no need for app to
> do this. It just scans, other modules picks up the entry and
> processes the scanned checks. We tested by 1) truncating the
> tbl_01 table and 2) running the Scan module, and everything
> scanned fine(we cant truncate the table in prod though).
>
> If we had code, or access to it, it is a simple solution -
> but we dont.
>
> so I was looking for some way to see if anything could be
> done on DB side, do avoid the select returning all rows from
> tbl_01 when this module runs.
>
> Not an option: app upgrade, code change (mgmt is strictly
> against investing in system which will be obsolete in future
> a.k.a checks). mgmt is fine with the agony it cause for IT
> and scan operators.
>
> We know the issue, we know the solution, but no simple way
> to implement it i guess. Some might suggest to truncate/trim
> the table, but the post date check volumne is large i.e.
> these cannot be deleted.
>
>
>
>
>
>
>> How many rows can this app modify at a time with an
>> INSERT/UPDATE/DELETE? And are they simple
>> INSERT/UPDATE/DELETE commands, eg, they do not perform
>> joins with any other tables? Does tbl_01 have any
>> triggers?
>>
>> Does this app access other tables where it does need to
>> have rows returned by a SELECT?
>>
>> Would it be acceptable to limit the SELECT to pulling back
>> 1 row (as opposed to 0 rows)?
>>
>> At some point you might want to explain why a) the app
>> runs the SELECT, b) why you don't want it to receive any
>> rows and c) does hobbling the SELECT cause any problems
>> for the app?
>>
>> Oh, and how big is tbl_01?
>>
>> =====================
>>
>> If ...
>>
>> - the application only affects 1 row with
>> INSERT/UPDATE/DELETE commands and ... - the application
>> does not need to join with multiple rows from any other
>> tables and ... - and you're ok with sending 1 row back as
>> a response to a SELECT then ...
>>
>> - create a login trigger that issues 'set rowcount 1' and
>> .. - assign said login trigger to this application's ASE
>> login
>>
>> The downside is that the 'set rowcount 1' will apply to
>> all queries and commands issued by the application, unless
>> of course the application overrides this be issuing a new
>> 'set rowcount' command.
>>
>> If ...
>>
>> - the application never issues another SELECT and ...
>> - you need to allow the option of performing a multi-row
>> INSERT/UPDATE/DELETE ...
>>
>> - create a user-defined function (no input parameters are
>> required) that issues a 'set rowcount 0' and 'set
>> export_options on' (assume it's named 'clear_rowcount')
>> and ... - alter tbl_01 and add a computed column like:
>>
>> alter tbl_01 add clear_rowcount as
>> dbo.clear_rowcount() not materialized
>>
>> When the application logs in the login trigger will limit
>> the first SELECT from tbl_01 to 1 row, the SELECT will
>> fire the clear_rowcount function which will 'set rowcount
>> 0', so all follow on SQL statements will not be limited to
>> a single row.
>>
>> ====================
>>
>> A variation on the login trigger/user-defined function
>> idea ...
>>
>> - same design but have the login trigger issue a 'set
>> nodata on' instead of the 'set rowcount 1' and ... -
>> create a user-defined function (no input parameters
>> required) that issues 'set nodata off' and 'set
>> export_options on' and (assume it's named
>> 'disable_nodata') and ... - add a non-materialized
>> computed column to tbl_01 that calls dbo.disable_nodata()
>>
>> While the initial SELECT will table scan tbl_01 it will
>> not send any data back to the application. The initial
>> SELECT will trigger a call to dbo.disable_nodata() which
>> will disable the trigger's 'set nodata on' thus allowing
>> follow on SQL statements to function as normal.
>>
>> NOTE: 'set nodata on' is new with ASE 15, ie, it won't
>> work in pre-15.
>>
>> ====================
>>
>> Obviously (?) a variation of the previous ideas would
>> combine the 'set rowcount 1' and 'set nodata on'.
>>
>> For the initial SELECT this would eliminate the table scan
>> in favor of pulling one row from the table, but said row
>> won't be returned to the application.
>>
>> A compound 'set rowcount 0/set nodata off' user-defined
>> function, added to tbl_01 as a non-materialized column,
>> would then allow follow on SQL statement to function as
>> normal.
>>
>> ====================
>>
>> If you can send an error back to the application without
>> causing it to crash or run amok ...
>>
>> - grant insert/update/delete on tbl_01 to the
>> application's ASE login - revoke select on tbl_01 from the
>> application's ASE login
>>
>> ====================
>>
>> On 03/01/2012 20:23, gen wrote:
>>> thanks for you reply. I thought about rlac but it dosent
>>> work in my case as there is just one user. moreover it
>>> is just one binary and tracing packets for this binary,
>>> the connection is made just once, followed by select and
>>> then update/insert/deletes.
>>>
>>> Let me make it more clear:
>>>
>>> 1) User01 connects
>>> 2) User01 runs select * from tbl_01
>>> 3) user01 runs other dms on tbl_01
>>> 4) user01 disconnects
>>>
>>> i want 2) to return 0 rows withour error, 3) should go
>>> through successfully updating/deleting qualified rows in
>>> where and insert should insert new rows and error only
>>> on duplicate keys.
>>>
>>>
>>>
>>>> How about using RLAC i.e Row Level Access Control ?.
>> If>> you use RLAC you can control what kind of rows
>> users can>> see. In your case you can simply restrict
>> user so that no>> rows are returned for the particular
>> user but in reality>> you still have other DML going
>> through for those users who>> have been granted
>> permissions for RLAC>>
>>>>
>>>>
>>>> -HTH
>>>>
>>>> Manish Negandhi
>>>>
>>>> [TeamSybase]
>>>>
>>>>
>>>>
>>>>
>>>> <gen> wrote in message
>>>> news:4f5039e2.44ff.1681692777@sybase.com...>i have a
>>>>> genuine requirement and may be some of u good folks
>> can>> shed some light how this could be done.>
>>>>> following command is issued against table with 100K
>>>>> rows: select * from tbl_01
>>>>>
>>>>> can we have the select return 0 rows? at the same time
>>>>> any insert/update/delete performed on tbl_01 should go
>>>>> through successfully.
>>>>>
>>>>> No revoking permission as that will error the select.
>>>>> None of the statments select/insert/update/delete
>> should>>> error out.
>>>>>
>>>>> it sounds a bit crazy to me, but this is my current
>>>>> requirement and my only option... why i will justify
>>>>> later. can this be done?
>>>>
>>>>


gen Posted on 2012-03-04 00:56:12.0Z
Sender: 40a6.4f52bc83.1804289383@sybase.com
From: gen
Newsgroups: sybase.public.ase.general
Subject: Re: select
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f52bdac.4119.1681692777@sybase.com>
References: <4f5289da@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 3 Mar 2012 16:56:12 -0800
X-Trace: forums-1-dub 1330822572 172.20.134.41 (3 Mar 2012 16:56:12 -0800)
X-Original-Trace: 3 Mar 2012 16:56:12 -0800, 172.20.134.41
Lines: 229
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30974
Article PK: 73864

DB is the only place i can make the changes, i was testing
your ideas and login trigger with set rowcount 1 seems to be
one workaround which might work.... didnt see any errors so
far

Adding additional column is creating errors in some
moudles...

login trigger might be a sound workaround hopefully...

> While not ideal, I did provide a few workarounds that
> would reduce/eliminate the overhead of the SELECT ... but
> even though workarounds require some modifications
> (albeit no changes to the application).
>
> If you're saying that no changes can be made to the
> database ... then you'll pretty much have to live with
> what you've got as there isn't any magic button that says
> 'do not return any rows for a SELECT by user XXXX'.
>
>
> On 03/03/2012 13:24, gen wrote:
> > I guess i should state the issue rather than ask how
> > something can be done based on my understanding.
> >
> > The app is Delphi binary. We have 14 binaries for
> > different modules which gets called by start.exe binary.
> > vendor is not available, sold/banckrupt/vanished. App
> > source code is not available. It is "check" scanning and
> > mainting system(check are getting obsolete)
> >
> > All module works fine all throughout the year except
> > Scan module.
> >
> > Check-Scan module or .exe scans images (inserts image
> > into table tbl_01), Monitoring all SQLs for this
> > .exe/module using montables we see 'this module run
> > select * from tbl_01" at start - based on sql's, we see
> > no need for app to do this. It just scans, other modules
> > picks up the entry and processes the scanned checks. We
> > tested by 1) truncating the tbl_01 table and 2) running
> > the Scan module, and everything scanned fine(we cant
> truncate the table in prod though). >
> > If we had code, or access to it, it is a simple solution
> > - but we dont.
> >
> > so I was looking for some way to see if anything could
> > be done on DB side, do avoid the select returning all
> > rows from tbl_01 when this module runs.
> >
> > Not an option: app upgrade, code change (mgmt is
> > strictly against investing in system which will be
> > obsolete in future a.k.a checks). mgmt is fine with the
> > agony it cause for IT and scan operators.
> >
> > We know the issue, we know the solution, but no simple
> > way to implement it i guess. Some might suggest to
> > truncate/trim the table, but the post date check volumne
> > is large i.e. these cannot be deleted.
> >
> >
> >
> >
> >
> >
> >> How many rows can this app modify at a time with an
> >> INSERT/UPDATE/DELETE? And are they simple
> >> INSERT/UPDATE/DELETE commands, eg, they do not perform
> >> joins with any other tables? Does tbl_01 have any
> >> triggers?
> >>
> >> Does this app access other tables where it does need to
> >> have rows returned by a SELECT?
> >>
> >> Would it be acceptable to limit the SELECT to pulling
> back >> 1 row (as opposed to 0 rows)?
> >>
> >> At some point you might want to explain why a) the app
> >> runs the SELECT, b) why you don't want it to receive
> any >> rows and c) does hobbling the SELECT cause any
> problems >> for the app?
> >>
> >> Oh, and how big is tbl_01?
> >>
> >> =====================
> >>
> >> If ...
> >>
> >> - the application only affects 1 row with
> >> INSERT/UPDATE/DELETE commands and ... - the application
> >> does not need to join with multiple rows from any other
> >> tables and ... - and you're ok with sending 1 row back
> as >> a response to a SELECT then ...
> >>
> >> - create a login trigger that issues 'set rowcount 1'
> and >> .. - assign said login trigger to this
> application's ASE >> login
> >>
> >> The downside is that the 'set rowcount 1' will apply to
> >> all queries and commands issued by the application,
> unless >> of course the application overrides this be
> issuing a new >> 'set rowcount' command.
> >>
> >> If ...
> >>
> >> - the application never issues another SELECT and ...
> >> - you need to allow the option of performing a
> multi-row >> INSERT/UPDATE/DELETE ...
> >>
> >> - create a user-defined function (no input parameters
> are >> required) that issues a 'set rowcount 0' and 'set
> >> export_options on' (assume it's named 'clear_rowcount')
> >> and ... - alter tbl_01 and add a computed column like:
> >>
> >> alter tbl_01 add clear_rowcount as
> >> dbo.clear_rowcount() not materialized
> >>
> >> When the application logs in the login trigger will
> limit >> the first SELECT from tbl_01 to 1 row, the SELECT
> will >> fire the clear_rowcount function which will 'set
> rowcount >> 0', so all follow on SQL statements will not
> be limited to >> a single row.
> >>
> >> ====================
> >>
> >> A variation on the login trigger/user-defined function
> >> idea ...
> >>
> >> - same design but have the login trigger issue a 'set
> >> nodata on' instead of the 'set rowcount 1' and ... -
> >> create a user-defined function (no input parameters
> >> required) that issues 'set nodata off' and 'set
> >> export_options on' and (assume it's named
> >> 'disable_nodata') and ... - add a non-materialized
> >> computed column to tbl_01 that calls
> dbo.disable_nodata() >>
> >> While the initial SELECT will table scan tbl_01 it will
> >> not send any data back to the application. The initial
> >> SELECT will trigger a call to dbo.disable_nodata()
> which >> will disable the trigger's 'set nodata on' thus
> allowing >> follow on SQL statements to function as
> normal. >>
> >> NOTE: 'set nodata on' is new with ASE 15, ie, it won't
> >> work in pre-15.
> >>
> >> ====================
> >>
> >> Obviously (?) a variation of the previous ideas would
> >> combine the 'set rowcount 1' and 'set nodata on'.
> >>
> >> For the initial SELECT this would eliminate the table
> scan >> in favor of pulling one row from the table, but
> said row >> won't be returned to the application.
> >>
> >> A compound 'set rowcount 0/set nodata off' user-defined
> >> function, added to tbl_01 as a non-materialized column,
> >> would then allow follow on SQL statement to function
> as >> normal.
> >>
> >> ====================
> >>
> >> If you can send an error back to the application
> without >> causing it to crash or run amok ...
> >>
> >> - grant insert/update/delete on tbl_01 to the
> >> application's ASE login - revoke select on tbl_01 from
> the >> application's ASE login
> >>
> >> ====================
> >>
> >> On 03/01/2012 20:23, gen wrote:
> >>> thanks for you reply. I thought about rlac but it
> dosent >>> work in my case as there is just one user.
> moreover it >>> is just one binary and tracing packets for
> this binary, >>> the connection is made just once,
> followed by select and >>> then update/insert/deletes.
> >>>
> >>> Let me make it more clear:
> >>>
> >>> 1) User01 connects
> >>> 2) User01 runs select * from tbl_01
> >>> 3) user01 runs other dms on tbl_01
> >>> 4) user01 disconnects
> >>>
> >>> i want 2) to return 0 rows withour error, 3) should go
> >>> through successfully updating/deleting qualified rows
> in >>> where and insert should insert new rows and error
> only >>> on duplicate keys.
> >>>
> >>>
> >>>
> >>>> How about using RLAC i.e Row Level Access Control
> ?. >> If>> you use RLAC you can control what kind of
> rows >> users can>> see. In your case you can simply
> restrict >> user so that no>> rows are returned for the
> particular >> user but in reality>> you still have other
> DML going >> through for those users who>> have been
> granted >> permissions for RLAC>>
> >>>>
> >>>>
> >>>> -HTH
> >>>>
> >>>> Manish Negandhi
> >>>>
> >>>> [TeamSybase]
> >>>>
> >>>>
> >>>>
> >>>>
> >>>> <gen> wrote in message
> >>>> news:4f5039e2.44ff.1681692777@sybase.com...>i have a
> >>>>> genuine requirement and may be some of u good folks
> >> can>> shed some light how this could be done.>
> >>>>> following command is issued against table with 100K
> >>>>> rows: select * from tbl_01
> >>>>>
> >>>>> can we have the select return 0 rows? at the same
> time >>>>> any insert/update/delete performed on tbl_01
> should go >>>>> through successfully.
> >>>>>
> >>>>> No revoking permission as that will error the
> select. >>>>> None of the statments
> select/insert/update/delete >> should>>> error out.
> >>>>>
> >>>>> it sounds a bit crazy to me, but this is my current
> >>>>> requirement and my only option... why i will justify
> >>>>> later. can this be done?
> >>>>
> >>>>


gen Posted on 2012-03-04 21:49:24.0Z
Sender: 1e68.4f53e273.1804289383@sybase.com
From: gen
Newsgroups: sybase.public.ase.general
Subject: Re: select
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f53e364.1e89.1681692777@sybase.com>
References: <4f52bdac.4119.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 4 Mar 2012 13:49:24 -0800
X-Trace: forums-1-dub 1330897764 172.20.134.41 (4 Mar 2012 13:49:24 -0800)
X-Original-Trace: 4 Mar 2012 13:49:24 -0800, 172.20.134.41
Lines: 234
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30976
Article PK: 73868

set rowcount 1 in login trigger did not work , as module did
not error but the data got messed up, there are other
non-image tables where update and delete touch more than 1
rows. it was but worth trying though.

> DB is the only place i can make the changes, i was testing
> your ideas and login trigger with set rowcount 1 seems to
> be one workaround which might work.... didnt see any
> errors so far
>
> Adding additional column is creating errors in some
> moudles...
>
> login trigger might be a sound workaround hopefully...
>
> > While not ideal, I did provide a few workarounds that
> > would reduce/eliminate the overhead of the SELECT ...
> > but even though workarounds require some modifications
> > (albeit no changes to the application).
> >
> > If you're saying that no changes can be made to the
> > database ... then you'll pretty much have to live with
> > what you've got as there isn't any magic button that
> > says 'do not return any rows for a SELECT by user XXXX'.
> >
> >
> > On 03/03/2012 13:24, gen wrote:
> > > I guess i should state the issue rather than ask how
> > > something can be done based on my understanding.
> > >
> > > The app is Delphi binary. We have 14 binaries for
> > > different modules which gets called by start.exe
> > > binary. vendor is not available,
> > > sold/banckrupt/vanished. App source code is not
> > > available. It is "check" scanning and mainting
> > system(check are getting obsolete) >
> > > All module works fine all throughout the year except
> > > Scan module.
> > >
> > > Check-Scan module or .exe scans images (inserts image
> > > into table tbl_01), Monitoring all SQLs for this
> > > .exe/module using montables we see 'this module run
> > > select * from tbl_01" at start - based on sql's, we
> > > see no need for app to do this. It just scans, other
> > > modules picks up the entry and processes the scanned
> > > checks. We tested by 1) truncating the tbl_01 table
> > > and 2) running the Scan module, and everything scanned
> > fine(we cant truncate the table in prod though). >
> > > If we had code, or access to it, it is a simple
> > > solution - but we dont.
> > >
> > > so I was looking for some way to see if anything could
> > > be done on DB side, do avoid the select returning all
> > > rows from tbl_01 when this module runs.
> > >
> > > Not an option: app upgrade, code change (mgmt is
> > > strictly against investing in system which will be
> > > obsolete in future a.k.a checks). mgmt is fine with
> > > the agony it cause for IT and scan operators.
> > >
> > > We know the issue, we know the solution, but no simple
> > > way to implement it i guess. Some might suggest to
> > > truncate/trim the table, but the post date check
> > > volumne is large i.e. these cannot be deleted.
> > >
> > >
> > >
> > >
> > >
> > >
> > >> How many rows can this app modify at a time with an
> > >> INSERT/UPDATE/DELETE? And are they simple
> > >> INSERT/UPDATE/DELETE commands, eg, they do not
> > perform >> joins with any other tables? Does tbl_01
> > have any >> triggers?
> > >>
> > >> Does this app access other tables where it does need
> > to >> have rows returned by a SELECT?
> > >>
> > >> Would it be acceptable to limit the SELECT to pulling
> > back >> 1 row (as opposed to 0 rows)?
> > >>
> > >> At some point you might want to explain why a) the
> > app >> runs the SELECT, b) why you don't want it to
> > receive any >> rows and c) does hobbling the SELECT
> > cause any problems >> for the app?
> > >>
> > >> Oh, and how big is tbl_01?
> > >>
> > >> =====================
> > >>
> > >> If ...
> > >>
> > >> - the application only affects 1 row with
> > >> INSERT/UPDATE/DELETE commands and ... - the
> > application >> does not need to join with multiple rows
> > from any other >> tables and ... - and you're ok with
> > sending 1 row back as >> a response to a SELECT then ...
> > >>
> > >> - create a login trigger that issues 'set rowcount 1'
> > and >> .. - assign said login trigger to this
> > application's ASE >> login
> > >>
> > >> The downside is that the 'set rowcount 1' will apply
> > to >> all queries and commands issued by the application
> > , unless >> of course the application overrides this be
> > issuing a new >> 'set rowcount' command.
> > >>
> > >> If ...
> > >>
> > >> - the application never issues another SELECT and ...
> > >> - you need to allow the option of performing a
> > multi-row >> INSERT/UPDATE/DELETE ...
> > >>
> > >> - create a user-defined function (no input parameters
> > are >> required) that issues a 'set rowcount 0' and 'set
> > >> export_options on' (assume it's named
> > 'clear_rowcount') >> and ... - alter tbl_01 and add a
> > computed column like: >>
> > >> alter tbl_01 add clear_rowcount as
> > >> dbo.clear_rowcount() not materialized
> > >>
> > >> When the application logs in the login trigger will
> > limit >> the first SELECT from tbl_01 to 1 row, the
> > SELECT will >> fire the clear_rowcount function which
> > will 'set rowcount >> 0', so all follow on SQL
> > statements will not be limited to >> a single row.
> > >>
> > >> ====================
> > >>
> > >> A variation on the login trigger/user-defined
> > function >> idea ...
> > >>
> > >> - same design but have the login trigger issue a 'set
> > >> nodata on' instead of the 'set rowcount 1' and ... -
> > >> create a user-defined function (no input parameters
> > >> required) that issues 'set nodata off' and 'set
> > >> export_options on' and (assume it's named
> > >> 'disable_nodata') and ... - add a non-materialized
> > >> computed column to tbl_01 that calls
> > dbo.disable_nodata() >>
> > >> While the initial SELECT will table scan tbl_01 it
> > will >> not send any data back to the application. The
> > initial >> SELECT will trigger a call to
> > dbo.disable_nodata() which >> will disable the trigger's
> > 'set nodata on' thus allowing >> follow on SQL
> > statements to function as normal. >>
> > >> NOTE: 'set nodata on' is new with ASE 15, ie, it
> > won't >> work in pre-15.
> > >>
> > >> ====================
> > >>
> > >> Obviously (?) a variation of the previous ideas would
> > >> combine the 'set rowcount 1' and 'set nodata on'.
> > >>
> > >> For the initial SELECT this would eliminate the table
> > scan >> in favor of pulling one row from the table, but
> > said row >> won't be returned to the application.
> > >>
> > >> A compound 'set rowcount 0/set nodata off'
> > user-defined >> function, added to tbl_01 as a
> > non-materialized column, >> would then allow follow on
> > SQL statement to function as >> normal.
> > >>
> > >> ====================
> > >>
> > >> If you can send an error back to the application
> > without >> causing it to crash or run amok ...
> > >>
> > >> - grant insert/update/delete on tbl_01 to the
> > >> application's ASE login - revoke select on tbl_01
> > from the >> application's ASE login
> > >>
> > >> ====================
> > >>
> > >> On 03/01/2012 20:23, gen wrote:
> > >>> thanks for you reply. I thought about rlac but it
> > dosent >>> work in my case as there is just one user.
> > moreover it >>> is just one binary and tracing packets
> > for this binary, >>> the connection is made just once,
> > followed by select and >>> then update/insert/deletes.
> > >>>
> > >>> Let me make it more clear:
> > >>>
> > >>> 1) User01 connects
> > >>> 2) User01 runs select * from tbl_01
> > >>> 3) user01 runs other dms on tbl_01
> > >>> 4) user01 disconnects
> > >>>
> > >>> i want 2) to return 0 rows withour error, 3) should
> > go >>> through successfully updating/deleting qualified
> > rows in >>> where and insert should insert new rows and
> > error only >>> on duplicate keys.
> > >>>
> > >>>
> > >>>
> > >>>> How about using RLAC i.e Row Level Access Control
> > ?. >> If>> you use RLAC you can control what kind of
> > rows >> users can>> see. In your case you can simply
> > restrict >> user so that no>> rows are returned for the
> > particular >> user but in reality>> you still have
> > other DML going >> through for those users who>> have
> > been granted >> permissions for RLAC>>
> > >>>>
> > >>>>
> > >>>> -HTH
> > >>>>
> > >>>> Manish Negandhi
> > >>>>
> > >>>> [TeamSybase]
> > >>>>
> > >>>>
> > >>>>
> > >>>>
> > >>>> <gen> wrote in message
> > >>>> news:4f5039e2.44ff.1681692777@sybase.com...>i have
> > a >>>>> genuine requirement and may be some of u good
> > folks >> can>> shed some light how this could be done.>
> > >>>>> following command is issued against table with
> > 100K >>>>> rows: select * from tbl_01
> > >>>>>
> > >>>>> can we have the select return 0 rows? at the same
> > time >>>>> any insert/update/delete performed on tbl_01
> > should go >>>>> through successfully.
> > >>>>>
> > >>>>> No revoking permission as that will error the
> > select. >>>>> None of the statments
> > select/insert/update/delete >> should>>> error out.
> > >>>>>
> > >>>>> it sounds a bit crazy to me, but this is my
> > current >>>>> requirement and my only option... why i
> > will justify >>>>> later. can this be done?
> > >>>>
> > >>>>


Bret Halford Posted on 2012-03-05 17:17:41.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:10.0.2) Gecko/20120216 Thunderbird/10.0.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: select
References: <4f52bdac.4119.1681692777@sybase.com> <4f53e364.1e89.1681692777@sybase.com>
In-Reply-To: <4f53e364.1e89.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4f54f535$1@forums-1-dub>
Date: 5 Mar 2012 09:17:41 -0800
X-Trace: forums-1-dub 1330967861 10.22.241.152 (5 Mar 2012 09:17:41 -0800)
X-Original-Trace: 5 Mar 2012 09:17:41 -0800, vip152.sybase.com
Lines: 33
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30979
Article PK: 73869


On 3/4/2012 2:49 PM, gen wrote:
> set rowcount 1 in login trigger did not work , as module did
> not error but the data got messed up, there are other
> non-image tables where update and delete touch more than 1
> rows. it was but worth trying though.
>

This is certainly an interesting problem. Can you tell
by looking at sysprocesses which of the modules is doing
the select? In general, I think row level access control
would be the most likely approach, but I don't think there
is quite enough infrastructure for the rule to determine
whether the spid is currently doing select, insert, update,
or delete.

There may actually be a fairly easy method of accomplishing
this by modifying the client. The general idea is that
the select statement may well be stored as a literal value
in the executable, and if so, you can carefully edit
that literal value from "select * from tbl_01" to, say,
"select * from tbl_mt" and then create an empty table
named "tbl_mt" in your database to match. The key is to
keep the length of the string exactly the same.

From a unix prompt, run the "strings" utility against
the module executable and search the output for a string
matching undesired select statement. If it finds one
(or more), make a copy of the executable and
find a hex editor, make the change and test it.
(if there are more than one, try changing one at a time
until you find the right one).


"Mark A. Parsons" <iron_horse Posted on 2012-03-03 03:01:52.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: select
References: <4f503baa$1@forums-1-dub> <4f503d36.45c8.1681692777@sybase.com> <4f518446@forums-1-dub>
In-Reply-To: <4f518446@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4f5189a0@forums-1-dub>
Date: 2 Mar 2012 19:01:52 -0800
X-Trace: forums-1-dub 1330743712 10.22.241.152 (2 Mar 2012 19:01:52 -0800)
X-Original-Trace: 2 Mar 2012 19:01:52 -0800, vip152.sybase.com
Lines: 137
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30971
Article PK: 73866

ps - user-defined functions and computed columns are new with ASE 15.x, ie, you can't use these in pre-15.

On 03/02/2012 19:39, Mark A. Parsons wrote:
> How many rows can this app modify at a time with an INSERT/UPDATE/DELETE? And are they simple INSERT/UPDATE/DELETE
> commands, eg, they do not perform joins with any other tables? Does tbl_01 have any triggers?
>
> Does this app access other tables where it does need to have rows returned by a SELECT?
>
> Would it be acceptable to limit the SELECT to pulling back 1 row (as opposed to 0 rows)?
>
> At some point you might want to explain why a) the app runs the SELECT, b) why you don't want it to receive any rows and
> c) does hobbling the SELECT cause any problems for the app?
>
> Oh, and how big is tbl_01?
>
> =====================
>
> If ...
>
> - the application only affects 1 row with INSERT/UPDATE/DELETE commands and ...
> - the application does not need to join with multiple rows from any other tables and ...
> - and you're ok with sending 1 row back as a response to a SELECT then ...
>
> - create a login trigger that issues 'set rowcount 1' and ...
> - assign said login trigger to this application's ASE login
>
> The downside is that the 'set rowcount 1' will apply to all queries and commands issued by the application, unless of
> course the application overrides this be issuing a new 'set rowcount' command.
>
> If ...
>
> - the application never issues another SELECT and ...
> - you need to allow the option of performing a multi-row INSERT/UPDATE/DELETE ...
>
> - create a user-defined function (no input parameters are required) that issues a 'set rowcount 0' and 'set
> export_options on' (assume it's named 'clear_rowcount') and ...
> - alter tbl_01 and add a computed column like:
>
> alter tbl_01 add clear_rowcount as dbo.clear_rowcount() not materialized
>
> When the application logs in the login trigger will limit the first SELECT from tbl_01 to 1 row, the SELECT will fire
> the clear_rowcount function which will 'set rowcount 0', so all follow on SQL statements will not be limited to a single
> row.
>
> ====================
>
> A variation on the login trigger/user-defined function idea ...
>
> - same design but have the login trigger issue a 'set nodata on' instead of the 'set rowcount 1' and ...
> - create a user-defined function (no input parameters required) that issues 'set nodata off' and 'set export_options on'
> and (assume it's named 'disable_nodata') and ...
> - add a non-materialized computed column to tbl_01 that calls dbo.disable_nodata()
>
> While the initial SELECT will table scan tbl_01 it will not send any data back to the application. The initial SELECT
> will trigger a call to dbo.disable_nodata() which will disable the trigger's 'set nodata on' thus allowing follow on SQL
> statements to function as normal.
>
> NOTE: 'set nodata on' is new with ASE 15, ie, it won't work in pre-15.
>
> ====================
>
> Obviously (?) a variation of the previous ideas would combine the 'set rowcount 1' and 'set nodata on'.
>
> For the initial SELECT this would eliminate the table scan in favor of pulling one row from the table, but said row
> won't be returned to the application.
>
> A compound 'set rowcount 0/set nodata off' user-defined function, added to tbl_01 as a non-materialized column, would
> then allow follow on SQL statement to function as normal.
>
> ====================
>
> If you can send an error back to the application without causing it to crash or run amok ...
>
> - grant insert/update/delete on tbl_01 to the application's ASE login
> - revoke select on tbl_01 from the application's ASE login
>
> ====================
>
> On 03/01/2012 20:23, gen wrote:
>> thanks for you reply. I thought about rlac but it dosent
>> work in my case as there is just one user. moreover it is
>> just one binary and tracing packets for this binary, the
>> connection is made just once, followed by select and then
>> update/insert/deletes.
>>
>> Let me make it more clear:
>>
>> 1) User01 connects
>> 2) User01 runs select * from tbl_01
>> 3) user01 runs other dms on tbl_01
>> 4) user01 disconnects
>>
>> i want 2) to return 0 rows withour error, 3) should go
>> through successfully updating/deleting qualified rows in
>> where and insert should insert new rows and error only on
>> duplicate keys.
>>
>>
>>
>>> How about using RLAC i.e Row Level Access Control ?. If
>>> you use RLAC you can control what kind of rows users can
>>> see. In your case you can simply restrict user so that no
>>> rows are returned for the particular user but in reality
>>> you still have other DML going through for those users who
>>> have been granted permissions for RLAC
>>>
>>>
>>>
>>> -HTH
>>>
>>> Manish Negandhi
>>>
>>> [TeamSybase]
>>>
>>>
>>>
>>>
>>> <gen> wrote in message
>>> news:4f5039e2.44ff.1681692777@sybase.com...>i have a
>>>> genuine requirement and may be some of u good folks can
>>> shed some light how this could be done.>
>>>> following command is issued against table with 100K
>>>> rows: select * from tbl_01
>>>>
>>>> can we have the select return 0 rows? at the same time
>>>> any insert/update/delete performed on tbl_01 should go
>>>> through successfully.
>>>>
>>>> No revoking permission as that will error the select.
>>>> None of the statments select/insert/update/delete should
>>>> error out.
>>>>
>>>> it sounds a bit crazy to me, but this is my current
>>>> requirement and my only option... why i will justify
>>>> later. can this be done?
>>>
>>>