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.

GRANT UPDATE on VIEW, a known problem?

9 posts in General Discussion (old) Last posting was on 2007-06-06 09:43:39.0Z
Kevin Waugh Posted on 2007-06-01 08:57:40.0Z
From: "Kevin Waugh" <k.g.waugh@open.ac.uk>
Newsgroups: sybase.public.sqlanywhere
Subject: GRANT UPDATE on VIEW, a known problem?
Lines: 31
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1807
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1896
NNTP-Posting-Host: pcma133.open.ac.uk
X-Original-NNTP-Posting-Host: pcma133.open.ac.uk
Message-ID: <465fdf84$1@forums-1-dub>
Date: 1 Jun 2007 01:57:40 -0700
X-Trace: forums-1-dub 1180688260 137.108.10.235 (1 Jun 2007 01:57:40 -0700)
X-Original-Trace: 1 Jun 2007 01:57:40 -0700, pcma133.open.ac.uk
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:27
Article PK: 866485

Does anyone know if this is a known problem in SQLAnywhere? (It appears to
be a repeatable problem.)

Basically I have the following situation :
1) a user X owns a table T with several columns.

2) X then creates a view called V on that table. V contains only certain
rows and a WHERE clause to restrict the rows. V is created WITH CHECK
OPTION. The view is updatable, single rows including primary key, no joins,
no aggregations.

3) X grants update permission on V to another user Y. GRANT UPDATE ON V TO
Y;

4) Y should now be able to update X.V . UPDATE X.V set colx = newvalue where
keycol = value

However in SQLAnywhere at the third step above I get an error message that
table T does not exist.

I can't see anything in the SQLAnywhere manuals to suggest the above
shouldn't allow V to be updated by Y. (I've even tried creating the V with
the SELECT.. FROM statement using X.T so that there can be no issues of
ownership on the base table). The same (mis)behaviour happens with GRANT
DELETE and GRANT INSERT. However, GRANT SELECT does work as expected.

Any suggestions or pointers to known problems gratefully received.

Thanks, Kevin Waugh


Breck Carter [Team iAnywhere] Posted on 2007-06-01 14:25:21.0Z
From: "Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: sybase.public.sqlanywhere
Subject: Re: GRANT UPDATE on VIEW, a known problem?
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <h1b063l2tesmntlpbjqkjc2ros4i94i105@4ax.com>
References: <465fdf84$1@forums-1-dub>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: bcarter.sentex.ca
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
Date: 1 Jun 2007 07:25:21 -0700
X-Trace: forums-1-dub 1180707921 64.7.134.118 (1 Jun 2007 07:25:21 -0700)
X-Original-Trace: 1 Jun 2007 07:25:21 -0700, bcarter.sentex.ca
Lines: 81
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:28
Article PK: 866483

What version of SQL Anywhere are you using? What *exact commands* did
you use?

The following scripts work OK in 10.0.1.3488.

Breck

-- Connect as DBA, then run this...
GRANT CONNECT TO X IDENTIFIED BY SQL;
GRANT RESOURCE TO X;
GRANT CONNECT TO Y IDENTIFIED BY SQL;

-- Connect as X, then run this...
CREATE TABLE T (
a INTEGER NOT NULL PRIMARY KEY,
b INTEGER NOT NULL,
c INTEGER NOT NULL );
CREATE VIEW V AS
SELECT *
FROM T
WHERE a <= 10
WITH CHECK OPTION;
GRANT UPDATE ON V TO Y;
GRANT SELECT ON V TO Y;
INSERT T VALUES ( 1, 1, 1 );
INSERT T VALUES ( 100, 100, 100 );
COMMIT;
BEGIN
SELECT * FROM T;
SELECT * FROM X.V;
END;

-- Connect as Y, then run this...
UPDATE X.V SET b = 99 WHERE a = 1;
COMMIT;
SELECT * FROM X.V;

-- Result looks OK in 10.0.1.3488
a,b,c
1,99,1

On 1 Jun 2007 01:57:40 -0700, "Kevin Waugh" <k.g.waugh@open.ac.uk>

wrote:

>Does anyone know if this is a known problem in SQLAnywhere? (It appears to
>be a repeatable problem.)
>
>Basically I have the following situation :
>1) a user X owns a table T with several columns.
>
>2) X then creates a view called V on that table. V contains only certain
>rows and a WHERE clause to restrict the rows. V is created WITH CHECK
>OPTION. The view is updatable, single rows including primary key, no joins,
>no aggregations.
>
>3) X grants update permission on V to another user Y. GRANT UPDATE ON V TO
>Y;
>
>4) Y should now be able to update X.V . UPDATE X.V set colx = newvalue where
>keycol = value
>
>However in SQLAnywhere at the third step above I get an error message that
>table T does not exist.
>
>I can't see anything in the SQLAnywhere manuals to suggest the above
>shouldn't allow V to be updated by Y. (I've even tried creating the V with
>the SELECT.. FROM statement using X.T so that there can be no issues of
>ownership on the base table). The same (mis)behaviour happens with GRANT
>DELETE and GRANT INSERT. However, GRANT SELECT does work as expected.
>
>Any suggestions or pointers to known problems gratefully received.
>
>Thanks, Kevin Waugh
>

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
breck.carter@risingroad.com


