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.

REVOKE/GRANT inside conditional block?

3 posts in General Discussion Last posting was on 2004-09-07 17:31:18.0Z
Mike Dodd Posted on 2004-09-02 20:20:37.0Z
Sender: 4731.41377f6c.1804289383@sybase.com
From: Mike Dodd
Newsgroups: ianywhere.public.general
Subject: REVOKE/GRANT inside conditional block?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <41378095.473b.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 2 Sep 2004 13:20:37 -0700
X-Trace: forums-1-dub 1094156437 10.22.241.41 (2 Sep 2004 13:20:37 -0700)
X-Original-Trace: 2 Sep 2004 13:20:37 -0700, 10.22.241.41
Lines: 43
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3484
Article PK: 7388

I have 25 separate (but related) databases where I need to
change the user IDs from one format to another. The best
approach I've come up with is a SQL script that has GRANT
statements for all users in all databases (I can easily get
this information). However, not all users are in every
database, so I need conditional statements. Here's a sample:

SET TEMPORARY OPTION ON_ERROR = CONTINUE;
IF 'old_ID' IN (SELECT user_name FROM sysuserperm) THEN
REVOKE CONNECT FROM new_ID;
GRANT CONNECT TO new_ID IDENTIFIED BY newuser1234;
IF 'GROUP_1' IN (SELECT group_name FROM sysgroups WHERE
member_name = 'old_ID') THEN
GRANT MEMBERSHIP IN GROUP GROUP_1 TO new_ID;
END IF;
END IF;

This checks the sysuserperm view to see if the old ID exists
in this database. If so, it revokes the new ID (in case I
have to run the script multiple times), and creates the new
ID with a password. It then checks the sysgroups view to
find out what groups the old ID is in, and puts the new ID
in the same groups (not all groups are shown
here).

The problem is, SQL Anywhere 8 doesn't want to execute the
REVOKE or GRANT statements inside the conditional block. I
get a -140 error (user doesn't exist), presumably on the
REVOKE statement, but the script doesn't continue to the
GRANT statement; the user is never created. If I comment-out
the IF/END IF statements, the script executs correctly.

Is there something I should be doing to make the REVOKE and
GRANT statements work like this? Or is it impossible for
them to execute inside a conditional statement?

If the latter, does anyone have a suggestion on a better way
to do
this?

Thanks in advance for any help.

Mike


"Bruce Hay" <h_a_y Posted on 2004-09-03 14:08:55.0Z
From: "Bruce Hay" <h_a_y@i~a~n~y~w~h~e~r~e.com>
Newsgroups: ianywhere.public.general
References: <41378095.473b.1681692777@sybase.com>
Subject: Re: REVOKE/GRANT inside conditional block?
Lines: 53
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
X-Original-NNTP-Posting-Host: hay-t30.sybase.com
Message-ID: <41387c86$1@forums-2-dub>
X-Original-Trace: 3 Sep 2004 07:15:34 -0700, hay-t30.sybase.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 3 Sep 2004 06:57:58 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 3 Sep 2004 07:08:55 -0700
X-Trace: forums-1-dub 1094220535 10.22.108.75 (3 Sep 2004 07:08:55 -0700)
X-Original-Trace: 3 Sep 2004 07:08:55 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3487
Article PK: 7389

The On_error option affects how DBISQL handles errors in a script, but not
how the server handles errors in a batch. The IF ... END IF statement is a
batch and constitutes your entire script, so setting the option is pointless
in this case. When the server sees the first error while running the batch,
it terminates the batch. You should add another IF statement to
conditionally execute the REVOKE only if new_ID exists.

<Mike Dodd> wrote in message news:41378095.473b.1681692777@sybase.com...
> I have 25 separate (but related) databases where I need to
> change the user IDs from one format to another. The best
> approach I've come up with is a SQL script that has GRANT
> statements for all users in all databases (I can easily get
> this information). However, not all users are in every
> database, so I need conditional statements. Here's a sample:
>
> SET TEMPORARY OPTION ON_ERROR = CONTINUE;
> IF 'old_ID' IN (SELECT user_name FROM sysuserperm) THEN
> REVOKE CONNECT FROM new_ID;
> GRANT CONNECT TO new_ID IDENTIFIED BY newuser1234;
> IF 'GROUP_1' IN (SELECT group_name FROM sysgroups WHERE
> member_name = 'old_ID') THEN
> GRANT MEMBERSHIP IN GROUP GROUP_1 TO new_ID;
> END IF;
> END IF;
>
> This checks the sysuserperm view to see if the old ID exists
> in this database. If so, it revokes the new ID (in case I
> have to run the script multiple times), and creates the new
> ID with a password. It then checks the sysgroups view to
> find out what groups the old ID is in, and puts the new ID
> in the same groups (not all groups are shown
> here).
>
> The problem is, SQL Anywhere 8 doesn't want to execute the
> REVOKE or GRANT statements inside the conditional block. I
> get a -140 error (user doesn't exist), presumably on the
> REVOKE statement, but the script doesn't continue to the
> GRANT statement; the user is never created. If I comment-out
> the IF/END IF statements, the script executs correctly.
>
> Is there something I should be doing to make the REVOKE and
> GRANT statements work like this? Or is it impossible for
> them to execute inside a conditional statement?
>
> If the latter, does anyone have a suggestion on a better way
> to do
> this?
>
> Thanks in advance for any help.
>
> Mike


Mike Dodd Posted on 2004-09-07 17:31:18.0Z
Sender: 676b.413df17a.1804289383@sybase.com
From: Mike Dodd
Newsgroups: ianywhere.public.general
Subject: Re: REVOKE/GRANT inside conditional block?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <413df204.676f.1681692777@sybase.com>
References: <41387c86$1@forums-2-dub>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 7 Sep 2004 10:38:12 -0700, 10.22.241.42
Lines: 25
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 7 Sep 2004 10:19:57 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 7 Sep 2004 10:31:18 -0700
X-Trace: forums-1-dub 1094578278 10.22.108.75 (7 Sep 2004 10:31:18 -0700)
X-Original-Trace: 7 Sep 2004 10:31:18 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3497
Article PK: 7398


> When the server sees the first error while
> running the batch, it terminates the batch. You should add
> another IF statement to conditionally execute the REVOKE
> only if new_ID exists.

That did the trick; thank you very much.

In the interest of sharing information, here is the
successful script. The semicolons are needed after the END
IF statements because the actual script has multiple lines
like this.

IF TRIM('old_ID') IN (SELECT user_name FROM sysuserperm)
THEN
IF TRIM('new_ID') IN (SELECT user_name FROM sysuserperm)
THEN
REVOKE CONNECT FROM new_ID;
END IF;
GRANT CONNECT TO new_ID IDENTIFIED BY newuser1234;
IF 'OWNER' IN (SELECT group_name FROM sysgroups WHERE
member_name = TRIM('old_ID')) THEN
GRANT MEMBERSHIP IN GROUP OWNER TO new_ID;
ELSEIF (...other groups here...)
END IF;
END IF;