locked
SQL SERVER SECURITY RRS feed

  • Question

  • DEVELOPED THE BELOW SCRIPT 

    FACING ISSUES FOR ADJUSTMENTAMOUNTS_IN

    USE [master];

    CREATE LOGIN [RestrictedUser] WITH PASSWORD = 'No way? Yes way!';
    GO
    CREATE DATABASE [DatabaseB]
    GO
    CREATE DATABASE [DatabaseA]
    GO
    USE [DatabaseA];
    GO
    CREATE USER [RestrictedUser] FOR LOGIN [RestrictedUser];

    GO
    CREATE FUNCTION dbo.DataFromOtherDB()
    RETURNS @Results TABLE ([SomeValue] INT)
    AS
    BEGIN
        INSERT INTO @Results ([SomeValue])
            SELECT [SomeValue]
            FROM   DatabaseB.dbo.LotsOfValues;

        RETURN;
    END;
    GO

    CREATE TABLE [dbo].[SecurityMapping](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [DATAAREAID] [nvarchar](4) NOT NULL,
    [AccesibleDBRole] [nvarchar](128) NULL,
    [IsActive] [bit] NULL
    ) ON [PRIMARY]

    GO

    CREATE FUNCTION [dbo].[FNR_ADJUSTMENTAMOUNTS_IN] () RETURNS @ADJUSTMENTAMOUNTS_IN TABLE
     
    (
          [ADJUSTMENTAMOUNT] [NUMERIC]
        , [TAXCOMPONENTTABLE] [BIGINT]
        , [TAXREGISTRATIONNUMBERTABLE] [BIGINT]
        , [DATAAREAID] [NVARCHAR](4)
        , [RECVERSION] [INT]
        , [PARTITION] [BIGINT]
        , [RECID] [BIGINT]

    ) AS BEGIN

    INSERT INTO @ADJUSTMENTAMOUNTS_IN 
    SELECT 
    UD.ADJUSTMENTAMOUNT, 
    UD.TAXCOMPONENTTABLE, 
    UD.TAXREGISTRATIONNUMBERTABLE, 
    UD.DATAAREAID,
    UD.RECVERSION, 
    UD.PARTITION, UD.RECID 
    FROM [MicrosoftDynamicsAX].dbo.[ADJUSTMENTAMOUNTS_IN] UD
    JOIN dbo.SecurityMapping  SEC ON SEC.DATAAREAID= UD.DATAAREAID 
      AND   Is_Member( SEC.AccesibleDBRole)= 1  
     
    RETURN; END 

    GO

    GRANT SELECT ON [dbo].[FNR_ADJUSTMENTAMOUNTS_IN] TO [RestrictedUser];
    GRANT SELECT ON  dbo.DataFromOtherDB TO [RestrictedUser];
    GO
    ---

    USE [DatabaseB];
    go
    CREATE TABLE dbo.[LotsOfValues]
    (
        [LotsOfValuesID] INT IDENTITY(1, 1) NOT NULL
            CONSTRAINT [PK_LotsOfValues] PRIMARY KEY,
        [SomeValue] INT
    );

    INSERT INTO dbo.[LotsOfValues] VALUES
        (1), (10), (100), (1000);
    GO

    ---

    CREATE TABLE [dbo].[ADJUSTMENTAMOUNTS_IN](
    [ADJUSTMENTAMOUNT] [numeric](32, 16) NOT NULL DEFAULT ((0)),
    [TAXCOMPONENTTABLE] [bigint] NOT NULL DEFAULT ((0)),
    [TAXREGISTRATIONNUMBERTABLE] [bigint] NOT NULL DEFAULT ((0)),
    [DATAAREAID] [nvarchar](4) NOT NULL DEFAULT ('dat'),
    [RECVERSION] [int] NOT NULL DEFAULT ((1)),
    [PARTITION] [bigint] NOT NULL DEFAULT ((5637144576.)),
    [RECID] [bigint] NOT NULL,
     CONSTRAINT [I_8100RECID] PRIMARY KEY CLUSTERED 
    (
    [RECID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[ADJUSTMENTAMOUNTS_IN]  WITH CHECK ADD CHECK  (([RECID]<>(0)))
    GO

    ALTER TABLE [dbo].[ADJUSTMENTAMOUNTS_IN]  WITH CHECK ADD CHECK  (([RECID]<>(0)))
    GO
    USE [DatabaseA];
    SELECT * FROM [dbo].[FNR_ADJUSTMENTAMOUNTS_IN] ()
    SELECT * FROM  dbo.DataFromOtherDB ()
    EXECUTE AS LOGIN = 'RestrictedUser';

    --SELECT * FROM [dbo].[FNR_ADJUSTMENTAMOUNTS_IN] ();
    SELECT * FROM  dbo.DataFromOtherDB ()

    GO
    REVERT;

    CREATE CERTIFICATE [AccessOtherDB]
        ENCRYPTION BY PASSWORD = 'SomePassword'
        WITH SUBJECT = 'Used for accessing other DB',
        EXPIRY_DATE = '2099-12-31';

    ADD SIGNATURE TO [dbo].[FNR_ADJUSTMENTAMOUNTS_IN] BY CERTIFICATE [AccessOtherDB] WITH PASSWORD = 'SomePassword';
    ADD SIGNATURE TO dbo.DataFromOtherDB BY CERTIFICATE [AccessOtherDB] WITH PASSWORD = 'SomePassword';

    DECLARE @CertificatePublicKey NVARCHAR(MAX) =CONVERT(NVARCHAR(MAX), CERTENCODED(CERT_ID(N'AccessOtherDB')), 1);
    EXEC (N'USE [DatabaseB];CREATE CERTIFICATE [AccessOtherDB] FROM BINARY = ' + @CertificatePublicKey + N';');
    EXEC (N'USE [DatabaseB];CREATE USER [AccessOtherDbUser] FROM CERTIFICATE [AccessOtherDB];
    GRANT SELECT ON [dbo].[ADJUSTMENTAMOUNTS_IN] TO [AccessOtherDbUser];
    GRANT SELECT ON [dbo].[LotsOfValues] TO [AccessOtherDbUser]
    ');


    EXECUTE AS LOGIN = 'RestrictedUser';
    select * from dbo.DataFromOtherDB()
    -- Success!!
    EXECUTE AS LOGIN = 'RestrictedUser';
    SELECT * FROM dbo.[FNR_ADJUSTMENTAMOUNTS_IN]();
    ---- FAILED!

    Thursday, July 26, 2018 1:56 PM

All replies

  • What issue are you seeing? I ran you script, and after having changed the database name in FNR_ADJUSTMENTAMOUNTS_IN to DatabaseB, it ran successfully. I got one error messsage, but that was at the point where you ran the function before you had created the certificates and that.

    Thursday, July 26, 2018 9:24 PM
  • Thank you very much ...
    Friday, July 27, 2018 1:25 AM