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

asked 04 May, 08:48

vegazbabz's gravatar image

vegazbabz
113
accept rate: 0%


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.

permanent link

answered 06 May, 09:12

Breck%20Carter's gravatar image

Breck Carter
27.6k472628905
accept rate: 21%

edited 06 May, 09:20

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

(07 May, 05:17) vegazbabz
Replies hidden

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.

(07 May, 06:30) Volker Barth

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

permanent link

answered 04 May, 09:26

Volker%20Barth's gravatar image

Volker Barth
32.1k327469688
accept rate: 32%

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

permanent link

answered 04 May, 09:07

Reimer%20Pods's gravatar image

Reimer Pods
4.4k374789
accept rate: 12%

edited 04 May, 09:09

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.

permanent link

answered 07 May, 06:42

Volker%20Barth's gravatar image

Volker Barth
32.1k327469688
accept rate: 32%

edited 07 May, 06:46

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:

×53
×6

question asked: 04 May, 08:48

question was seen: 111 times

last updated: 07 May, 06:46