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

asked 21 Mar '13, 12:11

ThomasD's gravatar image

ThomasD
21114
accept rate: 0%

edited 21 Mar '13, 12:24

Mark%20Culp's gravatar image

Mark Culp
24.9k10141297


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.

permanent link

answered 22 Mar '13, 19:12

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
7.3k35107
accept rate: 32%

edited 23 Mar '13, 06:58

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819

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

(27 Mar '13, 11:46) ThomasD

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

select * from Owner.Table;

HTH

permanent link

answered 21 Mar '13, 13:07

Thomas%20Duemesnil's gravatar image

Thomas Dueme...
2.7k293965
accept rate: 17%

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

(21 Mar '13, 13:50) Volker Barth

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

Regards, Thomas

(22 Mar '13, 02:53) ThomasD
Replies hidden
1

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.

(22 Mar '13, 02:59) Thomas Dueme...
1

The following links may also be of help:

(22 Mar '13, 04:28) Volker Barth
1

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...

(22 Mar '13, 07:34) Volker Barth

...which is deprecated in Version 16.

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

(22 Mar '13, 09:20) Breck Carter

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 ...

(22 Mar '13, 09:39) Reimer Pods
1

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".

(26 Mar '13, 08:08) Karim Khamis
showing 3 of 8 show all flat view
Your answer
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
×128
×28
×7

question asked: 21 Mar '13, 12:11

question was seen: 6,897 times

last updated: 27 Mar '13, 11:59