• Arun Kumar

Grant Select on all tables in a schema

I encountered this situation where I wanted to grant SELECT on all the tables owned by one user to another user.


There are two simple ways to achieve this:


Generate SQL for each table


In this method, we will generate GRANT SELECT statement for each table using below query. Here we are trying to GRANT SELECT on each table owned by HR user to SCOTT user:

Select 'GRANT SELECT ON HR.'||Table_Name||' TO SCOTT;' 
From All_Tables Where Owner='HR';

Here is the sample output of the above command:

oracle-sql-grant (1)

Now you can execute individual statements from the above output to GRANT SELECT ON all HR tables to SCOTT user.


Using simple LOOP statement


We can take use of PL/SQL and write a simple loop statement as below to automatically execute the GRANT SELECT statements for us. Again we are trying to GRANT SELECT on every table owned by HR user to SCOTT user:

BEGIN
   FOR R IN (SELECT owner, table_name FROM all_tables WHERE owner='HR') LOOP
      EXECUTE IMMEDIATE 'grant select on '||R.owner||'.'||R.table_name||' to SCOTT';
   END LOOP;
END;
/

Here is the sample output of the above command:

grant-select-on-all-schema-tables (1)

Recent Posts

See All

How to plan database creation

Database creation process involves several operating system files to work together. You create database only once and later on keep on adding more data files. Before you can simply go ahead and start

Oracle Transportable Tablespace

Assume that you have a schema (IQS) on production server and you want to import the schema into test server. The ideal approach would be to perform entire schema export and import using data pump. Rig

Oracle Easy Connect Method

At times you will have a requirement to connect an remote Oracle database without a TNS entry. In such situation you will be using the Oracle easy connect method to connect Oracle database servery Eas

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis

Feedback