[select] Grant SELECT on multiple tables oracle

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

This question is related to select oracle11g grant

The answer is


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


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>...;

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.


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.


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.


Examples related to select

Warning: Use the 'defaultValue' or 'value' props on <select> instead of setting 'selected' on <option> SQL query to check if a name begins and ends with a vowel Angular2 *ngFor in select list, set active based on string from object SQL: Two select statements in one query How to get selected value of a dropdown menu in ReactJS DATEDIFF function in Oracle How to filter an array of objects based on values in an inner array with jq? Select unique values with 'select' function in 'dplyr' library how to set select element as readonly ('disabled' doesnt pass select value on server) Trying to use INNER JOIN and GROUP BY SQL with SUM Function, Not Working

Examples related to oracle11g

Convert timestamp to date in Oracle SQL Query to display all tablespaces in a database and datafiles Oracle Installer:[INS-13001] Environment does not meet minimum requirements Forgot Oracle username and password, how to retrieve? Extract number from string with Oracle function How to solve : SQL Error: ORA-00604: error occurred at recursive SQL level 1 Add days Oracle SQL How to determine tables size in Oracle ORA-28000: the account is locked error getting frequently Oracle listener not running and won't start

Examples related to grant

Postgresql: error "must be owner of relation" when changing a owner object Give all permissions to a user on a PostgreSQL database Remove privileges from MySQL database What GRANT USAGE ON SCHEMA exactly do? Permission denied for relation Grant SELECT on multiple tables oracle How to grant remote access to MySQL for a whole subnet? Grant all on a specific schema in the db to a group role in PostgreSQL grant remote access of MySQL database from any IP address Query grants for a table in postgres