Sql-server – How to connect multiple users/schemas in Oracle 11g

oracleoracle-11g-r2permissionssql server

I'm almost new user to databases, so I think I'm doing something wrong.

  • I have the database created.
  • I created 3 users, 1 it's an Admin, 2 are just users.
  • I created 2 roles, 1 for the Admin and 2 for the other users.

I'm learning that the schemas in Oracle are different from SQL Server. In SQL Server I created tables assigned to a schema, for example: CREATE TABLE schema1.table1 and then I was be able to see them all with users asigned to that DB; with Oracle, I think a schema is the sum of the sequence, synonyms, etc that a user has. (If not, please correct me)

I want to achieve that in Oracle, so I created the users/schemas; after that I got connected with user1 and created the tables with that user; later, I got connected with user2 and created the tables related to the user.

Now, when I'm trying to create the alter relating user1 tables and user2 tables with admin, it says I don't have enough privileges.

ALTER I'm trying to do:

ALTER TABLE user1.PhoneTable
    ADD (CONSTRAINT C_001 FOREIGN KEY (Status) 
            REFERENCES user2.ClientTable (Status) ON DELETE SET NULL);

I created those users because if I wanna see all the tables related to the Phone(user1), I got connected to that user and that should be all, I just wanna have the DB with some order.

Admin privileges:

GRANT 
    CREATE SESSION,
    UNLIMITED TABLESPACE,
    CREATE TABLE,
    DROP ANY TABLE,
    CREATE CLUSTER,
    CREATE SYNONYM,
    CREATE PUBLIC SYNONYM,
    CREATE VIEW,
    CREATE SEQUENCE,
    CREATE DATABASE LINK,
    CREATE PROCEDURE,
    CREATE TRIGGER,
    CREATE MATERIALIZED VIEW,
    CREATE ANY DIRECTORY,
    DROP ANY DIRECTORY,
    CREATE TYPE,
    CREATE LIBRARY,
    CREATE OPERATOR,
    CREATE INDEXTYPE,
    CREATE DIMENSION,
    CREATE ANY CONTEXT,
    SELECT ANY DICTIONARY,
    CREATE JOB,
    ALTER ANY TABLE,
TO myAdmin;

User privileges:

GRANT 
    CREATE session, 
    CREATE table, 
    CREATE view
    CREATE procedure, 
    CREATE synonym,
    ALTER ANY table, 
    ALTER view, 
    ALTER procedure, 
    ALTER synonym,
    DROP table, 
    DROP view, 
    DROP procedure, 
    DROP synonym
TO myUsers;

Best Answer

You need to grant the REFERENCES privilege on the reference table to user2. (See GRANT, Table Privileges section. Note that this cannot be granted to a role, must be granted to the user directly.)

Here's a demo:

SQL> create user user1 identified by user1;
User created.
SQL> grant create session, create table, unlimited tablespace to user1;
Grant succeeded.

SQL> create user user2 identified by user2;
User created.
SQL> grant create session, create table, unlimited tablespace to user2;
Grant succeeded.

SQL> create table user1.ref_table (id number primary key);
Table created.
SQL> insert into user1.ref_table values (1);
1 row created.
SQL> insert into user1.ref_table values (2);
1 row created.
SQL> commit;
Commit complete.

SQL> grant references on user1.ref_table to user2;
Grant succeeded.

SQL> connect user2/user2;
Connected.
SQL> create table oth_table (thing number, fk number);
Table created.
SQL> alter table oth_table add(constraint fk1 foreign key (fk)
  2      references user1.ref_table on delete set null);
Table altered.

SQL> insert into oth_table values (42, 1);
1 row created.
SQL> insert into oth_table values (256, 2);
1 row created.
SQL> commit;
Commit complete.

SQL> connect user1/user1;
Connected.
SQL> delete from ref_table where id = 1;
1 row deleted.
SQL> commit;
Commit complete.

SQL> connect user2/user2;
Connected.
SQL> select * from oth_table;

     THING     FK
---------- ----------
    42
       256      2

If you want admin to do all the work, it'll need the CREATE ANY INDEX privilege, and you still need the REFERENCES grant to user2.

Here's how that could work:

SQL> create user user1 identified by user1;
User created.
SQL> create user user2 identified by user2;
User created.
SQL> create user admin identified by admin;
User created.

--- Grants
----------------
SQL> grant create session, create table, unlimited tablespace to user1;
Grant succeeded.
SQL> grant create session, create table, unlimited tablespace to user2;
Grant succeeded.
SQL> grant create session, create any table,
  2        create any index, alter any table to admin;
Grant succeeded.

--- Create reference table as admin
----------------
SQL> connect admin/admin
Connected.
SQL> create table user1.ref_table (id number primary key);
Table created.

--- Do the "references" grant
----------------
SQL> connect / as sysdba
Connected.
SQL> grant references on user1.ref_table to user2;
Grant succeeded.

--- Create the second table as admin
----------------
SQL> connect admin/admin
Connected.
SQL> create table user2.oth_table (foo number, bar number);
Table created.

--- Add the constraint
----------------
SQL> alter table user2.oth_table add(constraint fk
  2      foreign key (bar) references user1.ref_table(id)
  3      on delete set null);
Table altered.
Related Question