How to grant permissions to database but restrict to certain schemas

azure-sql-databasepermissionsroleschema

I am new to database administration and work within a small team of data analysts trying to modernise. We have recently set up an Azure SQL DB server we are accessing using SQL Server Management Studio.

We have an admin account and I have created a user login for each of us for day-to-day work. I would like all user logins to have the ability to create, drop, select etc. freely for all objects where the schema is unspecified or specified as "dbo" but to only have read-only permissions where the schema is specified as "testschema". I have attempted this using the following code from the admin account:

IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'testschema')) 
BEGIN
    EXEC ('CREATE SCHEMA [testschema] AUTHORIZATION [dbo]')
END

---- Create permissions group:
EXEC sp_addrolemember 'team_member', 'Mel';

-- GRANT BLANKET PERMISSIONS
GRANT CONTROL, ALTER, DELETE, EXECUTE, INSERT, REFERENCES, UPDATE ON SCHEMA::dbo TO team_member;

-- REVOKE PERMISSIONS TO SPECIFIED SCHEMAS
DENY CONTROL, ALTER, DELETE, EXECUTE, INSERT, REFERENCES,
          UPDATE ON SCHEMA::testschema TO team_member;

-- ALLOW TO SELECT AND VIEW ON THESE SCHEMAS
GRANT SELECT, VIEW DEFINITION ON SCHEMA::testschema TO team_member;

I have been playing around with granting, revoking and denying permissions to myself all morning but I can't seem to get the permissions settings correct so that my user account can create and drop tables with the "dbo" schema and see and select from tables with the "testschema" schema, but can't drop/alter/delete tables with the "testschema" schema.

Can anyone advise on the correct syntax for doing this?

EDIT: I have altered my original code slightly to DENY rather than REVOKE and instead of general permissions I have set dbo-specific schema permissions. The permissions are given as follows and appear to be correct, but team_member users still can not see or select from any testschema tables (there are no other currently-used schemas beyond "testschema" and "dbo").

Best Answer

A combination of DENY and a GRANT works for me.

For example:

GRANT SELECT ON schema::[dbo] TO [user_name]
DENY SELECT ON schema::[other_schema] TO [user_name]
DENY SELECT ON schema::[schema_1] TO [user_name]
GRANT SELECT ON schema::[schema_Safe] TO [user_name]

If you need to prevent to drop the table by some user, try this:

DENY DELETE ON OBJECT::dbo.table_to_deny TO restricted_user;

Related Solutions

Why can’t the schema owner create a new table

If you are using SQL Server 2005 or later then to create an table in a schema you need both CREATE TABLE at the database level and ALTER at the schema level. Ownership of a schema covers the ALTER permission requirement but not the CREATE TABLE one.

CREATE TABLE is also granted by membership in the db_ddladmin fixed database role. This role is for backwards compatibility - do not use it for new security implementations.

Postgresql – How to GRANT for all tables across all schemas

If you need to do this only once, the quickest way is probably the following.

Find all the schemas you want to touch by querying, for example, the pg_namespace system catalog:

SELECT nspname 
FROM pg_namespace
WHERE nspname LIKE 'shard%'; -- tweak this to your needs

Then we can expand the query output into GRANT statements:

SELECT 'GRANT SELECT ON ALL TABLES IN SCHEMA ' || nspname || ' TO foo;'
FROM pg_namespace
WHERE nspname LIKE 'shard%';

This will give you an enormous amount of statements, which you can just copy over and run. As you have a big number of schemas, the easiest is possibly the following (running it in psql):

\copy (SELECT 'GRANT ...' ...) TO some_local_file.sql

\i some_local_file.sql

Of course, if you prefer, you could do this using dynamic SQL, too - I find the above solution slightly better, as it leaves a file behind with the actions taken, that can then be checked in under version control.

If you happen to use a psql version 9.6 (or later), there is just another possibility using \gexec:

SELECT 'GRANT SELECT ...'
... -- you can even omit the semicolon here 
\gexec

Notes:

I've written an answer about a similar issue a while ago - you may find useful details there.
there is no possibility of doing this from GRANT, unfortunately. As it is not uncommon what you need here, I'd expect this functionality appearing sooner or later.
just to emphasize, the above commands will do the trick for the already existing tables. Future tables need the default privileges (which you've set, for a given role).

Best Answer

Related Solutions

Why can’t the schema owner create a new table

Postgresql – How to GRANT for all tables across all schemas

Related Question