Kevin Waugh Posted on 2007-06-04 09:23:41.0Z
From: "Kevin Waugh" <k.g.waugh@open.ac.uk>
Newsgroups: sybase.public.sqlanywhere
References: <465fdf84$1@forums-1-dub> <h1b063l2tesmntlpbjqkjc2ros4i94i105@4ax.com>
Subject: Re: GRANT UPDATE on VIEW, a known problem?
Lines: 175
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1807
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1896
NNTP-Posting-Host: pcma133.open.ac.uk
X-Original-NNTP-Posting-Host: pcma133.open.ac.uk
Message-ID: <4663da1d$1@forums-1-dub>
Date: 4 Jun 2007 02:23:41 -0700
X-Trace: forums-1-dub 1180949021 137.108.10.235 (4 Jun 2007 02:23:41 -0700)
X-Original-Trace: 4 Jun 2007 02:23:41 -0700, pcma133.open.ac.uk
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:29
Article PK: 866486

Hi Breck,
thanks for the feedback. Apologies for the misdirection I should have done
exactly what you did and tried to recreate the problem from scratch. I very
much appreciate your input as it made me go back and check exactly what I
had.

In fact the View was a red-herring, when I reduced it to the T table and
granted UPDATE rights on T to user Y, I got an error that the table T was
not found. This error message also misled me, it was referring to a
reference to T in a constraint defined on the table T. The T not found was
one in the constraint not in the UPDATE statement that Y was evaluating.

So, the problem I've now identified is:

The base table T contains a constraint that references a table (D) in X's
schema. CHECK(staff_no IN (SELECT staff_no FROM D));

What is happening is that the UPDATE by user Y is triggering the constraint,
which is being evaluated in Y's schema and the table D is not being found
(the original problem had a constraint that had the T table referenced in
it.) I tried writing the constraint with qualified names X.D but then the
update is halted with the error that Y does not have the required priviliges
to SELECT from X.D.

So it looks as if constraints involving table references need to be fully
qualified and SELECT priviliges, for those referenced tables, needs to be
granted to anyone who might execute code that results in the constraint
being evaluated.

Amending your script gives..
-- Connect as DBA, then run this...
GRANT CONNECT TO X IDENTIFIED BY SQL;
GRANT RESOURCE TO X;
GRANT CONNECT TO Y IDENTIFIED BY SQL;

-- Connect as X, then run this...
CREATE TABLE D (
a INTEGER NOT NULL PRIMARY KEY
);
INSERT INTO D VALUES (1);
INSERT INTO D VALUES (2);
INSERT INTO D VALUES (100);
COMMIT;

CREATE TABLE T (
a INTEGER NOT NULL PRIMARY KEY,
b INTEGER NOT NULL,
c INTEGER NOT NULL,
CONSTRAINT test_d CHECK(a IN (SELECT a FROM D WHERE a < 1000)) );
CREATE VIEW V AS
SELECT *
FROM T
WHERE a <= 10
WITH CHECK OPTION;
GRANT UPDATE ON V TO Y;
GRANT SELECT ON V TO Y;
INSERT T VALUES ( 1, 1, 1 );
INSERT T VALUES ( 100, 100, 100 );
COMMIT;
BEGIN
SELECT * FROM T;
SELECT * FROM X.V;
END;

-- Connect as Y, then run this...
UPDATE X.V SET b = 99 WHERE a = 1;
-- Error reported at this point is table 'D' not found.
COMMIT;
SELECT * FROM X.V;


replaceing the constraint above with,CONSTRAINT test_d CHECK(a IN (SELECT a
FROM X.D WHERE a < 1000)) rsults in the lack of SELECT priviliges on X.D
mentioned above.

This is in 9.0.2.2451

Sorry to have misled with the earlier example.

Kevin
========================================================

"Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com> wrote in
message news:h1b063l2tesmntlpbjqkjc2ros4i94i105@4ax.com...
> What version of SQL Anywhere are you using? What *exact commands* did
> you use?
>
> The following scripts work OK in 10.0.1.3488.
>
> Breck
>
> -- Connect as DBA, then run this...
> GRANT CONNECT TO X IDENTIFIED BY SQL;
> GRANT RESOURCE TO X;
> GRANT CONNECT TO Y IDENTIFIED BY SQL;
>
> -- Connect as X, then run this...
> CREATE TABLE T (
> a INTEGER NOT NULL PRIMARY KEY,
> b INTEGER NOT NULL,
> c INTEGER NOT NULL );
> CREATE VIEW V AS
> SELECT *
> FROM T
> WHERE a <= 10
> WITH CHECK OPTION;
> GRANT UPDATE ON V TO Y;
> GRANT SELECT ON V TO Y;
> INSERT T VALUES ( 1, 1, 1 );
> INSERT T VALUES ( 100, 100, 100 );
> COMMIT;
> BEGIN
> SELECT * FROM T;
> SELECT * FROM X.V;
> END;
>
> -- Connect as Y, then run this...
> UPDATE X.V SET b = 99 WHERE a = 1;
> COMMIT;
> SELECT * FROM X.V;
>
> -- Result looks OK in 10.0.1.3488
> a,b,c
> 1,99,1
>
> On 1 Jun 2007 01:57:40 -0700, "Kevin Waugh" <k.g.waugh@open.ac.uk>
> wrote:
>
> >Does anyone know if this is a known problem in SQLAnywhere? (It appears
to
> >be a repeatable problem.)
> >
> >Basically I have the following situation :
> >1) a user X owns a table T with several columns.
> >
> >2) X then creates a view called V on that table. V contains only certain
> >rows and a WHERE clause to restrict the rows. V is created WITH CHECK
> >OPTION. The view is updatable, single rows including primary key, no
joins,
> >no aggregations.
> >
> >3) X grants update permission on V to another user Y. GRANT UPDATE ON V
TO
> >Y;
> >
> >4) Y should now be able to update X.V . UPDATE X.V set colx = newvalue
where
> >keycol = value
> >
> >However in SQLAnywhere at the third step above I get an error message
that
> >table T does not exist.
> >
> >I can't see anything in the SQLAnywhere manuals to suggest the above
> >shouldn't allow V to be updated by Y. (I've even tried creating the V
with
> >the SELECT.. FROM statement using X.T so that there can be no issues of
> >ownership on the base table). The same (mis)behaviour happens with GRANT
> >DELETE and GRANT INSERT. However, GRANT SELECT does work as expected.
> >
> >Any suggestions or pointers to known problems gratefully received.
> >
> >Thanks, Kevin Waugh
> >
>
> --
> Breck Carter [Team iAnywhere]
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com
> The book:
http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
> breck.carter@risingroad.com


Breck Carter [Team iAnywhere] Posted on 2007-06-04 10:34:41.0Z
From: "Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: sybase.public.sqlanywhere
Subject: Re: GRANT UPDATE on VIEW, a known problem?
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <2lq763la0bdetna9jgipt4s30pk9dekin0@4ax.com>
References: <465fdf84$1@forums-1-dub> <h1b063l2tesmntlpbjqkjc2ros4i94i105@4ax.com> <4663da1d$1@forums-1-dub>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 208.253.124.99
X-Original-NNTP-Posting-Host: 208.253.124.99
Date: 4 Jun 2007 03:34:41 -0700
X-Trace: forums-1-dub 1180953281 208.253.124.99 (4 Jun 2007 03:34:41 -0700)
X-Original-Trace: 4 Jun 2007 03:34:41 -0700, 208.253.124.99
Lines: 41
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:30
Article PK: 866484

On 4 Jun 2007 02:23:41 -0700, "Kevin Waugh" <k.g.waugh@open.ac.uk>

wrote:

>replaceing the constraint above with,CONSTRAINT test_d CHECK(a IN (SELECT a
>FROM X.D WHERE a < 1000)) rsults in the lack of SELECT priviliges on X.D
>mentioned above.
>
>This is in 9.0.2.2451

That's the way it works in 10.0.1.3488 as well, and I think it's
overkill. IMO the constraints should be executed with the privileges
of the table owner rather than the caller, since that is the way that
triggers work.

Speaking of triggers, try replacing the CHECK with this:

CREATE TRIGGER T BEFORE INSERT, UPDATE ON T
REFERENCING NEW AS new_T
FOR EACH ROW
BEGIN
IF new_T.a NOT IN ( SELECT a FROM X.D WHERE a < 1000 ) THEN
ROLLBACK TRIGGER WITH RAISERROR 99999
'T.a NOT IN ( SELECT a FROM X.D WHERE a < 1000 )';
END IF;
END;

It is tempting to call a function owned by X from the CHECK
constraint, but it would have to be NOT DETERMINISTIC and those kinds
of functions cannot be called from inside CHECK constraints. I would
prefer this over a trigger, since triggers are obscure and tend to
cause maintenance grief.

Breck


--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
breck.carter@risingroad.com


Kevin Waugh Posted on 2007-06-04 10:49:12.0Z
From: "Kevin Waugh" <k.g.waugh@open.ac.uk>
Newsgroups: sybase.public.sqlanywhere
References: <465fdf84$1@forums-1-dub> <h1b063l2tesmntlpbjqkjc2ros4i94i105@4ax.com> <4663da1d$1@forums-1-dub> <2lq763la0bdetna9jgipt4s30pk9dekin0@4ax.com>
Subject: Re: GRANT UPDATE on VIEW, a known problem?
Lines: 81
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1807
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1896
NNTP-Posting-Host: pcma133.open.ac.uk
X-Original-NNTP-Posting-Host: pcma133.open.ac.uk
Message-ID: <4663ee28@forums-1-dub>
Date: 4 Jun 2007 03:49:12 -0700
X-Trace: forums-1-dub 1180954152 137.108.10.235 (4 Jun 2007 03:49:12 -0700)
X-Original-Trace: 4 Jun 2007 03:49:12 -0700, pcma133.open.ac.uk
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:31
Article PK: 866487

Thanks for the confirmation Breck.

The context for this is a teaching example.

