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. 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 '18, 05:17)
vegazbabz
Replies hidden
Well, there are several topics here:
One method to grant SELECT on individual tables is by using a loop as in my former answer.
(07 May '18, 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.) |
|
Hi, |
|
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. |