September 5, 2018 at 4:46 am
Hi all
I need to create a schema to store a set of tables but I don't want anyone (including those in the DB_Owner role) to be able to see it.
The only people able to see the schema is anyone with SysAdmin access (that's us developers who also double as part-time DBAs).
Is what I want to do possible?
If so, has anyone got any idiot-proof instructions as I've tried to create the schema as a test and then grant/deny various options but those with DB_Owner access can still see the schema.
Anyone any ideas?
September 5, 2018 at 6:05 am
What do you mean my "see" exactly? If you mean know of it's existance, then yes, they will be able to see it. If you mean access the data, then a DENY will stop them, kind of. It will stop them being able to access the data themselves. What it won't do, however, is stop them creating another user and impersonating them. For example:CREATE DATABASE testDB;
GO
USE testDB;
GO
CREATE USER testUser WITHOUT LOGIN;
ALTER ROLE db_owner ADD MEMBER testUser;
CREATE TABLE test (id int);
DENY INSERT ON test TO testUser;
GO
EXECUTE AS USER = 'testUser';
INSERT INTO test
VALUES (1); --Fails. INSERT is denied
GO
CREATE USER testUser2 WITHOUT LOGIN
ALTER ROLE db_owner ADD MEMBER testUser2;
GO
EXECUTE AS USER = 'testUser2';
INSERT INTO test
VALUES (1); --Success.
REVERT;
REVERT;
GO
USE master;
GO
DROP DATABASE testDB;
GO
Why not create a completely different database instead and not create any users or logins on it? Then no one, apart from the SA's, have access.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 5, 2018 at 6:23 am
Thanks Thom.
I've done a deny select (and I'll probably need to do some other DENYs as well) on the relevant table already.
This is part of a test on our DEV server.
Ideally, I wanted the extra schema not to be even visible so people don't know it exists (unless you're part of the SA group).
As this is just a test, I'm going to be transferring any I learn to our "active" database which has two types of tables in it.
We have:-
staging tables (small amounts of data for cleansing purposes)
actual tables which contains all the latest information on a variety of subjects.
The general idea was to have the staging tables hidden from general view so people couldn't even see them, and therefore couldn't select from them by accident.
As for moving the tables to a new database, it's an option but would take quite a while.
Might have to bite the bullet and take this option if we can't get the hidden schema to work.
September 5, 2018 at 1:00 pm
What about putting the staging tables in a separate DB on the same instance.
Then ensure that your ETL pushes from the staging DB to the final DB.
That way, the users on the final DB will not know about the staging objects.
September 6, 2018 at 1:45 am
@desnorton - We did think about that but it would be even more work to move the tables, stored procedures and rewrite the stored procedures in both databases.
Anyway, I think I've solved it.
I've created the new schema and added a dummy table.
I then did a DENY (everything) on the schema to my test user (it's a SQL-only login so it's not attached to an AD account).
The code I ran was this:-
DENY ALTER, SELECT, INSERT, UPDATE, DELETE, EXECUTE
ON SCHEMA::Secure TO TestUser;
Logged in as that user, the schema/table doesn't even appear.
I just need to sort out some other permissions now for some items they should be able to run (mostly for exporting files that people can normally do) and it looks like we're good to go.
If this works, I can apply the same logic/code to my other databases (after sorting out schemas) and hopefully it will have the same effect.
Can anyone see any flaws in what I've done?
September 6, 2018 at 1:56 am
richardmgreen1 - Thursday, September 6, 2018 1:45 AM@desnorton - We did think about that but it would be even more work to move the tables, stored procedures and rewrite the stored procedures in both databases.Anyway, I think I've solved it.
I've created the new schema and added a dummy table.
I then did a DENY (everything) on the schema to my test user (it's a SQL-only login so it's not attached to an AD account).The code I ran was this:-
DENY ALTER, SELECT, INSERT, UPDATE, DELETE, EXECUTE
ON SCHEMA::Secure TO TestUser;Logged in as that user, the schema/table doesn't even appear.
I just need to sort out some other permissions now for some items they should be able to run (mostly for exporting files that people can normally do) and it looks like we're good to go.
If this works, I can apply the same logic/code to my other databases (after sorting out schemas) and hopefully it will have the same effect.
Can anyone see any flaws in what I've done?
I can't see any flaws, as such, however, just not sure that'll stop a DBO. You could set those permissions explicitly on Public, but that doesn't stop a dbo from removing those permissions. If you set it at every user level, that doesn't stop a dbo creating a new user with dbo privileges.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 6, 2018 at 2:04 am
Never thought of that bit.
I might have to remove DBO permissions from all our current users if I can figure out the correct set of permissions to give instead.
September 6, 2018 at 2:11 am
richardmgreen1 - Thursday, September 6, 2018 2:04 AMNever thought of that bit.
I might have to remove DBO permissions from all our current users if I can figure out the correct set of permissions to give instead.
The SQL I posted in my initial reply shows just one way a DBO could get around the permissions. You can set permissions for a DBO, however, for one that know what they are doing getting around them can be trivial. My first reply shows how a DBO could get around permissions set on their own User, however, getting around public is quick simple as well:
CREATE DATABASE TestDB;
GO
USE TestDB;
GO
CREATE USER TestDBO WITHOUT LOGIN;
ALTER ROLE db_owner ADD MEMBER TestDBO;
GO
CREATE SCHEMA special;
GO
CREATE TABLE special.NewTable (ID int);
INSERT INTO special.NewTable (ID)
VALUES (1),(2);
GO
DENY ALTER, SELECT, INSERT, UPDATE, DELETE, EXECUTE
ON SCHEMA::special TO public;
GO
EXECUTE AS USER = 'TestDBO';
GO
SELECT *
FROM special.NewTable; --Fails
GO
REVOKE ALTER, SELECT, INSERT, UPDATE, DELETE, EXECUTE
ON SCHEMA::special TO public;
GO
SELECT *
FROM special.NewTable; --Succeeds
GO
REVERT;
USE master;
GO
DROP DATABASE TestDB;
GO
Locking down a DBO user is very difficult, as although they can't change their own permissions, there's little stopping them changing someone else's and then using theirs.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 6, 2018 at 2:22 am
Thanks for that.
I'm currently looking at alternatives to the full-on "DBO" role if I can figure out just the permissions someone will need to be able to do their job without have db_owner access. I'm thinking a judicious use of GRANT and DENY commands might get me there.
::edit::
I've got most of it, apart from one procedure we use to export Excel files.
It uses linked servers to create the spreadsheet before exporting the data into it.
I'm thinking it's a permission on either master or msdb that's needed to create the linked server.
September 6, 2018 at 4:29 am
The plot thickens.....
This is what I've done so far (my dodgy notes):-
Login TestUser (created as SQL-only login with password)
Current permissions:-
Server roles - public
msdb - datareader, exec_function, execute, public, RSExecrole, SQLAgentOperatorRole, SQLAgentReaderRole, SQLAgentUserRole
Utilities - alterobject, alterschema, create_function, create_sproc, create_table, create_view, datareader, datawriter, exec_function, execute, public
Securables - alter any linked server (grant), connect sql (grant)
Linked Servers / Providers / Microsoft.ACE.OLEDB.12.0 - untick "Allow inprocess"
The msdb permissions were needed to run a job to export some files to CSV (might be able to remove some of these).
The utilities permissions were needed to allow my test user to carry out the daily jobs.
The above permissions are a cut-down version of the permissions from our current AD group.
My issue now is that the user needs to create a linked server to export data to Excel. The code we use to do that is as follows:-DECLARE
@Directory NVARCHAR(250)= 'H:'
,@FileName NVARCHAR(250)= 'Test.xlsx'
,@SheetName NVARCHAR(250)= 'Results'
,@HasHeaders BIT = 1
,@Server NVARCHAR(250)= 'DW-LANDING\CASESENSITIVE'
,@Database NVARCHAR(250)= 'SystmOneData'
,@Schema NVARCHAR(250)= 'dbo'
,@Table NVARCHAR(250)= 'vw_Last_Package_Execution_Data'
--get the columns from the source table
DECLARE @Results TABLE
(
ColName VARCHAR(250)
,DataType VARCHAR(50)
)
DECLARE
@SQL NVARCHAR(MAX)
,@Columns VARCHAR(MAX) = ''
,@DataSrc NVARCHAR(1000) = @Directory + '\' + @FileName
,@ProvStr NVARCHAR(1000) = CASE
WHEN RIGHT(@FileName,3)='XLS' THEN
'Excel 8.0'
ELSE
'Excel 12.0 Xml'
END
+ '; HDR=YES'
IF @server = @@SERVERNAME --exporting from local server
SET @sql = N'SELECT
COLUMN_NAME
,DATA_TYPE
FROM
['+@Database+'].INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME ='''+@Table+'''
ORDER BY
ORDINAL_POSITION'
ELSE --exporting from remote server
SET @sql = N'SELECT
COLUMN_NAME
,DATA_TYPE
FROM
['+@Server+'].['+@Database+'].INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME ='''+@Table+'''
ORDER BY
ORDINAL_POSITION'
INSERT INTO @Results
EXEC (@SQL)
--Only export if rows exist
IF (SELECT COUNT(1) FROM @Results)>0
BEGIN
--SELECT @Columns = @Columns + '`'+ColName + '` Text,' FROM @Results
SELECT @Columns = @Columns + '`'+ColName + '` LongText,' FROM @Results
SET @Columns = LEFT(@Columns,LEN(@Columns)-1)
--drop the linked server if it already exists
BEGIN TRY
EXEC master.dbo.sp_dropserver @FileName, 'droplogins'
END TRY
BEGIN CATCH
END CATCH
--create the linked server
EXEC master.dbo.sp_addlinkedserver
@server = @FileName
,@srvproduct = N''
,@provider = N'Microsoft.ACE.OLEDB.12.0'
,@datasrc = @DataSrc
,@provstr = @ProvStr
--enable remote procedure calls on linked server
EXEC master.dbo.sp_serveroption
@server=@FileName
,@optname=N'rpc out'
,@optvalue=N'true'
--create the workbook
SET @sql ='EXEC(''Create table `'+@SheetName+'`('+@Columns+')'') AT [' + @FileName + ']'
EXEC sp_executesql @sql
--get sql to convert columns to varchar for export
SET @Columns=''
SELECT
@Columns = @Columns
+ CASE DataType
WHEN 'date' THEN
'CONVERT(VARCHAR(10),['+ColName + '],103),'
WHEN 'datetime' THEN
'CONVERT(VARCHAR(10),['+ColName + '],103)+'' ''+CONVERT(VARCHAR(8),['+ColName + '],114),'
ELSE
'CAST(['+ColName + '] AS VARCHAR(250)),'
END
FROM
@Results
SET @Columns = LEFT(@Columns,LEN(@Columns)-1)
--export the data to excel
IF @server = @@SERVERNAME --exporting from local server
SET @sql = 'INSERT INTO ['+@FileName+']...['+@SheetName+']
SELECT '+@Columns+' FROM ['+@Database+'].['+@Schema+'].['+@Table+']'
ELSE --exporting from remote server
SET @sql = 'INSERT INTO ['+@FileName+']...['+@SheetName+']
SELECT '+@Columns+' FROM ['+@Server+'].['+@Database+'].['+@Schema+'].['+@Table+']'
EXEC sp_executesql @sql
--drop the linked server
EXEC sp_dropserver @FileName, 'droplogins'
END
ELSE
PRINT 'No rows exist. Export Cancelled'
I've given the user the "alter any linked server" permission to the creation of the linked server.
The error I'm currently getting from the above code is as follows:-
Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Test.xlsx".
If I run my code to export data to an Excel file as this user, it creates an entry in sys.servers so that bit appears to be working OK.
Also, if I run the above code as me (using Windows authentication) it works.
I'm guessing that the error is due to the fact that this is a SQL-only login with no permissions on the drives.
Can anyone confirm please?
September 7, 2018 at 11:56 am
richardmgreen1 - Thursday, September 6, 2018 4:29 AMThe plot thickens.....This is what I've done so far (my dodgy notes):-
Login TestUser (created as SQL-only login with password)Current permissions:-
Server roles - public
msdb - datareader, exec_function, execute, public, RSExecrole, SQLAgentOperatorRole, SQLAgentReaderRole, SQLAgentUserRole
Utilities - alterobject, alterschema, create_function, create_sproc, create_table, create_view, datareader, datawriter, exec_function, execute, public
Securables - alter any linked server (grant), connect sql (grant)Linked Servers / Providers / Microsoft.ACE.OLEDB.12.0 - untick "Allow inprocess"
The msdb permissions were needed to run a job to export some files to CSV (might be able to remove some of these).
The utilities permissions were needed to allow my test user to carry out the daily jobs.The above permissions are a cut-down version of the permissions from our current AD group.
My issue now is that the user needs to create a linked server to export data to Excel. The code we use to do that is as follows:-
DECLARE
@Directory NVARCHAR(250)= 'H:'
,@FileName NVARCHAR(250)= 'Test.xlsx'
,@SheetName NVARCHAR(250)= 'Results'
,@HasHeaders BIT = 1
,@Server NVARCHAR(250)= 'DW-LANDING\CASESENSITIVE'
,@Database NVARCHAR(250)= 'SystmOneData'
,@Schema NVARCHAR(250)= 'dbo'
,@Table NVARCHAR(250)= 'vw_Last_Package_Execution_Data'--get the columns from the source table
DECLARE @Results TABLE
(
ColName VARCHAR(250)
,DataType VARCHAR(50)
)DECLARE
@SQL NVARCHAR(MAX)
,@Columns VARCHAR(MAX) = ''
,@DataSrc NVARCHAR(1000) = @Directory + '\' + @FileName
,@ProvStr NVARCHAR(1000) = CASE
WHEN RIGHT(@FileName,3)='XLS' THEN
'Excel 8.0'
ELSE
'Excel 12.0 Xml'
END
+ '; HDR=YES'IF @server = @@SERVERNAME --exporting from local server
SET @sql = N'SELECT
COLUMN_NAME
,DATA_TYPE
FROM
['+@Database+'].INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME ='''+@Table+'''
ORDER BY
ORDINAL_POSITION'ELSE --exporting from remote server
SET @sql = N'SELECT
COLUMN_NAME
,DATA_TYPE
FROM
['+@Server+'].['+@Database+'].INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME ='''+@Table+'''
ORDER BY
ORDINAL_POSITION'INSERT INTO @Results
EXEC (@SQL)--Only export if rows exist
IF (SELECT COUNT(1) FROM @Results)>0
BEGIN
--SELECT @Columns = @Columns + '`'+ColName + '` Text,' FROM @Results
SELECT @Columns = @Columns + '`'+ColName + '` LongText,' FROM @ResultsSET @Columns = LEFT(@Columns,LEN(@Columns)-1)
--drop the linked server if it already exists
BEGIN TRY
EXEC master.dbo.sp_dropserver @FileName, 'droplogins'
END TRY
BEGIN CATCH
END CATCH--create the linked server
EXEC master.dbo.sp_addlinkedserver
@server = @FileName
,@srvproduct = N''
,@provider = N'Microsoft.ACE.OLEDB.12.0'
,@datasrc = @DataSrc
,@provstr = @ProvStr--enable remote procedure calls on linked server
EXEC master.dbo.sp_serveroption
@server=@FileName
,@optname=N'rpc out'
,@optvalue=N'true'--create the workbook
SET @sql ='EXEC(''Create table `'+@SheetName+'`('+@Columns+')'') AT [' + @FileName + ']'
EXEC sp_executesql @sql--get sql to convert columns to varchar for export
SET @Columns=''SELECT
@Columns = @Columns
+ CASE DataType
WHEN 'date' THEN
'CONVERT(VARCHAR(10),['+ColName + '],103),'
WHEN 'datetime' THEN
'CONVERT(VARCHAR(10),['+ColName + '],103)+'' ''+CONVERT(VARCHAR(8),['+ColName + '],114),'
ELSE
'CAST(['+ColName + '] AS VARCHAR(250)),'
END
FROM
@ResultsSET @Columns = LEFT(@Columns,LEN(@Columns)-1)
--export the data to excel
IF @server = @@SERVERNAME --exporting from local server
SET @sql = 'INSERT INTO ['+@FileName+']...['+@SheetName+']
SELECT '+@Columns+' FROM ['+@Database+'].['+@Schema+'].['+@Table+']'
ELSE --exporting from remote server
SET @sql = 'INSERT INTO ['+@FileName+']...['+@SheetName+']
SELECT '+@Columns+' FROM ['+@Server+'].['+@Database+'].['+@Schema+'].['+@Table+']'EXEC sp_executesql @sql
--drop the linked server
EXEC sp_dropserver @FileName, 'droplogins'
END
ELSE
PRINT 'No rows exist. Export Cancelled'
I've given the user the "alter any linked server" permission to the creation of the linked server.The error I'm currently getting from the above code is as follows:-
Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Test.xlsx".If I run my code to export data to an Excel file as this user, it creates an entry in sys.servers so that bit appears to be working OK.
Also, if I run the above code as me (using Windows authentication) it works.I'm guessing that the error is due to the fact that this is a SQL-only login with no permissions on the drives.
Can anyone confirm please?
Create the linked server yourself and keep it instead of recreating it for each import.
Limit this users access.
September 10, 2018 at 6:04 am
This is a generic procedures where users can specify quite a lot of the parameters for the destination folder, the server, filename and table/view to be exported.
We can't use a static procedure as we aren't sure what will be exported.
September 11, 2018 at 2:45 am
Hi all
I'm making progress (I think).
I've now got a domain-level user I can use for testing purposes (it's got no privileges on the file-system so I've sorted that out as well).
I've been through this link and made sure that my test user has got ""Alter any linked server" permission ticked under the Grant column (as we're using T-SQL to create the linked server) but now I'm getting this error:-
Msg 15247, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 34 [Batch Start Line 0]
User does not have permission to perform this action.
Anyone any ideas as to what I'm missing?
September 11, 2018 at 4:59 pm
richardmgreen1 - Tuesday, September 11, 2018 2:45 AMHi allI'm making progress (I think).
I've now got a domain-level user I can use for testing purposes (it's got no privileges on the file-system so I've sorted that out as well).I've been through this link and made sure that my test user has got ""Alter any linked server" permission ticked under the Grant column (as we're using T-SQL to create the linked server) but now I'm getting this error:-
Msg 15247, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 34 [Batch Start Line 0]
User does not have permission to perform this action.Anyone any ideas as to what I'm missing?
This is from the code for the permission check that sp_MSaddserver_internal does: -- CHECK PERMISSIONS
if not (has_perms_by_name(null, null, 'alter any linked server') = 1)
begin.....
So it just checks for alter any linked server permissions, raises the error or moves on.
Sue
September 12, 2018 at 2:32 am
Thanks Sue
I've just run SELECT has_perms_by_name(null, null, 'alter any linked server') for the relevant user and it's coming back as a zero (which I'm assuming means no permissions).
I've double-checked the user in question and it definitely has a tick in the Grant column for "Alter any linked server".
Anyone any ideas as to why it's not being recognised?
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply