GRANT SELECT to all tables for one user

[Former Member]

How do I grant select privileges to one user for all tables?

[Breck_Carter · Accepted solution]

GRANT MEMBERSHIP IN SelectAll TO user1;

where you have previously created SelectAll as a group with SELECT privileges:

GRANT CONNECT TO SelectAll; -- no password so it can't actually be used to connect
GRANT GROUP TO SelectAll; -- make it a group
GRANT SELECT ON table1 TO SelectAll;
GRANT SELECT ON table2 TO SelectAll;
-- ...and so on

You could also use the goofy new syntax for "roles" but there's really no point 🙂

You might also want to turn the owner into a group and grant membership on that, otherwise user1 will have to name qualify everything (DBA.table1 etc).

For example if DBA owns all the tables

GRANT GROUP TO DBA; -- make DBA a group as well as a user id
GRANT MEMBERSHIP IN DBA to SelectAll; -- let SelectAll members "see" tables owned by DBA

Note that the DBA privilege of the DBA user id is NOT inherited by group membership so no worries there.

[Former Member · Accepted solution]

Thank you, however, if I still need to write GRANT SELECT for all my tables, then it is not what I am looking for.

[VolkerBarth · Accepted solution]

Well, there are several topics here:

• The owner of a table has all permissions on that table by design, so he does not need a particular SELECT permission.
• If a user is not the owner, he has to be granted permissions, either directly (as you seem to ask for) or by membership of a group/role with that permission (which Breck has suggested, to prevent the need to specify direct permissions for single users).

One method to grant SELECT on individual tables is by using a loop as in my former answer.

[VolkerBarth]

If that is a one-time task, you can often use a FOR loop over the system catalog to build separate statements and then execute them via dynamic SQL, such as (here for a user names "MyUserName"):

begin
  for names as curs dynamic scroll cursor for
    select 'grant select on "' || su.user_name || '"."' || table_name || '" to "MyUserName";' as Stmt 
    from sys.systab st key join sys.sysuser su
    where su.user_name = 'DBA'  -- possible restrict to particular schema
       and table_type = 1       -- and/or table type
    order by st.table_name;
  do
    execute immediate Stmt
  end for;
end;

Of course for regular usage, you could easily turn that into a stored procedure (and add checks for already granted permissions and the like.)

[reimer_pods]

Hi,
AFAIK there is no generic command to do that. Some years ago I've writen a SP for that purpose. I hope I'm getting this link right:
GrantSelect.sql

[VolkerBarth]

With v16 and above, you can use the SELECT ANY TABLE system privilege to grant that permission for any table in the database (here in the SQL Anywhere 16 demo database):

create user MyTestUser identified by 'MyPassword';
grant select any table to MyTestUser;

-- Now login as that user:
-- SELECT with specifying the owner (here GROUPO) works
select * from GROUPO.contacts;
select * from GROUPO.customers;
-- SELECT without owner specification fails with SQLCODE -141:
select * from contacts;

-- succeeds when MyTestUser is added to group GROUPO:
grant membership in group GROUPO to MyTestUser;
select * from contacts;

So in case you are using v16/v17, there's no need to grant SELECT privileges for individual tables (although it still may be relevant to do so if you don't want to grant those for really all tables!).

Here are some samples from the docs on groups/roles and the SELECT ANY TABLE privilege.