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

possible duplicate of [Oracle SQL privelege authorization on multiple attributes and tables with one statement](http://stackoverflow.com/questions/13106087/oracle-sql-privelege-authorization-on-multiple-attributes-and-tables-with-one-st) — Jon Heller, Mar 16 '13 at 18:57

Chris Saxon · Accepted Answer · 2023-05-26T09:58:46.337

25

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

Oracle Database 23c has extended grant to allow you to give one user access to all tables in another schema:

grant select any table
  on schema table_owner
  to query_user;

edited May 26 '23 at 09:58

answered Mar 16 '13 at 16:39

Chris Saxon

9,105
1
26
42

score 9 · Answer 2 · edited Nov 12 '13 at 09:25

9

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.

edited Nov 12 '13 at 09:25

Krypton

3,337
5
32
52

answered Nov 12 '13 at 08:46

m_abr

91
1
1

are you supposed to copy / paste the result to execute it? – justin cress Jan 20 '15 at 14:59
@justincress, with this answer you will get a series of sentences that will save you time. I would call it a workaround. – Felipe Vidal Nov 08 '19 at 02:03
You need a space just before and after the text ||table_name|| – ziggy Jul 09 '20 at 17:21

score 5 · Answer 3 · answered Mar 16 '13 at 18:00

my suggestion is...create role in oracle using

create role <role_name>;

then assign privileges to that role using

grant select on <table_name> to <role_name>;

then assign that group of privileges via that role to any user by using

grant  <role_name> to <user_name>...;

score 0 · Answer 4 · answered Dec 21 '17 at 15:53

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.

score 0 · Answer 5 · answered Dec 10 '18 at 15:09

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.

Grant SELECT on multiple tables oracle

5 Answers5

Linked

Related