Tuesday, September 5, 2017

Giving special rights for group or user

SQL Server provides great flexibility for different types of security solutions.
In this post I want to show one of them.

Problem description:
1. Need to create a group/user "User1", which has to have only CRUD (Create-Read-Update-Delete) permissions for data in schema called "Schema1".
2. Need to create a group/user "User2", which has to have similar permissions as "User1" and have to be able create Views/Procedures/Functions in schema called "Schema2".
3. The group/user "User1" has to have Select/Execute permissions for all newly created objects in "Schema2".

Solution: Create a special database role for group/user "User2".

Here is how it can be done:

Preparation:

/* Create Test database */
use master;
GO
CREATE DATABASE TestPermissionDB;
GO
ALTER DATABASE [TestPermissionDB] SET CONTAINMENT = PARTIAL WITH NO_WAIT;
GO
USE TestPermissionDB;
GO

/* Create Test Schemas */
CREATE SCHEMA Schema1;
GO 
CREATE SCHEMA Schema2;
GO 
/* Create Test Users and give them CRUD permissions*/
CREATE USER User1 WITH PASSWORD=N'39+pjFkM6+9ll131C9RBWIYL4KcaFSHIqYwK16+B9ec=';
GO
CREATE USER User2 WITH PASSWORD=N'sw/Efa4VCM6bUrz5s+jl8zuRas5r6U8IP8eiUO83NTk=';
GO
ALTER ROLE [db_datareader] ADD MEMBER User1;
GO
ALTER ROLE [db_datawriter] ADD MEMBER User1;
GO
ALTER ROLE [db_datareader] ADD MEMBER User2;
GO
ALTER ROLE [db_datawriter] ADD MEMBER User2;
GO
/* Create Test table */
SELECT 'ABC' as Sample_Data
INTO Schema1.tbl_Sample_Table;
GO

Give special permissions for User1:

/* Grant Permissions for User1 */
GRANT EXECUTE TO User1;
GO

Create special Role:

/* Create special test role and give it specific permissions */
CREATE ROLE SpecialRole;
GO
GRANT CREATE PROCEDURE to SpecialRole;
GO
GRANT CREATE VIEW to SpecialRole;
GO
GRANT CREATE FUNCTION to SpecialRole;
GO

And here is the magic.
/* Associate special test role with Schema2 and assign User2 to that role */
ALTER AUTHORIZATION ON SCHEMA::Schema2 TO SpecialRole;
GO
ALTER ROLE SpecialRole ADD MEMBER User2;
GO

From this point User2 has all permissions it needs.

Test User2 Permissions:

/* test Permissions for User2*/
EXECUTE AS USER = 'User2' ;  
GO  
CREATE FUNCTION Schema2.fn_Test() RETURNS CHAR(3) as 
BEGIN
RETURN (SELECT TOP 1 Sample_Data FROM Schema1.tbl_Sample_Table)
END;
GO  
CREATE VIEW Schema2.vw_Test as 
SELECT * FROM Schema1.tbl_Sample_Table;
GO  
CREATE PROCEDURE Schema2.sp_Test @VAL CHAR(3) as 
SELECT * FROM Schema1.tbl_Sample_Table
WHERE Sample_Data = @VAL;
GO  
REVERT ;  
GO 
Everything should execute successfully.

Now will test restrictions for User2:
/* test Permission failures for User2*/
EXECUTE AS USER = 'User2' ;  
GO  
CREATE VIEW Schema1.vw_Failed_Test as 
SELECT * FROM Schema1.tbl_Sample_Table;
GO  
CREATE PROCEDURE dbo.sp_Failed_Test as 
SELECT * FROM Schema1.tbl_Sample_Table;
GO  
REVERT ;   
It should return following errors:
Msg 2760, Level 16, State 1, Procedure vw_Failed_Test, Line 1 [Batch Start Line 92]
The specified schema name "Schema1" either does not exist or you do not have permission to use it.
Msg 2760, Level 16, State 1, Procedure sp_Failed_Test, Line 1 [Batch Start Line 95]
The specified schema name "dbo" either does not exist or you do not have permission to use it.

