1

I have two server logins, OMEGACA and TEST, and an ALL SERVER for LOGON trigger:

CREATE TRIGGER [OMEGACA_ACC]
ON ALL SERVER WITH EXECUTE AS 'OMEGACA'
FOR LOGON
AS
    -- ...............

OMEGACA has server roles public and sysadmin. It is also owner of database OmegaCoreAudit. In this database, I have the OMEGACA schema and the following procedure to disable/enable the OMEGACA_ACC trigger:

USE [OmegaCoreAudit]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [OMEGACA].[P_SYS_MANAGE_ACC] 
    (@p_trigger_status int)
AS
BEGIN
    SET NOCOUNT ON;    

    -- Check Trigger Status
    IF @p_trigger_status = 0
    BEGIN
        DISABLE TRIGGER OMEGACA_ACC ON ALL SERVER;
    END
    ELSE IF @p_trigger_status = 1
    BEGIN
        ENABLE TRIGGER OMEGACA_ACC ON ALL SERVER;
    END
    ELSE
    BEGIN
        RAISERROR('Wrong status 1/0 value !', 16, 1);
    END;
END;

I want user TEST to be able to execute this procedure to enable/disable the server trigger.

I do NOT want to grant this user TEST the "CONTROL SERVER" directly!

So, I:

  1. Grant CONTROL SERVER to OMEGACA login with WITH GRANT option
  2. Map TEST login to OmegaCoreAudit TEST user
  3. Grant EXECUTE ON [OMEGACA].[P_SYS_MANAGE_ACC] to TEST user

Now, when as TEST login I do call:

USE [OmegaCoreAudit]
GO

DECLARE @RC int
-- TODO: Set parameter values here.

EXECUTE @RC = [OMEGACA].[P_SYS_MANAGE_ACC] 0
GO

I get this error:

Msg 1088, Level 16, State 120, Procedure OMEGACA.P_SYS_MANAGE_ACC, Line 21 [Batch Start Line 2]
Cannot find the object "OMEGACA_ACC" because it does not exist or you do not have permissions.

Question

How can I have TEST login execute this procedure correctly?

Improve this question
6
  • "I do NOT want to grant this user (TEST) the "CONTROL SERVER" directly !" Those are the permissions they need. There's no ownership chain here. Commented May 10 at 17:23
  • Thank You @ThomA. But the CONTROL SERVER is too big, and I would not like to grant it directly - but indirectly. This is precisely why I asked this question. Commented May 10 at 17:26
  • 3
    I've not tried it, but you might be able to sign the procedure with a certificate that has a login with the control server permission. Commented May 10 at 17:35
  • Side note; you really should be using THROW nowadays. Commented May 10 at 17:46
  • 1
    I agree with @ThomA that you should look at signing the proc for server-level permissions. See this article for examples and more info. Commented May 10 at 20:43

1 Answer 1

Reset to default
3

You're misunderstanding what WITH GRANT OPTION does. That's just to be able to give GRANT to other users/logins. It doesn't allow you to execute on behalf of a user without granting them permission.

Moreover, the procedure doesn't even have EXECUTE AS OWNER, which is necessary to make the procedure run under the OMEGACA user, who owns the database. Once you add that,

CREATE OR ALTER PROCEDURE [OMEGACA].[P_SYS_MANAGE_ACC] 
    (@p_trigger_status int)
WITH EXECUTE AS OWNER
AS

you are still locked into the current database.

There are three main ways to break out of database-level impersonation to the instance-level.

  • TRUSTWORTHY
    This can be highly insecure, if the database or its users are fundamentally untrusted.
  • DB ownership chaining. This can now be selected per database.
    Similar caveats apply.
  • Module signing.
    This is the most secure, but can be a bit of a pain to set up.

I recommend module signing. A full tutorial is here, but the main points are:

  • Create a certificate in master encrypted with a password.
  • Sign the procedure using the certificate.
  • Create a login from the certificate (there is no password for this login, you can't use it outside of impersonation contexts).
  • Assign permissions to that login (CONTROL SERVER here).
  • WITH EXECUTE AS is not necessary once the module is signed.
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Thnx very much @Charli . For "It doesn't allow you to execute on behalf of a user without granting them permission." - I am very sad about this, it was my original idea. I do that in Oracle DB and was expecting the same in MS SQL. While for "WITH EXECUTE AS OWNER" - is that a fourth alternative (You have proposed three of them)? best regards, Altin
WITH EXECUTE AS OWNER only works within the same database, so doesn't work with something that needs server-level permissions. You could for example do anything else database-level though, such as create/drop objects, change permissions etc. So it's not an option at all in this instance. You can be sad all you like, all that WITH GRANT OPTION does is allow a user/role to give the GRANT to another user/role, but if you don't grant those permissions (because you don't actually trust the other user) then you need impersonation instead. I believe it works the same in Oracle.
Thank You very much @Charlieface. I have tried another option, which seems to be working: Grant CONTROL SERVER to TEST login. At this point will work. To workaround elevated privileges of "control server" I DENY INS, DEL UPD (will be looking for more) to TEST user in OmegaCoreAudit DB.
That's extremely risky. With CONTROL SERVER they can pretty much do anything, they are basically sysadmin so they could probably find a way to remove the DENY. Like just impersonate sa. Or they could also for example just drop your whole database, shutdown the instance, backup the database to some location they control, install SQLCLR to mess around with raw instance memory, create a loopback connection using a linked server under the sysadmin permissions.
Ultimately you want this user to change the server, and putting an object in master to do so seems like the least of your worries. DENY is going to be difficult, there are going to be so many ways round it. Just do the right thing. Alternatively reconsider why you need a login trigger in the first place, or why a non-sysadmin should be enabling or disabling it, which seems to be the main issue.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.