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.

row-level security with joins?

8 posts in General Discussion Last posting was on 2010-01-09 22:04:49.0Z
jimmy Posted on 2010-01-08 02:58:07.0Z
From: jimmy <jimmy_sharma@yahoo.com>
Newsgroups: sybase.public.ase.general
Subject: row-level security with joins?
Date: Thu, 7 Jan 2010 18:58:07 -0800 (PST)
Organization: http://groups.google.com
Lines: 37
Message-ID: <38a21767-4e49-400d-83da-254568ead217@u7g2000yqm.googlegroups.com>
NNTP-Posting-Host: 68.239.139.206
Mime-Version: 1.0
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1262919487 12566 127.0.0.1 (8 Jan 2010 02:58:07 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 8 Jan 2010 02:58:07 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: u7g2000yqm.googlegroups.com; posting-host=68.239.139.206; posting-account=Qnz6_AoAAADmjCiItB47BgJtOvzgK4kG
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0; GTB6.3; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; .NET CLR 3.5.30729; .NET CLR 3.0.30618),gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!newsfeed.yul.equant.net!news-raspail.gip.net!news.gsl.net!gip.net!aotearoa.belnet.be!news.belnet.be!news-out1.kabelfoon.nl!newsfeed.kabelfoon.nl!xindi.nntp.kabelfoon.nl!198.186.194.249.MISMATCH!news-out.readnews.com!transit3.readnews.com!postnews.google.com!u7g2000yqm.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28845
Article PK: 78087

I am trying to replace an aging middleware that was build using Sybase
OpenServer to provide row level security.

At a high level this is how it works

Application fires –

SELECT * from deals

Middle Tier checks the security rule on the table for the requesting
user A and decides the SQL should be

SELECT d.* From deals d, deal_permission dp WHERE
d.deal_id = dp.deal_id AND dp.user_id = “A”

For User B (a super user) it leaves the query as is

SELECT d.* From deals d

Fires the modified query into main database, gets the data and sends
it back to the client.

I am now trying to replicate this logic into Sybase’s native row level
security using access rules, but just can’t seem to figure out how
to.

I get the part about using get_appcontext and set_appcontext described
here http://groups.google.com/group/sybase.public.ase.general/msg/4ff7ca86444b8770

but list of deal_ids in deal_permission could be a whole lot - it
makes no sense to populate this every time people log-in.

Also there are several other tables and dimension that I need to
similar approach for.

What would you advise?


Rob V [ Sybase ] Posted on 2010-01-08 08:05:16.0Z
Reply-To: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
From: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Newsgroups: sybase.public.ase.general
References: <38a21767-4e49-400d-83da-254568ead217@u7g2000yqm.googlegroups.com>
Subject: Re: row-level security with joins?
Lines: 71
Organization: Sypron BV / TeamSybase / Sybase
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: <4b46e73c$1@forums-1-dub>
Date: 8 Jan 2010 00:05:16 -0800
X-Trace: forums-1-dub 1262937916 10.22.241.152 (8 Jan 2010 00:05:16 -0800)
X-Original-Trace: 8 Jan 2010 00:05:16 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28847
Article PK: 78089

The idea of using access rules is basically to do away with a permissions
table, but implement the permissions restrictions by means of the access
rules.
So that would means you'd have a column in 'deals' that identifies the
user(s) allowed to access that deal. You'd create an access rules for that
column, and forget about deal-permission.

More details here:
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc31654.1502/html/sag1/CIHBAACF.htm

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., Amersfoort, The Netherlands
Chamber of Commerce 27138666
-----------------------------------------------------------------

"jimmy" <jimmy_sharma@yahoo.com> wrote in message
news:38a21767-4e49-400d-83da-254568ead217@u7g2000yqm.googlegroups.com...
I am trying to replace an aging middleware that was build using Sybase
OpenServer to provide row level security.

At a high level this is how it works

Application fires –

SELECT * from deals

Middle Tier checks the security rule on the table for the requesting
user A and decides the SQL should be

SELECT d.* From deals d, deal_permission dp WHERE
d.deal_id = dp.deal_id AND dp.user_id = “A”

For User B (a super user) it leaves the query as is

SELECT d.* From deals d

Fires the modified query into main database, gets the data and sends
it back to the client.

I am now trying to replicate this logic into Sybase’s native row level
security using access rules, but just can’t seem to figure out how
to.

I get the part about using get_appcontext and set_appcontext described
here
http://groups.google.com/group/sybase.public.ase.general/msg/4ff7ca86444b8770

but list of deal_ids in deal_permission could be a whole lot - it
makes no sense to populate this every time people log-in.

Also there are several other tables and dimension that I need to
similar approach for.

What would you advise?


