Granting rights to users in Sybase Central

[Former Member]

Hello, we are using Sybase ASA 12 (as an OEM-DB from Novell) and i would like grant Select-rights to an existing user. I used Sybase Central and created anew user. After this i set the "S"-Permission in the permissions-tab of the table for this user. But when i try to connect with this user via SQL-Client (Squirrel) Sybase says "Error: SQL Anywhere Error -141: Table 'xxx' not found SQLState: 42W33 ErrorCode: 2706" When i relogin to Squirrel with my DBA the table is found. How can i grant rights to other users? Do i have to set rights on table spaces? I looked in the docs but they didn't help me.

Kind regards, Thomas

[Former Member · Accepted solution]

For version 12 (and older versions) the first 2 postings cover this territory quite well but the salient points may not be obvious.

Basically the issue is this: You are required to provide the table object owner, if you are not the owner and your database user is not a member in the group that owns that object. {It may also be required when there is an ambiguous reference as well.}

Since your question was "Do you know how to avoid this prefix?" and since you are speaking of a situation where Novell has defined the group, you would need to find a way to grant your user membership in Novell's supplied group. Since this is an OEM design you may need Novell's assistance in and/or permission to do this.

If it turns out your database user does not have sufficient permissions to do that, that would then be by design and something your application vendor may have intended.

HTH

References:
http://dcx/index.html#1201/en/dbadmin/udns.html

When referring to a database object, you require a prefix unless:
1. You are the owner of the database object.
2. The database object is owned by a group ID of which you are a member.

[Former Member · Accepted solution]

I checked it: There are no groups created by the vendor. So i have to use the owner-prefix. Thanks for your help.

Regards, Thomas

[thomas_duemesnil]

Probably you have to add the Owner of the Prefix to you select statement

select * from Owner.Table;

HTH

[VolkerBarth]

...or add the user to the "owner" group, as that will allow the group members to "see" the table without explicit qualification...

[Former Member]

Great, that works. Thank you! Do you know how to avoid this prefix?

Regards, Thomas

[thomas_duemesnil]

You could read Volker's Comment. The Normal advise is to create a group. Use this group as the owner of all tables. Then add the users to the group so the tablename is resolved with out the prefix.

[VolkerBarth]

The following links may also be of help:

• From the v12.0.1 docs: Database object names and prefixes

• FAQ on how to create a user with read-only access

[VolkerBarth]

Just to add: You don't need necessarily to create a group - you may simply have to turn the current owner (say, DBA) into a group if he is not already a group...

[Breck_Carter]

...which is deprecated in Version 16.

Let The RBSM Pain Begin! (new role-based security model)

[reimer_pods]

I remember having read that dbunload(16) allows the old group/user credentials model to be ported to a newly created SA16 database (the default is new behavior).

But I can't find it right now, gonna keep on looking ...

[Former Member]

It is true that user groups are deprecated in SA 16; but the functionality of "turning a user into a group" still exists by using "user extended roles". The following should work fine:

create table DBA.test(c1 int);

create user testUser identified by testUser;

grant select on DBA.test to testUser;

create role for user DBA;

grant role DBA to testUser;

The above should allow testUser to simply issue a "select * from test".