azure database, user have database level permission, and then grant schema level permission ,what will happen?

Huang, Winston-HR 300 Reputation points
2024-06-26T08:23:08.6666667+00:00

hi friend,

we use azure Synapse type database.

i found two principals (called A and B )have been granted control/execute/connect permission on database level.

now i want to granting 'select' permission on schema(called SC) to principal A,

what will happen if i take action? it will made that principal A only have 'select' permission on schema SC ,principal A can not control/execute/connect other schemas?

thanks~

Is there a fine-grained security access control for database level permissions and schema level permissions?

Will there be a conflict between granting database level permissions and schema level permissions to users simultaneously?

thanks~

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,621 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. BhargavaGunnam-MSFT 28,616 Reputation points Microsoft Employee
    2024-06-26T19:04:23.9833333+00:00

    Hello Huang, Winston-HR,

    To your question regarding principal A and the 'SELECT' permission on schema SC:

    • Granting 'SELECT' permission on schema SC to principal A will allow them to select data from objects within SC.
    • This action does not remove their ability to CONTROL, EXECUTE, or CONNECT to the database or other schemas unless those permissions are explicitly revoked.
    • It's important to review the combination of permissions to ensure they align with the intended level of access and do not inadvertently grant more access than intended.

    from the below Microsoft documenation:

    A schema is a database-level securable contained by the database that is its parent in the permissions hierarchy.

    https://video2.skills-academy.com/en-us/sql/t-sql/statements/grant-schema-permissions-transact-sql?view=sql-server-ver16

    Please see the example in the doument:

    For example, this issue may occur in the following scenarios. These scenarios assume that a user, referred as U1, has the ALTER permission on the S1 schema. The U1 user is denied to access a table object, referred as T1, in the schema S2. The S1 schema and the S2 schema are owned by the same owner.

    The U1 user has the CREATE PROCEDURE permission on the database and the EXECUTE permission on the S1 schema. Therefore, the U1 user can create a stored procedure, and then access the denied object T1 in the stored procedure.

    The U1 user has the CREATE SYNONYM permission on the database and the SELECT permission on the S1 schema. Therefore, the U1 user can create a synonym in the S1 schema for the denied object T1, and then access the denied object T1 by using the synonym.

    The U1 user has the CREATE VIEW permission on the database and the SELECT permission on the S1 schema. Therefore, the U1 user can create a view in the S1 schema to query data from the denied object T1, and then access the denied object T1 by using the view.

    When managing permissions, always consider the implications of the permissions hierarchy and how different permissions can interact.

    I hope this answers your question.

    If this answers your question, please consider accepting the answer by hitting the Accept answer and up-vote as it helps the community look for answers to similar questions.

    0 comments
  2. Nandan Hegde 30,716 Reputation points MVP
    2024-06-27T07:27:35.3666667+00:00

    Adding on top, just a simple aspect is the Service principal A has Grant control access on the database.

    That is somewhat the next highest privilege post db_owner role.

    So there is no explict need to grant access on schema as it already has the permissions to do it

    0 comments