CODEWITHSUNDEEP
sqlsql-server-2005t-sql
Will Rickards
Will Rickards

Reputation: 2786

How do I make one user see a different table with same name

Goal: When everybody else does SELECT * FROM mytable they see one version of the table. But when a specific user does SELECT * FROM mytable they see another version of the table.

I think I'm like halfway there with creating a new role and putting the single user in it. Then creating a copy of the default table with SELECT * INTO newrole.mytable FROM dbo.mytable. But when the user does SELECT * FROM mytable they still see the dbo.mytable. How do I get them to default to the newrole.mytable? I still need them to see all the other dbo tables just not this one.

Upvotes: 2

Views: 308

Answers (4)

Jeff Cooper
Jeff Cooper

Reputation: 46

I use Postgres primarily, so YMMV, but in postgres you need to

1) Create the new schema, preferably owned by the new role, and put the table in it 2) Set the search_path variable to include that schema BEFORE the other one.

Hope it helps.

Upvotes: 1

Mark Brackett
Mark Brackett

Reputation: 85665

Create a new schema, and a duplicate table (or view onto dbo.table if that's what you want) in it - eg., otheruser.table. Then, set the user's login to default to that schema:

USE atest
GO

CREATE ROLE [arole]
GO

CREATE SCHEMA [aschema] AUTHORIZATION [arole]
GO

CREATE USER [auser] FOR LOGIN [modify_user] WITH DEFAULT_SCHEMA = aschema
GO

EXEC sp_addrolemember 'arole', 'auser'
GO

CREATE TABLE dbo.atable ( col1 int )
GO

CREATE TABLE aschema.atable (col2 varchar(10))
GO

INSERT INTO dbo.atable( col1 ) VALUES( 1 )
GO

INSERT INTO aschema.atable( col2 ) VALUES( 'One' )
GO

PRINT 'dbo'
SELECT * FROM atable
GO

EXECUTE AS USER = 'auser'
GO

PRINT 'aschema'
SELECT * FROM atable
GO

REVERT
GO

Upvotes: 1

Spencer Ruport
Spencer Ruport

Reputation: 35117

This is a very bad idea. I'm not sure why people try all these crazy methods to improve security but it's just plain counter productive.

Ultimately every security system comes down to some line like the following if(User.HasAccessTo(object)). In fact, if you've designed a well thought out security system that's almost exactly how it should work. The more disjointed your authentication checks, the more likely you'll make a mistake. If only some users have access to certain record information you should add a flag to those records and verify access based on that.

Upvotes: -1

ldog
ldog

Reputation: 12151

I don't know if this may help but you may be able to make a view of a different table with the same name, here is an excerpt from http://www.w3schools.com/SQl/sql_view.asp:

In SQL, a view is a virtual table based on the result-set of an SQL statement.

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.

Upvotes: 1

Related Questions