Sql-server – Granting permissions only on a set list of objects

permissionsschemasql server

I have a SQL Server 2005 database with a large number of tables in the dbo schema. I now created a new schema (call it myschema) that only has three table-valued functions and two stored procedures in it. All of that code has access to the tables in dbo.

The code in myschema will ultimately be called from a web service and I am struggling to get the permissions right for the user I created for the web service.

At first, I created the user with no roles except public and then gave it specific permissions on the securables in myschema. But then I could log on using that user and select from (and ever update) anything in dbo.

So I gave the user the denydatareader and denydatawriter roles, which effectively restricted the access to the objects in dbo.

The result of this is that I can execute the two stored procedures just fine.

But if I try to use the table-valued functions, I get this error:

The SELECT permission was denied on the object 'MyFunction', database
'MyDB', schema 'myschema'.

This is despite my use of:

grant select on myschema.MyFunction to MyUser

I'm guessing that's because of my brilliant use of denydatareader.

So what is the correct way to give a user access only to a list of specific stored procedures and table-valued functions and not to anything else?

Update:
Based on Nico's answer below, here's what I did. For the sake of brevity, just pretend you see the exists checks.

create table dbo.testtable(id int)
go
insert into dbo.testtable (id) values (1)
insert into dbo.testtable (id) values (2)
insert into dbo.testtable (id) values (3)
go
create schema myschema
go
create login mylogin with password = 'SomePassword', check_policy = off, default_database=[MyDatabase], default_language=[us_english];
create user myuser for login mylogin with default_schema = [myschema];
create role myschemarole;

execute sp_addrolemember N'myschemarole', N'myuser';

Then logged in with mylogin:

-- Error: The SELECT permission was denied...
select * from dbo.testtable 

-- Error: The SELECT permission was denied...
update dbo.testtable set id = 4 where id = 2

-- Success: (3 row(s) affected)
update dbo.testtable set id = 4

-- Success: (1 row(s) affected)
insert into dbo.testtable (id) values (5)

As long as I don't have a where clause, I can insert and update to my heart's content. And on top of that, "use master" also works and the sys schema is also fair game.

Does this mean I still need denydatawriter?

Best Answer

I've done this a couple of weeks ago - Created LOGIN, ROLE and USER. Added USER to ROLE and granted explicit permissions to the ROLE itself. The advantage in my opinion is, that you can add further users later on without the struggle to grant the permissions, again.

First I created the login:

IF NOT EXISTS 
( 
  SELECT 
    "name" 
  FROM 
    "master"."dbo"."syslogins" 
  WHERE 
    "name" = 'your_login'
)
BEGIN 
  CREATE LOGIN your_login WITH PASSWORD = 'your_password', CHECK_POLICY = OFF; 
END

I've added the CHECK_POLICY param because things are created on user interaction and I'm not able to see, if policies are activated server-sided.

Followed by the user:

IF NOT EXISTS 
( 
  SELECT 
    1 
  FROM 
    "sys"."database_principals" 
  WHERE 
    "name" = N'your_username'
)
BEGIN 
  CREATE USER your_username FOR LOGIN your_login;
END

And the role third:

IF NOT EXISTS 
( 
  SELECT 
    1 
  FROM 
    "sys"."database_principals" 
  WHERE
    "name" = N'your_role_name'  
)
BEGIN
  CREATE ROLE "your_role_name"; 
END

Finally I added the user to the created role:

EXECUTE sp_addrolemember N'your_role_name', N'your_username';

After that, permissions can be set for each type of object, eg

GRANT EXECUTE ON "dbo"."your_sproc" TO your_role_name;