We wanted to focus on good data control; using constraints and
privilige-based access. We chose SQLAnywhere because it appeared to have
good support for the extended CHECK constraints (many DBMSs won't permit
subqueries within a constraint) and also included ROLEs along with GRANT
priviliges. Unfortunately it looks like we've reached the limit of the
SQLAnywhere implementation of the combination of the two.

And for those worried readers who wonder why we're teaching an
implementation of constraints that are rarely implemented in major
products... it's because later we teach Triggers and, as Breck highlights, a
trigger can replace check constraints if handled very carefully. We have a
whole section of the course on how to achieve an implementation in DBMS with
different capabilities (triggers in place of constraints containing
subqueries, functions/procedures in other extreme cases. etc.) We found it
easier to teach declarative constraints before getting into procedural
execution of triggers.

Looks like we'll have to restrict constraints to the "single" user
environment.

Thanks for the input,
if there are any suggested workarounds that retain the constraint, rather
than substituting them with triggers, I'd be grateful to hear them.

Kevin Waugh

"Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com> wrote in
message news:2lq763la0bdetna9jgipt4s30pk9dekin0@4ax.com...
> On 4 Jun 2007 02:23:41 -0700, "Kevin Waugh" <k.g.waugh@open.ac.uk>
> wrote:
>
> >replaceing the constraint above with,CONSTRAINT test_d CHECK(a IN (SELECT
a
> >FROM X.D WHERE a < 1000)) rsults in the lack of SELECT priviliges on X.D
> >mentioned above.
> >
> >This is in 9.0.2.2451
>
> That's the way it works in 10.0.1.3488 as well, and I think it's
> overkill. IMO the constraints should be executed with the privileges
> of the table owner rather than the caller, since that is the way that
> triggers work.
>
> Speaking of triggers, try replacing the CHECK with this:
>
> CREATE TRIGGER T BEFORE INSERT, UPDATE ON T
> REFERENCING NEW AS new_T
> FOR EACH ROW
> BEGIN
> IF new_T.a NOT IN ( SELECT a FROM X.D WHERE a < 1000 ) THEN
> ROLLBACK TRIGGER WITH RAISERROR 99999
> 'T.a NOT IN ( SELECT a FROM X.D WHERE a < 1000 )';
> END IF;
> END;
>
> It is tempting to call a function owned by X from the CHECK
> constraint, but it would have to be NOT DETERMINISTIC and those kinds
> of functions cannot be called from inside CHECK constraints. I would
> prefer this over a trigger, since triggers are obscure and tend to
> cause maintenance grief.
>
> Breck
>
>
> --
> Breck Carter [Team iAnywhere]
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com
> The book:
http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
> breck.carter@risingroad.com


Breck Carter [Team iAnywhere] Posted on 2007-06-04 20:25:38.0Z
From: "Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: sybase.public.sqlanywhere
Subject: Re: GRANT UPDATE on VIEW, a known problem?
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <k5t863pqck3npbjpa54mjse4v741amu3b6@4ax.com>
References: <465fdf84$1@forums-1-dub> <h1b063l2tesmntlpbjqkjc2ros4i94i105@4ax.com> <4663da1d$1@forums-1-dub> <2lq763la0bdetna9jgipt4s30pk9dekin0@4ax.com> <4663ee28@forums-1-dub>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 199.90.35.10
X-Original-NNTP-Posting-Host: 199.90.35.10
Date: 4 Jun 2007 13:25:38 -0700
X-Trace: forums-1-dub 1180988738 199.90.35.10 (4 Jun 2007 13:25:38 -0700)
X-Original-Trace: 4 Jun 2007 13:25:38 -0700, 199.90.35.10
Lines: 98
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:34
Article PK: 866490

FWIW, in the real world, for most applications, SELECT privileges are
granted to all users... this may be the reason why we have this
limitation: nobody's really complained yet because they haven't
noticed it. So GRANT SELECT on everything is not a stretch, especially
for teaching purposes. IMO anyway :)

Breck

On 4 Jun 2007 03:49:12 -0700, "Kevin Waugh" <k.g.waugh@open.ac.uk>

wrote:

>Thanks for the confirmation Breck.
>
>The context for this is a teaching example.
>
>We wanted to focus on good data control; using constraints and
>privilige-based access. We chose SQLAnywhere because it appeared to have
>good support for the extended CHECK constraints (many DBMSs won't permit
>subqueries within a constraint) and also included ROLEs along with GRANT
>priviliges. Unfortunately it looks like we've reached the limit of the
>SQLAnywhere implementation of the combination of the two.
>
>And for those worried readers who wonder why we're teaching an
>implementation of constraints that are rarely implemented in major
>products... it's because later we teach Triggers and, as Breck highlights, a
>trigger can replace check constraints if handled very carefully. We have a
>whole section of the course on how to achieve an implementation in DBMS with
>different capabilities (triggers in place of constraints containing
>subqueries, functions/procedures in other extreme cases. etc.) We found it
>easier to teach declarative constraints before getting into procedural
>execution of triggers.
>
>Looks like we'll have to restrict constraints to the "single" user
>environment.
>
>Thanks for the input,
>if there are any suggested workarounds that retain the constraint, rather
>than substituting them with triggers, I'd be grateful to hear them.
>
>Kevin Waugh
>
>
>
>
>
>"Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com> wrote in
>message news:2lq763la0bdetna9jgipt4s30pk9dekin0@4ax.com...
>> On 4 Jun 2007 02:23:41 -0700, "Kevin Waugh" <k.g.waugh@open.ac.uk>
>> wrote:
>>
>> >replaceing the constraint above with,CONSTRAINT test_d CHECK(a IN (SELECT
>a
>> >FROM X.D WHERE a < 1000)) rsults in the lack of SELECT priviliges on X.D
>> >mentioned above.
>> >
>> >This is in 9.0.2.2451
>>
>> That's the way it works in 10.0.1.3488 as well, and I think it's
>> overkill. IMO the constraints should be executed with the privileges
>> of the table owner rather than the caller, since that is the way that
>> triggers work.
>>
>> Speaking of triggers, try replacing the CHECK with this:
>>
>> CREATE TRIGGER T BEFORE INSERT, UPDATE ON T
>> REFERENCING NEW AS new_T
>> FOR EACH ROW
>> BEGIN
>> IF new_T.a NOT IN ( SELECT a FROM X.D WHERE a < 1000 ) THEN
>> ROLLBACK TRIGGER WITH RAISERROR 99999
>> 'T.a NOT IN ( SELECT a FROM X.D WHERE a < 1000 )';
>> END IF;
>> END;
>>
>> It is tempting to call a function owned by X from the CHECK
>> constraint, but it would have to be NOT DETERMINISTIC and those kinds
>> of functions cannot be called from inside CHECK constraints. I would
>> prefer this over a trigger, since triggers are obscure and tend to
>> cause maintenance grief.
>>
>> Breck
>>
>>
>> --
>> Breck Carter [Team iAnywhere]
>> RisingRoad SQL Anywhere and MobiLink Professional Services
>> www.risingroad.com
>> The book:
>http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
>> breck.carter@risingroad.com
>

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
breck.carter@risingroad.com


Kevin Waugh Posted on 2007-06-05 10:03:58.0Z
From: "Kevin Waugh" <k.g.waugh@open.ac.uk>
Newsgroups: sybase.public.sqlanywhere
References: <465fdf84$1@forums-1-dub> <h1b063l2tesmntlpbjqkjc2ros4i94i105@4ax.com> <4663da1d$1@forums-1-dub> <2lq763la0bdetna9jgipt4s30pk9dekin0@4ax.com> <4663ee28@forums-1-dub> <k5t863pqck3npbjpa54mjse4v741amu3b6@4ax.com>
Subject: Re: GRANT UPDATE on VIEW, a known problem?
Lines: 121
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1807
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1896
NNTP-Posting-Host: pcma133.open.ac.uk
X-Original-NNTP-Posting-Host: pcma133.open.ac.uk
Message-ID: <4665350e$1@forums-1-dub>
Date: 5 Jun 2007 03:03:58 -0700
X-Trace: forums-1-dub 1181037838 137.108.10.235 (5 Jun 2007 03:03:58 -0700)
X-Original-Trace: 5 Jun 2007 03:03:58 -0700, pcma133.open.ac.uk
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:35
Article PK: 866491

Sounds like that limits "non-disclosure" security quite a bit.
I'd have thought the ability to partition the visible parts of the data
would be a useful facility.

As you say we'll find a way forward for teaching purposes.

Thanks again for the input,
Kevin Waugh

"Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com> wrote in
message news:k5t863pqck3npbjpa54mjse4v741amu3b6@4ax.com...
> FWIW, in the real world, for most applications, SELECT privileges are
> granted to all users... this may be the reason why we have this
> limitation: nobody's really complained yet because they haven't
> noticed it. So GRANT SELECT on everything is not a stretch, especially
> for teaching purposes. IMO anyway :)
>
> Breck
>
> On 4 Jun 2007 03:49:12 -0700, "Kevin Waugh" <k.g.waugh@open.ac.uk>
> wrote:
>
> >Thanks for the confirmation Breck.
> >
> >The context for this is a teaching example.
> >
> >We wanted to focus on good data control; using constraints and
> >privilige-based access. We chose SQLAnywhere because it appeared to have
> >good support for the extended CHECK constraints (many DBMSs won't permit
> >subqueries within a constraint) and also included ROLEs along with GRANT
> >priviliges. Unfortunately it looks like we've reached the limit of the
> >SQLAnywhere implementation of the combination of the two.
> >
> >And for those worried readers who wonder why we're teaching an
> >implementation of constraints that are rarely implemented in major
> >products... it's because later we teach Triggers and, as Breck
highlights, a
> >trigger can replace check constraints if handled very carefully. We have
a
> >whole section of the course on how to achieve an implementation in DBMS
with
> >different capabilities (triggers in place of constraints containing
> >subqueries, functions/procedures in other extreme cases. etc.) We found
it
> >easier to teach declarative constraints before getting into procedural
> >execution of triggers.
> >
> >Looks like we'll have to restrict constraints to the "single" user
> >environment.
> >
> >Thanks for the input,
> >if there are any suggested workarounds that retain the constraint, rather
> >than substituting them with triggers, I'd be grateful to hear them.
> >
> >Kevin Waugh
> >
> >
> >
> >
> >
> >"Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com> wrote in
> >message news:2lq763la0bdetna9jgipt4s30pk9dekin0@4ax.com...
> >> On 4 Jun 2007 02:23:41 -0700, "Kevin Waugh" <k.g.waugh@open.ac.uk>
> >> wrote:
> >>
> >> >replaceing the constraint above with,CONSTRAINT test_d CHECK(a IN
(SELECT
> >a
> >> >FROM X.D WHERE a < 1000)) rsults in the lack of SELECT priviliges on
X.D
> >> >mentioned above.
> >> >
> >> >This is in 9.0.2.2451
> >>
> >> That's the way it works in 10.0.1.3488 as well, and I think it's
> >> overkill. IMO the constraints should be executed with the privileges
> >> of the table owner rather than the caller, since that is the way that
> >> triggers work.
> >>
> >> Speaking of triggers, try replacing the CHECK with this:
> >>
> >> CREATE TRIGGER T BEFORE INSERT, UPDATE ON T
> >> REFERENCING NEW AS new_T
> >> FOR EACH ROW
> >> BEGIN
> >> IF new_T.a NOT IN ( SELECT a FROM X.D WHERE a < 1000 ) THEN
> >> ROLLBACK TRIGGER WITH RAISERROR 99999
> >> 'T.a NOT IN ( SELECT a FROM X.D WHERE a < 1000 )';
> >> END IF;
> >> END;
> >>
> >> It is tempting to call a function owned by X from the CHECK
> >> constraint, but it would have to be NOT DETERMINISTIC and those kinds
> >> of functions cannot be called from inside CHECK constraints. I would
> >> prefer this over a trigger, since triggers are obscure and tend to
> >> cause maintenance grief.
> >>
> >> Breck
> >>
> >>
> >> --
> >> Breck Carter [Team iAnywhere]
> >> RisingRoad SQL Anywhere and MobiLink Professional Services
> >> www.risingroad.com
> >> The book:
> >http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
> >> breck.carter@risingroad.com
> >
>
> --
> Breck Carter [Team iAnywhere]
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com
> The book:
http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
> breck.carter@risingroad.com


Breck Carter [Team iAnywhere] Posted on 2007-06-05 14:14:02.0Z
From: "Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: sybase.public.sqlanywhere
Subject: Re: GRANT UPDATE on VIEW, a known problem?
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <hpra63tu3aju5fm0kosa7qibmi80n4vo0v@4ax.com>
References: <465fdf84$1@forums-1-dub> <h1b063l2tesmntlpbjqkjc2ros4i94i105@4ax.com> <4663da1d$1@forums-1-dub> <2lq763la0bdetna9jgipt4s30pk9dekin0@4ax.com> <4663ee28@forums-1-dub> <k5t863pqck3npbjpa54mjse4v741amu3b6@4ax.com> <4665350e$1@forums-1-dub>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 199.90.35.10
X-Original-NNTP-Posting-Host: 199.90.35.10
Date: 5 Jun 2007 07:14:02 -0700
X-Trace: forums-1-dub 1181052842 199.90.35.10 (5 Jun 2007 07:14:02 -0700)
X-Original-Trace: 5 Jun 2007 07:14:02 -0700, 199.90.35.10
Lines: 139
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:36
Article PK: 866492

You are right. As I said earlier, I think it is overkill to require
SELECT privileges for a constraint when you don't need them for a
trigger; i.e., the constraint should run with the privileges of the
table owner, not the user invoking the constraint. If you want to post
a suggestion in the product_futures_discussion newsgroup, I'll support
ya.

Breck

On 5 Jun 2007 03:03:58 -0700, "Kevin Waugh" <k.g.waugh@open.ac.uk>

wrote:

