Sql-server – Can view adapt to implicitly reference user’s default schema

permissionsschemasql serversql-server-2008-r2

A bit of background. I have a base application and most clients use it as standard. However some clients have small code and database customisations. Each of these clients has their own branch and maintenance can be tricky.

I want to consolidate all these into a single database structure (not a single database – we aren't doing multi-tenancy) to enable upgrades to be applied in a much more uniform fashion. I'm still at the proof of concept stage, but the route I was going down would be to have the standard objects stay in the schema they currently exist in (mostly dbo) and have the custom objects reside in a schema for each client.

For example, I could have dbo.users and client1.users which has some additional columns. If I set the default schema for the client to be "client1" then the following query

SELECT * FROM users

will return data from the client1 schema or the dbo schema depending on which login is connected. This is absolutely perfect for what I'm trying to achieve.

The problem I'm running into is with Views. I have many views which are in the dbo schema and refer to the Users table. No matter which user I connect to the database as, these views always select from dbo.users.

So I'm guessing the question I have is: Can I prefix the tables in the view with some variable like "DEFAULT"? e.g.

SELECT u.username, u.email, a.level
FROM DEFAULT.users u INNER JOIN DEFAULT.accessLevels a ON u.accessID = a.accessID

If this isn't possible and I'm totally barking up the wrong tree, do you have any suggestions as to how I can achieve what I'm setting out to do?

Many thanks.

Best Answer

Create multiple views and/or table-valued functions. You can't override the schema without specifying it explicitly unless you build dynamic SQL to do so for you, but you can't do that in a view or a function - only a stored procedure.

With that said, you could do that with something like this, however it will fall back to dbo (or some other schema you hard-code) if a match is not found or if you are using Windows groups for example (which prior to SQL Server 2012 do not support default schema):

CREATE PROCEDURE dbo.whatever_procedure
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql NVARCHAR(MAX), @s SYSNAME;

  SELECT @s = d.default_schema_name
    FROM sys.database_principals AS d
    LEFT OUTER JOIN sys.server_principals AS s
    ON d.[sid] = s.[sid]
    WHERE SUSER_SNAME() IN (d.name, s.name);

  SELECT @s = COALESCE(@s, N'dbo');

  SET @sql = N'SELECT x FROM ' + QUOTENAME(@s) 
    + '.whatever_view_name;';

  EXEC sp_executesql @sql;
END
GO

You can test this:

USE tempdb;
GO
CREATE SCHEMA foo;
GO
CREATE LOGIN flubat WITH PASSWORD='flubat',CHECK_POLICY = OFF;
GO
CREATE USER flubat FROM LOGIN flubat WITH DEFAULT_SCHEMA = foo;
GO
GRANT SELECT ON SCHEMA::foo TO flubat;
GO
CREATE VIEW dbo.whatever_view_name AS SELECT x = 'dbo';
GO
CREATE VIEW foo.whatever_view_name AS SELECT x = 'foo';
GO
-- create the above procedure
GO
GRANT EXEC ON dbo.whatever_procedure TO flubat;
GO

And then execute it as yourself and also as flubat:

EXEC dbo.whatever_procedure;
GO
EXECUTE AS USER = N'flubat';
GO
EXEC dbo.whatever_procedure;
GO
REVERT;

Results:

x 
----
dbo

x
----
foo

But like I said, this is potentially fragile, and I would lean toward either creating a view per schema, or changing the design altogether.

Related Solutions

Sql-server – Workarounds for setting default schema for domain group

Taking @Mr.Brownstone's idea, you could definitely try this:

DECLARE @sql nvarchar(MAX) = N'';

SELECT @sql += N'
  ALTER SCHEMA dbo TRANSFER custom_schema.' + QUOTENAME(name) + ';'
  FROM sys.objects
  WHERE SCHEMA_NAME([schema_id]) = N'custom_schema';

EXEC sys.sp_executesql @sql;

Now, it may be the case that you have foreign keys etc. that will need to be dropped and re-created (and I've shown how to automate that too), or as I mentioned in a comment, you may have explicit references to custom_schema in your existing objects. You may also have duplicate objects (say, dbo.Customers and custom_schema.Customers) that you'll need to handle. But the general idea should be sound.

Another workaround would be to upgrade to SQL Server 2012, where you can set a DEFAULT_SCHEMA for a domain group. I haven't tested this, but this Connect item says it has been made so.

In either case, I still think the app should be fixed eventually. As I've mentioned many times before, you should ALWAYS use an explicit schema prefix when creating or referencing objects.

Postgresql – Will table inheritance in PostgreSql suit this situation

It is viable provided that you note that keys and indexes must be declared on each child table.

In fact this is pretty close to an ideal use for table inheritance. Just make sure you are familiar with the gotchas, and that you pay close attention to foreign key creation in the user schemas.

One caveat is that you can't cascade CREATE statements into all the schemas. This would have to be scripted. You could however create plpgsql functions to do this. Just note that such statements usually are not parameterized so you need to use quote_ident() and quote_literal as appropriate.

As per request here's an example. In the master schema. Note the key and unique constraints here are for documentation purposes only.

CREATE SCHEMA master;
SET SEARCH_PATH="master;

CREATE TABLE users (
   id bigserial not null unique,
   login text primary key,
   check (false) NO INHERIT -- 9.2 ONLY, prevents any rows in this table
);

CREATE TABLE roles (
   id bigserial not null unique,
   role_name text primary key,
   check (false) NO INHERIT -- 9.2 ONLY, prevents any rows in this table
);

CREATE TABLE user_roles (
   user_id bigint references users(id),
   role_id bigint references roles(id),
   primary key (user_id, role_id)
   check (false) NO INHERIT -- 9.2 ONLY, prevents any rows in this table
);

RESET SEARCH_PATH;

Naturally if not on PostgreSQL 9.2 drop the check constraints since they would be inherited in prior versions and then no tables could have rows.

Then in a child schema:

CREATE SCHEMA customer123;
SET SEARCH_PATH 'customer123';

CREATE TABLE users(unique (id), primary key(login)) INHERITS (master.users);
CREATE TABLE roles(unique (id), prikary key(role_name)) INHERITS (master.roles);
CREATE TABLE user_roles(
     foreign key users_id REFERENCES customer123.users(id),
     foreign key role_id REFERENCES customer123.roles(id),
     primary key (users_id, role_id)
);
RESET SEARCH_PATH;

Note in this case you must carefully control access to the master schema because:

SELECT tableoid, login from master.users;

gives you every user account of every tenant plus enough info to determine which tenant it is.

Best Answer

Related Solutions

Sql-server – Workarounds for setting default schema for domain group

Postgresql – Will table inheritance in PostgreSql suit this situation

Related Question