Sql-server – Hide Table Structure

permissionssql serversql-server-2008-r2

Is there a way to restrict a user from viewing the table structure? I just want them to have a SELECT, INSERT, UPDATE, DELETE access only on that table. I want to restrict a specific programmer on the following:
1. He is not allowed to see any table definition
2. He is not allowed to create Stored Procedure
3. Since there were already Stored Procedures (SPs) for saving, updating, selecting and deleting records, that programmer will just use this SPs in accessing/manipulating records.
Overall, for security reason, we don't want that programmer to have any of design of the database.

Best Answer

This seems a strange request but it is possible.

GRANT SELECT ON YourTable TO FOO;
GRANT INSERT ON YourTable TO FOO;
GRANT UPDATE ON YourTable TO FOO;
GRANT DELETE ON YourTable TO FOO;
DENY VIEW DEFINITION ON YourTable TO FOO;

They will of course be able to see the names of the columns by issuing a SELECT * and be able to get the datatypes with a bit more effort by select into a new (possibly #temp) table. They will lose intellisense on that table.

You also say

Since there were already Stored Procedures (SPs) for saving, updating, selecting and deleting records, that programmer will just use this SPs in accessing/manipulating records.

In that case probably they don't need any permissions at all on the table. If the table and stored procedures have the same owner you just need to grant exec permissions on the procedure and the access to the table in the procedure will still succeed through ownership chaining.

Related Solutions

Sql-server – SQL Server column level security

Column level security does not work that way. I'm aware of no mechanism to globally deny access to a certain column for a given user. The GRANT/DENY only works on specific statements like SELECT, UPDATE and so on in combination with a given object.

So in your case, if you removed access on SELECT for column X on table Y the user can still happily execute "select *" views on that table because the view is a different object and is unaffected by this security setting!

The good news is that you can use column permissions on views also. It works just the same as with tables, but you have to set the permission on every view that includes the SSN column.

Sql-server – Restrict record to be update or deleted according to dates inside row for SQL Server 2005

A constraint can't control DML operations unless the update actually attempts to change the data in that column. It certainly can't control deletes unless we're talking about table-level constraints but I recommend against this.

Most people go with after triggers for this type of logic, but this means (a) attempt to perform the change (b) if it fails your logic, roll it back. I much prefer an INSTEAD OF trigger for this. The following will allow updates / deletes only to rows that fall within your date span (of course you will have to replace the 'date span ' strings with actual dates), even if a multi-row operation might affect some rows but not others.

CREATE TRIGGER dbo.PreventUpdate_table_name
ON dbo.table_name
INSTEAD OF UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE t
      SET t.col = i.col
      FROM dbo.table_name AS t
      INNER JOIN inserted AS i
      ON t.key = i.key
      WHERE t.date_column >= 'acceptable date span start'
      AND t.date_column < 'acceptable date span end';
END
GO

CREATE TRIGGER dbo.PreventDelete_table_name
ON dbo.table_name
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;

    DELETE t
      FROM dbo.table_name AS t
      INNER JOIN inserted AS i
      ON t.key = i.key
      WHERE t.date_column >= 'acceptable date span start'
      AND t.date_column < 'acceptable date span end';
END
GO

Best Answer

Related Solutions

Sql-server – SQL Server column level security

Sql-server – Restrict record to be update or deleted according to dates inside row for SQL Server 2005

Related Question