In Oracle, Can you create a new table with the same groups if you use CTAS Query?

I use the query CTAS to create a new table, however, when CTAS has finished, other users can´t select the new table, but they had access to the old, Is it a way to pass all the users and groups to the new table? because the old table will be deleted.

Answer

“A way” is to grant (at least) select privileges to all those users.

If you used a role and granted select privilege to that role, and then granted role to those users, things would be quite simpler – just grant select privilege on the new table to the same role, and everyone will “see” it.

Otherwise, you can write query to create those grant statements for you.

For example, in Scott’s schema there’s the EMP table. I’ve previously granted privileges on it to other users in my database, and now I’m going to create a “new” CTAS table and grant privileges to the same set of users.

SQL> create table my_new_table as select * from emp;

Table created.

SQL> select 'grant select on my_new_table to ' || grantee ||';' run_me
  2  from all_tab_privs_made
  3  where owner = 'SCOTT'
  4    and table_name = 'EMP';

RUN_ME
---------------------------------------------------------------
grant select on my_new_table to SYS;
grant select on my_new_table to SUPERUSER;
grant select on my_new_table to MY_ROLE;
grant select on my_new_table to MIKE;

Now simply copy/paste the above bunch of grant statements:

SQL> grant select on my_new_table to SYS;

Grant succeeded.

SQL> grant select on my_new_table to SUPERUSER;

Grant succeeded.

SQL> grant select on my_new_table to MY_ROLE;

Grant succeeded.

SQL> grant select on my_new_table to MIKE;

Grant succeeded.

SQL>

---

If there’s zillion of users, PL/SQL option would be simpler as it would do everything for you (i.e. no copy/pasting):

SQL> begin
  2    for cur_r in (select grantee
  3                  from all_tab_privs_made
  4                  where owner = 'SCOTT'
  5                    and table_name = 'EMP'
  6                 )
  7    loop
  8      execute immediate 'grant select on my_new_table to ' || cur_r.grantee;
  9    end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL>