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 · 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

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

User · Answer

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

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

[select] Grant SELECT on multiple tables oracle

Examples related to select

Examples related to oracle11g

Examples related to grant