0

I use the below to grant select permission to amy on table1:

grant select schema.table1 to amy;

However, the below does not work:

grant select on schema.* to amy;

the error is

ORA-00903: invalid table name

Please advise what is wrong in my command. What I want is grant select to amy on ALL tables.

thanks

William Robertson
  • 15,273
  • 4
  • 38
  • 44
patrickchan
  • 11
  • 6

3 Answers3

1

The syntax for the GRANT command (as of Oracle 21c) makes it clear that the granted item can only be a specific object:

on_object_clause:

on_object_clause

The best you can do is create a role and grant the role to Amy, but you will still have to grant privileges on each individual table to the role.

create role analyst;
grant analyst to amy;

grant read on hr.countries to analyst;
grant read on hr.departments to analyst;
grant read on hr.employees to analyst;
William Robertson
  • 15,273
  • 4
  • 38
  • 44
  • thanks reply , if I have 100 tables need to grant to Amy , I have repeatedly do grant read on table1 to analyst for 100 times ? if yes , is there any way can do it by one single script rather than do it 100 times ? – patrickchan Aug 13 '22 at 14:18
  • Yes, loop over user_all_tables and use `execute immediate` to grant read to a role. (I consider 'grant select' as deprecated because it gives permission to lock tables, which 'read' doesn’t.) – William Robertson Aug 18 '22 at 09:14
1

To create a role and grant it to amy:

create role select_all_tables;
grant select_all_tables to amy;
alter user amy default role select_all_tables;

Use the following query to generate a script, which you can copy/paste and run to complete all the grants:

select 'grant select on '|| owner || '.' || table_name ||' to select_all_tables;' 
from dba_tables where owner='SCHEMA';

copy and paste the output, and execute it as as script.

grant select on schema.table1 to select_all_tables;
grant select on schema.table2 to select_all_tables;
...

If you would rather grant the privileges directly and not use a role, alter the script query:

select 'grant select on '|| owner || '.' || table_name ||' to amy;' 
from dba_tables where owner='SCHEMA';

and then run the output as a script:

grant select on schema.table1 to amy;
grant select on schema.table2 to amy;
...
pmdba
  • 6,457
  • 2
  • 6
  • 16
-2
grant select on table to public;

But be careful when you do that -- make sure it's what you really want to do.

Beefstu
  • 804
  • 6
  • 11
  • 1
    PUBLIC isn't exactly a "specific user" (which is what the OP wants). And it still grants privileges on a single table, so ... what is your point? – Littlefoot Aug 13 '22 at 09:33