You don't have to add denydatareader or else, if you explicitly grant permissions to the specific objects. Beware that an for example INSERT permission doesn't include the SELECT permission on the same object. ( at least in my case it didn't ;) )

If you have problems executing or selecting from your UDF, check if you have granted all permissions on the referenced objects in the UDF itself!

Update after Cobus' update in OP:

I've executed the queries you provided based on my initial answer, results following:

select * from dbo.testtable

-- The SELECT permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.

update dbo.testtable set id = 4 where id = 2

-- The SELECT permission was denied on the object 'testtable', database 'xyz', schema 'dbo'. -- The UPDATE permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.

update dbo.testtable set id = 4

-- The INSERT permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.

insert into dbo.testtable (id) values (5)

-- The INSERT permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.

It behaves as intended. Do you have any further permissions granted / set or properties which allow you to access dbo-schema?

Related Solutions

Sql-server – Granting Execute Permissions on Stored Procedures, Functions and Views

It is true that you cannot grant EXEC permissions on a function that returns a table. This type of function is effectively more of a view than a function. You need to grant SELECT instead, e.g.:

GRANT SELECT ON dbo.Table_Valued_Function TO [testuser];

So your script would look more like this (sorry, but I absolutely loathe INFORMATION_SCHEMA and much prefer to use the catalog views, which also don't need functions like OBJECTPROPERTY):

DECLARE 
    @sql      NVARCHAR(MAX) = N'',
    @username VARCHAR(255)  = 'testuser';

SELECT @sql += CHAR(13) + CHAR(10) + N'GRANT ' + CASE 
    WHEN type_desc LIKE 'SQL_%TABLE_VALUED_FUNCTION'
      OR type_desc = 'VIEW'
    THEN ' SELECT ' ELSE ' EXEC ' END 
    + ' ON ' + QUOTENAME(SCHEMA_NAME([schema_id])) 
    + '.' + QUOTENAME(name) 
    + ' TO ' + @username + ';'
 FROM sys.all_objects
WHERE is_ms_shipped = 0 AND
( 
    type_desc LIKE '%PROCEDURE' 
    OR type_desc LIKE '%FUNCTION'
    OR type_desc = 'VIEW'
);

PRINT @sql;
-- EXEC sp_executesql @sql;

Now you can grant EXEC on a schema, and always create these procedures in that schema (actually one of the purposes of schemas!), which @jgardner04 already suggested, however in order for this solution to apply to table-valued functions as well, you'd also have to grant SELECT. Which is okay if you're not storing any data in tables in that schema (or at least that you want to hide from them), but it will apply to any tables and views as well, which might not be your intention.

Another idea (e.g. if you can't, or don't want to, use schemas) is to write a DDL Trigger that captures the CREATE_PROCEDURE, CREATE_FUNCTION and CREATE_VIEW events, and grants permissions to a user (or a set of users, if you want to store them in a table):

CREATE TRIGGER ApplyPermissionsToAllProceduressAndFunctions -- be more creative!
    ON DATABASE FOR CREATE_PROCEDURE, CREATE_FUNCTION, CREATE_VIEW
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE
        @sql       NVARCHAR(MAX),
        @EventData XML = EVENTDATA();

    ;WITH x ( sch, obj ) 
    AS
    (
        SELECT
          @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'), 
          @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)')
    )
    SELECT @sql = N'GRANT ' + CASE 
      WHEN o.type_desc LIKE 'SQL_%TABLE_VALUED_FUNCTION' 
        OR o.type_desc = 'VIEW'
      THEN ' SELECT ' 
      ELSE ' EXEC ' END 
        + ' ON ' + QUOTENAME(x.sch) 
        + '.' + QUOTENAME(x.obj) 
        + ' TO testuser;' -- hard-code this, use a variable, or store in a table
    FROM x
    INNER JOIN sys.objects AS o
    ON o.[object_id] = OBJECT_ID(QUOTENAME(x.sch) + '.' + QUOTENAME(x.obj));

    EXEC sp_executesql @sql;
END
GO

The drawback I find with DDL Triggers is that you can quickly forget that they're there. So a year down the road when you decide to stop granting these permissions to all new objects, it might take a while to troubleshoot why it's still happening. At my last job we logged all actions invoked by DDL triggers to a central "event log" of sorts, and that was our go-to place for tracking down any actions that happened on the server that nobody seems to remember (and it was a DDL trigger about half the time). So you may consider adding some additional logic that will help with that.

EDIT

Adding code for schema-based, and I'll mention again that this will grant permissions on any procedures, functions and tables created in the foo schema.

CREATE SCHEMA foo;
GRANT EXEC, SELECT ON SCHEMA::foo TO testuser;

Now if you create the following procedure, testuser will be able to execute:

CREATE PROCEDURE foo.proc1
AS 
BEGIN
    SET NOCOUNT ON;
    SELECT 1;
END
GO

Sql-server – Grant access to all objects (with a few exceptions) to a role

The sad truth is that you cannot do anything computerwise about the 'sa' activities. This is because the 'sa' (or a member of the sysadmin server role) are defined to be able to do pretty much anything.

So, of course, you should only have sysadmin's that you can trust to do their best to adhere to the standards for the database. But even the best sysadmin is imperfect. So you might set up some auditing to report on changes made in the database.

(Or, more low tech, periodically check your views for a restricted table name in a view.)

So it depends on what you are concerned about and how much effort you need to put into protecting the secure resources.

In terms of views that expose secure information, you can DENY permissions on a view. Even if a sysadmin created the view, the DENY of the view for those without approval will prevent their access.

Perhaps any high security views could be named something like SecureViewXYZZY so as to emphasize the reason for those views and the restriction of who can use them.

Best Answer

Related Solutions

Sql-server – Granting Execute Permissions on Stored Procedures, Functions and Views

Sql-server – Grant access to all objects (with a few exceptions) to a role

Related Question