Grant SELECT on multiple tables oracle

Question

I have 3 tables table1 table2 table3  I want to grant select for example  these tables to a user  user1   I know that I can grant with    grant select on table1 to user1  grant select on table2 to user1  grant select on table3 to user1    Can I grant the 3 tables to user1 using only 1 query   Thanks

User · Accepted Answer

No  As the documentation shows  you can only grant access to one object at a time

User · Answer

If you want to grant to both tables and views try   SELECT DISTINCT        OWNER                   TABLE NAME          to db user   FROM     ALL TAB COLS  WHERE     TABLE NAME LIKE  TABLE NAME       For just views try   SELECT      grant select on          OWNER                   VIEW NAME          to REPORT DW   FROM     ALL VIEWS WHERE     VIEW NAME LIKE  VIEW NAME       Copy results and execute

User · Answer

my suggestion is   create role in oracle using   create role  lt role name gt     then assign privileges to  that role using   grant select on  lt table name gt  to  lt role name gt     then assign that group of privileges via that role to any user by using   grant   lt role name gt  to  lt user name gt

User · Answer

You can do it with dynamic query  just run the following script in pl-sql or sqlplus   select  grant select on user name owner    table name    to user name1    from dba tables t where t owner  user name owner    and then execute result

User · Answer

This worked for me on my Oracle database   SELECT    GRANT SELECT  insert  update  delete ON mySchema      TABLE NAME      to myUser   FROM     user tables where table name like  myTblPrefix     Then  copy the results  paste them into your editor  then run them like a script   You could also write a script and use  Execute Immediate  to run the generated SQL if you don t want the extra copy paste steps

[select] Grant SELECT on multiple tables oracle

Examples related to select

Examples related to oracle11g

Examples related to grant