>
>Sounds like that limits "non-disclosure" security quite a bit.
>I'd have thought the ability to partition the visible parts of the data
>would be a useful facility.
>
>As you say we'll find a way forward for teaching purposes.
>
>Thanks again for the input,
>Kevin Waugh
>
>
>
>"Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com> wrote in
>message news:k5t863pqck3npbjpa54mjse4v741amu3b6@4ax.com...
>> FWIW, in the real world, for most applications, SELECT privileges are
>> granted to all users... this may be the reason why we have this
>> limitation: nobody's really complained yet because they haven't
>> noticed it. So GRANT SELECT on everything is not a stretch, especially
>> for teaching purposes. IMO anyway :)
>>
>> Breck
>>
>> On 4 Jun 2007 03:49:12 -0700, "Kevin Waugh" <k.g.waugh@open.ac.uk>
>> wrote:
>>
>> >Thanks for the confirmation Breck.
>> >
>> >The context for this is a teaching example.
>> >
>> >We wanted to focus on good data control; using constraints and
>> >privilige-based access. We chose SQLAnywhere because it appeared to have
>> >good support for the extended CHECK constraints (many DBMSs won't permit
>> >subqueries within a constraint) and also included ROLEs along with GRANT
>> >priviliges. Unfortunately it looks like we've reached the limit of the
>> >SQLAnywhere implementation of the combination of the two.
>> >
>> >And for those worried readers who wonder why we're teaching an
>> >implementation of constraints that are rarely implemented in major
>> >products... it's because later we teach Triggers and, as Breck
>highlights, a
>> >trigger can replace check constraints if handled very carefully. We have
>a
>> >whole section of the course on how to achieve an implementation in DBMS
>with
>> >different capabilities (triggers in place of constraints containing
>> >subqueries, functions/procedures in other extreme cases. etc.) We found
>it
>> >easier to teach declarative constraints before getting into procedural
>> >execution of triggers.
>> >
>> >Looks like we'll have to restrict constraints to the "single" user
>> >environment.
>> >
>> >Thanks for the input,
>> >if there are any suggested workarounds that retain the constraint, rather
>> >than substituting them with triggers, I'd be grateful to hear them.
>> >
>> >Kevin Waugh
>> >
>> >
>> >
>> >
>> >
>> >"Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com> wrote in
>> >message news:2lq763la0bdetna9jgipt4s30pk9dekin0@4ax.com...
>> >> On 4 Jun 2007 02:23:41 -0700, "Kevin Waugh" <k.g.waugh@open.ac.uk>
>> >> wrote:
>> >>
>> >> >replaceing the constraint above with,CONSTRAINT test_d CHECK(a IN
>(SELECT
>> >a
>> >> >FROM X.D WHERE a < 1000)) rsults in the lack of SELECT priviliges on
>X.D
>> >> >mentioned above.
>> >> >
>> >> >This is in 9.0.2.2451
>> >>
>> >> That's the way it works in 10.0.1.3488 as well, and I think it's
>> >> overkill. IMO the constraints should be executed with the privileges
>> >> of the table owner rather than the caller, since that is the way that
>> >> triggers work.
>> >>
>> >> Speaking of triggers, try replacing the CHECK with this:
>> >>
>> >> CREATE TRIGGER T BEFORE INSERT, UPDATE ON T
>> >> REFERENCING NEW AS new_T
>> >> FOR EACH ROW
>> >> BEGIN
>> >> IF new_T.a NOT IN ( SELECT a FROM X.D WHERE a < 1000 ) THEN
>> >> ROLLBACK TRIGGER WITH RAISERROR 99999
>> >> 'T.a NOT IN ( SELECT a FROM X.D WHERE a < 1000 )';
>> >> END IF;
>> >> END;
>> >>
>> >> It is tempting to call a function owned by X from the CHECK
>> >> constraint, but it would have to be NOT DETERMINISTIC and those kinds
>> >> of functions cannot be called from inside CHECK constraints. I would
>> >> prefer this over a trigger, since triggers are obscure and tend to
>> >> cause maintenance grief.
>> >>
>> >> Breck
>> >>
>> >>
>> >> --
>> >> Breck Carter [Team iAnywhere]
>> >> RisingRoad SQL Anywhere and MobiLink Professional Services
>> >> www.risingroad.com
>> >> The book:
>> >http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
>> >> breck.carter@risingroad.com
>> >
>>
>> --
>> Breck Carter [Team iAnywhere]
>> RisingRoad SQL Anywhere and MobiLink Professional Services
>> www.risingroad.com
>> The book:
>http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
>> breck.carter@risingroad.com
>

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
breck.carter@risingroad.com


Kevin Waugh Posted on 2007-06-06 09:43:39.0Z
From: "Kevin Waugh" <k.g.waugh@open.ac.uk>
Newsgroups: sybase.public.sqlanywhere
References: <465fdf84$1@forums-1-dub> <h1b063l2tesmntlpbjqkjc2ros4i94i105@4ax.com> <4663da1d$1@forums-1-dub> <2lq763la0bdetna9jgipt4s30pk9dekin0@4ax.com> <4663ee28@forums-1-dub> <k5t863pqck3npbjpa54mjse4v741amu3b6@4ax.com> <4665350e$1@forums-1-dub> <hpra63tu3aju5fm0kosa7qibmi80n4vo0v@4ax.com>
Subject: Re: GRANT UPDATE on VIEW, a known problem?
Lines: 162
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1807
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1896
NNTP-Posting-Host: pcma133.open.ac.uk
X-Original-NNTP-Posting-Host: pcma133.open.ac.uk
Message-ID: <466681cb$1@forums-1-dub>
Date: 6 Jun 2007 02:43:39 -0700
X-Trace: forums-1-dub 1181123019 137.108.10.235 (6 Jun 2007 02:43:39 -0700)
X-Original-Trace: 6 Jun 2007 02:43:39 -0700, pcma133.open.ac.uk
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:37
Article PK: 866493

Hi Breck,

I hadn't seen the futures discussion group, I'll post the suggestion there.
Thanks again for the interest in this problem,
Kevin

"Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com> wrote in
message news:hpra63tu3aju5fm0kosa7qibmi80n4vo0v@4ax.com...
> You are right. As I said earlier, I think it is overkill to require
> SELECT privileges for a constraint when you don't need them for a
> trigger; i.e., the constraint should run with the privileges of the
> table owner, not the user invoking the constraint. If you want to post
> a suggestion in the product_futures_discussion newsgroup, I'll support
> ya.
>
> Breck
>
> On 5 Jun 2007 03:03:58 -0700, "Kevin Waugh" <k.g.waugh@open.ac.uk>
> wrote:
>
> >
> >Sounds like that limits "non-disclosure" security quite a bit.
> >I'd have thought the ability to partition the visible parts of the data
> >would be a useful facility.
> >
> >As you say we'll find a way forward for teaching purposes.
> >
> >Thanks again for the input,
> >Kevin Waugh
> >
> >
> >
> >"Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com> wrote in
> >message news:k5t863pqck3npbjpa54mjse4v741amu3b6@4ax.com...
> >> FWIW, in the real world, for most applications, SELECT privileges are
> >> granted to all users... this may be the reason why we have this
> >> limitation: nobody's really complained yet because they haven't
> >> noticed it. So GRANT SELECT on everything is not a stretch, especially
> >> for teaching purposes. IMO anyway :)
> >>
> >> Breck
> >>
> >> On 4 Jun 2007 03:49:12 -0700, "Kevin Waugh" <k.g.waugh@open.ac.uk>
> >> wrote:
> >>
> >> >Thanks for the confirmation Breck.
> >> >
> >> >The context for this is a teaching example.
> >> >
> >> >We wanted to focus on good data control; using constraints and
> >> >privilige-based access. We chose SQLAnywhere because it appeared to
have
> >> >good support for the extended CHECK constraints (many DBMSs won't
permit
> >> >subqueries within a constraint) and also included ROLEs along with
GRANT
> >> >priviliges. Unfortunately it looks like we've reached the limit of
the
> >> >SQLAnywhere implementation of the combination of the two.
> >> >
> >> >And for those worried readers who wonder why we're teaching an
> >> >implementation of constraints that are rarely implemented in major
> >> >products... it's because later we teach Triggers and, as Breck
> >highlights, a
> >> >trigger can replace check constraints if handled very carefully. We
have
> >a
> >> >whole section of the course on how to achieve an implementation in
DBMS
> >with
> >> >different capabilities (triggers in place of constraints containing
> >> >subqueries, functions/procedures in other extreme cases. etc.) We
found
> >it
> >> >easier to teach declarative constraints before getting into procedural
> >> >execution of triggers.
> >> >
> >> >Looks like we'll have to restrict constraints to the "single" user
> >> >environment.
> >> >
> >> >Thanks for the input,
> >> >if there are any suggested workarounds that retain the constraint,
rather
> >> >than substituting them with triggers, I'd be grateful to hear them.
> >> >
> >> >Kevin Waugh
> >> >
> >> >
> >> >
> >> >
> >> >
> >> >"Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com> wrote
in
> >> >message news:2lq763la0bdetna9jgipt4s30pk9dekin0@4ax.com...
> >> >> On 4 Jun 2007 02:23:41 -0700, "Kevin Waugh" <k.g.waugh@open.ac.uk>
> >> >> wrote:
> >> >>
> >> >> >replaceing the constraint above with,CONSTRAINT test_d CHECK(a IN
> >(SELECT
> >> >a
> >> >> >FROM X.D WHERE a < 1000)) rsults in the lack of SELECT priviliges
on
> >X.D
> >> >> >mentioned above.
> >> >> >
> >> >> >This is in 9.0.2.2451
> >> >>
> >> >> That's the way it works in 10.0.1.3488 as well, and I think it's
> >> >> overkill. IMO the constraints should be executed with the privileges
> >> >> of the table owner rather than the caller, since that is the way
that
> >> >> triggers work.
> >> >>
> >> >> Speaking of triggers, try replacing the CHECK with this:
> >> >>
> >> >> CREATE TRIGGER T BEFORE INSERT, UPDATE ON T
> >> >> REFERENCING NEW AS new_T
> >> >> FOR EACH ROW
> >> >> BEGIN
> >> >> IF new_T.a NOT IN ( SELECT a FROM X.D WHERE a < 1000 ) THEN
> >> >> ROLLBACK TRIGGER WITH RAISERROR 99999
> >> >> 'T.a NOT IN ( SELECT a FROM X.D WHERE a < 1000 )';
> >> >> END IF;
> >> >> END;
> >> >>
> >> >> It is tempting to call a function owned by X from the CHECK
> >> >> constraint, but it would have to be NOT DETERMINISTIC and those
kinds
> >> >> of functions cannot be called from inside CHECK constraints. I would
> >> >> prefer this over a trigger, since triggers are obscure and tend to
> >> >> cause maintenance grief.
> >> >>
> >> >> Breck
> >> >>
> >> >>
> >> >> --
> >> >> Breck Carter [Team iAnywhere]
> >> >> RisingRoad SQL Anywhere and MobiLink Professional Services
> >> >> www.risingroad.com
> >> >> The book:
> >> >http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
> >> >> breck.carter@risingroad.com
> >> >
> >>
> >> --
> >> Breck Carter [Team iAnywhere]
> >> RisingRoad SQL Anywhere and MobiLink Professional Services
> >> www.risingroad.com
> >> The book:
> >http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
> >> breck.carter@risingroad.com
> >
>
> --
> Breck Carter [Team iAnywhere]
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com
> The book:
http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
> breck.carter@risingroad.com