jimmy Posted on 2010-01-09 04:24:33.0Z
From: jimmy <jimmy_sharma@yahoo.com>
Newsgroups: sybase.public.ase.general
Subject: Re: row-level security with joins?
Date: Fri, 8 Jan 2010 20:24:33 -0800 (PST)
Organization: http://groups.google.com
Lines: 53
Message-ID: <4b486c7c-c1b0-4388-85cb-957c1a1b0fa4@k19g2000yqc.googlegroups.com>
References: <38a21767-4e49-400d-83da-254568ead217@u7g2000yqm.googlegroups.com> <4b46e73c$1@forums-1-dub>
NNTP-Posting-Host: 68.239.139.206
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1263011074 24877 127.0.0.1 (9 Jan 2010 04:24:34 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Sat, 9 Jan 2010 04:24:34 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: k19g2000yqc.googlegroups.com; posting-host=68.239.139.206; posting-account=Qnz6_AoAAADmjCiItB47BgJtOvzgK4kG
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/532.0 (KHTML, like Gecko) Chrome/3.0.195.38 Safari/532.0,gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!newsfeed.news2me.com!nx02.iad01.newshosting.com!newshosting.com!198.186.194.249.MISMATCH!transit3.readnews.com!news-out.readnews.com!news-xxxfer.readnews.com!postnews.google.com!k19g2000yqc.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28850
Article PK: 78091

Rob - Not quite..
first, Let me see if you I get what you are suggesting...

if Current setup is this --
Table: Deal (Deal_ID, deal_description, deal_size, fee)
Table: Deal_Permission (Deal_id, user_id)

are you suggesting I change the table to be
Table: New_Deal (NewPrimaryKey, Deal_ID, deal_description, deal_size,
fee, USER_ID) /* Thus making a 1M row table into a 100M rows? that
cant be right?*/

Unless you are suggesting I still use the
Table: Deal (Deal_ID, deal_description, deal_size, fee)
BUT write a access rule that essentially does a @deal_id IN (select
deal_id from deal_permission where user_id = suser_id() )

which would be great if *only* I can write a rule like that

Using a SQLUDF or a JAVA UDF isnt much of an option here either since
as a function they can't return a dataset only scalar values.

Do you see my dilemma?




On Jan 8, 3:05 am, "Rob V [ Sybase ]"

<r...@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY> wrote:
> The idea of using access rules is basically to do away with a permissions
> table, but implement the permissions restrictions by means of the access
> rules.
> So that would means you'd have a column in 'deals' that identifies the
> user(s) allowed to access that deal. You'd create an access rules for that
> column, and forget about deal-permission.
>
> More details here:http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocen...
>
> HTH,
>
> Rob V.
> -----------------------------------------------------------------
> Rob Verschoor
>
> Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
> and Replication Server 15.0.1/12.5 // TeamSybase
>
> Author of Sybase books (order online atwww.sypron.nl/shop):
> "Tips, Tricks & Recipes for Sybase ASE" (ASE 15 edition)
> "The Complete Sybase ASE Quick Reference Guide"
> "The Complete Sybase Replication Server Quick Reference Guide"


jimmy Posted on 2010-01-09 04:35:17.0Z
From: jimmy <jimmy_sharma@yahoo.com>
Newsgroups: sybase.public.ase.general
Subject: Re: row-level security with joins?
Date: Fri, 8 Jan 2010 20:35:17 -0800 (PST)
Organization: http://groups.google.com
Lines: 68
Message-ID: <3940677a-1880-420b-885c-9fb711a85289@26g2000yqo.googlegroups.com>
References: <38a21767-4e49-400d-83da-254568ead217@u7g2000yqm.googlegroups.com> <4b46e73c$1@forums-1-dub> <4b486c7c-c1b0-4388-85cb-957c1a1b0fa4@k19g2000yqc.googlegroups.com>
NNTP-Posting-Host: 68.239.139.206
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1263011717 22820 127.0.0.1 (9 Jan 2010 04:35:17 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Sat, 9 Jan 2010 04:35:17 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: 26g2000yqo.googlegroups.com; posting-host=68.239.139.206; posting-account=Qnz6_AoAAADmjCiItB47BgJtOvzgK4kG
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/532.0 (KHTML, like Gecko) Chrome/3.0.195.38 Safari/532.0,gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!newsfeed.news2me.com!nx02.iad01.newshosting.com!newshosting.com!69.16.185.11.MISMATCH!npeer01.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!postnews.google.com!26g2000yqo.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28851
Article PK: 78092

I should also mention that the deal table security is not hierarchical
in nature (i.e. by region, or by country or by Cost center etc..)
Every deal is need to know, so very entitlements are unfortunately
very atomic. Hence my comment about 1M expanding to 100M.

Everyone thinks its doable, but Clearly there is something very
obvious here that I am not able to get my head wrapped around :(

On Jan 8, 11:24 pm, jimmy <jimmy_sha...@yahoo.com> wrote:
> Rob - Not quite..
> first, Let me see if you I get what you are suggesting...
>
> if Current setup is this --
> Table: Deal (Deal_ID, deal_description, deal_size, fee)
> Table: Deal_Permission (Deal_id, user_id)
>
> are you suggesting I change the table to be
> Table: New_Deal (NewPrimaryKey, Deal_ID, deal_description, deal_size,
> fee, USER_ID)  /* Thus making a 1M row table into a 100M rows? that
> cant be right?*/
>
> Unless you are suggesting I still use the
> Table: Deal (Deal_ID, deal_description, deal_size, fee)
> BUT write a access rule that essentially does a @deal_id IN (select
> deal_id from deal_permission where user_id = suser_id() )
>
> which would be great if *only* I can write a rule like that
>
> Using a SQLUDF or a JAVA UDF isnt much of an option here either since
> as a function they can't return a dataset only scalar values.
>
> Do you see my dilemma?
>
> On Jan 8, 3:05 am, "Rob V   [ Sybase ]"
>
>
>
> <r...@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY> wrote:
> > The idea of using access rules is basically to do away with a permissions
> > table, but implement the permissions restrictions by means of the access
> > rules.
> > So that would means you'd have a column in 'deals' that identifies the
> > user(s) allowed to access that deal. You'd create an access rules for that
> > column, and forget about deal-permission.
>
> > More details here:http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocen...
>
> > HTH,
>
> > Rob V.
> > -----------------------------------------------------------------
> > Rob Verschoor
>
> > Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
> > and Replication Server 15.0.1/12.5 // TeamSybase
>
> > Author of Sybase books (order online atwww.sypron.nl/shop):
> > "Tips, Tricks & Recipes for Sybase ASE" (ASE 15 edition)
> > "The Complete Sybase ASE Quick Reference Guide"
> > "The Complete Sybase Replication Server Quick Reference Guide"


dumbdog Posted on 2010-01-09 15:40:03.0Z
Sender: 62c6.4b452ec3.1804289383@sybase.com
From: dumbdog
Newsgroups: sybase.public.ase.general
Subject: Re: row-level security with joins?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4b48a353.4196.1681692777@sybase.com>
References: <3940677a-1880-420b-885c-9fb711a85289@26g2000yqo.googlegroups.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 9 Jan 2010 07:40:03 -0800
X-Trace: forums-1-dub 1263051603 10.22.241.41 (9 Jan 2010 07:40:03 -0800)
X-Original-Trace: 9 Jan 2010 07:40:03 -0800, 10.22.241.41
Lines: 136
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28854
Article PK: 78093

You dont need to change your schema design.
Try this and see if it works for you!

1> create table stupid_deal(dealid int, deal_what1 int,
deal_what2 int)
2> go

1> create table stupid_deal_perm(dealid int, usernm
varchar(30))
2> go

1> create function f_stupid_perm (@dealid int)
2> returns int
3> as
4> declare @why int
5> if exists ( select 1 from stupid_deal_perm where
dealid=@dealid and usernm =
suser_name())
6> select @why=1
7> else
8> select @why=0
9> return @why
10> go


1> create access rule r1_stupid_access as
dbo.f_stupid_perm(@dealid) = 1
2> go


1> sp_bindrule r1_stupid_access , "stupid_deal.dealid"
2> go


1> insert into stupid_deal select 1, 1,1
2> insert into stupid_deal select 2, 1,1

1> insert into stupid_deal_perm select 1, 'dumbdog'
2> insert into stupid_deal_perm select 2, 'stupiddog'

----- OKAY WHAT U NEED IS DONE ----
----- NOW setup the user to verify what you did ------

1> sp_addlogin dumbdog, dumbdog
1> sp_addlogin stupiddog, stupiddog

-- go to your stupid database --
1>sp_addalias dumbdog, dbo
1> sp_addalias stupiddog, dbo

ALL WORK DONE - Verify

Now Login as dumbdog and run
select * from stupid_deal
you will see only rows with dealid = 1

Confim by loggin as stupiddog and run
select * from stupid_deal
you will see only rows with dealid = 2

DONE - GO HOME NOW

> I should also mention that the deal table security is not
> hierarchical in nature (i.e. by region, or by country or
> by Cost center etc..) Every deal is need to know, so very
> entitlements are unfortunately very atomic. Hence my
> comment about 1M expanding to 100M.
>
> Everyone thinks its doable, but Clearly there is something
> very obvious here that I am not able to get my head
> wrapped around :(
>
>
>
>
> On Jan 8, 11:24 pm, jimmy <jimmy_sha...@yahoo.com>
wrote:
> > Rob - Not quite..
> > first, Let me see if you I get what you are
> suggesting... >
> > if Current setup is this --
> > Table: Deal (Deal_ID, deal_description, deal_size, fee)
> > Table: Deal_Permission (Deal_id, user_id)
> >
> > are you suggesting I change the table to be
> > Table: New_Deal (NewPrimaryKey, Deal_ID,
> > deal_description, deal_size, fee, USER_ID)  /* Thus
> > making a 1M row table into a 100M rows? that cant be
> right?*/ >
> > Unless you are suggesting I still use the
> > Table: Deal (Deal_ID, deal_description, deal_size, fee)
> > BUT write a access rule that essentially does a @deal_id
> > IN (select deal_id from deal_permission where user_id
=
> suser_id() ) >
> > which would be great if *only* I can write a rule like
> that >
> > Using a SQLUDF or a JAVA UDF isnt much of an option here
> > either since as a function they can't return a dataset
> only scalar values. >
> > Do you see my dilemma?
> >
> > On Jan 8, 3:05 am, "Rob V   [ Sybase ]"
> >
> >
> >
> > <r...@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY> wrote:
> > > The idea of using access rules is basically to do away
> > > with a permissions table, but implement the
> > > permissions restrictions by means of the access rules.
> > > So that would means you'd have a column in 'deals'
> > > that identifies the user(s) allowed to access that
> > > deal. You'd create an access rules for that column,
> and forget about deal-permission. >
> > > More details
>
here:http://infocenter.sybase.com/help/index.jsp?topic=/co
> m.sybase.infocen... >
> > > HTH,
> >
> > > Rob V.
> > >
> ----------------------------------------------------------
> > > ------- Rob Verschoor
> >
> > > Certified Sybase Professional DBA for ASE
> > > 15.0/12.5/12.0/11.5/11.0 and Replication Server
> 15.0.1/12.5 // TeamSybase >
> > > Author of Sybase books (order online
> > > atwww.sypron.nl/shop): "Tips, Tricks & Recipes for
> > > Sybase ASE" (ASE 15 edition) "The Complete Sybase ASE
> > > Quick Reference Guide" "The Complete Sybase
> Replication Server Quick Reference Guide"
>


"Mark A. Parsons" <iron_horse Posted on 2010-01-09 21:11:22.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: row-level security with joins? [SQL UDF performance issue]
References: <3940677a-1880-420b-885c-9fb711a85289@26g2000yqo.googlegroups.com> <4b48a353.4196.1681692777@sybase.com>
In-Reply-To: <4b48a353.4196.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 091223-0, 12/23/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b48f0fa$1@forums-1-dub>
Date: 9 Jan 2010 13:11:22 -0800
X-Trace: forums-1-dub 1263071482 10.22.241.152 (9 Jan 2010 13:11:22 -0800)
X-Original-Trace: 9 Jan 2010 13:11:22 -0800, vip152.sybase.com
Lines: 264
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28857
Article PK: 78099

Good example of using a SQL UDF in conjunction with an access rule.

However, I would like to point out an issue that folks need to keep in the back of their mind when using SQL UDFs ...

------------------

SQL UDFs can be big performance hogs.

Even if the code within the SQL UDF has been optimized for good performance, there's a sizable overhead for simply
invoking the SQL UDF.

While the overhead for a single SQL UDF invocation may be negligible, the overhead becomes quite noticeable when the SQL
UDF is invoked a large volume of times.

NOTE: I've seen occasions where query performance has been degraded by 200% to 20,000+% when using SQL UDFs.

A quick, simple example to demonstrate SQL UDF performance:

login:

sa

@@maxpagesize:

2048

table:

t1 (id int, place_holder char(90) not null)
- 1 million records (~50K pages)
- allpages locking
- no indexes/RI on table
- all pages in cache

SQL UDF:

create function get_user_name(@id int)
returns varchar(30)
as
return suser_name()

Query_1:

select count(*)
from t1
where dbo.get_user_name(id) = 'sa'

Query_2:

select count(*)
from t1
where dbo.get_user_name(1) = 'sa'

Run times:

Query_1: 31.553 seconds
Query_2: 0.303 seconds

Query_1 takes ~104 times longer (~10,300%) to run than Query_2.

Query_1 takes longer to run because it has to call get_user_name() once for each of the 1,000,000 million id's in the t1
table.

Query_2 only calls the get_user_name() function once (for @id = 1); in this case the optimizer is smart enough to know
the function reference is, in essence, a hard coded value having nothing to do with the individual data records.

Obviously (?) the overhead of calling a SQL UDF will also depend on what exactly the SQL UDF is doing. In this
simplistic case we're just calling the suser_name() built-in function. Had the SQL UDF included a query (or several
queries?), each SQL UDF invocation would have incurred the additional overhead of running said query(s) ... logical IOs,
physical IOs, additional cpu, etc.

Also take into consideration that this simple example shows a single SQL UDF invocation for each record processed.
Obviously the total time to run the query goes up if/when multiple SQL UDFs are required for each record processed, eg:

Query_3:

select count(*)
from t1
where dbo.get_user_name(id) = 'sa'
and dbo.get_user_name(id+1) = 'sa'

run time: ~62 seconds

Query_4:

select count(*)
from t1
where dbo.get_user_name(id) = 'sa'
and dbo.get_user_name(id) = 'sa'

run time: ~62 seconds

NOTE: The optimizer doesn't consider the 2 WHERE
clauses as 'the same' so we end up calling
the SQL UDF twice for each record in t1.


NOTE: Each time we run an instance of Query_2, Query_3 or Query_4 we peg one dataserver engine at 100% cpu utilization
for the duration of the query (ie, 32-62 seconds). Guess what happens if you have multiple users running this type of
cpu-intensive query against a 'limited' number of ASE dataservers?

------------------

OK, so why the (huge?) tangent on SQL UDF performance in reply to dumbdog's post?

1 - the SQL UDF has the deal_id as an input value

2 - the SQL UDF is attached (via access rule) to the stupid_deal.dealid column

Net result is that each time 'select * from stupid_deal' is run, a SQL UDF call must be made for *each* record in the
stupid_deal table.

*Each* of these SQL UDF calls must make a suser_name() function call as well as access the stupid_deal_perm table.
(Hint: Preface the query with 'set statistics io on' and see how much output you get.)

dumbdog's example is, in essence, performing a cursor-based join between the stupid_deal and stupid_deal_perm tables.
Chances are this SQL UDF example will perform less efficiently than the OPs set-based join between the deal and
deal_permissions tables ... and it will certainly run less efficiently than the OPs super user query which does not
require a join to the deal_permissions table.

------------------

While SQL UDFs can provide a means of encapsulating (sometimes complicated) logic into a simple function call, there's a
potential trade-off in the form of reduced performance that SQL developers need to keep in mind.

Again, dumbdog's provided a good example of using SQL UDFs and access rules ... just beware of potential performance
tradeoffs.

dumbdog wrote:
> You dont need to change your schema design.
> Try this and see if it works for you!
>
> 1> create table stupid_deal(dealid int, deal_what1 int,
> deal_what2 int)
> 2> go
>
> 1> create table stupid_deal_perm(dealid int, usernm
> varchar(30))
> 2> go
>
> 1> create function f_stupid_perm (@dealid int)
> 2> returns int
> 3> as
> 4> declare @why int
> 5> if exists ( select 1 from stupid_deal_perm where
> dealid=@dealid and usernm =
> suser_name())
> 6> select @why=1
> 7> else
> 8> select @why=0
> 9> return @why
> 10> go
>
>
> 1> create access rule r1_stupid_access as
> dbo.f_stupid_perm(@dealid) = 1
> 2> go
>
>
> 1> sp_bindrule r1_stupid_access , "stupid_deal.dealid"
> 2> go
>
>
> 1> insert into stupid_deal select 1, 1,1
> 2> insert into stupid_deal select 2, 1,1
>
> 1> insert into stupid_deal_perm select 1, 'dumbdog'
> 2> insert into stupid_deal_perm select 2, 'stupiddog'
>
> ----- OKAY WHAT U NEED IS DONE ----
> ----- NOW setup the user to verify what you did ------
>
> 1> sp_addlogin dumbdog, dumbdog
> 1> sp_addlogin stupiddog, stupiddog
>
> -- go to your stupid database --
> 1>sp_addalias dumbdog, dbo
> 1> sp_addalias stupiddog, dbo
>
> ALL WORK DONE - Verify
>
> Now Login as dumbdog and run
> select * from stupid_deal
> you will see only rows with dealid = 1
>
> Confim by loggin as stupiddog and run
> select * from stupid_deal
> you will see only rows with dealid = 2
>
> DONE - GO HOME NOW
>
>
>
>> I should also mention that the deal table security is not
>> hierarchical in nature (i.e. by region, or by country or
>> by Cost center etc..) Every deal is need to know, so very
>> entitlements are unfortunately very atomic. Hence my
>> comment about 1M expanding to 100M.
>>
>> Everyone thinks its doable, but Clearly there is something
>> very obvious here that I am not able to get my head
>> wrapped around :(
>>
>>
>>
>>
>> On Jan 8, 11:24 pm, jimmy <jimmy_sha...@yahoo.com>
> wrote:
>>> Rob - Not quite..
>>> first, Let me see if you I get what you are
>> suggesting... >
>>> if Current setup is this --
>>> Table: Deal (Deal_ID, deal_description, deal_size, fee)
>>> Table: Deal_Permission (Deal_id, user_id)
>>>
>>> are you suggesting I change the table to be
>>> Table: New_Deal (NewPrimaryKey, Deal_ID,
>>> deal_description, deal_size, fee, USER_ID) /* Thus
>>> making a 1M row table into a 100M rows? that cant be
>> right?*/ >
>>> Unless you are suggesting I still use the
>>> Table: Deal (Deal_ID, deal_description, deal_size, fee)
>>> BUT write a access rule that essentially does a @deal_id
>>> IN (select deal_id from deal_permission where user_id
> =
>> suser_id() ) >
>>> which would be great if *only* I can write a rule like
>> that >
>>> Using a SQLUDF or a JAVA UDF isnt much of an option here
>>> either since as a function they can't return a dataset
>> only scalar values. >
>>> Do you see my dilemma?
>>>
>>> On Jan 8, 3:05 am, "Rob V [ Sybase ]"
>>>
>>>
>>>
>>> <r...@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY> wrote:
>>>> The idea of using access rules is basically to do away
>>>> with a permissions table, but implement the
>>>> permissions restrictions by means of the access rules.
>>>> So that would means you'd have a column in 'deals'
>>>> that identifies the user(s) allowed to access that
>>>> deal. You'd create an access rules for that column,
>> and forget about deal-permission. >
>>>> More details
> here:http://infocenter.sybase.com/help/index.jsp?topic=/co
>> m.sybase.infocen... >
>>>> HTH,
>>>> Rob V.
>>>>
>> ----------------------------------------------------------
>>>> ------- Rob Verschoor
>>>> Certified Sybase Professional DBA for ASE
>>>> 15.0/12.5/12.0/11.5/11.0 and Replication Server
>> 15.0.1/12.5 // TeamSybase >
>>>> Author of Sybase books (order online
>>>> atwww.sypron.nl/shop): "Tips, Tricks & Recipes for
>>>> Sybase ASE" (ASE 15 edition) "The Complete Sybase ASE
>>>> Quick Reference Guide" "The Complete Sybase
>> Replication Server Quick Reference Guide"
>>


"Mark A. Parsons" <iron_horse Posted on 2010-01-09 22:04:49.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: row-level security with joins?
References: <38a21767-4e49-400d-83da-254568ead217@u7g2000yqm.googlegroups.com> <4b46e73c$1@forums-1-dub> <4b486c7c-c1b0-4388-85cb-957c1a1b0fa4@k19g2000yqc.googlegroups.com> <3940677a-1880-420b-885c-9fb711a85289@26g2000yqo.googlegroups.com>
In-Reply-To: <3940677a-1880-420b-885c-9fb711a85289@26g2000yqo.googlegroups.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit
X-Antivirus: avast! (VPS 091223-0, 12/23/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b48fd81$1@forums-1-dub>
Date: 9 Jan 2010 14:04:49 -0800
X-Trace: forums-1-dub 1263074689 10.22.241.152 (9 Jan 2010 14:04:49 -0800)
X-Original-Trace: 9 Jan 2010 14:04:49 -0800, vip152.sybase.com
Lines: 295
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28859
Article PK: 78101

NOTE: Instead of replying to each of your posts I'm just going to combine my questions/comments into this post.

----------------

re: clarification on the "deal table security is not hierarchical" comment ...

Assume we have 2 users ... user_A and user_B. Assume neither user is considered a 'super user'.

Assume we have 2 deals ... deal_100 and deal_200.

By "not hierarchical" do you mean that the following is possible?

user_A can access deal_100. user_A cannot access deal_200.

user_B cannot access deal_100. user_B can access deal_200.

----------------

re: super user access (#1) ...

You mentioned earlier that your application's middle tier currently submits 1 of 2 possible queries depending on whether
the user in question is a super user:

- user is not a super user:

Query_1:

select d.*
from deals d,
deal_permission dp
where d.deal_id = dp.deal_id
and dp.user_id = “A”

- user is a super user:

Query_2:

select d.*
from deals d

How many super users are there in your system?

Just thinking out loud ...

If there are (relatively) few super users I'm wondering why you don't just add them to the deal_permissions table.

PRO: Use the same query (Query_1) for all users.

PRO: No need to pay extra $$ for the RLAC license.

PRO: No new columns needed on the deals table.

CON: This could add a large volume of records to the deal_permissions table.

CON: Performance would be greatly degraded (for super users) by the unnecessary join to the deal_permissions table.

----------------

re: super user access (#2) ... continuing the idea of adding super users to the deal_permissions table ...



To reduce the volume of entries in the deal_permissions table you could look at adding a new/single record (to
deal_permissions) for every record in the deals table. These new records would have a user_id = 'super_user'.

PRO: Reduction in number of records in the deal_permissions table.

PRO: Single query can be used by all users.

PRO: No need to pay extra $$ for the RLAC license.

CON: Still have a performance hit (joining to deal_permissions table) for super users.

CON: Need means of remapping a super user's id into the catchall id of 'super_user'.



remapping super user id's into 'super_user' #1:
===============================================

Add an additional table for matching super user id's to the 'super_user' catchall.

CON: Makes queries more complicated due to additional join requirement for super user lookups.

CON: Requires 2 sets of queries (non-super users don't need to check the super user lookup table).

CON: Performance is degraded further to support the additional join requirements.



remapping super user id's into 'super_user' #2:
===============================================

We'll use Application Context Functions (ACFs) to replace the joins against the super user lookup table.

- login trigger does a one-time lookup of the all user ids against a super user lookup table

- [login trigger] if the user is not a super user:

select set_appcontext ('user','id',suser_name())

NOTE: Assuming suser_name(), as opposed to user_name(), is sufficient for security purposes

- [login trigger] if the user is a super user:

select set_appcontext ('user','id','super_user')

Now, in conjunction with 'super_user' records in deal_permissions, your query becomes:

Query_3:

select d.*
from deals d,
deal_permission dp
where d.deal_id = dp.deal_id
and dp.user_id = get_appcontext('user','id')

CON: Still have a performance hit (joining to deal_permissions table) for super users.



remapping super user id's into 'super_user' #3:
===============================================

- create a SQL UDF to perform the super user lookup

create function get_user_id
returns varchar(30)
as
declare @suser_id varchar(30)
select @suser_id = suser_name()

if exists (select suser_id
from super_user_lookup
where suser_id = @suser_id)
begin
select @suser_id = 'super_user'
end
return @suser_id

Again, in conjunction with 'super_user' records in deal_permissions, your query becomes:

Query_4:

select d.*
from deals d,
deal_permission dp
where d.deal_id = dp.deal_id
and dp.user_id = dbo.get_user_id()

PRO: No need for a login trigger.

CON: Still have a performance hit (joining to deal_permissions table) for super users.

----------------

Are the 2 sample queries (above; Query_1, Query_2) indicative of actually queries against the deals table, ie:

How often do super users actually pull back *ALL* deals?

How often do non-super users pull back *ALL* of their deals?

----------------

What do you see as a final solution?

Are you trying to replace the current middle tier solution of 2 different queries (based on a session's 'super user'
status) with a single query? If so, it would seem that your super users are going to see a degradation in performance
due to the requirement to check their status for each deal they touch in the deals table.

Are you open to using views to help manage your access permissions? If so, and you're using ASE 12.5+ (supports UNIONs
in views), you could try rolling your 2 queries with a SQL UDF/view combo like such:

=====================================
create function is_super_user
returns tinyint
as
declare @super_user tinyint
select @super_user = 0

if exists (select suser_name
from super_user_lookup
where suser_name = suser_name())
begin
select @super_user = 1
end
return @super_user
=====================================

=====================================
create view deal_view
as
select d.*
from deals d
where dbo.is_super_user() = 1
union all
select d.*
from deals d,
deal_permission dp
where d.deal_id = dp.deal_id
and dbo.is_super_user() = 0
and dp.user_id = “A” -- suser_name() ??
=====================================

You'll need to test this to see what kind of performance you get. In a quick test on ASE 15.0.2 I see the second part
of the view being ignored for a super user, while the first part of the view is ignored for a regular user.

While you'd still need the super_user_lookup table, you shouldn't need to add the 'super_user' records to the
deal_permissions table. Net result is that super users shouldn't need to perform the extra join against deal_permissions.

Another option would be to encapsulate all the desired logic into a stored proc. If you have to access the data via a
SELECT you could place a proxy table on the front of the stored proc so that you can, in essence, SELECT from the stored
proc.

----------------

While there's probably a way to make the changes you want, the final solution will come down to a question of a) what
kinds of changes are you willing to make in the application and database, b) how much complexity can you manage/support
and c) what kind of performance trade-offs are you willing to accept for a (possibly) simplified solution.

NOTE: None of the above ideas require you to purchase a RLAC license. Even if you decide to use RLAC you need to
realize that there will be some additional technical complexity involved in implementing RLAC. So while some of the
above ideas (or variations of said ideas) also have some technical complexity associated with them ... they don't cost
you any additional money.

----------------

If the above ideas don't address your issues ... you'll need to fill is in with more details.

jimmy wrote:
> I should also mention that the deal table security is not hierarchical
> in nature (i.e. by region, or by country or by Cost center etc..)
> Every deal is need to know, so very entitlements are unfortunately
> very atomic. Hence my comment about 1M expanding to 100M.
>
> Everyone thinks its doable, but Clearly there is something very
> obvious here that I am not able to get my head wrapped around :(
>
>
>
>
> On Jan 8, 11:24 pm, jimmy <jimmy_sha...@yahoo.com> wrote:
>> Rob - Not quite..
>> first, Let me see if you I get what you are suggesting...
>>
>> if Current setup is this --
>> Table: Deal (Deal_ID, deal_description, deal_size, fee)
>> Table: Deal_Permission (Deal_id, user_id)
>>
>> are you suggesting I change the table to be
>> Table: New_Deal (NewPrimaryKey, Deal_ID, deal_description, deal_size,
>> fee, USER_ID) /* Thus making a 1M row table into a 100M rows? that
>> cant be right?*/
>>
>> Unless you are suggesting I still use the
>> Table: Deal (Deal_ID, deal_description, deal_size, fee)
>> BUT write a access rule that essentially does a @deal_id IN (select
>> deal_id from deal_permission where user_id = suser_id() )
>>
>> which would be great if *only* I can write a rule like that
>>
>> Using a SQLUDF or a JAVA UDF isnt much of an option here either since
>> as a function they can't return a dataset only scalar values.
>>
>> Do you see my dilemma?
>>
>> On Jan 8, 3:05 am, "Rob V [ Sybase ]"
>>
>>
>>
>> <r...@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY> wrote:
>>> The idea of using access rules is basically to do away with a permissions
>>> table, but implement the permissions restrictions by means of the access
>>> rules.
>>> So that would means you'd have a column in 'deals' that identifies the
>>> user(s) allowed to access that deal. You'd create an access rules for that
>>> column, and forget about deal-permission.
>>> More details here:http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocen...
>>> HTH,
>>> Rob V.
>>> -----------------------------------------------------------------
>>> Rob Verschoor
>>> Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
>>> and Replication Server 15.0.1/12.5 // TeamSybase
>>> Author of Sybase books (order online atwww.sypron.nl/shop):
>>> "Tips, Tricks & Recipes for Sybase ASE" (ASE 15 edition)
>>> "The Complete Sybase ASE Quick Reference Guide"
>>> "The Complete Sybase Replication Server Quick Reference Guide"
>


Rob V [ Sybase ] Posted on 2010-01-09 19:12:23.0Z
Reply-To: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
From: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Newsgroups: sybase.public.ase.general
References: <38a21767-4e49-400d-83da-254568ead217@u7g2000yqm.googlegroups.com> <4b46e73c$1@forums-1-dub> <4b486c7c-c1b0-4388-85cb-957c1a1b0fa4@k19g2000yqc.googlegroups.com>
Subject: Re: row-level security with joins?
Lines: 82
Organization: Sypron BV / TeamSybase / Sybase
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: <4b48d517@forums-1-dub>
Date: 9 Jan 2010 11:12:23 -0800
X-Trace: forums-1-dub 1263064343 10.22.241.152 (9 Jan 2010 11:12:23 -0800)
X-Original-Trace: 9 Jan 2010 11:12:23 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28856
Article PK: 78098

Apparently you're saying that a row can be owned (=accessed) by multiple
users? In that case you will need to join with your permissions table
indeed. But what you don't need is the predicate that tests for the users
("...dp.user_id = "A" ...) since this will be done by the access rule. So
you'd need to remove that from your query.

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., Amersfoort, The Netherlands
Chamber of Commerce 27138666
-----------------------------------------------------------------

"jimmy" <jimmy_sharma@yahoo.com> wrote in message
news:4b486c7c-c1b0-4388-85cb-957c1a1b0fa4@k19g2000yqc.googlegroups.com...
Rob - Not quite..
first, Let me see if you I get what you are suggesting...

if Current setup is this --
Table: Deal (Deal_ID, deal_description, deal_size, fee)
Table: Deal_Permission (Deal_id, user_id)

are you suggesting I change the table to be
Table: New_Deal (NewPrimaryKey, Deal_ID, deal_description, deal_size,
fee, USER_ID) /* Thus making a 1M row table into a 100M rows? that
cant be right?*/

Unless you are suggesting I still use the
Table: Deal (Deal_ID, deal_description, deal_size, fee)
BUT write a access rule that essentially does a @deal_id IN (select
deal_id from deal_permission where user_id = suser_id() )

which would be great if *only* I can write a rule like that

Using a SQLUDF or a JAVA UDF isnt much of an option here either since
as a function they can't return a dataset only scalar values.

Do you see my dilemma?




On Jan 8, 3:05 am, "Rob V [ Sybase ]"

<r...@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY> wrote:
> The idea of using access rules is basically to do away with a permissions
> table, but implement the permissions restrictions by means of the access
> rules.
> So that would means you'd have a column in 'deals' that identifies the
> user(s) allowed to access that deal. You'd create an access rules for that
> column, and forget about deal-permission.
>
> More details
> here:http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocen...
>
> HTH,
>
> Rob V.
> -----------------------------------------------------------------
> Rob Verschoor
>
> Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
> and Replication Server 15.0.1/12.5 // TeamSybase
>
> Author of Sybase books (order online atwww.sypron.nl/shop):
> "Tips, Tricks & Recipes for Sybase ASE" (ASE 15 edition)
> "The Complete Sybase ASE Quick Reference Guide"
> "The Complete Sybase Replication Server Quick Reference Guide"