ASE database user for read-only access

[franciscojos_cebrinpedrei]

Hi everybody.

We have just migrate our QA SAP system to ASE. In our old database we use a database user for some of our custom-made aplications that read some information direct to the SAP database. Can anybody tell me how can I create a user like this in ASE?. I'm connecting with TOAD but I can see the way to give it the right authorizations. Is there a way to grant only some Z* tables?.

Best regards.

[former_member188883]

Hi Francisco,

You can login to Sybase database using a dba login id.

Then create a test user using command

CREATE USER TEST IDENTIFIED BY <password for test user>

Provide grant to this test user using the command.

GRANT INSERT, DELETE ON <Z-Table name> TO TEST

Regards,

Deepak Kori

[franciscojos_cebrinpedrei]

Thanks Deepak but this is Oracle-like statements. What I'm looking for is the right Sybase statements.

I have created the user on the database, create a role and grant the role to select permision on a table.

I loggon with the new user/password but when I try to make a select on the table the systems returns a permision error.

Best regards.

[former_member188883]

Hi Francisco,

Please share what grants have been given and what is the permission error you receive

Regards,

Deepak Kori

[former_member188958]

Roles aren't necessarily automatically activated when you log in.  They can be configured that way.   You can also manually activate the role with the "set role <name> on" command.

From a core ASE perspective, see

SyBooks Online and Adding or dropping auto-activated roles

presumably dbacockpit also provides the auto-activate setting, but I don't know dbacockpit.

-bret

[franciscojos_cebrinpedrei]

use master

go

exec sp_addlogin 'new_user', 'password', @defdb='Mydb', @deflanguage='us_english', @auth_mech='ANY', @fullname='new_user'

go

create role new_role

go

grant select on mydb.owner.table to new_role

go

grant role New_Role to new_user

go

exec sp_adduser 'new_user', 'new_user', 'public'

Best regards.

[franciscojos_cebrinpedrei]

Hi Bret.

What I'm trying to do is this Oracle code in sybase:

CREATE USER new_user IDENTIFIED BY password

CREATE ROLE new_readonly_role

GRANT select ON database.owner.table TO new_readonly_role

GRANT new_readonly_role TO new_user

I try to do this in sybase but when I execute GRANT the database prompts an error:

grant select on table to new_readonly_role returns -> "You may only GRANT or REVOKE permission on objects in the current database. If I use my_database the error is "No such user/role ___ exists (but the role exists).

If I try to create again the new_readonly_role in my_database the error is "You must be in the master database to run CREATE ROLE command".

Best regards.