CODEWITHSUNDEEP
sybasesap-ase
Jack Vo
Jack Vo

Reputation: 55

Sybase - How to create read-only user only can SELECT all tables?

I need to create an user which only can SELECT all tables in Sybase database. The way is : 1. Create role which can SELECT all tables. 2. Grant role to this user.

My Sybase version is : Adaptive Server Enterprise/15.0.2

Upvotes: 1

Views: 7594

Answers (2)

user121391
user121391

Reputation: 617

While the other answer is technically correct, it omits a crucial detail: you cannot simply grant the permissions for all tables to a single user or group - you have to specify all table (and procedure, view etc.) names yourself. Wildcards do not work and result in generic and not very helpful Error -131: Syntax error messages.

I've adapted a very good workaround by marc_s from SQL Server 2008 for Sybase (tested on SYL Anywhere, but should work on ASE too, if the documentation is correct).

It could of course be improved to be fully automatic and account for changed tables, but if you just want to quickly give some users read-only access to a relatively static database (be it the sales department or an application), then this just works well enough:

  1. Create a user (you could also create a group with CREATE ROLE):

     CREATE USER readonly IDENTIFIED BY 'secretpassword';

  2. Check which tables you need to include (if you want to exclude the system tables, for example). To do that, look into systable for all tables/views/procedures and sysuserperm for user details:

     SELECT
   t.table_id,
   t.table_name,
   t.table_type,
   t.creator, 
   u.user_name
 FROM systable AS t
 JOIN sysuserperm AS u ON t.creator = u.user_id
 ORDER BY t.table_id ASC;

  3. Depending on your data and needs, you can modify the query to just return the table_names that you need. In this example we get all views and tables that are owned by DBA (owner = schema). Now create the GRANT statements:

     SELECT
   t.table_name,
   GrantCmd = 'GRANT SELECT ON dba.' + t.table_name + ' TO readonly;',
   RevokeCmd = 'REVOKE SELECT ON dba.' + t.table_name + ' FROM readonly;'
 FROM systable AS t
 WHERE t.creator = 1
 AND (t.table_type = 'BASE' OR t.table_type = 'VIEW');

  4. Copy the resulting statements, wrap them in a transaction and execute it:

     BEGIN TRANSACTION;
 GRANT SELECT ON dba.table1 TO readonly;
 GRANT SELECT ON dba.table2 TO readonly;
 GRANT SELECT ON dba.table3 TO readonly;
 <...>
 COMMIT TRANSACTION;

  5. Now you can execute SELECT statements. You have to manually add the context to the tables though:

    SELECT * from table1;     -- Error -141: table not found
SELECT * from dba.table1; -- works as expected
DELETE FROM dba.table1;   -- Error -121: permission denied

  6. If you want to revoke access, just use the revoke part from before.

Upvotes: 0

Meet
Meet

Reputation: 418

  • create a readonly role
  • grant only the select permission to the new role
  • add the user to that role

Upvotes: 3

Related Questions