Want to grant select to a specific user on all tables-CodePudding

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

CodePudding user response：

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;

CodePudding user response：

grant select on table to public;

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