Test permissions for User1:

 /* test Permissions for User1*/
EXECUTE AS USER = 'User1' ;  
GO  
INSERT INTO Schema1.tbl_Sample_Table VALUES ('XYZ');
GO
EXEC Schema2.sp_Test 'XYZ';
GO
UPDATE Schema1.tbl_Sample_Table 
SET Sample_Data = '123' 
WHERE Sample_Data = 'XYZ';
GO
SELECT * FROM Schema2.vw_Test;
GO
DELETE FROM Schema1.tbl_Sample_Table
WHERE Sample_Data = '123';
GO
SELECT Schema2.fn_Test() as Function_Result;
GO
REVERT ;  
GO
It should return no errors:
If we try to use User1 to create an object like this:
/* test Permission failures for User1*/
EXECUTE AS USER = 'User1' ;  
GO  
CREATE PROCEDURE Schema2.sp_Failed_Test as SELECT 1 as ABC;
GO
REVERT ;  
GO
Will get an error:
Msg 262, Level 14, State 18, Procedure sp_Failed_Test, Line 1 [Batch Start Line 130]
CREATE PROCEDURE permission denied in database 'TestPermissionDB'.

Do not forget to drop test database at the end:
/* Drop Test database */
use master;
GO
DROP DATABASE TestPermissionDB;
GO

Tuesday, August 8, 2017

Drawing SQL Sphere

<= Drawing 3D Cube

Couple of years ago I came up with an algorithm of drawing an ellipse using SQL Server spatial geometry: http://slavasql.blogspot.com/2015/02/drawing-ellipse-in-ssms.html

I've used that algorithm to make a sphere and as in my previous blog of drawing 3D Cube I use external procedure to simplify the process.
This time instead of temporary stored procedure I'm using a function to generate Geometrical content.
Here is the function's code:
SET NOCOUNT ON
GO
USE tempdb;
GO
IF OBJECT_ID('tempdb.dbo.Elipse') IS NOT NULL
DROP FUNCTION dbo.Elipse
GO
CREATE FUNCTION dbo.Elipse(
@x FLOAT = 0,   -- x coordinate of center
@y FLOAT = 0,   -- y coordinate of center
@rh FLOAT = 1, -- Horizontal radius
@rv FLOAT = 0, -- Vertical Radius
@as FLOAT = 0, -- Starting angle
@af FLOAT = 0 -- Finishing angle
) RETURNS GEOMETRY AS
BEGIN
SELECT @af = CASE @af WHEN 0 THEN 2*Pi() ELSE @af END
 , @rv = CASE @rv WHEN 0 THEN @rh ELSE @rv END

DECLARE @MP VARCHAR(MAX)='';
-- angle that will be increased each loop
DECLARE @theta FLOAT = @as;
-- amount to add to theta each time (degrees)
DECLARE @step FLOAT = 2 * PI()/1000;  
DECLARE @x1 FLOAT, @y1 FLOAT
, @x2 FLOAT = @x + @rh * SIN(@theta)
, @y2 FLOAT = @y + @rv * COS(@theta);

WHILE @theta <= @af
SELECT 
    @x1 = @x2, @y1 = @y2,
    @x2 = @x + @rh * SIN(@theta), 
    @y2 = @y + @rv * COS(@theta), 
    @theta += @step,
 @MP = @MP + '(' 
        + CAST(@x1 as VARCHAR) +  ' ' 
        + CAST(@y1 as VARCHAR) + ',' 
        + CAST(@x2 as VARCHAR) +  ' ' 
        + CAST(@y2 as VARCHAR) + '),';

RETURN (CONVERT(GEOMETRY,'MULTILINESTRING(' 
        + LEFT(@MP,LEN(@MP)-1) + ')')).MakeValid();
