SQL Remote Publisher Clarification

[former_SQLA_member1694871]

We are getting ready to start replication testing on our development server under SQL Anywhere 12. This is a database that we migrated in stages from ASA6 up to SQL Anywhere 12.

Under our old ASA6 deployment, if you were to select the publisher under Users & Groups, or select the shortcut to your publisher under the SQL Remote folder in Sybase Central, you used to get a properties window that had a permissions tab where you could move between tables, views and procedures.

For the record, our publisher user is inherited from the public group.

What I've noticed is that if I try to view the permissions on our publisher under ASA6, we see a list of all of our available tables, views or procedures on that tab. Each permission is set as a "--", but my impression was the it was inheriting its permissions from public.

What I've noticed under SQL Anywhere 12, is that if you have selected your publisher (which in this instance is still inherited from public), and try to view table, view or procedure permissions, you get nothing in the list. I can add tables to the list by right clicking on the list and selecting tables (or views, or procedures), but I'm not sure if I'm doing something I don't need to do.

What I don't want to do is end up stepping on my permissions that are established by the public group.

Can somebody tell me if this is just way the UI works in Sybase Central 12 and that table, view or procedure permissions are invoked automatically from the public group?

Please advise if you need any clarification. TIA for any help.

Jeff Gibson
Intercept Solutions - Sybase SQL Anywhere OEM Partner
Nashville, TN

[former_SQLA_member1694871]

And for the record. I was all set to add some screen shots, but forum rules precluded me from doing that since I didn't have 100 reputation points. Any and all views and comments would be appreciated. 🙂

[VolkerBarth]

By "our publisher user is inherited from the public group" I assume you say your publisher is a member of the public group?

I don't know if I understand you correctly, and I usually don't use SC at all, but with SA 12, permissions granted to a group are still implicitly granted to all group members.

[VolkerBarth]

I don't think this limit is very useful. Maybe there should be a limit for uploading general files but for images, I think it's a needless hurdle.

At least now you're closer to the rep limit:)

[Breck_Carter]

PUBLIC doesn't have any permissions on user tables so nothing in inherited (no SELECT, UPDATE, etc). AFAIK the publisher user id doesn't need any permissions on user tables, just GRANT PUBLISH. I'm guessing you are seeing a GUI behavior change that is of no consequence.

[VolkerBarth]

I can confirm that the publisher does just need GRANT PUBLISH permission, nothing more. It's not even necessary to be able to connect as that user (so the PWD can be empty).

[former_SQLA_member1694871]

That would be correct Volker. Our publisher is a member of the public group.

I was just trying to get across that the permissions have been set at the group level. I always think of it as a user inheriting the permissions that are set at the group level.

[former_SQLA_member1694871]

But it does if you assign public as the user/group that has permissions to the table. 🙂

(For all I know that may be a design mistake that was made years ago when the database was created).

I did a test in ASA6. I added a test table to our development database (usertest). However, I did not set any permissions on the table.

Then I looked at the permissions on the Public group. I selected to View permissions the user/group has on tables. I scrolled through the list of tables and there in the list was my usertest table I had created. And as expected, all of the permissions were set to "--".

After that I went back to the usertest table and granted the public group permissions to the table.

Once that was complete I went back to the Public group and as expected, the usertest table had had the following permissions
(A D I R S U)

Now if I go and view permissions on my users, (which are all part of the Public group), all of the tables in the database show up in the permissions list (including the new usertest table). However, each permission shows as (--) which to me has always meant that it is getting its permissions from the group it is a part of. This includes our publisher user.

Now, if I step our database up into SQL Anywhere 11, all the points I made about ASA6 are the same under SQL Anywhere 11.

It looks to me like the culprit is SQL Anywhere 12. When they moved the permissions to tabs under the user or group selected the changed the way that whole area worked.

If I select the Public group under SQL Anywhere 12, the table permissions all show under that tab. As do Views and Procedures. But if I select a user that is a part of the Public group, Tables, Views and Procedures are all empty. Which sounds like the point you made about the "GUI behavior change".

Which leads me to my next question. Is the lack of seeing tables, views or procedures by design, or is it something that was missed in development/testing.

Maybe I should peel through the help documentation some more to see if I can find anything on this subject. 🙂

Thanks everyone!

Jeff Gibson
Intercept Solutions - Sybase SQL Anywhere OEM Partner
Nashville, TN

[former_SQLA_member1694871]

Thanks Volker. Good to know.

[Former Member · Accepted solution]

In SA 12.0.1, permissions moved from the property sheet to the main Sybase Central window.

There was another change too. When viewing permissions for a user, previously all objects (tables, views and procedures) were listed regardless of whether they had any permissions set. Now, only objects with permissions set are shown. Note that I'm speaking of permissions that have been explicitly set for a user. (The permissions editors don't show permissions that are inherited through group membership).

So, it sounds to me like you don't have any permissions explicitly set for your publisher. If the publisher is a member of PUBLIC, then the publisher will inherit any permissions set for PUBLIC. However, you will have to select the PUBLIC group in the tree to view its permissions.

[former_SQLA_member1694871 · Accepted solution]

Thanks for the heads up on this Rich! Probably would have helped to read this first before putting my thesis up under Breck's response to me. 🙂

Can you tell me where I can find this in the help documentation? I've looked some but haven't been able to find anything that made the point that tables, views or procedures would no longer show under a user that is a part of a group (Like it used to up through SQL Anywhere 11).

Jeff Gibson
Intercept Solutions - Sybase SQL Anywhere OEM Partner
Nashville, TN