Sql-server – Does this mean the ‘public’ has full select, insert, update, and delete access

permissionsSecuritysql serversql-server-2008sql-server-2008-r2

I'm trying to create a new DB user in SQL Server 2008 R2 that can only select from one SQL View. But no matter what I try when I login as this new user I can select from any table. I ran this query:

select  princ.name
,       princ.type_desc
,       perm.permission_name
,       perm.state_desc
,       perm.class_desc
,       object_name(perm.major_id)
from    sys.database_principals princ
left join
        sys.database_permissions perm
on      perm.grantee_principal_id = princ.principal_id
WHERE princ.name = 'public'
AND object_name(perm.major_id) = 'User_Table'

And get these results:

name    type_desc       permission_name     state_desc  class_desc          (No column name)
public  DATABASE_ROLE   DELETE              GRANT       OBJECT_OR_COLUMN    User_Table
public  DATABASE_ROLE   INSERT              GRANT       OBJECT_OR_COLUMN    User_Table
public  DATABASE_ROLE   REFERENCES          GRANT       OBJECT_OR_COLUMN    User_Table
public  DATABASE_ROLE   SELECT              GRANT       OBJECT_OR_COLUMN    User_Table
public  DATABASE_ROLE   UPDATE              GRANT       OBJECT_OR_COLUMN    User_Table

Does this mean no matter what anyone that logs in into the database will have full access based on the 'public' permissions?

Best Answer

This means that if the user has not had permissions explicitly set for the securable (or inherited from permissions explicitly set) that public has permissions for, then those will be applied. In other words, if you have User1 with DELETE permissions denied on User_Table, then User1 will not be able to delete data from that table.

Please see BOL's reference on Database-Level Roles:

public Database Role
Every database user belongs to the public database role. When a user has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object.

Example

use TestDB;
go

create login Login1
with
    password = 'password',
    check_policy = off;
go

create user User1
for login Login1;
go

-- this will return 1 (meaning, yes it is a role member)
select is_rolemember('public', 'User1');


-- let's do a test to show the above theory
create table SomeTable
(
    id int identity(1, 1) not null,
    SomeText varchar(30) not null
        default replicate('a', 30)
);
go

insert into SomeTable
values(default);
go 10

-- give the public role permissions to SELECT on SomeTable
grant select
on SomeTable
to public;
go

-- this will be successful, because User1 is part of public
execute as user = 'User1';
go

select *
from SomeTable;

revert;
go


-- create a new role to deny SELECT on SomeTable
exec sp_addrole 'Role1';
go

deny select
on SomeTable
to Role1;
go

-- add User1 to this new role
exec sp_addrolemember 'Role1', 'User1';
go


-- this will not be successful because User1 now has been denied SELECT on SomeTable
execute as user = 'User1';
go

select *
from SomeTable;

revert;
go

Related Solutions

Sql-server – How to add a user with access to a single view

Please don't use the UI for this. It's a confusing mess.

It sounds to me like what you want is to create a user in a database, for a specific login, who only has permissions to select from one view. So, since you already have the login created:

USE your_db;
GO
CREATE USER username FROM LOGIN username;
GO
GRANT SELECT ON dbo.MyViewName TO username;
GO

EDIT here is an example of a script that will lead to the error you mention.

First, create some table in the unrelated_db:

CREATE DATABASE unrelated_db;
GO
USE unrelated_db;
GO
CREATE TABLE dbo.foo(bar INT);
GO

Now create a relatively restricted login:

USE [master];
GO
CREATE LOGIN username WITH PASSWORD='foo', CHECK_POLICY = OFF;
GO

Now create a database where the view will live, and add the login as a user:

CREATE DATABASE velojason;
GO
USE velojason;
GO
CREATE USER username FROM LOGIN username;
GO

Now create a function that will reference the table in the other database, and a synonym to the other table:

CREATE FUNCTION dbo.checkbar()
RETURNS INT
AS 
BEGIN
    RETURN 
    (
      SELECT TOP (1) bar 
        FROM unrelated_db.dbo.foo 
        ORDER BY bar
    );
END
GO
CREATE SYNONYM dbo.foo FOR unrelated_db.dbo.foo;
GO

Now create a local table:

CREATE TABLE dbo.PaymentDetails
(
  PaymentID INT
);
GO

Now create a view that references the table, the function and the synonym, and grant SELECT to username:

CREATE VIEW dbo.SomeView
AS
  SELECT 
    p.PaymentID, 
    x = dbo.checkbar(), -- function that pulls from other DB
    y = (SELECT bar FROM dbo.foo) -- synonym to other DB
    FROM dbo.PaymentDetails AS p;
GO
GRANT SELECT ON dbo.SomeView TO username;
GO

Now try to execute as username and select only the local column from the view:

EXECUTE AS USER = 'username';
GO
  -- even though I don't reference any of the columns 
  -- in the other DB, I am denied SELECT on the view:
SELECT PaymentID FROM dbo.SomeView;
GO
REVERT;
GO

Result:

Msg 916, Level 14, State 1, Line 3
The server principal "username" is not able to access the database "unrelated_db" under the current security context.

Now change the view to not reference any external objects, and run the above SELECT again, and it works:

ALTER VIEW dbo.SomeView
AS
  SELECT 
    p.PaymentID 
    --x = dbo.checkbar(),
    --y = (SELECT bar FROM dbo.foo)
    FROM dbo.PaymentDetails AS p;
GO

Short of showing us the scripts for the Payment Details, Account Details and MyView objects, maybe you can let us know if this query returns any results. You can find references to various objects through the catalog view sys.sql_expression_dependencies, but this view is not perfect - I believe it depends on all the views being refreshed (in the case where views reference other views, for example, or underlying schema has changed) in order to be accurate.

DECLARE 
  @dbname   SYSNAME = N'unrelated_db',
  @viewname SYSNAME = N'dbo.SomeView';

SELECT DISTINCT 
    [This object] = 
    OBJECT_SCHEMA_NAME([referencing_id]) 
      + '.' + OBJECT_NAME([referencing_id]), 
    [references this object] = 
    OBJECT_SCHEMA_NAME([referenced_id]) 
      + '.' + OBJECT_NAME([referenced_id]), 
    [and touches this database] = referenced_database_name,
    [and is a(n)] = o.type_desc,
    [if synonym, it references] = s.base_object_name
FROM sys.sql_expression_dependencies AS d
LEFT OUTER JOIN sys.objects AS o
ON o.[object_id] = d.referenced_id
LEFT OUTER JOIN sys.synonyms AS s
ON d.referenced_id = s.[object_id]
AND s.base_object_name LIKE '%[' + @dbname + ']%'
WHERE OBJECT_ID(@viewname) IN (
        referenced_id, 
        referencing_id, 
        (SELECT referencing_id FROM sys.sql_expression_dependencies 
        WHERE referenced_database_name = @dbname)
) OR referenced_database_name = @dbname;

SQL Server isn't just going to try to access unrelated_db for the fun of it... there must be some tie to that database from the view you're trying to use. Unfortunately if we can't see the view definition and more details about the objects it touches, all we can do is speculate. The two main things I can think of are synonyms or functions that use three-part names, but seeing the actual scripts will give us a much better idea instead of guessing. :-)

You may also want to check sys.dm_sql_referenced_entities, however this function returns nothing useful in the example above.

Sql-server – SELECT permission on view querying table from another database

Sounds to me like you have a case of ownership chaining. The link should provide you with the details on how to make sure the chain stays intact.

Best Answer

Related Solutions

Sql-server – How to add a user with access to a single view

Sql-server – SELECT permission on view querying table from another database

Related Question