Tags: create security policy with RLS, Enhancing securities with Row Level Security (RLS), RLS, RLS - Predicates'Security Policy, RLS in SQL server 2016, Row level Security, Security features in SQL 2016
SQL Server 2016 has introduced Row Level Security (RLS) – it’s an enhanced security feature that enables control over access to rows in a table. For general security purpose there are also many features like “Always Encrypted”, “Dynamic Data Masking”, “enhancement of Transparent Data Encryption”, but RLS allows us to easily control which users can access which data with complete transparency to the application. This enables us to easily restrict the data based on the user identity or security context. Row-Level Security in SQL Server 2016 helps us to maintain a consistent data access policy and reduce the risk of accidental data leakage.
Actually, Row Level Security (RLS) is a concept that provides security at the row level within the database layer, instead of at the application layer. RLS may be implemented by using a function and a new security policy feature without even changing application code.
Before implementing RLS, First, we should know the few new terms which one need to learn and understand this feature.
Security Predicate: This is not a new object but an inline table valued function -inline TVF -which contains the logic of filtering the rows.
Security Policy: This is a new object which can be CREATE, ALTER and DROP. It may be consider as a container of predicates which can be applied to tables. One policy can contain security predicate to many tables. A policy can be in an ON or OFF state.
RLS supports two types of security predicates.
- Filter predicates silently filter the rows available to read operations (SELECT, UPDATE, and DELETE).
- Block predicates explicitly block write operations (AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE) that violate the predicate.
Let’s take an example :
–Create a Database named “VirendraRLS_Test”
CREATE DATABASE VirendraRLS_Test;
— Create users
CREATE USER Viren WITHOUT LOGIN;
CREATE USER Yaduvanshi WITHOUT LOGIN;
— Create a table with sample data and grant SELECT to the new users
CREATE TABLE dbo.Employee
( [EmployeeName] VARCHAR(25),[BasicSalary] MONEY)
INSERT INTO dbo.Employee
GRANT SELECT ON dbo.Employee TO Viren
GRANT SELECT ON dbo.Employee TO Yaduvanshi
— Create a filter predicate function as below
CREATE FUNCTION dbo.RLSPredicate (@EmployeeName as sysname)
SELECT 1 AS RLSPredicateResult WHERE @EmployeeName = USER_NAME();
— Add filter predicate to the table
CREATE SECURITY POLICY RLSUserFilter
ADD FILTER PREDICATE dbo.RLSPredicate(EmployeeName)
ON dbo.Employee WITH (STATE=ON);
EXECUTE (‘SELECT * FROM Employee’) AS USER=‘Viren’
EXECUTE (‘SELECT * FROM Employee’) AS USER=‘Yaduvanshi’
Tags: Benefits of the Buffer Pool Extension, BPE, BPE SQL Server, Buffer Pool Extension, enabling BPE, New feature with SQL Server 2014, SQL Server 2014 : Buffer Pool Extension (BPE), SQL Server Buffer Pool Extension, sys.dm_os_buffer_descriptors, sys.dm_os_buffer_pool_extension_configuration, What is BPE
Buffer pool extension introduced in SQL server 2014. The buffer pool extension provides the seamless integration of a nonvolatile RAM extension to the Database Engine buffer pool to significantly improve I/O throughput. As we know the primary purpose of a SQL Server Database is to store and retrieve data, in this operation, commonly Data is read from disk into memory, once the data is changed, it is marked as dirty and the dirty pages are written to disk and flagged as clean data. clean pages may be flushed from memory when the data cache known as Buffer Pool comes under pressure and in this situation data got deleted from memory and SQL Server has to read it from disk the next time a user runs a query that requires the data.
Now there are no problems as data size is less than memory, but as data size is more than memory – It’s started creating issues, To resolve this, In SQL Server 2014 Buffer Pool Extensions introduced to solve problem if we have not enough memory. In the case of Buffer Pool Extensions, SQL Server uses the disk space to store clean buffers having unmodified data pages that out of RAM.
The buffer pool extension feature extends the buffer pool cache with nonvolatile storage (usually SSD). Because of this extension, the buffer pool can accommodate a larger database working set, which forces the paging of I/O’s between RAM and the SSDs. This effectively offloads small random I/O’s from mechanical disks to SSDs. Because of the lower latency and better random I/O performance of SSDs, the buffer pool extension significantly improves I/O throughput.
SSD storage is used as an extension to the memory subsystem rather than the disk storage subsystem. That is, the buffer pool extension file allows the buffer pool manager to use both DRAM and NAND-Flash memory to maintain a much larger buffer pool of lukewarm pages in nonvolatile random access memory backed by SSDs. This creates a multilevel caching hierarchy with level 1 (L1) as the DRAM and level 2 (L2) as the buffer pool extension file on the SSD. Only clean pages are written to the L2 cache, which helps maintain data safety. The buffer manager handles the movement of clean pages between the L1 and L2 caches.
The following illustration provides a high-level architectural overview of the buffer pool relative to other SQL Server components.
When enabled, the buffer pool extension specifies the size and file path of the buffer pool caching file on the SSD. This file is a contiguous extent of storage on the SSD and is statically configured during startup of the instance of SQL Server. Alterations to the file configuration parameters can only be done when the buffer pool extension feature is disabled. When the buffer pool extension is disabled, all related configuration settings are removed from the registry. The buffer pool extension file is deleted upon shutdown of the instance of SQL Server.
- The buffer pool extension size can be up to 32 times the value of max_server_memory.
- A ratio between the size of the physical memory (max_server_memory) and the size of the buffer pool extension of 1:16 or less. A lower ratio in the range of 1:4 to 1:8 may be optimal.
- BPE feature is available for 64-bit SQL Server only.
- Its available with SQL Server 2014 Standard, Business Intelligence and Enterprise only.
- If BPE is enabled and you suppose have to modify the size of the file used by BPE on the non-volatile disk, then first need tp disable BPE and set the new size and enable BPE again. If the size is less than previously set, SQL Server must be restarted for the changes to take effect on the non-volatile disk.
Implementing SSD Buffer Pool Extension
— Enabling BPE as 50GB
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
(FILENAME = ‘F:\SSD_Data\VirendraTest.BPE’,SIZE = 50 GB)
— Disable BPE
ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF
— To See BPE status
(CASE WHEN ([is_modified] = 1 AND ([is_in_bpool_extension] IS NULL OR [is_in_bpool_extension] = 0)) THEN N’Dirty’
WHEN ([is_modified] = 0 AND ([is_in_bpool_extension] IS NULL OR [is_in_bpool_extension] = 0)) THEN N’Clean’
WHEN ([is_modified] = 0 AND [is_in_bpool_extension] = 1) THEN N’BPE’ END) AS N’Page State’,
(CASE WHEN ([database_id] = 32767) THEN N’Resource Database’ ELSE DB_NAME ([database_id]) END) AS N’Database Name’,
COUNT(1) AS N’Page Count’
GROUP BY [database_id], [is_modified], [is_in_bpool_extension]
ORDER BY [database_id], [is_modified], [is_in_bpool_extension]
Happy Reading : Please Comment !
Tags: permission to truncate table, truncate permission, Truncate Table, Truncate Table permission to a user, Truncate Table permission to specific user
Let in your environment there are lots of SQL user and as a DBA you have to give permission to a specific user to truncate table only. The specified User have no select/delete/insert/update permission on table.
In this scenario we have to grant ALTER permission to that user.
USE [VirendraTest] — VirendraTest is here database Name
GRANT ALTER ON [dbo].[TestTable] TO [Virendra] — Virendra is a User
Tags: Number of records affected during report excution, Report execution details, report processing time, Reporting Server (SSRS) – Activity Details, Reports generation time, Reports rendering time, SQL Server Reporting Server - Reports details, SQL Server Reporting server details, SSRS Details
Hi Guys, Here I am starting How To series, hope it will be helpful for SQL’s lovers.
It’s a daily routine we see system reports where all things like Server health, DB Status, Jobs Status, Disk Spaces … etc. are as per defined standard or not. Hope you guys also worked/managed SSRS – report server also. Hope you have also got queries / complaints from your internal or external clients stating reports are slow / reports are not being generated / reports server is not working / reports are being generated from long time …… etc.
Here is a query, it will show where are problems – is it related to data Retrieval or related to data processing or where it is related to rendering. Below query will show all details
Use ReportServer — Use configured DB Name
SELECT EL.ReportID ‘Report ID’,
CT.name ‘Report Name’,
CT.Path ‘Report Path’,
WHEN EL.RequestType = 0 THEN ‘Interactive’
WHEN EL.RequestType = 1 THEN ‘Subscription’
WHEN EL.RequestType = 2 THEN ‘Refresh Cache’ END AS ‘Request Type’,
EL.Format ‘Report Format’,
DATEDIFF(ss,EL.TimeStart,EL.TimeEnd) AS ‘TotalDuration(Sec)’,
(EL.TimeDataRetrieval/1000.00) AS ‘Data Retrieval Time (Sec)’,
(EL.Timeprocessing/1000.00) AS ‘Processing Time(Sec)’,
(EL.TimeRendering/1000.00) AS ‘Rendering Time(Sec)’,
WHEN EL.Source=1 THEN ‘LIVE’
WHEN EL.Source=2 THEN ‘Cache’
WHEN EL.Source=3 THEN ‘Snapshot’
WHEN EL.Source=4 THEN ‘History’
WHEN EL.Source=5 THEN ‘Ad hoc(Report Builder)’
WHEN EL.Source=6 THEN ‘Session’
WHEN EL.Source=7 THEN ‘Report Definition Customization Extension(RDCE)’
END AS ‘Source’,
EL.ByteCount/1024.00 AS ‘Size(Kb)’,
EL.[RowCount] AS ‘Number of Records’
FROM ExecutionLog EL
INNER JOIN [Catalog] CT ON CT.itemid=EL.reportid
Order by EL.TimeStart Desc
Use it and please share the comments/views/your finding on same – Happy Reading !