0

I Just need to create table from a user to any user under the same DB. letz consider 3 Schemas.

Schema_1,Schema_2 and Schema_3.

schema 1 had DBA Privilege.

Is it possible to table in SChema_2 or Schema_3 from Schema_1????

or we need to give this role "CREATE ANY TABLE" also ??

coppro
  • 14,338
  • 5
  • 58
  • 73
King
  • 31
  • 2
  • 2
  • 6

3 Answers3

1

The DBA role should have this privilege in a typical installation - you can see other roles/users who have this by:

select grantee from dba_sys_privs where privilege = 'CREATE ANY TABLE'

dpbradley
  • 11,645
  • 31
  • 34
0

As @dpbradly states, Oracle "out of the box" privileges for the DBA role include the 'CREATE ANY TABLE' privilege. You can check this out with the following query (and see all the system privileges granted to this role as well):

SELECT * FROM role_sys_privs WHERE role = 'DBA' ORDER BY PRIVILEGE;

The Oracle data dictionary is your friend.

DCookie
  • 42,630
  • 11
  • 83
  • 92
0

Ok lets make some things clear

  1. CREATE ANY TABLEis system privilege not role

  2. you can grant any user DBA privileges by simply writing GRANT DBA TO <USER_NAME> WITH ADMIN OPTION; That being said, schema_1 will be able to create table on any user as he have CREATE ANY TABLE privilege

    create table hr.dbaMade_tab (id number, name varchar2(20) );

    If you schema_1 grants CREATE ANY TABLE then schema_2 and schema_3 will be able to create table to any table. Do not grant DBA role to anyone. Make new role and give it necessary privileges and grant that role to other users. In your case schema_2 and schema_3. Hope that helps.

Faz13able
  • 116
  • 1
  • 5