locked
Applicationrole questions. RRS feed

  • Question

  • So I'm considering using an application role, never had any experience woth this. I've been reading about it and my original idea on how to use it does not seem to work. Also I wonder how save this is.
    This is my situation: students following a course need to create a database based on a some master database(not the systemDB). So I restore a backup created for this situation and restore it with a different name. Not a problem. We dont wish the students to have rights which they need to restore a database, so I created a SP and thought to have this SP execute the restore operation with an application role. I thought I could execute the restore from this SP under an application role, but it seems I'm mistaken: an application role is used to connect to the server/database and cannot be used to change a connection from within a SP. This is correct?

    I have an alternative. This restore operation is started from an application, which connects to the SQL Server, so I could use the application role from this application to connect and execute the restore action. But I am confused here: if I understand correctly you have to send the login AND password(!) to use the application role as your connection with the database. This means the developer(s) will know about this login and could use it for other connections.
    If my interpretation is correct, securitywise this would be unacceptable. Do I miss something here?

    EDIT: I've looked at the EXECUTE AS option and this is an interesting alternative for an applicationrole. However, my question remains: securitywise speaking, what are the implications? If I create an SP like WITH EXECUTE AS 'sa', what is to prevent anybody to execute it? I see there are difficulties to overcome, but still.


    Greetz, FreeHansje
    Tuesday, July 7, 2009 8:45 AM

Answers

  • If the backed up database isn't also owned by 'sa', you also need to mark the database containing the stored procedure as TRUSTWORTHY so that the impersonated security context is honored outside the database:
     
    ALTER DATABASE MyDatabase SET TRUSTWORTHY ON;

    --
    Hope this helps.
     
    Dan Guzman
    SQL Server MVP
    http://weblogs.sqlteam.com/dang/
    • Marked as answer by FreeHansje Thursday, July 9, 2009 9:38 AM
    Wednesday, July 8, 2009 12:14 PM

All replies

  • It is safe to use EXECUTE AS if you grant permissions appropriately.  I suggest you create a database role for this purpose and grant procedure execute permissions to that role. Only students that are role members will be able to execute the restore database procedure.

    Be aware that only database principals can be specified as the stored procedure security context. In order to run as 'sa', you'll need to create the procedure in an sa-owned database and specify either EXECUTE AS 'dbo' or EXECUTE AS OWNER in a dbo-owned procedure.

     

    --
    Hope this helps.
     
    Dan Guzman
    SQL Server MVP
    http://weblogs.sqlteam.com/dang/
    Tuesday, July 7, 2009 2:58 PM
  • Hi Dan,
    I tested your 2nd suggestion and created a DB with owner SA under my login with SA-permissions. I created an SP with the option EXECUTE AS OWNER, where I executed a CREATE DATABASE action. I reverted to a less priviliged account and tried to execute this procedure, but no succes. I receive this message:

    CREATE DATABASE permission denied in database 'master'.


    The strange thing is, if I create this SP in the MASTER DB and do the same actions I can create a new database as a user with no SA or DBCreator permission, just EXECUTE permission on this SP. I have to USE the Master here. I would prefer not to use the master. If I have understood it correctly, the WITH EXECUTE AS option is there for just that: giving temp permissions to logins, which do not have the needed rights themselves. Or I miss something else; any help or insight is appreciated.
    Greetz, FreeHansje
    Wednesday, July 8, 2009 10:22 AM
  • If the backed up database isn't also owned by 'sa', you also need to mark the database containing the stored procedure as TRUSTWORTHY so that the impersonated security context is honored outside the database:
     
    ALTER DATABASE MyDatabase SET TRUSTWORTHY ON;

    --
    Hope this helps.
     
    Dan Guzman
    SQL Server MVP
    http://weblogs.sqlteam.com/dang/
    • Marked as answer by FreeHansje Thursday, July 9, 2009 9:38 AM
    Wednesday, July 8, 2009 12:14 PM
  • Tnx Dan, this works for me.
    I was figuring things out based on this article: http://blogs.msdn.com/raulga/archive/2006/07/03/655587.aspx
    Came to the conclusion that I really need an existing user, since the REAL users using the SP do not have rights to create a temp-user.

    Tnx again.
    Greetz, FreeHansje
    Thursday, July 9, 2009 9:38 AM
  • Although my suggestion to use TRUSTWORTHY worked, I think the digital certificate approach in Raul's blog post is a better solution.  Below is an example script you can customize for your needs.

    USE MyUserDatabase
    GO

    CREATE CERTIFICATE RestoreDatabaseCert
        ENCRYPTION BY PASSWORD = 'My c3r+ p@zzw0Rd'
        WITH SUBJECT = 'Certificate used to restore database',
        START_DATE = '20090101', EXPIRY_DATE = '21000101'
    GO

    CREATE PROC dbo.RestoreTemplateDatabase
    AS
    RESTORE DATABASE MyTemplateDatabase
    FROM DISK='C:\Backups\MyTemplateDatabase.bak'
    WITH REPLACE, STATS=1
    GO

    ADD SIGNATURE TO dbo.RestoreTemplateDatabase
     BY CERTIFICATE RestoreDatabaseCert
        WITH PASSWORD = 'My c3r+ p@zzw0Rd'
    GO
     
    GRANT EXEC ON dbo.RestoreTemplateDatabase TO StudentsRole
    GO

    BACKUP CERTIFICATE RestoreDatabaseCert
     TO FILE = 'c:\Certs\RestoreDatabaseCert.cer'
     WITH PRIVATE KEY( FILE = 'c:\Certs\RestoreDatabaseCert.pvk',
      ENCRYPTION BY PASSWORD = 'My c3r+ p@zzw0Rd',
      DECRYPTION BY PASSWORD = 'My c3r+ p@zzw0Rd')
    GO

    ALTER CERTIFICATE RestoreDatabaseCert REMOVE PRIVATE KEY
    GO

    USE master
    GO
     
    CREATE CERTIFICATE RestoreDatabaseCert
    FROM FILE = 'c:\Certs\RestoreDatabaseCert.cer'
    GO

    CREATE LOGIN RestoreDatabaseLogin
    FROM CERTIFICATE RestoreDatabaseCert
    GO

    EXEC sp_addsrvrolemember 'RestoreDatabaseLogin','dbcreator'
    GO

    --
    Hope this helps.

     
    Dan Guzman
    SQL Server MVP
    http://weblogs.sqlteam.com/dang/
    Thursday, July 9, 2009 3:10 PM