END
GO

The easiest way to use that function is to run a SELECT statement of it with some parameters:
SELECT tempdb.dbo.Elipse (10, 10, 1, .5, default, default).STBuffer(0.002);
If you decide to not specify the fourth parameter (@rv - Vertical Radius) you'll get a perfect circle!
SELECT tempdb.dbo.Elipse (0,0,1,default,default,default).STBuffer(0.005);
If you will specify starting and finishing angles you can get a part of an ellipse like this:
SELECT tempdb.dbo.Elipse (10,10,1,0.5,Pi()/4,5*Pi()/4).STBuffer(0.01);
And finally, if you combine several ellipses into one picture you can get a sphere:
/*
Make sphere with Axial tilt 23.437%
*/
DECLARE @d FLOAT = RADIANS(23.437), /*Axial tilt*/
@LR FLOAT = Pi()/4, /* Rotation LEft-Right*/
@x FLOAT = 0, /*Coordinate X*/
@y FLOAT = 0, /*Coordinate Y*/
@r FLOAT = 1, /*Radius*/
@w FLOAT = 0.001  /*Line width*/

DECLARE @g TABLE (g GEOMETRY);

-- Main circle
INSERT INTO @g 
SELECT tempdb.dbo.Elipse (@x, @y, @r, default, default, default )

-- North Pole
INSERT INTO @g SELECT CONVERT(GEOMETRY,'POINT('
       +CAST(@x as VARCHAR)+' '+CAST(@y + @r * COS(@d) as VARCHAR)
       +')').STBuffer(@w)

-- Draw Equator:
INSERT INTO @g 
SELECT tempdb.dbo.Elipse (@x, @y, @r, @r * SIN(@d), Pi()/2, 3*Pi()/2)

-- Draw 30 degrees north:
INSERT INTO @g 
SELECT tempdb.dbo.Elipse (
 @x, @y + @r * COS(Pi()/6)*COS(@d), 
 @r*SIN(Pi()/6), 
 @r * COS(Pi()/6)*COS(@d) - @r * COS(Pi()/6+@d),
 ACOS( TAN(@d) / TAN(Pi()/6) )
 , 2*Pi()-ACOS( TAN(@d) / TAN(Pi()/6)  ))

-- Draw 60 degrees north:
INSERT INTO @g 
SELECT tempdb.dbo.Elipse (
 @x, @y + @r * COS(Pi()/3)*COS(@d), 
 @r*SIN(Pi()/3), 
 @r * COS(Pi()/3)*COS(@d) - @r * COS(Pi()/3+@d),
 ACOS( TAN(@d) / TAN(Pi()/3) )
 , 2*Pi()-ACOS( TAN(@d) / TAN(Pi()/3) ) )

-- Draw 60 degrees South:
INSERT INTO @g 
SELECT tempdb.dbo.Elipse (
 @x, @y - @r * COS(Pi()/3)*COS(@d), 
 @r*SIN(Pi()/3), 
 @r * COS(Pi()/3)*COS(@d) - @r * COS(Pi()/3+@d),
 Pi()-ACOS( TAN(@d) / TAN(Pi()/3) )
 , Pi()+ACOS( TAN(@d) / TAN(Pi()/3) ) )

-- Draw 30 degrees south:
INSERT INTO @g 
SELECT tempdb.dbo.Elipse (
 @x, @y - @r * COS(Pi()/6)*COS(@d), 
 @r*SIN(Pi()/6), 
 @r * COS(Pi()/6)*COS(@d) - @r * COS(Pi()/6+@d),
 Pi()-ACOS( TAN(@d) / TAN(Pi()/6) )
 , Pi()+ACOS( TAN(@d) / TAN(Pi()/6)  ))

SELECT g.STBuffer(0.005) FROM @g
GO

That sphere is supposed to represent the Earth looking at the Sun at Summer Solstice.