So we have four SQL Remote Users that we are attempting to revoke from the list of SQL Remote Users on our production database.

I would remove the users outright, but they will still be logging into the database internally in the office.

When I attempt to revoke each remote, I get the warning about subscriptions. I say I'm sure. Revoke the user's remote status.

Immediately I receive an error saying - The user user_name could not be modified in the database. Cannot revoke the permissions while user user_name is actively replicating transactions.

SQLCODE: -1080 SQLSTATE: 40001 SQL Statement: REVOKE REMOTE FROM user_name

I went into each users subscriptions and turned them off. And then removed them. I also did a remote reset on each user.

When I attempted to revoke the remote again, I still received the same error.

Am I missing something additional that needs to be deleted or changed in order to revoke these users from the SQL Remote Users list?

Any thoughts would be greatly appreciated!!

SQL Anywhere 12.0.1.3942

Thanks!

Jeff Gibson
Intercept Solutions
Nashville, TN

asked 23 Sep '16, 15:43

Jeff%20Gibson's gravatar image

Jeff Gibson
1.8k406172
accept rate: 20%

Dropping the subscriptions would be the missing step I believe.

(23 Sep '16, 17:07) Nick Elson S...
Replies hidden

I went into each users subscriptions and turned them off. And then removed them.

I think that's meant with dropping, correct?

However, as to the docs (at tleast those for v16), a REVOKE REMOTE statement already does drop all subscriptions for the according user:

Automatic commit. Drops all subscriptions for the user.

Hm, I'm not sure whether this is different for v12. We have always simply dropped remotes by issuing REVOKE CONNECT from the according users, and that both revoked the REMOTE privilege and dropped all according subscriptions, even with v8 and before... However, those were always particular users that did not connect locally.


Besides that, is it possibly that SQL Remote has been running at the consolidated while you tried to revoke the remote privilege of those users, and/or were they connected locally at that time? (I don't know whether this should matter but I'd try when SQL Remote is not currently running and the users are not connected...)

(26 Sep '16, 02:55) Volker Barth

It would appear Volker is correct. You can get this error when either dropping or revoking a remote user. It will be thrown if dbremote was left running.

(26 Sep '16, 09:40) Nick Elson S...

Is the user you are trying to revoke remote from connected to the database on another connection and using resources? If so, do you know what they are doing?

Is dbremote running at the time you try and REVOKE REMOTE?

Reg

(26 Sep '16, 09:54) Reg Domaratzki
Replies hidden

Not at all Reg. We had kicked everyone out. Replication runs at the top of the hour at this site. We ended up even shutting down the service that fired off replication on the hour.

So I'm still stuck on these 4 users that can be revoked from a sqlremoteuser status back to a normal user status.

Thoughts?

(27 Sep '16, 02:34) Jeff Gibson

We even stopped and then dropped the subscriptions manually on all of these remote users before attempting to remove them. So I'm still at a loss on where to go with this.

(27 Sep '16, 02:37) Jeff Gibson

That's correct Volker. By remove I meant drop. But I was handling everything through the interface. Not the command line.

(27 Sep '16, 02:37) Jeff Gibson

Puzzling. Just another wild guess: Would it be possible to drop those users completely and add them back as normal users?

(In order to preserve their passwords, you might do a reload first and preserve the according GRANT CONNECT IDENTIFIED BY ENCRYPTED... statements and re-apply them (and any other according GRANT statements) after the users have been dropped.)

(27 Sep '16, 03:05) Volker Barth

Is it possible that you have events defined in the database that are holding resources preventing the REVOKE REMOTE?

This is the second issue you've posted that seemed to be about a mysterious connection holding locks/resources. What was the solution to the issue in August?

http://sqlanywhere-forum.sap.com/questions/27794/need-help-with-a-blocked-passthrough

Reg

(27 Sep '16, 08:20) Reg Domaratzki
showing 3 of 9 show all flat view
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×438
×103

question asked: 23 Sep '16, 15:43

question was seen: 2,093 times

last updated: 27 Sep '16, 08:29