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.
The basic concept is to use GRANT/DENY Schema Permissions. You can efficiently manage permissions by creating a role and then adding members to it.
Below is an example that will explain you in detail
use master
go
--Create Logins
CREATE LOGIN UserA WITH Password='UserA123';
go
CREATE LOGIN UserB WITH Password='UserB123';
use AdventureWorks2008R2
go
--Create Database Users
CREATE USER UserA;
go
CREATE USER UserB;
go
--Create the Test Schemas
CREATE SCHEMA SchemaA AUTHORIZATION UserA
go
CREATE SCHEMA SchemaB AUTHORIZATION UserB
go
-- create test tables
create table schemaA.TableA (fname char(5))
go
insert into schemaA.TableA (fname) values ('Kin-A')
go
create table SchemaB.TableB (fname char(5))
go
insert into SchemaB.TableB (fname) values ('Kin-B')
go
Now test :
--Test for UserA in SchemaA
EXEC('select * from schemaA.TableA') AS USER = 'UserA'
go
--Kin-A
-- Test for UserB in SchemaB == this should fail
EXEC('select * from SchemaB.TableB') AS USER = 'UserA'
go
--Msg 229, Level 14, State 5, Line 1
--The SELECT permission was denied on the object 'TableB', database 'AdventureWorks2008R2', schema 'SchemaB'.
Now create Stored Procedures :
CREATE PROCEDURE SchemaB.proc_SelectUserB
AS
select * from schemaA.TableA;
go
create procedure schemaA.proc_SchemaA
as
select * from schemaA.TableA
Now Grant execute permissions to UserA on schemaB's SP
GRANT EXECUTE ON OBJECT::[SchemaB].[proc_SelectUserB] TO [UserA]
go
Test it .. to see if UserA is able to run SP from schemaB. This will PASS
EXECUTE AS LOGIN='UserA';
Exec SchemaB.proc_SelectUserB;
revert;
go
--- Kin-A
But UserA wont be able to see data from SchemaB
EXECUTE AS LOGIN='UserA';
select * from SchemaB.TableB
revert;
go
--- Msg 229, Level 14, State 5, Line 3
--- The SELECT permission was denied on the object 'TableB', database 'AdventureWorks2008R2', schema 'SchemaB'.
Alternatively you can use DATABASE ROLE and just add users to it for better manageability of permissions:
EXEC sp_addrole 'SchemaBUsesSchemaAProc'
go
EXEC sp_addrolemember 'SchemaBUsesSchemaAProc','UserA';
go
Below statement will make sure that UserA is able to see schemaA and NOT schemaB. The good thing is that you can just add users to SchemaBUsesSchemaAProc
role and they will inherit all the permissions granted to that role.
GRANT SELECT ON SCHEMA::SchemaA TO SchemaBUsesSchemaAProc;
go
If you only want to allow UserA to execute SP's which are owned by SchemaB then below statement will do the job:
GRANT EXECUTE ON OBJECT::[SchemaB].[proc_SelectUserB] TO [SchemaBUsesSchemaAProc]
go
This way, UserA is not able to see SchemaB's tables, but still can execute procs from SchemaB.
Below will explain the permission hierarchy :
Best Answer
Sounds like you had objects in SQL Server 2000, and their owner was dropped, leaving those objects "orphaned." When the database was upgraded to any version >= 2005, the "owner" was changed to a
schema_id
, but for those owners that had been dropped, that schema didn't exist.To fix this, you can create a bunch of dummy schemas until you have one that correlates to each missing
schema_id
, and then run a statement like this for each object in each missing schema:The only complication might be if you have objects with the same name but different
schema_id
values; these will collide. Once you've sorted it all out, then you can drop all the dummy schemas.At some point you might consider creating a new empty database in a modern version, copying your tables and data to it, and then getting rid of the old one. Might eliminate more surprises like this in the future.