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.
you first need to connect as root to the database, then you create the admin user.
console
connect /as sysdba
sql
create user admin identified by secret
grant dba to admin
conn admin/secret
Best Answer
In oracle if you wish to create a user who has only the select privilege you do as shown below:
If you wish to grant select on dictionary views then:
If you wish the read_only user could select ddl of any objects belongs to any schema then: