user535296 wrote:What's the oracle version? Are both the users in the same database or in different ones?
what should I do so that user A can create tables on user B' schema?
If they are in different databases than in addition to the above mentioned step,you need to create a DB link between two databases as well.
Connected to: Oracle Database 11g Enterprise Edition Release 126.96.36.199.0 - Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL> create user usera identified by a; User created. SQL> grant create session,create table to usera; Grant succeeded. SQL> create user userb identified by b' 2 create user userb identified by b' 3 SQL> create user userb identified by b; User created. SQL> grant create session ,create table to userb; Grant succeeded. SQL> alter user usera quota unlimited on users; User altered. SQL> alter user userb quota unlimited on users; User altered. SQL> conn userb/user ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. SQL> conn userb/b Connected. SQL> create table userb_table(a char); Table created. SQL> grant all on userb_table to usera; Grant succeeded. SQL> conn usera/a Connected. SQL> sho user USER is "USERA" SQL> create table usera_userb as select * from userb.userb_table; Table created. SQL> desc usera_userb Name Null? Type ----------------------------------------- -------- ---------------------------- A CHAR(1) SQL>
this will allow userA to create tables in all schemas even SYS. Correct?
IMHO,I wouldn't grant a user ,create any table priv.
SQL> conn / as sysdba Connected. SQL> grant create any table to usera; Grant succeeded. SQL> conn usera/a Connected. SQL> create table sys.a(a char); create table sys.a(a char) * ERROR at line 1: ORA-01031: insufficient privileges SQL>
is there other way to allow one schema create a table in another schema without granting create any table? we are not allowed to grant create any table due to security violations.Not possible without granting a create table privileges. Bear in mind it will always be a breach.
987929 wrote:do NOT hijack somebody elses thread.
dont you think there is a risk having other DDL changes like alter existing table , truncate or dropping the table in the definition. if we can restrict them do so, can you please help me.
Appreciate your help.