Chapter 2 user authentication, authorization, and security (9): prevents login names and users from viewing metadata

Source: Workshop

Without the consent of the author, no one shall be published in the form of "original" or used for commercial purposes. I am not responsible for any legal liability.

Previous Article: http://blog.csdn.net/dba_huangzj/article/details/38944121

 

Preface:

 

Before SQL Server 2005, all servers and database metadata were visible to everyone. When the online banking system shares the SQL server instance with the customer, it is possible to see the information of other users. Starting from 2005, you can control permissions to restrict the login name or user to view unnecessary metadata.

 

Implementation:

 

If you need to hide the database from all login names, you can remove the view any database permission on the public role:

USE master; GO REVOKE VIEW ANY DATABASE TO public;

When allowing some accounts to view all databases, you can create a user-defined server role:

USE master; CREATE SERVER ROLE [DatabaseViewer]; GO GRANT VIEW ANY DATABASE TO [DatabaseViewer]; ALTER SERVER ROLE [DatabaseViewer] ADD MEMBER [Fred];

 

Note that both master and tempdcb are always visible to all logins.

 

You cannot selectively view certain database settings. One login can either view all databases from object resource manager or all databases. If a login name is granted the view any database server permission, you can view all the databases on the server in object resource manager or query all the databases in the SYS. Databases directory view. If the login name does not have this permission but is mapped to a database user, it still cannot see all databases, but you can use sys. databases returns the database information and uses the use database command to switch the database.

The only way to allow the database to be selectively visible is to use the login name as the database owner:

ALTER AUTHORIZATION ON DATABASE::marketing TO [Fred];

The database owner has all permissions in the database, but one database has only one owner. You cannot have multiple login names set the owner of a database at the same time.

In the database, you can define a specific database object to be visible to some users. In SSMs, right-click the [Security] node of the database and select [security object] in [user] → [attribute ], then, add a specific object, such as a table, stored procedure, or architecture, to [Search. Then, select the [authorize] column of [view definition:

 

You can also use commands. Note the script here. If you do not remember the command, you can click this button to automatically generate the Code:

use [AdventureWorks2012] GO GRANT VIEW DEFINITION ON [dbo].[AWBuildVersion] TO [test] GO

Principle:

You can use the following statement to view the metadata permissions that can be authorized:

SELECT  parent_class_desc AS parent ,         class_desc AS class ,         permission_name AS permission FROM    sys.fn_builtin_permissions(NULL) WHERE   permission_name LIKE ‘VIEW%‘ ORDER BY CASE parent_class_desc            WHEN ‘‘ THEN 0            WHEN ‘SERVER‘ THEN 1            WHEN ‘DATABASE‘ THEN 2            WHEN ‘SCHEMA‘ THEN 3          END ,         class ,         permission;

 

[View definition] Permission is a permission that can be viewed outside the scope of the server. If you want to view all the definitions in the instance, you must use [view any definition] to grant this permission to log on to view all the definitions in the instance, [view all databases] is suitable for login names that only need to access the database but do not need to access other objects on the server.

In the database, you can see objects with permissions. By default, a user is only a member of the public database role and has no permissions. You can use the db_datareader fixed database role to allow this user to view all the tables and grant view definition to the stored procedure, function, or trigger, allowing you to see the underlying code. If you don't want others to see it, you can use with encryption when creating an object (as described later .)

Chapter 2 user authentication, authorization, and security (9): prevents login names and users from viewing metadata