views:

157

answers:

4

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.

+1  A: 

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.

ldog
You can't make a view with the same name as an existing table in the same schema. If you try, you'll get error Msg 2714 ("object with that name already exists").
Rick
Unless you put that view into a different schema, then you can't create a view with the same name as a table. Furthermore, you can't direct a specific user to a view instead of a table. This, frankly, doesn't answer the question at all - and is incorrect as well.
Mark Brackett
A: 

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.

Spencer Ruport
+1  A: 

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
Mark Brackett
are schemas in sql server 2000? The actual implementation will be 2005 but I have 2000 on my test instance.
Will Rickards
How would you handle mixed roles if you use schemas?
OMG Ponies
This approach is also poor if MyTable in either schema is identical.
OMG Ponies
@Will Rickards - Yes, schemas are in SQL 2000 - though some of the specific T-SQL syntax changes.
Mark Brackett
@rexem - You assign a *user* a default schema - you set the role as owner of the schema (so that you can drop a user without dropping the schema and it's associated objects). Frankly, the role isn't necessary for this example.
Mark Brackett
@rexem - Also, MyTable being identical doesn't really make a difference. It'd be confusing, but really the intent of the OP is to "lie" to the user - which equals confusion in most cases. To be fair though, schemas are a lesser known MSSQL feature - and I wouldn't suggest their use in most cases.
Mark Brackett
I haven't tested the schema thing, I got it working without schemas but I'll go ahead and accept this answer as it is probably the correct way to do it. This is just a proof of concept at this stage anyway.
Will Rickards
Proof of concept for a bad idea.
Spencer Ruport
+1  A: 

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.

Jeff Cooper