• Giving Permissions through Stored Procedures

  • 저장 프로시저를 통한 권한 부여

  • Giving Permissions through Stored Procedures

  • 저장 프로시저를 통한 권한 부여

  • Ownership Chaining, Certificates and the Problematic EXECUTE AS
  • 소유권 체인, 인증서와 문제 많은 EXECUTE AS
  • When designing an application for SQL Server, you rarely want users to have full permissions to access the tables in the database. Many applications are designed to perform all database access through stored procedures, and it is through the stored procedures users can access and update data. The procedures perform validations of business rules to protect the integrity of the database.
  • SQL Server를 사용한 응용 프로그램을 디자인할 때, 사용자에게 테이블 접근을 위한 모든 권한을 부여하고 싶지는 않을 것이다. 많은 프로그램이 모든 데이터베이스 접근을 저장 프로시저를 통해서 하도록 설계되어 있고, 사용자는 저장 프로시저를 통해서 데이터에 접근하고 데이터를 갱신한다. 프로시저는 업무 규칙을 검증하여 데이터베이스의 무결성을 지킨다.
  • In this article I will in depth discuss three different ways to achieve this:
  • 이 글에서는 그것을 위한 서로 다른 세 가지 방법에 대해 깊게 다뤄보려 한다.
  • I will also briefly cover three other ways:
  • 또한 세가지 다른 방법도 간략히 언급한다.
  • This article applies to SQL 2005 SP2 and later. Particularly, there were some bugs and limitations in the RTM version of SQL 2005, that I don't touch, as there is no reason why you should be running SQL 2005 RTM or SP1. (And for that matter, SP2. You should have installed at least Service Pack 3 by now, if not SP4.) If you are using SQL 2000, you should know that this article focuses on features added in SQL 2005.
  • Table of Contents

  • The classic method for granting permissions through stored procedures is ownership chaining. This is the prime method for plain table access, but there are permissions that are not grantable through ownership chaining. Two such cases that we will look at in this article are dynamic SQL and reloading a table through BULK INSERT. Due to its importance, ownership chaining is the first mechanism that I will cover in this article. However, before that I will discuss owner/schema-separation, a change in SQL 2005 that may boggle the mind of old-time users of SQL Server and which has some effects on ownership chaining.
  • 저장 프로시저를 통해 권한을 부여하는 고전적인 방법은 소유권 체인이다. 이것은 일반 테이블 접근에 가장 주요한 방법이지만 소유권 체인으로는 허가할 수 없는 권한도 있다. 여기서 알아볼 두 가지 경우는 동적 SQL과 BULK INSERT를 사용한 테이블 재적재이다. 소유권 체인은 매우 중요하므로 이 글에서 제일 먼저 다룰 것이다. 그러나 그 전에, SQL Server 구버전을 써왔던 사람들을 깜짝 놀라게 할 SQL 2005의 변경점인 소유자와 스키마 분리에 대해 논의하고자 한다. 이는 소유권 체인에도 일부 영향을 주었다.
  • SQL 2005 introduced two new methods to give users access through stored procedures: you can sign procedures with certificates, and you can use impersonation with the EXECUTE AS clause. Both these methods permit you to encapsulate any permission in a stored procedure. Certificates are more complex to use, whereas EXECUTE AS can be deceivingly simple. To wit,EXECUTE AS has some side effects that can be nasty. If you are a developer, this text tries to make you aware of what harm casual use of EXECUTE AS could cause. And if you are a DBA, this article warns you of what creative developers can inflict to your database with EXECUTE AS.
  • SQL 2005는 저장 프로시저를 통해 사용자에게 접근을 허가하는 두 가지 새로운 방법을 제공한다: 인증서로 프로시저에 서명할 수 있고, EXECUTE AS 절을 사용하여 가장(impersonation)을 사용할 수 있다. 두 방법 모두 저장 프로시저에 어떠한 권한이라도 캡슐화할 수 있게 한다. 인증서는 좀 더 쓰기 복잡하지만 EXECUTE AS는 거짓말처럼 간단하다. 더하자면 EXECUTE AS는 꽤 문제가 될 수 있는 부작용도 일부 가지고 있다. 독자가 개발자라면 이 글은 EXECUTE AS를 가볍게 사용했을 때 벌어질 수 있는 해악을 이해시키고자 한다. 또는 DBA라면, 이 글은 창의적인 개발자가 EXECUTE AS를 써서 당신이 관리하는 DB에 저지를 수 있는 것에 대해 경고하고자 한다.
  • Whereas the above-mentioned methods can be applied to individual procedures, application roles, "application proxies" andTerminal Server are solutions that you typically use on an application-wide scale. (I have put "application proxy" in quotes throughout the article, as this is a term that I've coined myself and it may not be established terminology.)
  • This article includes several example scripts that demonstrate the various methods. Before you start to run these scripts all over town, I like to point out a few things.
  • All these scripts assume that you are logged in with sysadmin rights, and I strongly recommend that you run the examples on a development machine. Some scripts assume that you have enabled xp_cmdshell, which is disabled by default. Enable it withsp_configure, if this is acceptable with your local security policy. The use of xp_cmdshell is mainly for convenience, and it is not required to demonstrate the essentials of the examples. You can perform those actions manually if needed.
  • Furthermore, all scripts create at least one database and at least one login. Some scripts also create files in the file system. If the scripts run uninterrupted, all objects are dropped at the end; logins, databases and files alike. (So first check that you don't have any database with names that coincide with the databases in the scripts!)
  • The reason the scripts create databases is simplicity. That permits me to create objects, users etc in the database, and clean up all by dropping the database. The scripts create logins because it's difficult to demonstrate security features when running assysadmin.
  • To contain everything into one script, I make heavily use of the EXECUTE AS and REVERT statements, although it will take until the second half of the article before I discuss them in detail. For now, just think of them as an alternative to open a second query window to run as a test user. If you prefer, you can stop the scripts at EXECUTE AS, log into a second query window as the test user to run the part up to REVERT.
  • Before I go on to the main body of this text, I would like to make a short digression about security in general.
  • Security is often in conflict with other interests in the programming trade. You have users screaming for a solution, and they want it now. At this point, they don't really care about security, they just want to get their business done. But if you give them a solution that has a hole, and that hole is later exploited, you are the one that will be hung. So as a programmer you always need to have security in mind, and make sure that you play your part right
  • 보안은 프로그래밍이라는 거래의 다른 흥미로운 것들과 자주 충돌한다. 사용자들이 당장 해결책을 찾아내라고 소리친다. 이 때 그들은 보안에는 별 신경을 쓰지 않는다. 그냥 당장 업무가 처리되기를 바랄 뿐이다. 그러나 사용자에게 구멍이 있는 해결책을 제공하고 그 보안 구멍이 악용된다면 책임은 당신이 지게 될 것이다. 따라서 프로그래머로서 당신은 항상 보안을 염두에 두어야 하고, 스스로의 역할을 다하도록 해야 한다.
  • One common mistake in security is to think "we have this firewall/encryption/whatever, so we are safe". I like to think of security of something that consists of a number of defence lines. Anyone who has worked with computer systems knows that there are a lot of changes in them, both in their configuration and in the program code. Your initial design may be sound and safe, but as the system evolves, there might suddenly be a security hole and a serious vulnerability in your system.
  • 보안에서 자주하는 실수는 "우리는 이런 방화벽을 쓰니까/암호화를 하니까/뭐뭐가 있으니까 안전하다"고 생각하는 것이다. 나는 보안이란 여러 개의 방어선으로 구성된 무언가라고 생각한다. 컴퓨터 시스템 쪽 일을 해본 사람들이라면 구성이나 프로그램 코드에 매우 자주 변경이 일어난다는 것을 알고 있을 것이다. 당신이 만든 최초의 설계는 아마도 견고하고 안전할 수 있겠지만, 시스템이 발전할수록, 보안 구멍이나 심각한 취약점이 생길 수 있다.
  • By having multiple lines of defence you can reduce the risk for this to happen. If a hole is opened, you can reduce the impact of what is possible to do through that hole. An integral part of this strategy is to never grant more permissions than is absolutely necessary. Exactly what this means in this context is something I shall return to.
  • 여러 개의 방어선을 둠으로써 이러한 위험의 발생을 줄일 수 있다. 구멍이 뚫렸다면, 그 보안 구멍을 악용했을 때 시스템에 끼칠 영향도를 줄일 수 있다. 이러한 전략에서 빼놓을 수 없는 것은 "반드시 필요한 이상의 권한을 부여하지 않는 것"이다. 여기서 내가 하고 싶이 딱 그것이다.
  • note icon
    Exactly what this means in this context is something I shall return to. ->?
  • Before we look at any of the methods to grant permissions, we need to look at a change in SQL 2005 which can be a bit breath-taking to users coming from older versions of SQL Server.
  • 권한을 부여하는 방법에 대해 살펴보기 전에, 먼저 구버전을 쓰던 사용자들이라면 숨이 턱 막힐지도 모를 SQL 2005의 변경점에 대해 알아보자.
  • Since the dawn of time, SQL Server have permitted a four-part notation of objects, and it has usually been presented as
  • 처음부터 SQL Server는 보통 "서버.데이터베이스.소유자.개체"로 나타낼 수 있는 개체의 4 파트 표기법을(four-part notation of objects) 허용했다.
  • server.database.owner.object
  • But in SQL 2005 this changed to
  • 그러나 SQL 2005 에서는 "서버.데이터베이스.스키마.개체"로 변경되었다.
  • server.database.schema.object
  • You may ask, what is this schema? The answer is that schema has always been there, but up to SQL 2000, schema and owner was always the same. In SQL 2005 owner and schema are two different entities.
  • 아마도 "스키마란 무엇인가?" 라고 물어보고 싶을 것이다. 스키마는 항상 거기 있었다. 그러나 SQL 2000 까지는 스키마와 소유자가 항상 같았다. SQL 2005 에서 소유자와 스키마는 두 개의 서로 다른 엔터티이다.
  • The purpose of a schema is simple to understand: it permits you to have different namespaces in database. Say that for a larger application, there are several groups that work more or less independently. Each group could have their own schema for their specific objects to avoid name clashes. While you could do this in SQL 2000 as well, the fact that all schemas had different owners, made this unpractical. In SQL 2005 all schemas can have the same owner.
  • 스키마의 목적은 간단하다. 스키마는 데이터베이스에 서로 다른 네임스페이스(namespace)를 둘 수 있게 한다. 대규모 응용 프로그램에는 좀 더 또는 덜 독립적으로 동작하는 여러 그룹이 있다. 각 그룹은 이름 충돌을 피하기 위해 특정 개체를 위한 스스로의 스키마를 소유할 수 있다. SQL 2000 에서도 가능하긴 했는데, 실제로 모든 스키마는 서로 다른 소유자에 속해 있어서 쓸모가 없었다. SQL 2005 에서는 하나의 소유자가 모든 스키마를 소유할 수 있다.
  • An example of a database with several schemas is the AdventureWorks database; the database which Microsoft use for all their samples since SQL 2005.
  • 여러 스키마를 쓰는 데이터베이스의 예제로 Microsoft가 SQL 2005부터 예제 데이터베이스로 사용하는 AdventureWorks를 들 수 있다.
  • SQL Server comes with no less than 13 pre-defined schemas. That's a lot, but ten of them exist solely for backwards compatibility, and they are namesakes with predefined users and roles in SQL 2000. (Since users and roles also were schemas inSQL 2000, Microsoft figured that there could be applications using them.) You can drop the nine schemas that stem from roles (db_owner etc) from your database, and if you drop them from the model database, the schemas will not appear in new databases. For some reason you cannot drop the guest schema.
  • SQL Server는 자그-_-마치 13개의 미리 정의된 스키마가 딸려온다. 많아보이지만, 그 중 10개는 단지 하위 호환성을 위해 존재하며, SQL 2000에 있었던 것과 같은 사용자와 역할이다. - SQL 2000에서도 사용자와 역할은 스키마였으므로, MS는 이것을 사용하는 응용 프로그램들이 있었을 것이라고 생각한 것 같다 - db_owner 등과 같이 역할에서 파생된 9개의 스키마는 삭제해도 좋다. model 데이터베이스에서 삭제하면 이후에 새 데이터베이스에서도 생성되지 않을 것이다. 어째서인지 guest 스키마는 삭제할 수 없다.
  • Two schemas, sys and INFORMATION_SCHEMA, are reserved for system objects, and you cannot create objects in these schemas.
  • 두 스키마, sys와 INFORMATION_SCHEMA는 시스템 개체를 위해 예약된 것이다. 해당 스키마 안에 개체를 생성할 수 없다.
  • Finally, there is the dbo schema, which is the only predefined schema you normally create objects in. The tacky name is short fordatabase owner, and is a heritage from the previous days of owner/schema-unification.
  • 마지막으로 dbo 스키마가 있다. dbo 스키마는 보통 그 안에 개체를 만들어 넣는 스키마 중에서 유일하게 미리 정의된 스키마이다. 이 볼품없는 이름은 '데이터베이스 소유자'의 약자인데, 이것은 이전에 소유자와 스키마가 통일되어 있었을 때의 유산이라 하겠다.
  • There are several statements related to schemas and users, and I will give a brief overview here to point out the differences between the new commands added in SQL 2005, and the older system procedures from previous versions.
  • 스키마와 사용자에 관한 명령이 몇 개 있는데, 여기서 간략히 SQL 2005에 추가된 명령어와 이전 버전에서 사용하던 프로시저 간의 차이를 짚어보고자 한다.
  • To create a schema, you use not surprisingly CREATE SCHEMA, and most often you just say like:
  • 스키마를 생성하려면, 당연히 CREATE SCHEMA 명령을 사용할 것이다. 보통 이렇게 말이다.
  • CREATE SCHEMA myschema
  • CREATE SCHEMA myschema
  • CREATE SCHEMA is one of these statements that must be alone in batch. That is, no statements can precede or follow it. That may seem a little funny for such a simple command, but there is an older form of CREATE SCHEMA which is more complex that was introduced in SQL 6.5 and which serves a different purpose. (Please see Books Online for details, if you really want to know.)
  • CREATE SCHEMA는 명령 배치에서 단독으로 사용되어야 하는 구문 중 하나이다. 즉 그 앞이나 뒤에 어떤 명령이 오면 안된다. 이런 간단한 명령에 그러한 제한을 두는 것이 이상할지 모르겠지만, 이것은 옛날 SQL 6.5에서 CREATE SCHEMA 명령이 다른 형태로 쓰였던 좀 복잡한 우여곡절이 있기 때문이다. (상세한 것은 온라인 설명서 참조)
  • The preferred way to create a user since SQL 2005 is:
  • SQL 2005에서 데이터베이스 사용자를 생성하기 위해 선호되는 방법은:
  • CREATE USER newuser [WITH DEFAULT_SCHEMA = someschema]
  • CREATE USER newuser [WITH DEFAULT_SCHEMA = someschema]
  • There are two system procedures to create users, sp_adduser and sp_grantdbaccess. They are both deprecated and will be removed eventually. There is an important difference between CREATE USER and the two system procedures: CREATE USERcreates a user whose default schema is dbo, unless you specify otherwise. On the other hand, sp_adduser andsp_grantdbaccess for compatibility reasons perform the corresponding to:
  • 데이터베이스 사용자를 생성하는 두 개의 시스템 프로시저 sp_adduser와 sp_grantdbaccess가 있다. 둘 다 낡은 구문이며 언젠가 사라질 것이다. CREATE USER 문과 두 시스템 프로시저 사이에는 중요한 차이점이 있는데, CREATE USER는 별도로 지정하지 않는다면 기본 스키마가 dbo인 사용자를 생성하고, sp_adduser와 sp_grantdbaccess는 기존 버전과의 호환성 때문에 다음 명령을 실행한다:
  • CREATE SCHEMA newuser go CREATE USER newuser WITH DEFAULT_SCHEMA = newuser go ALTER AUTHORIZATION ON SCHEMA::newuser TO newuser
  • CREATE SCHEMA newuser
    go
    CREATE USER newuser WITH DEFAULT_SCHEMA = newuser
    go
    ALTER AUTHORIZATION ON SCHEMA::newuser TO newuser
  • (The last command makes newuser owner of the schema created in his name.) Most likely, you don't need that schema, so there is good reason to avoid these old system procedures entirely. CREATE USER also has some options not offered bysp_adduser and sp_grantdbaccess. For instance, you can say:
  • (마지막 명령은 새로 추가된 사용자를 자신의 이름과 동일한 스키마의 소유자로 한다) 아마도 그러한 스키마를 필요로 하지는 않았을 것이므로, 오래된 시스템 프로시저는 좀 피하는게 좋다. CREATE USER는 sp_adduser와 sp_grantdbaccess에서는 제공되지 않았던 몇 가지 옵션도 가지고 있다.

    예를 들어서
  • CREATE USER thisdbonly WITHOUT LOGIN
  • CREATE USER thisdbonly WITHOUT LOGIN
  • This creates a database user that is not tied to a login. In some of the the test scripts, I use this option to create test users, but you will also see examples where WITHOUT LOGIN can be used to create a user that is a container for a certain permission. We will look at other options later in this article.
  • 이 명령은 서버 로그인에 묶이지 않은 데이터베이스 사용자를 생성한다. 테스트 사용자를 생성하기 위해 스크립트 중 일부에서 이 옵션을 사용하고 있으나, 예제를 통해 WITHOUT LOGIN을 사용하면 특정 권한을 담기 위한 컨테이너 역할을 하는 사용자를 생성할 수 있다는 것을 알게 될 것이다. 이 글의 뒷부분에서 보기로 한다.
  • There is also CREATE ROLE that replaces sp_addrole in the same vein that CREATE USER replaces sp_adduser. That is, CREATE ROLE creates the role only. sp_addrole also creates a schema that you are unlikely to have any need for. And while we are at it, there is a CREATE LOGIN which replaces sp_addlogin. As with CREATE USER, CREATE LOGIN has some new options, that we will come back to later in this article.
  • CREATE USER가 sp_adduser를 대체하듯 sp_addrole을 대체하는 CREATE ROLE 명령도 있다. 즉, CREATE ROLE은 역할만 생성하고 sp_addrole은 아무짝에도 필요없을 스키마도 같이 생성한다는 것이다. 물론 sp_addlogin을 대체하는 CREATE LOGIN 명령도 있다. CREATE USER처럼 CREATE LOGIN에도 새 옵션이 좀 있는데, 역시나 나중에 다룰 것이다.
  • Finally, there is DROP USER instead of sp_dropuser etc. A little note here: if you have users created with sp_addlogin orsp_grantdbaccess, sp_dropuser is the most convenient way to drop them, since there is a schema that needs to be dropped before you can drop the user, and DROP USER will not do that for you.
  • 마지막으로 sp_dropuser를 대체하는 DROP USER도 있다. 참고로 sp_addlogin / sp_grantdbaccess로 사용자를 성성했다면 sp_dropuser로 지우는게 제일 편리할 것이다. DROP USER는 생성된 스키마를 지우지 않지만 이 프로시저는 사용자를 지우기 전에 스키마를 먼저 지운다.
  • If you create objects in a schema that is owned by another user, the schema owner will be the owner of the objects you create,not you. Thus, if you give a user permission to create objects in a schema you own, but no other permissions in the schema, he will not be able to access the objects he creates.
  • 다른 사용자가 소유한 스키마에 개체를 생성하면, 생성한 스키마의 소유주는 당신이 아닌 그 사용자가 될 것이다. 따라서, 다른 사용자에게 당신이 소유한 스키마에 대한 개체 생성 권한을 부여했더라도 스키마에 대한 다른 권한을 부여하지 않으면 그 사용자는 자신이 생성한 개체에 접근할 수 없을 것이다.
  • This can be a bit of a surprise, but it's actually logical. Assume that all developers of an application have their own user, while they create objects in a common schema. For ownership chaining to work (which we look at in a second), all objects must have the same owner, so it much simpler if all objects are owned by the schema owner from the start. Else you would constantly have to change the ownership of the procedures.
  • 이건 좀 의외일 수 있지만 논리적이다. 모든 응용 프로그램 개발자가 각자 사용자를 가지고 있다고 가정하고, 공통 스키마 안에 개체를 생성한다고 해보자. 소유권 체인이 동작하려면 (곧 살펴볼) 모든 개체의 소유자는 같아야 하므로, 처음부터 동일한 스키마 소유자가 모든 개체를 소유한다면 훨씬 간편할 것이다. 아니면 프로시저의 소유권을 지속적으로 변경해야 할 것이다.
  • note icon
    아휴 일단 여기까지
  • Ownership chaining is the classical way of giving users access to objects through stored procedures in SQL Server. And whileSQL Server provides two other methods, ownership chaining is what you will use 99 % of the time. Certificates and impersonation is something you only have reason to use when ownership chaining does not do the job.
  • 소유권 체인은 SQL Server의 저장 프로시저를 통해서 사용자에게 접근 권한을 부여하는 오래된 방법이다. 두가지 다른 방법도 있지만, 소유권 체인이야말로 거의 항시 (99%) 사용하는 방법일 것이다. 인증서와 가장은 소유권 체인이 제 역할을 못할 때나 사용하게 될 것이다.
  • How does ownership chaining work? Say that you have a procedure sp1 owned by user A. sp1 performs a SELECT from tbl1 andtbl2. tbl1 is owned by A, whereas tbl2 is owned by user B. User C has permission to execute sp1. To be able run this procedure successfully, C needs SELECT permission on tbl2 but not on tbl1. Since sp1 and tbl1 have the same owner, the permission check is suppressed, and this is ownership chaining. Ownership chaining is also in effect in triggers, user-defined functions and views.
  • 소유권 체인이 어떻게 동작하는가? 사용자 A가 소유한 프로시저 sp1이 있다고 하자. sp1은 tbl1과 tbl2에서 SELECT를 수행한다. tbl1은 사용자 A가 소유주이지만 tbl2는 사용자 B가 가지고 있다. 사용자 C는 sp1을 실행할 권한을 가지고 있다. 이 프로시저가 제대로 동작하려면 C는 tbl2에 대한 SELECT 권한이 필요하지만 tbl1은 아니다. sp1과 tbl1의 소유자가 같기 때문에 권한 검증을 하지 않기 때문이다. 이것이 소유권 체인이다. 또한 트리거, 사용자 정의 함수(UDF)나 뷰 등에서도 동작한다.
  • Now, this may seem a little complex to grasp, but in real life it is often a lot simpler. In my experience, having several object owners in a database is not very common. In very many cases, dbo, the database owner, owns all objects in a database. A common way to implement security in a database application is to perform all access through stored procedures that validates input parameters, enforces business rules etc. When dbo owns all procedures and tables, users only need permissions to execute the stored procedures. Thanks to ownership chaining, they do not need any direct permissions on the tables. But as we will learn soon, there are permissions that cannot be transferred through ownership chaining.
  • Note: in older versions of SQL Server, applications might have used different object owners in order to implement different namespaces, that is schemas. But since in SQL 2005, dbo can own all schemas, this should no longer be necessary.
  • Here is an example script that demonstrates ownership chaining. Despite what I said in the previous section about dbo owning everything, the example includes two objects owned by other users, to demonstrate what happens when the ownership chain is broken.
  • (Please refer to the introductory note about the example scripts in this article.)
  • USE master go -- Create a test user and a test database. CREATE LOGIN testuser WITH PASSWORD = 'TesT=0=UsEr' CREATE DATABASE ownershiptest go -- Move to the test database. USE ownershiptest go -- Create a user to run the tests. CREATE USER testuser go -- Create two database-only users that will own some objects. CREATE USER procowner WITHOUT LOGIN CREATE USER tableowner WITHOUT LOGIN go -- Create three test tables. As this is an example to demonstrate -- permissions, we don't care about adding any data to them. CREATE TABLE tbl1 (a int NOT NULL) CREATE TABLE tbl2 (b int NOT NULL) CREATE TABLE tbl3 (c int NOT NULL) go -- Make the user tableowner owner of tbl3. ALTER AUTHORIZATION ON tbl3 TO tableowner go -- Create a couple of stored procedures. CREATE PROCEDURE sp1 AS SELECT a FROM tbl1 go CREATE PROCEDURE sp2inner AS SELECT a FROM tbl1 go CREATE PROCEDURE sp2 AS SELECT b FROM tbl2 EXEC sp2inner go CREATE PROCEDURE sp3 AS SELECT c FROM tbl3 go CREATE PROCEDURE sp2procowner AS SELECT b FROM tbl2 EXEC sp2inner go -- Make procowner the owner of sp2procowner. ALTER AUTHORIZATION ON sp2procowner TO procowner go -- Grant permissions to testuser to execute all procedures, -- except for sp2inner. GRANT EXECUTE ON sp1 TO testuser GRANT EXECUTE ON sp2 TO testuser GRANT EXECUTE ON sp2procowner TO testuser GRANT EXECUTE ON sp3 TO testuser go -- Run some commands as testuser, with its permissions etc. EXECUTE AS LOGIN = 'testuser' go -- sp1 runs fine, as dbo owns both sp1 and tbl1. PRINT 'EXEC sp1, this runs fine' EXEC sp1 go -- Also sp2 runs fine. Note that testuser can run sp2inner, when -- it's called from sp2. Ownership chaining applies here as well. PRINT 'EXEC sp2, this runs fine, despite no priv on sp2inner' EXEC sp2 go -- But sp2procowner fails twice. Because sp2procowner has a different -- owner than tbl2 and sp2inner, testuser would need direct permission on -- these objects, but he hasn't. PRINT 'EXEC sp2procowner, two permission errors' EXEC sp2procowner go -- And this fails as well, because while sp3 is owned by dbo, tbl3 is -- owned by another user, so ownership chaining is broken. PRINT 'EXEC sp3, permission error' EXEC sp3 go -- Stop being tester and clean up. REVERT go USE master go DROP LOGIN testuser DROP DATABASE ownershiptest
  • USE master
    go
    -- 테스트 사용자와 테스트 DB를 만든다
    CREATE LOGIN testuser WITH PASSWORD = 'TesT=0=UsEr'
    CREATE DATABASE ownershiptest
    go
    -- 테스트 DB로 변경
    USE ownershiptest
    go
    -- 테스트를 실행할 사용자를 생성
    CREATE USER testuser
    go
    -- 데이터베이스에만 있는 사용자를 두 개 생성한다
    -- 이 사용자들은 일부 개체를 가지게 된다
    CREATE USER procowner WITHOUT LOGIN
    CREATE USER tableowner WITHOUT LOGIN
    go
    -- 테스트 테이블 세개를 생성한다. 권한이 있음을 보여주기 위한 것이라서
    -- 데이터는 입력하지 않는다.
    CREATE TABLE tbl1 (a int NOT NULL)
    CREATE TABLE tbl2 (b int NOT NULL)
    CREATE TABLE tbl3 (c int NOT NULL)
    go
    -- 사용자 tableowner를 tbl3의 소유자로 한다
    ALTER AUTHORIZATION ON tbl3 TO tableowner
    go
    -- 저장 프로시저를 몇 개 만든다
    CREATE PROCEDURE sp1 AS
    SELECT a FROM tbl1
    go
    CREATE PROCEDURE sp2inner AS
    SELECT a FROM tbl1
    go
    CREATE PROCEDURE sp2 AS
    SELECT b FROM tbl2
    EXEC sp2inner
    go
    CREATE PROCEDURE sp3 AS
    SELECT c FROM tbl3
    go
    CREATE PROCEDURE sp2procowner AS
    SELECT b FROM tbl2
    EXEC sp2inner
    go
    -- 사용자 procowner를 sp2procowner의 소유자로 한다
    ALTER AUTHORIZATION ON sp2procowner TO procowner
    go
    -- 사용자 testuser에게 sp2inner를 뺀 모든 프로시저를 실행할 권한을 준다
    GRANT EXECUTE ON sp1 TO testuser
    GRANT EXECUTE ON sp2 TO testuser
    GRANT EXECUTE ON sp2procowner TO testuser
    GRANT EXECUTE ON sp3 TO testuser
    go
    -- 사용자 testuser의 권한으로 명령을 실행한다
    EXECUTE AS LOGIN = 'testuser'
    go
    -- dbo가 sp1과 tb1을 소유하므로 sp1은 잘 동작한다
    PRINT 'EXEC sp1, this runs fine'
    EXEC sp1
    go
    -- sp2도 잘 동작한다. sp2 안에서 실행되면 testuser도 sp2inner를
    -- 실행할 수 있다. 소유권 체인이 여기서도 적용되기 때문이다.
    -- (역주: testuser는 sp2inner의 실행 권한이 없음)
    PRINT 'EXEC sp2, this runs fine, despite no priv on sp2inner'
    EXEC sp2
    go
    -- 그러나 sp2procowner는 두번 실패한다. sp2procowner의 소유자는 tbl2와
    -- sp2inner의 소유자와 다르기 때문이다. testuser는 두 개체에 직접 접근할
    -- 권한이 필요하지만 권한이 없다
    PRINT 'EXEC sp2procowner, two permission errors'
    EXEC sp2procowner
    go
    -- 그리고 다음도 실패한다. sp3은 dbo가 소유하고 있으며, tbl3은 다른 사용자가
    -- 소유하고 있기 때문이다. 따라서 소유권 체인이 깨진다.
    PRINT 'EXEC sp3, permission error'
    EXEC sp3
    go
    -- 테스트를 중단하고 원래대로 복구함
    REVERT
    go
    USE master
    go
    DROP LOGIN testuser
    DROP DATABASE ownershiptest
  • Since ownership chaining is so commonly used, and works so smoothly when all objects are owned by dbo, it often comes as a surprise when users get a permission error when they run a stored procedure.
  • 소유권 체인은 흔하게 쓰이고, dbo가 모든 개체를 소유하고 있다면 순조롭게 동작한다. 따라서 사용자가 저장 프로시저를 실행했는데 권한 오류가 나타나면 보통 깜짝 놀라게 된다.
  • The story is that ownership chaining does not apply to all statements. Essentially, ownership chaining applies to DML statements (SELECT, INSERT, DELETE, UPDATE and MERGE) and EXECUTE of stored procedures and functions. If you put a statement likeCREATE TABLE into a stored procedure, the user must have permissions to create tables (which a plain user rarely has, save for temp tables). Same goes for many other administrative commands.
  • 까닭은 소유권 체인은 모든 명령에 적용되지 않기 때문이다. 본질적으로 소유권 체인은 DML 구문(SELECT, INSERT, DELETE, UPDATE 및 MERGE)과 저장 프로시저 및 사용자 정의 함수의 실행 시에 적용된다. 저장 프로시저 안에 CREATE TABLE 명령 등을 집어넣으면 사용자는 테이블을 생성할 권한이 반드시 있어야 한다. (임시 테이블을 제외하면 일반 사용자에게 그런 권한을 부여하는 경우는 거의 없다) 다른 관리 명령들에 대해서도 마찬가지다.
  • A statement that is worth special mention here is TRUNCATE TABLE, which logically is a DML statement; a quicker way to delete all rows in a table. But the permissions for this command are not transferable through ownership chaining, so if you want to write a stored procedure to permits users to empty a table, you may prefer to use DELETE although this is less efficient.
  • 여기서 잠깐 짚고 넘어갈 것은 TRUNCATE TABLE 문이다. 이것은 논리상 빠르게 테이블의 모든 행을 삭제하는 DML 문이다. 그러나 이 명령을 실행하기 위한 권한은 소유권 체인으로 전이되지 않는다. 따라서 사용자가 저장 프로시저를 써서 테이블을 비우게 하려면 좀 덜 효율적이라도 DELETE 문을 쓰는 것이 좋다.
  • Another example of a command where ownership chaining does not work is BULK INSERT; this command requires a server-level permission.
  • 소유권 체인이 동작하지 않는 다른 명령으로 BULK INSERT를 들 수 있다. 이 명령은 서버 수준의 권한이 필요하다.
  • These are situations that can be resolved by signing procedures with certificates or by using impersonation with EXECUTE AS, methods that we shall look into later in this article.
  • 이러한 상황은 인증서로 저장 프로시저에 서명하거나, EXECUTE AS로 가장(impersonation)하여 해결할 수 있다. 뒷 부분에서 이러한 방법을 알아볼 것이다.
  • Another case where ownership chaining does not work is dynamic SQL. Consider:
  • 소유권 체인이 동작하지 않는 다른 사례로는 동적 SQL이 있다. 예를 들어
  • CREATE PROCEDURE myproc AS EXEC('SELECT a, b FROM tbl')
  • (This is certainly not how you would use dynamic SQL in real life, but I wanted to keep the example short. Please see my articleThe Curse and Blessings of Dynamic SQL for a longer discussion on dynamic SQL, when to use it – and when to not.)
  • To run this procedure, a user needs SELECT permissions on tbl. The reason for this is that the batch of dynamic SQL is a scope of its own that is not part of the stored procedure. And this batch does not really have any owner at all, and thus the ownership chain is broken.
  • Since dynamic SQL is very powerful for some tasks – dynamic search conditions being the prime example – it was not uncommon in SQL 2000 and earlier version to give users SELECT rights, as long as this was compliant with corporate security policy. But sinceSQL 2005 this is not necessary; you can use procedure signing and impersonation to give users permission to execute dynamicSQL.
  • In SQL Server you can write stored procedures, triggers and user-defined functions in a CLR language such as C# or Visual Basic. You can perform data access from a CLR module by running a batch of SQL statements, but ownership chaining does not apply in this case. The reason for this is the same as with dynamic SQL: the SQL batch is a scope of its own that does not have any owner.
  • So when you write CLR modules that accesses tables, you must either grant the users direct permissions to these tables or employ module signing or impersonation. I am not covering how to use these mechanisms with CLR modules in this article, but the topic Module Signing in Books Online includes an example.
  • If a stored procedure sp1 in database A accesses a table tbl2 in database B, ownership chaining can apply as well, if the procedure owner also owns tbl2. In the trivial case, the two databases have the same owners and all involved objects are owned by dbo. The user running sp1 must also be a user in database B. (Unless you have enabled access for the guest user in database B, something I don't recommend.)
  • However, starting with SQL 2000 SP3, ownership chaining across databases is turned off by default. You can enable it on server level, or per database. To enable it on server level, set the configuration option cross db ownership chaining to 1 (withsp_configure or through SQL Server Management Studio). Now all databases on the server will be open for cross-db chaining.
  • To open an individual database for cross-db chaining, use the command ALTER DATABASE db SET DB_CHAINING ON. In the example above, both A and B must be enabled for DB chaining for users being able to access B..tbl2 through sp1 without any own permission on tbl2. To enable a database for chaining, you need sysadmin privileges.
  • As you might guess, there is a reason for database chaining being off by default. Assume that Jack and Jill own one database each. Jack is a user in Jill's database, but he only has permissions to run a few stored procedures there. If their databases are enabled for database chaining, Jack can get to Jill's inner secrets, by taking the following steps.
  • - Add Jill as a user in his own database.
  • - Create a schema in his database owned by Jill.
  • - Create stored procedures in the Jill schema that accesses Jill's database. Since Jill owns the schema, she also owns the procedures, as noted above. (Jack could also create the procedures in the dbo schema, and then make Jill owner of those procedures.)
  • Jack can now access all tables in Jill's database as he likes.
  • Microsoft are very discouraging about turning on database chaining, but for a server that hosts a single application that uses several databases, turning on database chaining on server level appears uncontroversial. It's a different thing on a consolidated server that hosts databases for many unrelated applications. Here, you should most probably never turn on the configuration option to open DB chaining for all databases. What if a user who owns two databases asks you to turn on chaining on these databases? As long it's only those two, it's fine, but then the next guy comes with his two databases. There is no way to say thatdb1 may chain to db2 but not to db3 or db4.
  • According to Books Online, you cannot enable master, model and tempdb for database chaining with ALTER DATABASE. It does not really say whether chaining is enabled for these databases if you turn on cross db ownership chaining, but some quick tests that I did indicate that even if this option is on, it does not apply to master, model, msdb and tempdb.
  • Personally, I recommend that you try to keep cross-database access to stored procedure calls. That is, rather than directly access a table in the other database, call a procedure in that database. In this case, ownership chaining across database is not really needed – instead give the users EXECUTE permission to the procedures in the other database.
  • We will now turn to the first of the two methods added in SQL 2005 to grant permissions through stored procedures, signing a procedure with a certificate.
  • We will first look at using certificates for giving permissions on database level. As an example, I will use dynamic SQL, which probably is the most common situation where you will want to use certificates as a supplement to ownership chaining.
  • Our example setup is this one:
  • CREATE TABLE testtbl (a int NOT NULL, b int NOT NULL) go CREATE PROCEDURE example_sp AS EXEC ('SELECT a, b FROM testtbl') go GRANT EXECUTE ON example_sp TO public go
  • As noted above, ownership chaining does not work in this case, because the batch of dynamic SQL does not have any real owner, and thus the chain is broken. To make it possible for a user to run this procedure without SELECT permission on testtbl, you need to take these four steps:
  • - Create a certificate.
  • - Create a user associated with that certificate.
  • - Grant that user SELECT rights on testtbl.
  • - Sign the procedure with the certificate, each time you have changed the procedure.
  • When the procedure is invoked, the rights of the certificate user are added to the rights of the actual user. Technically, we can describe this as the certificate user is added to the current user token. If the procedure invokes another SQL module – stored procedure, trigger, function etc – the certificate user is removed from the user token (unless that module is also signed by the certificate). There are two exceptions to this rule: system procedures and dynamic SQL invoked through EXEC() orsp_executesql. In this case the certificate user is still present in the user token, and its rights can apply.
  • This example shows the four steps in code.
  • CREATE CERTIFICATE examplecert ENCRYPTION BY PASSWORD = 'All you need is love' WITH SUBJECT = 'Certificate for example_sp', START_DATE = '20020101', EXPIRY_DATE = '21000101' go CREATE USER examplecertuser FROM CERTIFICATE examplecert go GRANT SELECT ON testtbl TO examplecertuser go -- And each time you change the procedure: ADD SIGNATURE TO example_sp BY CERTIFICATE examplecert WITH PASSWORD = 'All you need is love'
  • In the following sections, we will look closer at each of these statements.
  • CREATE CERTIFICATE examplecert ENCRYPTION BY PASSWORD = 'All you need is love' WITH SUBJECT = 'Certificate for example_sp', START_DATE = '20020101', EXPIRY_DATE = '21000101'
  • The statement CREATE CERTIFICATE has several options, but for our purposes the form above suffices. Here we create a newself-signed certificate which is protected by a password. The password is not awfully strong; I will return to the topic of passwords in the section Managing Certificates and Passwords.
  • The WITH SUBJECT clause is part of the metadata for the certificate; in the catalog view sys.certificates the subject appears in the column issuer_name.
  • There is no requirement to enter a start date and an expiry date for the certificate, but for practical reasons you may want to enter both. If you enter neither, the certificate is valid one year from now. Since it is likely that your procedure will be in use for more than one year, it's recommendable to give an expiry date far into the future. If you leave out the start date, SQL 2005 may produce this message:
  • Warning: The certificate you created is not yet valid; its start date is in the future.
  • The message is bogus since the default for the start date is the same second as you issue the command. The message is not an error, but informational only. If you don't want to see it, specify a start date. This issue has been fixed in SQL 2008.
  • CREATE USER examplecertuser FROM CERTIFICATE examplecert
  • We see here one more option for CREATE USER: we create a user from a certificate. Such a user exists in the database only and is not associated with any login. You can only create one user for each certificate.
  • GRANT SELECT ON testtbl TO examplecertuser
  • Here's the beauty of it: we grant examplecertuser exactly the rights it needs for our stored procedure to work. Of course, if you use a lot of dynamic SQL, you may prefer to grant the certificate user SELECT on the dbo schema or add it to db_datareader. You might even consider to add it to db_owner to relieve you from any further hassle, as you add more dynamic SQL to other stored procedures.
  • But stop there! Recall that discussion on philosophy in the beginning of the article and that one line of defence is to not grant more rights than necessary. This very much applies when you work with dynamic SQL. You know about SQL injection, don't you? If not, a quick recap: if you build SQL strings from input data, a malicious user might be able to inject SQL commands you did not intend your code to execute by including a single quote (') in the input data. For a longer recap, see the section on SQL injectionin my article on dynamic SQL.
  • You may already be aware of the risk of SQL injection, and you have taken the steps necessary to protect your procedure against this attack. But that is today. Code changes throughout the life-time of an application, and one day there is a need for an enhancement of the procedure, and the task is given to an inexperienced programmer who, unaware of the dangers of SQLinjection, breaks that line of defence. By giving the certificate user exactly the rights needed for the stored procedure, you have set up a second line of defence that reduces the potential damage significantly.
  • This is the syntax to sign a procedure:
  • ADD SIGNATURE TO example_sp BY CERTIFICATE examplecert WITH PASSWORD = 'All you need is love'
  • To use the certificate, you need to specify its password. You can sign a procedure with more than one certificate to add permissions from several certificate users.
  • If you change the procedure, the signature is lost, and you need to resign the procedure. Given for what we want to use certificates for, this may seem impractical. When we grant someone execution rights on a stored procedure, these permissions are retained when we alter the procedure. So why do we need to resign a procedure when we change it? Isn't that a shortcoming? It may seem so, but it is worth to understand the general purpose of signing things with certificates, which extends far beyond stored procedures in SQL Server. Say that you have a important message you want to pass to someone else, for instance over e-mail. You want to make it possible for the receiver to verify that he got exactly the message you sent him. Therefore you sign your message, which requires both a public and a private key. You make your public key available, and the receiver can then apply that key to message and the signature to verify that they agree. If someone has altered the text or the signature, the validation will fail.
  • That is, every time you change the stored procedure, the signature will change, and this is why you must resign the procedure. It could seem that for the particular purpose that we are using certificates for here, that this is just hassle. But as I discuss in the section Managing Certificates and Passwords, the fact that the procedure must be resigned can in fact be a considerable security advantage.
  • Here is a full-fledged example that you can play with. To show the difference, there are two procedures, of which only one is signed. (Please refer to the introductory note on the examples in this article.)
  • 여기에 돌려볼 수 있는 예제가 있다. 차이를 알려주기 위해 두 개의 프로시저가 있으나 그 중 하나만 서명이 되었다. (이 글의 예제에 대해서는 서문을 참고하기 바란다)
  • USE master go -- Create a test login and test database CREATE LOGIN testuser WITH PASSWORD = 'CeRT=0=TeST' CREATE DATABASE certtest go -- Move to the test database. USE certtest go -- Create the test user. CREATE USER testuser go -- Create the test table and add some data. CREATE TABLE testtbl (a int NOT NULL, b int NOT NULL) INSERT testtbl (a, b) VALUES (47, 11) go -- Create two test stored procedures, and grant permission. CREATE PROCEDURE unsigned_sp AS SELECT SYSTEM_USER, USER, name, type, usage FROM sys.user_token EXEC ('SELECT a, b FROM testtbl') go CREATE PROCEDURE example_sp AS SELECT SYSTEM_USER, USER, name, type, usage FROM sys.user_token EXEC ('SELECT a, b FROM testtbl') -- EXEC unsigned_sp go GRANT EXECUTE ON example_sp TO public GRANT EXECUTE ON unsigned_sp TO public go -- Create the certificate. CREATE CERTIFICATE examplecert ENCRYPTION BY PASSWORD = 'All you need is love' WITH SUBJECT = 'Certificate for example_sp', START_DATE = '20020101', EXPIRY_DATE = '20200101' go -- Create the certificate user and give it rights to access the test table. CREATE USER examplecertuser FROM CERTIFICATE examplecert GRANT SELECT ON testtbl TO examplecertuser go -- Sign the procedure. ADD SIGNATURE TO example_sp BY CERTIFICATE examplecert WITH PASSWORD = 'All you need is love' go -- Run as the test user, to actually see that this works. EXECUTE AS USER = 'testuser' go -- First run the unsigned procedure. This gives a permission error. EXEC unsigned_sp go -- Then run the signed procedure. Now we get the data back. EXEC example_sp go -- Become ourselves again. REVERT go -- Clean up USE master DROP DATABASE certtest DROP LOGIN testuser
  • USE master
    go
    -- 테스트 로그인과 테스트 DB를 만든다
    CREATE LOGIN testuser WITH PASSWORD = 'CeRT=0=TeST'
    CREATE DATABASE certtest
    go
    -- 테스트용 데이터베이스로 변경
    USE certtest
    go
    -- 테스트용 사용자 생성
    CREATE USER testuser
    go
    -- 테스트 테이블을 만들고 데이터를 집어넣음
    CREATE TABLE testtbl (a int NOT NULL, b int NOT NULL)
    INSERT testtbl (a, b) VALUES (47, 11)
    go
    -- (동일한) 테스트 프로시저 두개를 만들고 권한을 부여함
    CREATE PROCEDURE unsigned_sp AS
    SELECT SYSTEM_USER, USER, name, type, usage FROM sys.user_token
    EXEC ('SELECT a, b FROM testtbl')
    go
    CREATE PROCEDURE example_sp AS
    SELECT SYSTEM_USER, USER, name, type, usage FROM sys.user_token
    EXEC ('SELECT a, b FROM testtbl')
    -- EXEC unsigned_sp
    go
    GRANT EXECUTE ON example_sp TO public
    GRANT EXECUTE ON unsigned_sp TO public
    go
    -- 인증서 생성
    CREATE CERTIFICATE examplecert
    ENCRYPTION BY PASSWORD = 'All you need is love'
    WITH SUBJECT = 'Certificate for example_sp',
    START_DATE = '20020101', EXPIRY_DATE = '20200101'
    go
    -- 인증서 사용자를 만들고 테스트 테이블에 접근할 수 있는 권한을 줌
    CREATE USER examplecertuser FROM CERTIFICATE examplecert
    GRANT SELECT ON testtbl TO examplecertuser
    go
    -- 프로시저 서명
    ADD SIGNATURE TO example_sp BY CERTIFICATE examplecert
    WITH PASSWORD = 'All you need is love'
    go
    -- 테스트 사용자로 실행하고, 동작 여부를 확인하자
    EXECUTE AS USER = 'testuser'
    go
    -- 먼저 서명되지 않은 프로시저를 실행한다. 권한 오류가 발생한다.
    EXEC unsigned_sp
    go
    -- 그리고 서명된 프로시저를 실행하고 값이 나오는 것을 보자
    EXEC example_sp
    go
    -- 원래 권한으로 돌아오고
    REVERT
    go
    -- 뒷정리
    USE master
    DROP DATABASE certtest
    DROP LOGIN testuser
  • As you can see, I added this statement to the two test procedures in the example:
  • 위에서 봤듯이 예제의 두 프로시저에 이 명령을 집어넣었다.
  • SELECT SYSTEM_USER, USER, name, type, usage FROM sys.user_token
  • SELECT SYSTEM_USER, USER, name, type, usage FROM sys.user_token
  • When we run unsigned_sp, this returns
  • 서명되지 않은 unsigned_sp를 실행하면, 반환값은 아래와 같다.
  • SYSTEM_USER USER name type usage ------------ ---------- --------- --------- -------------- testuser testuser testuser SQL USER GRANT OR DENY testuser testuser public ROLE GRANT OR DENY
  • SYSTEM_USER USER name type usage
    ------------ ---------- --------- --------- --------------
    testuser testuser testuser SQL USER GRANT OR DENY
    testuser testuser public ROLE GRANT OR DENY
  • What this tells us is that we are logged in as testuser, and this is also the name of the user in the database. There are two rows in sys.user_token, one for the user, and one for the single role that testuser is a member of.
  • 이것은 우리가 testuser로 로그인했고, 데이터베이스에는 같은 이름의 사용자 컨텍스트임을 알려준다. sys_user_token에는 두 개의 행이 있는데, 하나는 사용자이고, 나머지 하나는 testuser가 속한 역할(role)을 보여주는 것이다.
  • But when we run example_sp, which is signed, there is an extra line:
  • 그러나 서명된 example_sp를 실행하면, 한 줄이 더 붙는다.
  • SYSTEM_USER USER name type usage ---------- -------- ----------- --------------------------- --------------- testuser testuser testuser SQL USER GRANT OR DENY testuser testuser public ROLE GRANT OR DENY testuser testuser examplecertuser USER MAPPED TO CERTIFICATE GRANT OR DENY
  • SYSTEM_USER USER name type usage
    ---------- -------- ----------- --------------------------- ---------------
    testuser testuser testuser SQL USER GRANT OR DENY
    testuser testuser public ROLE GRANT OR DENY
    testuser testuser examplecertuser USER MAPPED TO CERTIFICATE GRANT OR DENY
  • We see here that the user for the certificate has been added to the user token, so its permissions can apply as well. We can also see that we still are testuser, and no one else. This may seem like a pointless thing to mention, but as we shall see later, this is not the case when you use EXECUTE AS.
  • As you see, example_sp includes a call to unsigned_sp that has been commented out. If you remove that comment, and run the script again, when you call unsigned_sp from example_sp, you get a permission error just like when unsigned_sp is called directly. You will also see in the output from sys.user_token, that examplecertuser is not there.
  • There is one situation where certificate signing does not work. If the user has explicitly been denied access to one or more of the tables in the query with the DENY command, this takes precedence over the permissions granted to the certificate user. This is different from ownership chaining, where DENY never interferes with the permissions given through the stored procedure. (This is because ownership chaining suppresses the permission check altogether.) As will see later, this obstacle does not exist when you use impersonation with EXECUTE AS.
  • Another common situation where ownership chaining does not suffice is when you need to give users permissions to empty a table and reload it with BULK INSERT from a file. Here is a very simple procedure for this task:
  • CREATE PROCEDURE reload_sp AS TRUNCATE TABLE reloadable BULK INSERT reloadable FROM 'E:\temp\reloadable.csv' WITH (FIELDTERMINATOR=',', ROWTERMINATOR='\n')
  • Ownership chaining fails here for two reasons: 1) it does not apply to TRUNCATE TABLE. 2) to perform bulk operations, you need the server-level permission ADMINISTER BULK OPERATIONS or membership in the fixed server role bulkadmin.
  • You can address this by signing reload_sp, but this is more complicated than in the previous example, because you can only add server permissions when you are in the master database. Therefore, to set up reload_sp so it can be executed by an unprivileged user, there are no less than ten steps to go through:
  • - Create a certificate in the master database.
  • - Create a login for that certificate.
  • - Grant that login rights to perform bulk operations.
  • - Export the certificate to file.
  • - Switch to the application database.
  • - Import the certificate from the file.
  • - Delete the file from disk.
  • - Create a user for the certificate.
  • - Grant the certificate user rights to truncate the target table and insert into it.
  • - Sign the stored procedure with the certificate, each time you have changed the procedure.
  • In SQL Server 2012 the steps 4, 6 and 7 can be carried out in a different way. Since SQL 2012 at this writing still is in beta, I put the focus on the steps that works in all versions from SQL 2005 and on, and cover the new features in SQL 2012 later.
  • First some example code for the bit in master.
  • USE master go CREATE CERTIFICATE reloadcert ENCRYPTION BY PASSWORD = 'All you need is love' WITH SUBJECT = 'For bulk-load privileges', START_DATE = '20020101', EXPIRY_DATE = '20200101' go CREATE LOGIN reloadcert_login FROM CERTIFICATE reloadcert go GRANT ADMINISTER BULK OPERATIONS TO reloadcert_login go BACKUP CERTIFICATE reloadcert TO FILE = 'C:\temp\reloadcert.cer' WITH PRIVATE KEY (FILE = 'C:\temp\reloadcert.pvk' , ENCRYPTION BY PASSWORD = 'Tomorrow never knows', DECRYPTION BY PASSWORD = 'All you need is love') go
  • The creation of the certificate is the same as in the example with dynamic SQL. Since we need to grant a server permission, a mere certificate user won't do, but we must associate the certificate with a login. (Or more in line with the lingo introduced inSQL 2005, a server principal. "Login" is a misnomer here, as the login created for a certificate cannot actually log in.) Next we grant the certificate login the rights to run bulk load.
  • Finally we export the certificate to disk with the command BACKUP CERTIFICATE. The certificate consists of two parts: a public key which goes into the first file, and a private key. The private key requires a password on its own, Tomorrow never knows, in this example. The path where to write the files is a small complication that I will come back to. In this example I use C:\temp to keep the script simple. However, you may find that C:\temp does not work for you, because it does not exist at all, or the service account for SQL Server does not have permission to this directory.
  • Here are the parts you would run in the application database:
  • CREATE CERTIFICATE reloadcert FROM FILE = 'C:\temp\reloadcert.cer' WITH PRIVATE KEY (FILE = 'C:\temp\reloadcert.pvk', DECRYPTION BY PASSWORD = 'Tomorrow never knows', ENCRYPTION BY PASSWORD = 'A day in life') go EXEC xp_cmdshell 'DEL C:\temp\reloadcert.*' go CREATE USER reloadcert_user FOR CERTIFICATE reloadcert go GRANT ALTER, INSERT ON reloadable TO reloadcert_user go -- Sign the test procedure each time you have changed it. ADD SIGNATURE TO reload_sp BY CERTIFICATE reloadcert WITH PASSWORD = 'A day in life' go
  • Here we use CREATE CERTIFICATE in a different way than before. Instead of creating a new certificate, we import the certificate that we exported from master. We need to specify the password for the private key to be able to access the file. We must also define a password for the certificate in this database. In this example, I'm using different passwords for the certificate in master and in the application database just to show you that this is possible. It's probably more practical to use the same password in both databases, though.
  • We delete the files with the certificate from disk. This is a security precaution, since any database owner on the machine could load the certificate into his database. But it is also a matter of convenience; if you re-run the script and the certificate files are already on disk, BACKUP CERTIFICATE will fail.
  • Note: xp_cmdshell is disabled by default. An alternative is to delete the file directly from Windows manually.
  • Next, we create the certificate user. This user is not related to the login for the certificate, and I've stressed this by giving them different names. Again, in practice, you may prefer to use the same name for both. We grant the certificate user the database permissions that are needed: ALTER permission for TRUNCATE TABLE, and INSERT permission for BULK INSERT. Finally, we sign the procedure, using the password for the certificate in this database.
  • We are almost done, but if you do all this and try to run the procedure reload_sp as a non-privileged user, you will nevertheless get an error message that you don't have permissions to do bulk load. Because of a bug in SQL Server, we need to modify the procedure:
  • CREATE PROCEDURE reload_sp AS TRUNCATE TABLE reloadable EXEC('BULK INSERT reloadable FROM ''C:\temp\reloadtest.csv'' WITH (FIELDTERMINATOR='','', ROWTERMINATOR=''\n'')')
  • This bug is specific to bulk-load permissions, and I have not found any other server-level permission that has the same issue. (The specifics of the bug are that SQL Server checks the permissions for BULK INSERT before the certificate has been added to the user token. By putting BULK INSERT in an inner scope with dynamic SQL, we can work around the bug.)
  • As in the previous example there are two procedures, one signed and one unsigned, and I've added SELECT fromsys.login_token and sys.user_token, so that you can see how the certificate login and the certificate user are added and deleted. (Again, please refer to the introductory note for general notes on the examples.) If you get errors when you run the script that C:\temp does not exist, or you get permissions errors with C:\temp, see below.
  • USE master go -- Create a test file for bulk load. EXEC xp_cmdshell 'ECHO 978,123,234 > C:\temp\reloadtest.csv', no_output EXEC xp_cmdshell 'ECHO -98,13,85 >> C:\temp\reloadtest.csv', no_output go -- Create a test login. CREATE LOGIN testuser WITH PASSWORD = 'CeRT=0=TeST' go -- Create test database. CREATE DATABASE bulkcerttest go -- Create certificate in master. CREATE CERTIFICATE reloadcert ENCRYPTION BY PASSWORD = 'All you need is love' WITH SUBJECT = 'For bulk-load privileges', START_DATE = '20020101', EXPIRY_DATE = '20200101' go -- Create a login for the certificate. CREATE LOGIN reloadcert_login FROM CERTIFICATE reloadcert go -- Grant rights for the certificate login. GRANT ADMINISTER BULK OPERATIONS TO reloadcert_login go -- Save the certificate to disk. BACKUP CERTIFICATE reloadcert TO FILE = 'C:\temp\reloadcert.cer' WITH PRIVATE KEY (FILE = 'C:\temp\reloadcert.pvk' , ENCRYPTION BY PASSWORD = 'Tomorrow never knows', DECRYPTION BY PASSWORD = 'All you need is love') go -- Move to test database. USE bulkcerttest go -- Create the non-priv user. CREATE USER testuser go -- A test table. CREATE TABLE reloadable (a int NOT NULL, b int NOT NULL, c int NOT NULL) go -- Insert some test data. If test succeeds, this data should disappear. INSERT reloadable (a, b, c) VALUES (12, 23, 34) go -- Test procedure with BULK INSERT. BULK INSERT needs to be in -- EXEC() because of a bug in SQL Server. CREATE PROCEDURE reload_sp AS SELECT name, type, usage FROM sys.login_token SELECT name, type, usage FROM sys.user_token TRUNCATE TABLE reloadable EXEC('BULK INSERT reloadable FROM ''C:\temp\reloadtest.csv'' WITH (FIELDTERMINATOR='','', ROWTERMINATOR=''\n'')') go -- The same code, but this procedure we will not sign. CREATE PROCEDURE unsigned_sp AS SELECT name, type, usage FROM sys.login_token SELECT name, type, usage FROM sys.user_token --TRUNCATE TABLE reloadable EXEC('BULK INSERT reloadable FROM ''C:\temp\reloadtest.csv'' WITH (FIELDTERMINATOR='','', ROWTERMINATOR=''\n'')') go -- Give test user right to execute the procedures. GRANT EXECUTE ON reload_sp TO testuser GRANT EXECUTE ON unsigned_sp TO testuser go -- Import the certificate we created in master into the test database. CREATE CERTIFICATE reloadcert FROM FILE = 'C:\temp\reloadcert.cer' WITH PRIVATE KEY (FILE = 'C:\temp\reloadcert.pvk', DECRYPTION BY PASSWORD = 'Tomorrow never knows', ENCRYPTION BY PASSWORD = 'A day in life') go -- Delete the files. EXEC master..xp_cmdshell 'DEL C:\temp\reloadcert.*', 'no_output' go -- And create a user for the certificate. CREATE USER reloadcert_user FOR CERTIFICATE reloadcert go -- Grant this user rights to truncate and insert to the test table. GRANT ALTER, INSERT ON reloadable TO reloadcert_user go -- Sign the test procedures. ADD SIGNATURE TO reload_sp BY CERTIFICATE reloadcert WITH PASSWORD = 'A day in life' go -- Switch to the test user. EXECUTE AS LOGIN = 'testuser' go -- Run the unsigned procedure. You will get a permission error. EXEC unsigned_sp go -- Run the real reload procedure. EXEC reload_sp go -- Back to ourselves. REVERT go -- The data in the table has been replaced. SELECT a, b, c FROM reloadable go -- Clean up. USE master go DROP DATABASE bulkcerttest DROP LOGIN reloadcert_login DROP CERTIFICATE reloadcert DROP LOGIN testuser EXEC xp_cmdshell 'DEL C:\temp\reloadtest.csv', 'no_output'
  • In unsigned_sp I have commented TRUNCATE TABLE, in order to demonstrate the error you get because lack of bulk permissions. If you uncomment TRUNCATE TABLE, you will get a different permission error from unsigned_sp.
  • One problem here is that we need to bounce the certificate over disk. To do this, you need to determine a directory where you can write the certificate. This can be particularly difficult if you need to do this in a deployment script to be run on servers you never have seen. In this example, I used C:\temp for the sake of simplicity, but C:\temp does not exist on all servers. Even if it does, the service account for SQL Server may not have write access to that folder. If you leave out the path entirely, BACKUP CERTIFICATE will write the files to the default directory for new databases and likewise CREATE CERTIFICATE will read from this directory. It's reasonable to expect that SQL Server has write access to this folder, so far so good. Unfortunately, this path is not easily determined from within SQL Server, so there is a challenge if you want to delete the files programmatically from your deployment script. One way to get a path that is known to be writeable is this SELECT:
  • SELECT substring(physical_name, 1, len(physical_name) -
  • charindex('\', reverse(physical_name)) + 1)
  • FROM sys.database_files
  • WHERE file_id = 1
  • This retrieves the path to the directory where the first file for the current database resides. You would use this path throughout the script, which means that BACKUP/CREATE CERTIFICATE has to be embedded in dynamic SQL, as they don't accept variables for the file name.
  • CREATE CERTIFICATE FROM BINARY in SQL 2012

  • It would certainly be convenient, if you could copy a certificate directly from one database to another without bouncing it over disk, and there is a new feature in SQL Server 2012 that permits you to do this. There is a new clause to CREATE CERTIFICATE: FROM BINARY which permits you to specify the certificate as a binary constant. SQL 2012 also offers two new functionscertencoded and certprivatekey which permits you to retrieve the public and the private keys of the certificate. Thus, you can say:
  • DECLARE @public_key varbinary(MAX) = certencoded(my_cert), @private_key varbinary(MAX) = certprivatekey(my_cert, 'private_key_password', 'public_key_password') CREATE CERTIFICATE newcert FROM BINARY = @public_key WITH PRIVATE KEY (BINARY = @private_key, DECRYPTION BY PASSWORD = 'private_key_password', ENCRYPTION BY PASSWORD = 'new_public_key_password')
  • Almost. The functions do not accept the name for the certificate, but they want the the certificate id in sys.certificates. You can retrieve it with the cert_id function, see example below for how to use it. Furthermore, CREATE CERTIFICATE does not accept variables for the binary value, but you must provide a constant, which means that you are in for some dynamic SQL.
  • To use this new functionality to copy a certificate between databases, we replace the steps 4-7 above to read:
  • - Save the keys of the certificae to a temp table.
  • - Switch to the application database.
  • - Create the certificate from the data in the temp table.
  • - Drop the temp table.
  • The reason we like to use a temp table is that our example script is split into a number of batches. We cannot use variables, as they exist only for the duration of a batch. Whence the temp table. This is how step 4 looks like:
  • CREATE TABLE #keys (pubkey varbinary (MAX) NOT NULL, privkey varbinary(MAX) NOT NULL) INSERT #keys (pubkey, privkey) SELECT certencoded(cert_id('reloadcert')), certprivatekey(cert_id('reloadcert'), 'Tomorrow never knows', 'All you need is love')
  • The passwords you pass to certprivatekey correspond to the passwords we used with BACKUP CERTIFICATE above. That is, the first password is the password for the private key, which you have to make up at this point. The second password is the password for the public key that you used when you created the certificate in master.
  • Since we need to use dynamic SQL to create the certificate from the data in the temp table, this part gets a little more complicated than it would have to be. Here is how it looks with our bulk-copy example:
  • DECLARE @sql nvarchar(MAX) SELECT @sql = 'CREATE CERTIFICATE reloadcert FROM BINARY = ' + convert(nvarchar(MAX), pubkey, 1) + ' WITH PRIVATE KEY (BINARY = ' + convert(nvarchar(MAX), privkey, 1) + ', DECRYPTION BY PASSWORD = ''Tomorrow never knows'', ENCRYPTION BY PASSWORD = ''A day in life'')' FROM #keys
  • PRINT @sql EXEC (@sql) DROP TABLE #keys
  • A key here is the third argument to the convert function; this converts the binary value to a hex-string with a leading 0x. This style to convert was added in SQL 2008, in case you are not familiar with it. If you compare with the CREATE CERTIFICATEcommand when we imported the certificate from a file, this is very similar; all that has changed is that FILE is now BINARY and the extra syntactical fireworks imposed to us because we have to use dynamic SQL.
  • Before we execute the command, we print it, so we can understand what is going on if there is a syntax error in our dynamic SQL. Finally, we drop the temp table as a safety precaution. If we would leave it around, we could run into problems later if we would re-run the script or run a similar script from the same query window.
  • The script bulkcopy-2012.sql has the full example for bulk-load for SQL 2012, using CREATE CERTIFICATE FROM BINARY.
  • All and all, this is a welcome addition to SQL 2012, since it makes it easier to copy certificates between databases. Not so much for the different syntax, but you don't have to worry about disk paths in your scripts.
  • When you need to write a stored procedure that accesses data in another database, you can arrange permissions by signing your procedure with a certificate that exists in both databases. The steps are similar to the bulk-copy case, so I will go directly to an example script.
  • There are two things to note with this script: 1) testuser is never granted access to db1. That is, by signing your procedures with a certificate, you can give users access to data in a database they do not have access to themselves. This is different from ownership chaining, where the user must have been granted access to the target database. 2) I don't create any user for the certificate in db2, simply because in this example no permissions are needed to be granted through the certificate in db2.
  • Here is the script (please see the introductory note for general notes on the example scripts):
  • USE master go -- Create a test login. CREATE LOGIN testuser WITH PASSWORD = 'CeRT=0=TeST' go -- Create test two databases CREATE DATABASE db1 CREATE DATABASE db2 go -- Move to first test database. USE db1 go -- Create certificate in db1 CREATE CERTIFICATE crossdbcert ENCRYPTION BY PASSWORD = 'Lucy in the Skies with Diamonds' WITH SUBJECT = 'Cross-db test', START_DATE = '20020101', EXPIRY_DATE = '20200101' go -- Save the certificate to disk. BACKUP CERTIFICATE crossdbcert TO FILE = 'C:\temp\crossdbcert.cer' WITH PRIVATE KEY (FILE = 'C:\temp\crossdbcert.pvk' , ENCRYPTION BY PASSWORD = 'She said She said', DECRYPTION BY PASSWORD = 'Lucy in the Skies with Diamonds') go -- Create the certificate user. Note that we do not grant access to -- testuser. CREATE USER certuser FROM CERTIFICATE crossdbcert go -- A test table. CREATE TABLE testtbl (a int NOT NULL, b int NOT NULL, c int NOT NULL) go -- Insert some test data. INSERT testtbl (a, b, c) VALUES (12, 23, 34) go -- The certificate user needs to access this table. GRANT SELECT ON testtbl TO certuser go -- Switch to the second database. USE db2 go -- Welcome the test user to this database. CREATE USER testuser go -- Signed test procedure. CREATE PROCEDURE signed_sp AS SELECT a, b, c FROM db1..testtbl go -- Same code, but we will leave this one unsigned. CREATE PROCEDURE unsigned_sp AS SELECT a, b, c FROM db1..testtbl go -- Give test user right to execute the procedures. GRANT EXECUTE ON signed_sp TO testuser GRANT EXECUTE ON unsigned_sp TO testuser go -- Import the certificate we created in the first test database into the second. CREATE CERTIFICATE crossdbcert FROM FILE = 'C:\temp\crossdbcert.cer' WITH PRIVATE KEY (FILE = 'C:\temp\crossdbcert.pvk', DECRYPTION BY PASSWORD = 'She said She said', ENCRYPTION BY PASSWORD = 'Helter Skelter') go -- Delete the file with the certificate. EXEC master..xp_cmdshell 'DEL C:\temp\crossdbcert.*', 'no_output' go -- Sign the test procedures. ADD SIGNATURE TO signed_sp BY CERTIFICATE crossdbcert WITH PASSWORD = 'Helter Skelter' go -- Switch to the test login. EXECUTE AS LOGIN = 'testuser' go -- Run the unsigned procedure. You will get a permission error. EXEC unsigned_sp go -- Run the signed procedure. testuser can now access testdbl, even though -- he is not a user of db1. EXEC signed_sp go -- Back to ourselves. REVERT go -- Clean up. USE master go DROP DATABASE db1 DROP DATABASE db2 DROP LOGIN testuser
  •  

  • Counter Signatures

  • If you look up the command ADD SIGNATURE in Books Online, you will find that there is an optional keyword COUNTER which you can put before SIGNATURE, but in Books Online for SQL 2005 and SQL 2008 there is no information what this keyword means. It was added to Books Online first with SQL 2008 R2. Before that, the only place to learn about counter signatures was a a blog post from Laurenţiu Cristofor. He was one of the Program Managers for the security enhancements in SQL 2005.
  • When you counter-sign a procedure P1 with a certificate C, this has in itself no effect at all, even if permissions has been granted to a user for that certificate. But assume that there is also a procedure P2 that has been signed (and not counter-signed) with C, and that P2 calls P1. Normally, when you call an inner procedure from a signed procedure, the certificate user is removed from the user token. But when P1 is counter-signed with C, the certificate user remains in the user token. The net effect of this is that you can get the powers of P1 only if you call it through P2.
  • How could we use this? Here is one example. Assume that we have generic search procedure that in itself permits users to search all data. However, there are business rules that say that users may only see customers (or products or whatever) they have access to according to some scheme. These rules are enforced by an outer procedure that computes the values for some of the parameters to the inner procedure, thereby constraining the search. In the example below, this is extremely simple: the user may only see rows he owns. In a real-world scenario, both procedures would be far more elaborate. (Please see the introductory note for general notes on the example scripts).
  • USE master go -- Create a test login and test database. CREATE LOGIN testuser WITH PASSWORD = 'CeRT=0=TeST' CREATE DATABASE certtest go -- Move to the test database. USE certtest go -- Create the test user, and grant him permission to execute any -- stored procedure. CREATE USER testuser GRANT EXECUTE TO testuser go -- Create a test table and add some data. CREATE TABLE testtbl (a int NOT NULL, b int NOT NULL, owner sysname NOT NULL) INSERT testtbl (a, b, owner) VALUES (47, 11, 'testuser') INSERT testtbl (a, b, owner) VALUES (17, 89, 'someotheruser') go -- This is the inner procedure that permits you to view all data, -- but the selection could be constrained to a certain owner. CREATE PROCEDURE inner_sp @owner sysname = NULL AS SELECT SYSTEM_USER, USER, name, type, usage FROM sys.user_token DECLARE @sql nvarchar(MAX) SELECT @sql = N'SELECT a, b FROM testtbl WHERE 1 = 1 ' IF @owner IS NOT NULL SELECT @sql = @sql + ' AND owner = @owner' EXEC sp_executesql @sql, N'@owner sysname', @owner go -- The outer procedure which forces the owner to be the current user. CREATE PROCEDURE outer_sp AS SELECT SYSTEM_USER, USER, name, type, usage FROM sys.user_token DECLARE @owner sysname SELECT @owner = SYSTEM_USER EXEC inner_sp @owner go -- Create the certificate. CREATE CERTIFICATE examplecert ENCRYPTION BY PASSWORD = 'Being for the benefit of Mr Kite' WITH SUBJECT = 'Certificate for counter-sign example', START_DATE = '20020101', EXPIRY_DATE = '20200101' go -- Create the certificate user and grant access the test table. CREATE USER examplecertuser FROM CERTIFICATE examplecert GRANT SELECT ON testtbl TO examplecertuser go -- Sign the outer procedure. ADD SIGNATURE TO outer_sp BY CERTIFICATE examplecert WITH PASSWORD = 'Being for the benefit of Mr Kite' go -- And counter-sign the inner procedure. ADD COUNTER SIGNATURE TO inner_sp BY CERTIFICATE examplecert WITH PASSWORD = 'Being for the benefit of Mr Kite' go -- Run as the test user, to actually see that this works. EXECUTE AS USER = 'testuser' go -- First run the inner procedure directly. This gives a permission -- error. EXEC inner_sp go -- Then run the outer procedure. Now we get the data back, but -- only what we are permitted to see. EXEC outer_sp go -- Become ourselves again. REVERT go -- Clean up. USE master DROP DATABASE certtest DROP LOGIN testuser
  • True, this could also be implemented by signing inner_sp with the certificate directly, and then make sure that users does not have EXECUTE permission on this procedure, for instance with an explicit DENY. Thanks to ownership signing, users would still be able to call the inner procedure if they come from the outer procedure. But this would require you to manage two security mechanisms to achieve your goal, whereas with counter-signing you only need one.
  • Here is a second example, inspired by a newsgroup question. A poster wanted users of an application to be able to start a certain job with sp_start_job. To be able to start a job owned by someone else, you need to be member of the fixed roleSQLAgentOperatorRole in msdb. A start is to write a stored procedure that calls sp_start_job for this specific job, sign that procedure with a certificate, and then create a user from the certificate and make that user a member ofSQLAgentOperatorRole.
  • We have learnt previously that when you call a system procedure, the certificate user remains in the user token, and thus you can take benefit of the permissions granted to the certificate user. But it turns out that the procedures in msdb are not system procedures in that sense. So we need to sign sp_start_job as well, but a normal signature is not a very good idea since this would permit users to start any job. Instead we counter-sign sp_start_job with the same certificate that we sign the wrapper procedure with, and we are almost there. I found by testing that sp_start_job calls two other procedures, sp_sqlagent_notifyand sp_verify_job_identifiers, and they must be counter-signed as well.
  • I should hasten to add, that this solution is not unquestionable. Does Microsoft support signing of msdb procedures? If you install a service pack or a hotfix, you will need to reapply the signatures if Microsoft replaces the procedures with updated versions. They may also restructure the code, requiring you to counter-sign a different set of procedures.
  • Nevertheless, here is a complete script that demonstrates this technique. Note that to run it successfully, you need to have SQLServer Agent running, and you need to create a job called Testjob (which can do PRINT 'Hello world!'
  • or whatever.) As always, please see the introductory note for general notes on the example scripts. For the SQL 2012 version, please see the file jobstart-2012.sql.
  • USE master go -- Create a test login. CREATE LOGIN testuser WITH PASSWORD = 'CeRT=0=TeST' go -- Create test database. CREATE DATABASE jobstarttest go USE msdb -- Create certificate in msdb. CREATE CERTIFICATE jobstartcert ENCRYPTION BY PASSWORD = 'Strawberry Fields Forever' WITH SUBJECT = 'To permit starting the Testjob', START_DATE = '20020101', EXPIRY_DATE = '20200101' go -- Create a user for the certificate. CREATE USER jobstartcert_user FROM CERTIFICATE jobstartcert go -- Grant rights for the certificate login to run jobs. EXEC sp_addrolemember SQLAgentOperatorRole, jobstartcert_user go -- Counter-sign sp_start_job and its subprocedures. ADD COUNTER SIGNATURE TO sp_start_job BY CERTIFICATE jobstartcert WITH PASSWORD = 'Strawberry Fields Forever' ADD COUNTER SIGNATURE TO sp_verify_job_identifiers BY CERTIFICATE jobstartcert WITH PASSWORD = 'Strawberry Fields Forever' ADD COUNTER SIGNATURE TO sp_sqlagent_notify BY CERTIFICATE jobstartcert WITH PASSWORD = 'Strawberry Fields Forever' go -- Save the certificate to disk. BACKUP CERTIFICATE jobstartcert TO FILE = 'C:\temp\jobstartcert.cer' WITH PRIVATE KEY (FILE = 'C:\temp\jobstartcert.pvk' , ENCRYPTION BY PASSWORD = 'Looking through a Glass Onion', DECRYPTION BY PASSWORD = 'Strawberry Fields Forever') go -- Move to test database. USE jobstarttest go -- Create a database user for the test login. CREATE USER testuser go -- Create a procedure that starts a certain job. CREATE PROCEDURE start_this_job AS EXEC msdb..sp_start_job 'Testjob' go -- Give test user right to execute the procedure. GRANT EXECUTE ON start_this_job TO testuser go -- Import the certificate we created in msdb into the test database. CREATE CERTIFICATE jobstartcert FROM FILE = 'C:\temp\jobstartcert.cer' WITH PRIVATE KEY (FILE = 'C:\temp\jobstartcert.pvk', DECRYPTION BY PASSWORD = 'Looking through a Glass Onion', ENCRYPTION BY PASSWORD = 'Fixing a Hole') go -- Delete the files. EXEC master..xp_cmdshell 'DEL C:\temp\jobstartcert.*', 'no_output' go -- Sign the test procedures. ADD SIGNATURE TO start_this_job BY CERTIFICATE jobstartcert WITH PASSWORD = 'Fixing a Hole' go -- Switch to the test user. EXECUTE AS LOGIN = 'testuser' go -- Start the job, this succeeds. EXEC start_this_job go -- Back to ourselves. REVERT go -- Clean up. USE msdb go DROP COUNTER SIGNATURE FROM sp_sqlagent_notify BY CERTIFICATE jobstartcert DROP COUNTER SIGNATURE FROM sp_verify_job_identifiers BY CERTIFICATE jobstartcert DROP COUNTER SIGNATURE from sp_start_job BY CERTIFICATE jobstartcert DROP USER jobstartcert_user DROP CERTIFICATE jobstartcert go USE master go DROP DATABASE jobstarttest DROP LOGIN testuser
  • Instead of signing your procedure with certificate, you can use asymmetric keys. You create an asymmetric key in SQL Server with the command CREATE ASYMMETRIC KEY. The syntax is similar, but not identical, to CREATE CERTIFICATE. Please see Books Online for details.
  • From a cryptographic point of view, a certificate is an asymmetric key that has an issuer and an expiration date. Since it has an issuer, a certificate can participate in a chain of trust, which is important in for instance Service Broker dialogues. When it comes to signing stored procedures, I have (with quite some help from Razvan Socol) identified the following practical differences:
  • - An asymmetric key never expires, which for procedure-signing purposes is a slight advantage.
  • - You don't have to specify a subject for an asymmetric key.
  • - You cannot export an asymmetric key from a database. If you want to sign procedures in two databases with the same key, you could create an asymmetric key outside SQL Server and import it into the databases. (This is possible.) I will need to add the disclaimer that I have not tested whether this actually works.
  • - The fact that an asymmetric key cannot be exported, can on the other hand be seen as a security advantage, as someone cannot take your key into another database without your knowing.
  • - The key for a certificate in SQL Server is always 1024 bits, where as for an asymmetric key you can choose between 512, 1024 and 2048 bits. It's possible that there is a performance gain by using a shorter key for signing your procedures. However, I have not tested this, nor have I had it confirmed, so it's pure speculation on my part.
  • All and all, I can't find any of these points convincing enough to mandate any over the other. I have preferred to talk only about certificates in the main part of this text to simplify the presentation.
  • To see which procedures that have been signed in a database, you can run this query. crypt_type_desc will tell you whether the procedure is signed with a certificate or an asymmetric key, and whether it's regularly signed or counter-signed.
  • SELECT Module = object_name(cp.major_id), [Cert/Key] = coalesce(c.name, a.name), cp.crypt_type_desc FROM sys.crypt_properties cp LEFT JOIN sys.certificates c ON c.thumbprint = cp.thumbprint LEFT JOIN sys.asymmetric_keys a ON a.thumbprint = cp.thumbprint
  • To find the users mapped to certificates, you can use this query:
  • SELECT certname = c.name, "username" = dp.name FROM sys.certificates c JOIN sys.database_principals dp ON c.sid = dp.sid
  • In the same vein, to find logins mapped to certificates:
  • SELECT certname = c.name, loginname = sp.name FROM master.sys.certificates c JOIN sys.server_principals sp ON c.sid = sp.sid
  • (Queries for users/logins mapped to asymmetric keys are similar.)
  • If you want to find all databases where a certificate has been used, you will need to query them all, using the thumbprint and/or the subject as the key.
  • Normally passwords should be strong and kept secret, but I have already hinted that for procedure signing this may not always be necessary.
  • Let's first consider the case when you use a certificate to grant permissions on database level. What if an unauthorised user learns the password for a certificate that is used to sign one or more procedures? To be able to use the password for some evil, he would first need to have the rights to create procedures in some schema. Furthermore, to use ADD SIGNATURE he needsCONTROL permission on the certificate. In practice you would only have that permission if you are member of the db_owner role, in which case you can create your own certificates and sign procedures with them all day long. The potential threat I can see is that another database owner could borrow your keyboard while you are away, and export a certificate that gives access to some sensitive table. He could import the certificate into his database and sign a procedure that reads this data. Of course, he could just as well create a new certificate when he uses your keyboard, but if he uses an existing certificate the data theft is more likely to go unnoticed.
  • All and all, for certificates used for procedure signing on database level, the password is not your biggest secret. Nevertheless, below I present an approach that permits throw away the password altogether so that no one knows it, not even you.
  • Let's now look at using certificates to grant server-level permissions. (I am not discussing cross-database access specifically, but what I say here can be applied to cross-database access as well.) There is the plain and simple case where everyone who has db_owner rights in the user databases also are members of sysadmin or have CONTROL SERVER. This scenario is no different from database permissions: there isn't really anyone to hide the password for. All examples presented this far have been written under this assumption, since I wanted to focus on the mechanism as such. But if there are users who have db_ownerrights in a database without being sysadmin, it's a different story. Here you need to apply care.
  • Say that you are the DBA on a consolidated server and you are approached by an application admin, let's call her Anna DeMin who has db_owner rights in the database for her application. Anna has written the procedure reload_sp and wants you to sign it. To this end, you first review her code to ensure that she reads from the directory allotted for her application. You can then follow the steps outlined in the example script we saw previously.
  • In this situation, you need to make sure that Anna's does not learn the password for the certificate that you create in her database, since else she could change the procedure to read from somewhere else. You also need to hide the password for the private key, or delete it from disk directly so she cannot import it. Here is much of the beauty with certificates: as a server DBA, you can have full control over what permissions you have granted to user databases and to what code. To do that, you need to be able to manage your passwords.
  • Now, if you are a server DBA, I can hear you say that you don't have the time for all this, and you trust your application admins, so you will give Anna the cert and the password, and that's that. Of course, if you trust your colleagues that's great, but no matter whether you do or not, I have a script for you that permits you to automate most of this process. The one step I cannot automate for you is the code review.
  • The key points of the script.
  • - There is one certificate for each procedure you sign. Every time you need to re-sign a procedure after a change, the script throws the old certificate away and creates a new one. All certificates have names that start with SIGN followed by the fully qualified name of the signed procedure. The subject also includes the permissions granted. Thus, you can easily review which permissions you have granted by querying sys.certificates in master. Logins have the same names as the certificates.
  • - The password are GUIDs (with some extra characters to make sure that they pass the complexity rules enforced by Windows), and they are used only for the duration of the script and not saved.
  • - When the procedure has been signed, the private key is removed from the certificate with ALTER CERTIFICATE cert REMOVE PRIVATE KEY
  • . Once the private key has been removed, the certificate is only good for validation, but cannot be used to sign any new procedures.
  • - The script does not grant database-level permissions. In the bulk-load example, we granted INSERT and ALTER permissions on the target table. The application admin needs to create a separate certificate for this. Keep in mind that a procedure can be signed by more than one certificate.
  • The script does have any support for counter-signatures, but if you need this, you could extend the script for this purpose.
  • The script consists of two parts. The first part is the setup part, where you need define three things: 1) The target database. 2) The stored procedure (or function or trigger) to sign. 3) The server-level permission(s) to grant. Everything below the line with======
  • is the fixed part that you normally don't have to change. (Why is this a script and not a stored procedure? I wrote it as a script, because I figure that if you administer many servers, it is better to have as script on disk than installing a stored procedure on every server. Particularly, if you change the script by time, it's good to have a single copy of it.)
  • Here are all the steps the script takes. Note that if there is an error, the script aborts on the spot.
  • - Validate and normalise database and procedure names. This is to make sure that the script always generates the same name for the certificate, even if you use different case or is inconsistent with specifying the schema.
  • - Generate the name, subject and password for the certificate.
  • - If a login with the certificate name exists, drop it.
  • - Drop any old certificate in master.
  • - If the procedure is signed with the old certificate, remove the signature.
  • - As a safety precaution, remove any user created from the certificate in the target database.
  • - Drop the certificate in the target database, if it exists there.
  • - Create the new certificate in master.
  • - Create a login from the certificate.
  • - Grant permissions to the login.
  • - Export the certificate.
  • - Import the certificate in the target database.
  • - If xp_cmdshell is enabled, delete the certificate files. (Else you will need to delete them manually; they are located in the same directory as the master database.)
  • - Sign the procedure.
  • - Remove the private key from the certificate, both in the target database and in master.
  • If you want to test the script, you can use the bulk-load example above with some modifications: remove the certificate handling in master, and change CREATE CERTIFICATE in the user database to create a local certificate. Keep in mind that you still need to sign the procedure to grant ALTER and INSERT permissions on the table. Once you have run the script below, you can run theEXECUTE AS part in the bulk-load script to verify that the test user have all permissions. You find such a prepared version of the bulk-load example in the file grantrights-test.sql, instructions are included.
  • -- This script takes it base in the master database. USE master go DECLARE @procname nvarchar(260), @database sysname, @perms nvarchar(4000), @sp_executesql nvarchar(150), @certname sysname, @username sysname, @subject nvarchar(4000), @pwd char(39), @sql nvarchar(MAX), @filename nvarchar(1024), @cmd varchar(1024), @debug bit -- Set up parameters: the procedure to sign and the database it belongs to. SELECT @procname = 'reload_sp', @database = 'bulkcerttest' -- The permissions to grant through the certificate. Set NULL if you only -- want to remove current signature. SELECT @perms = 'ADMINISTER BULK OPERATIONS' -- Run with debug or not? SELECT @debug = 1 --============================ END OF SETUP ========================== -- A big TRY-CATCH block around everything to abort on first error. BEGIN TRY -- First verify that the database exists. IF db_id(@database) IS NULL RAISERROR('Database %s does not exist', 16, 1, @database) -- Make sure that database name is quoted and appears exactly as in sys.databases. SELECT @database = quotename(name) FROM sys.databases WHERE name = @database -- We will call sp_executesql a number of times in the target database. SELECT @sp_executesql = @database + '.sys.sp_executesql' -- Next we verify that the procedure exists and make sure that -- we have a normalised quoted name. We need to run a query in the -- target database. SELECT @sql = 'SELECT @procname = MIN(quotename(s.name) + ''.'' + quotename(o.name)) FROM sys.objects o JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE o.object_id = object_id(@procname)' IF @debug = 1 PRINT @sql EXEC @sp_executesql @sql, N'@procname nvarchar(260) OUTPUT', @procname OUTPUT IF @procname IS NULL RAISERROR('No procedure with the given name in database %s', 16, 1, @database) -- Construct name, subject and password for the certificate. SELECT @certname = 'SIGN ' + @database + '.' + @procname, @subject = 'Signing ' + @database + '.' + @procname + ' for ' + @perms, @pwd = convert(char(36), newid()) + 'Aa0' -- If a login exists for the cerficiate, we drop it IF EXISTS (SELECT * FROM sys.server_principals WHERE name = @certname AND type = 'C') BEGIN SELECT @sql = 'DROP LOGIN ' + quotename(@certname) IF @debug = 1 PRINT @sql EXEC (@sql) END -- And drop the certificate itself. IF EXISTS (SELECT * FROM sys.certificates WHERE name = @certname) BEGIN SELECT @sql = 'DROP CERTIFICATE ' + quotename(@certname) IF @debug = 1 PRINT @sql EXEC(@sql) END -- In the target database, we must remove the signature from the procedure, -- so that we can drop the certificate. SELECT @sql = ' IF EXISTS (SELECT * FROM sys.crypt_properties cp JOIN sys.certificates c ON cp.thumbprint = c.thumbprint WHERE cp.major_id = object_id(@procname) AND c.name = @certname) DROP SIGNATURE FROM ' + @procname + ' BY CERTIFICATE ' + quotename(@certname) IF @debug = 1 PRINT @sql EXEC @sp_executesql @sql, N'@certname sysname, @procname nvarchar(260)', @certname, @procname -- No user should have been created from the cert, but if so, we drop it. -- Since this may been performed by some else, we cannot trust the username -- to be the same as the certificate name. SELECT @sql = ' SELECT @username = NULL SELECT @username = dp.name FROM sys.database_principals dp JOIN sys.certificates c ON dp.sid = c.sid WHERE c.name = @certname' IF @debug = 1 PRINT @sql EXEC @sp_executesql @sql, N'@certname sysname, @username sysname OUTPUT', @certname, @username OUTPUT IF @username IS NOT NULL BEGIN SELECT @sql = 'DROP USER ' + quotename(@username) IF @debug = 1 PRINT @sql EXEC @sp_executesql @sql END -- And here goes the old cert. SELECT @sql = ' IF EXISTS (SELECT * FROM sys.certificates WHERE name = @certname) DROP CERTIFICATE ' + quotename(@certname) IF @debug = 1 PRINT @sql EXEC @sp_executesql @sql, N'@certname sysname', @certname IF @perms IS NULL PRINT 'No new permissions set, cleanup completed.' ELSE BEGIN -- Now we start to (re)create things. First create the certificate in master. SELECT @sql = 'CREATE CERTIFICATE ' + quotename(@certname) + ' ENCRYPTION BY PASSWORD = ''' + @pwd + ''' WITH SUBJECT = ''' + @subject + ''', START_DATE = ''20020101'', EXPIRY_DATE = ''20200101''' IF @debug = 1 PRINT @sql EXEC(@sql) -- And the login for the certificate. SELECT @sql = 'CREATE LOGIN ' + quotename(@certname) + ' FROM CERTIFICATE ' + quotename(@certname) IF @debug = 1 PRINT @sql EXEC(@sql) -- Grant the permissions. SELECT @sql = 'GRANT ' + @perms + ' TO ' + quotename(@certname) IF @debug = 1 PRINT @sql EXEC(@sql) -- Determine a path to where we can write the files for the certs. SELECT @filename = substring(physical_name, 1, len(physical_name) - charindex('\', reverse(physical_name)) + 1) + convert(char(36), newid()) FROM sys.database_files WHERE file_id = 1 -- And backup up the certificate to disk. SELECT @sql = ' BACKUP CERTIFICATE ' + quotename(@certname) + ' TO FILE = ''' + @filename + '.cer' + ''' WITH PRIVATE KEY (FILE = ''' + @filename + '.pvk' + ''', ENCRYPTION BY PASSWORD = ''' + @pwd + ''', DECRYPTION BY PASSWORD = ''' + @pwd + ''')' IF @debug = 1 PRINT @sql EXEC(@sql) -- And then restore in the target database. SELECT @sql = ' CREATE CERTIFICATE ' + quotename(@certname) + ' FROM FILE = ''' + @filename + '.cer' + ''' WITH PRIVATE KEY (FILE = ''' + @filename + '.pvk' + ''', ENCRYPTION BY PASSWORD = ''' + @pwd + ''', DECRYPTION BY PASSWORD = ''' + @pwd + ''')' IF @debug = 1 PRINT @sql EXEC @sp_executesql @sql -- If possible, delete the certs from disk. SELECT @cmd = 'DEL "' + @filename + '.*"' IF (SELECT value_in_use FROM sys.configurations WHERE name = 'xp_cmdshell') = 1 BEGIN IF @debug = 1 PRINT @cmd EXEC xp_cmdshell @cmd END ELSE BEGIN PRINT '******** xp_cmdshell disabled, you need run this command manually' PRINT @cmd END -- We can now sign the procedure. SELECT @sql = 'ADD SIGNATURE TO ' + @procname + ' BY CERTIFICATE ' + quotename(@certname) + ' WITH PASSWORD = ''' + @pwd + '''' IF @debug = 1 PRINT @sql EXEC @sp_executesql @sql -- Finally, drop the private key of the cert from the databases. SELECT @sql = 'ALTER CERTIFICATE ' + quotename(@certname) + ' REMOVE PRIVATE KEY' IF @debug = 1 PRINT @sql EXEC (@sql) SELECT @sql = 'ALTER CERTIFICATE ' + quotename(@certname) + ' REMOVE PRIVATE KEY' IF @debug = 1 PRINT @sql EXEC @sp_executesql @sql END END TRY BEGIN CATCH DECLARE @msg nvarchar(4000) SELECT @msg = error_message() RAISERROR(@msg, 16, 1) END CATCH
  • Before we move on, I like to point out a few virtues for dynamic SQL, even if they are not directly related to the topic of this article:
  • - Before all execution of dynamic SQL, I have a debug PRINT, so I can inspect the statement in case of an error.
  • - To run dynamic SQL in the target database, I make use of that you can run a system procedure in a different database by using three-part notation, and that EXEC accepts a variable for the procedure name.
  • - I consistently use quotename() to avoid syntax errors if there is a special character in an object name. For the database and procedure I apply the brackets once for all, whereas for the certificate name I do it every time I need to. (Since I also use the cert name in queries.)
  • - I fail on one point though: if the database or procedure name would include a a single quote, there would be a syntax error when creating the certificate because of the subject.
  • There is a version for SQL 2012 of this script in the file grantrights-2012.sql. It is worth dwelling on the piece where the certificate is copied for a second:
  • -- Copy the certificate to the target database. SELECT @sql = ' CREATE CERTIFICATE ' + quotename(@certname) + ' FROM BINARY = ' + convert(varchar(MAX), certencoded(cert_id(quotename(@certname))), 1) + ' WITH PRIVATE KEY (BINARY = ' + convert(varchar(MAX), certprivatekey(cert_id(quotename(@certname)), @pwd, @pwd), 1) + ', ENCRYPTION BY PASSWORD = ''' + @pwd + ''', DECRYPTION BY PASSWORD = ''' + @pwd + ''')' IF @debug = 1 PRINT @sql EXEC @sp_executesql @sql
  • Since the script is a single batch, there is no need for temp tables. Instead, I copy the certificate in a single group of statements. The above three statements replaces no less than four groups of statements in the script above. Observe also that in the call tocert_id, I apply qoutename on @certname. This is required, since the name of the certificates includes brackets.
  • We will now turn to the third method in SQL Server to provide permission through stored procedures: the EXECUTE AS clause. On the surface, EXECUTE AS is much simpler to use than certificates, but as it works through impersonation, there are side effects which may be unacceptable. We will also see that for granting server-level permissions, EXECUTE AS is inappropriate in environments where there are users who have full permissions on database level, but not on server-level.
  • EXECUTE AS is two things. It is a clause that you can add to a stored procedure or any other SQL module, and that is what you can use to grant permissions to non-privileged users. There is also a statement EXECUTE AS, and we will look at the statement before we turn to the clause.
  • The statement EXECUTE AS permits you to switch your execution context to impersonate another login or user. Here are examples of the two possibilities:
  • EXECUTE AS LOGIN = 'somelogin' EXECUTE AS USER = 'someuser'
  • Once you want to become your original self, you use the REVERT statement. (If you have changed databases, you will first need to return to the database where you issued the EXECUTE AS statement.) If the EXECUTE AS statement is executed in a lower-level scope – that is, in a stored procedure or a batch of dynamic SQL – there is an implicit REVERT when the scope exits. Thus if you run:
  • EXEC('EXECUTE AS LOGIN = ''frits''; SELECT SYSTEM_USER') SELECT SYSTEM_USER
  • the second SELECT will not return frits, but your own login name.
  • To perform EXECUTE AS you need IMPERSONATE rights on the login/user in question. (This permission is implied on all logins if you have sysadmin rights and on all users in a database where you have db_owner rights.)
  • As an extra thrill, you can stack EXECUTE AS, so you could first become login1, then user2 etc. Each REVERT would take you back to the previous context. This would require each login/user to have impersonation rights on the next login/user in the chain.
  • There are two apparent uses for the EXECUTE AS statement:
  • - A privileged user can use EXECUTE AS to test queries and procedures as another user, without having to open a new query window. This can be very handy, and all example scripts in this article use EXECUTE AS for this purpose.
  • - To implement "application proxies". In this case, the application authenticates the users outside the server. The application connects to the server with a proxy login that has IMPERSONATE rights on the real users and then issues EXECUTE AS to run as them. When you create a user in SQL 2005, you can specify the clause WITHOUT LOGIN to create a user that exists in the database only. Thus, you can implement a solution where the real users do not need any sort of direct access to SQL server.
  • In the latter case, the application should add the clause WITH NO REVERT or WITH COOKIE to the EXECUTE AS statement. Else a malicious user could inject a REVERT statement and gain the rights of the proxy login. (As this goes a little beyond the scope for this article, I refer you to Books Online for further details.)
  • If you use EXECUTE AS LOGIN this is exactly the same as if you had logged into SQL Server as that user directly. You will have the permissions of that login, you can access the databases that login can access and so on. I have not been able to detect any difference at all, save for the function original_login() that I will return to.
  • If you use EXECUTE AS USER it is a little different. As long as you only run commands within the database, it is just as if you had logged in as that user. But if you try to access another user database you will get an error message, and if you try to perform some server-level action like sp_who you will only get back a minimum of data, even if this user maps to a login in the sysadminrole. When you impersonate a database user you are by default sandboxed into that database. We will look more into this later. For now, I say that if you use EXECUTE AS to test permissions, you should in most cases use EXECUTE AS LOGIN. The exception is when you are testing access rights for users that are purposely created WITHOUT LOGIN.
  • I should also mention that there is an impersonation shortcut for the EXECUTE() command, so that you can say:
  • EXECUTE(@somesql) AS LOGIN = 'somelogin' EXECUTE(@somesql) AS USER = 'someuser'
  • The purpose of this is the same as for the EXECUTE AS statement; for a high-privileged user to impersonate a low-privileged user.
  • Before I move on, I should mention that there is an older command SETUSER which also can be used for impersonation. The semantics for SETUSER are less clear than for EXECUTE AS, and SETUSER is deprecated. If you are still using SETUSER, there is all reason to change to EXECUTE AS.
  • Note: When impersonating a Windows user, it's a common mistake to put the name in brackets, but this does not work and results in somewhat cryptic error message. That is, it should be EXECUTE AS 'Domain\User'
  • , not EXECUTE AS '[Domain\User]'
  • .
  • So far the statement EXECUTE AS. We will now look at the clause WITH EXECUTE AS you can add to your stored procedure. As for certificates, we will first look at using the EXECUTE AS clause to give users rights for actions within the database, and as with certificates we will use dynamic SQL as our example.
  • To repeat, these were the presumptions for the dynamic SQL example:
  • CREATE TABLE testtbl (a int NOT NULL, b int NOT NULL) go CREATE PROCEDURE example_sp AS EXEC ('SELECT a, b FROM testtbl') go GRANT EXECUTE ON example_sp TO public go
  • As we saw earlier, ownership chaining does not work here. To use EXECUTE AS to make it possible for users to run example_spwithout SELECT permission on testtbl, the steps to take are:
  • - Create a proxy user.
  • - Grant the proxy user the necessary permissions.
  • - Add the EXECUTE AS clause to the stored procedure.
  • In code, it looks like this:
  • -- Create a proxy user. CREATE USER exampleproxy WITHOUT LOGIN -- Give it permissions on the table. GRANT SELECT ON testtbl TO exampleproxy go -- Add EXECUTE AS to the procedure. CREATE PROCEDURE example_sp WITH EXECUTE AS 'exampleproxy' AS EXEC ('SELECT a, b FROM testtbl') go
  • Since the sole purpose for this user is to carry permissions, we create the user WITHOUT LOGIN. As for what rights to grant to the proxy user, the discussion in the section Granting Rights to the Certificate User applies here as well: only grant the permissions needed.
  • The effect of the EXECUTE AS clause is the same as of the EXECUTE AS USER statement: that is, impersonation. As with certificates, the user gets the rights of exampleproxy, but there are two important differences: 1) It's not that the rights of the proxy user are added to your rights, but you are John Malkovich. 2) If there is a call to an inner stored procedure or a trigger fires, you are not reverted back to your original self; you continue to execute in the context of the proxy user. It is not until you exit the stored procedure with the EXECUTE AS clause that you return to your true self.
  • This can have drastic and far-reaching consequences, which we shall look into in a moment. First though, a complete script that shows the use EXECUTE AS to grant permissions for dynamic SQL. (Again, please refer to the introductory note about the example scripts in this article):
  • USE master go -- Create a test login. CREATE LOGIN testuser WITH PASSWORD = 'ExECaS=0=TeST' go -- Create the database to run the test in. CREATE DATABASE execastest go USE execastest go -- Create the test user. CREATE USER testuser go -- Create the test table. CREATE TABLE testtbl (a int NOT NULL, b int NOT NULL) INSERT testtbl (a, b) VALUES (47, 11) go -- Create a proxy user and give it rights to access the test table. CREATE USER exampleproxy WITHOUT LOGIN GRANT SELECT ON testtbl TO exampleproxy go -- Create two test stored procedures, one with EXECUTE AS and one -- without, and grant permission. CREATE PROCEDURE noexecas_sp AS SELECT SYSTEM_USER, USER, name, type, usage FROM sys.user_token EXEC ('SELECT a, b FROM testtbl') go CREATE PROCEDURE example_sp WITH EXECUTE AS 'exampleproxy' AS SELECT SYSTEM_USER, USER, name, type, usage FROM sys.user_token EXEC ('SELECT a, b FROM testtbl') EXEC noexecas_sp go GRANT EXECUTE ON example_sp TO public GRANT EXECUTE ON noexecas_sp TO public go -- Switch to the test user. EXECUTE AS LOGIN = 'testuser' go -- First run the procedure without EXECUTE AS. This gives a permission -- error. EXEC noexecas_sp go -- Then the signed procedure with EXECUTE AS. Now get the data back. EXEC example_sp go -- Become ourselves again. REVERT go -- Clean up USE master DROP DATABASE execastest DROP LOGIN testuser
  • This is similar to the script for certificates, but you will notice that the outcome is different. When the test user runsnoexecas_sp directly, he gets a permission error as expected. But when example_sp calls noexecas_sp, there is no permission error, as was the case when we used a certificate. And when we look at the output from sys.user_token we see why. When noexecas_sp is called directly, we get:
  • SYSTEM_USER USER name type usage ------------- ------------ ---------- --------- -------------- testuser testuser testuser SQL USER GRANT OR DENY testuser testuser public ROLE GRANT OR DENY
  • But when noexecas_sp is called from example_sp, we see this:
  • SYSTEM_USER USER name type usage --------------- ----------- ----------- --------- -------------- S-1-9-3-2024... exampleproxy exampleproxy SQL USER GRANT OR DENY S-1-9-3-2024... exampleproxy public ROLE GRANT OR DENY
  • As you see, there is no trace of testuser. (The data in the column for SYSTEM_USER is due to that exampleproxy was createdWITHOUT LOGIN. In lieu of a login name, SYSTEM_USER returns the SID.)
  • With certificates, the permissions of the certificate user are added to the rights of the current user. This means that if there is some basic permission granted to everyone, say SELECT permission in a certain schema, you don't have to grant that permission to the certificate user. For a proxy user for EXECUTE AS you must grant all permissions needed. But this cuts both ways. Recall that certificates do not help when users have been explicitly denied permission, since DENY takes precedence over GRANT. This limitation does not exist with EXECUTE AS, since it's only the permissions of the proxy user that count.
  • SQL Server has a couple of functions that returns the current login or user: SYSTEM_USER, SESSION_USER, USER, user_name(),suser_sname() and a few more. All these are affected by the EXECUTE AS clause: instead of returning the current login/user, they return the login or user of the identity in the EXECUTE AS clause.
  • Now, where do you use these functions? I can think of two of very typical cases.
  • - In the WHERE clause of a view or stored procedure for row-level security.
  • - To fill in the values of auditing columns, through a DEFAULT constraint or a trigger or directly in a stored procedure.
  • When you use EXECUTE AS both these schemes break. Code that implements row-level security will return no data, or even worse, data that the real user does not have permission to see. Auditing will be useless, as all updates will appear to come from the same user.
  • Had the effect been constrained only to the very procedure with the EXECUTE AS clause, it could have been somewhat manageable. But since the impersonation lingers when other SQL modules are invoked, for instance triggers, this means that code that are not aware of the EXECUTE AS clause, will cease to work. Now, how is that for backwards compatibility?
  • Another side effect concerns existing code. Say that a procedure with EXECUTE AS calls an existing stored procedure old_sp, and this procedure makes some assumptions of what rights the current user (= the user behind the keyboard) has. For instance, it could use the built-in functions is_member() or permissions() to determine whether a user is entitled to see some data or whether some special action should be taken. When called from a procedure with EXECUTE AS, old_sp will draw the wrong conclusions.
  • There are also concerns for the DBA who likes to monitor his system with help of Profiler and various DMVs, that I will look into separately a little later.
  • What can you do to mitigate these consequences? We will look at four different possibilities: 1) EXECUTE AS CALLER, 2)original_login(), 3) SET CONTEXT_INFO and 4) DDL triggers. You will find none of these measures address the issues very satisfactorily. The first only solves a minor part of the problem and the next two require you to rewrite existing code. The last method performs a solid job – by outlawing the feature altogether.
  • Before looking into the methods above, we need to look at the EXECUTE AS clause in full, to see its full powers – or I am tempted to say its full horrors.
  • Rather than specifying an explicit user in the EXECUTE AS clause, you can specify any of the keywords CALLER, OWNER and SELF.
  • CALLER is innocent. This means that the procedure should execute in the context of the calling user. That is, how stored procedures how normally work, so EXECUTE AS CALLER is merely a way of explicitly expressing the default.
  • EXECUTE AS SELF is short for EXECUTE AS 'yourusername'. That is, if you create a procedure and add WITH EXECUTE AS SELF to it, anyone who runs the procedure will execute with your permissions. (And anything they update, you will be held accountable for.)
  • EXECUTE AS OWNER, finally, means that the procedure executes in the context of the procedure owner. As I discussed in thebeginning of the article this is normally the schema owner. Thus, if the procedure is created in the dbo schema, or any other schema owned by the database owner, the procedure will execute with permissions to do anything in the database!
  • Here are some serious implications. If all you care about is simplicity, then you can ignore all about creating proxy users and granting them permissions. All you need to do is:
  • CREATE PROCEDURE example_sp WITH EXECUTE AS OWNER AS --SELECT SYSTEM_USER, USER, name, type, usage FROM sys.user_token EXEC ('SELECT a, b FROM testtbl') go
  • And no more permissions problems!
  • But remember that philosophy about multiple lines of defence in the beginning of this text. As we discussed for certificates, by using a dedicated proxy user you add one more line of defence, so if your procedure would be open for SQL injection, an exploiter can only do a limited amount of harm. On the other hand, if you use EXECUTE AS OWNER, the database will be wide open to an intruder. (Access outside the database is another matter, that we will come back to.) Again, keep in mind that even if your use of dynamic SQL is tight and free from injection vulnerabilities, someone who modifies the procedure tomorrow may make a blunder and change that.
  • Note here also a possible fatal consequence for a row-level security scheme. It is not unlikely that such scheme is set up so thatdbo is permitted see all rows. This means that casual use of EXECUTE AS can result int users having access to data they don't have permission to see.
  • If you are the DBA (or at least the database owner) and are fortunate to have full control of all code that is added to the database (because you write all the code, or at least review all of it), it is only up to you. But if you are responsible for a larger application with many stored procedures, contributed by many developers, be afraid, be very afraid. One day you find that your auditing records say that a lot of data was changed by dbo, instead of the actual user. Some developer ran into an urgent problem with his dynamic SQL, posted a question on the forums and quickly learnt the four magic words WITH EXECUTE AS OWNER. His problems were solved, but yours had only just begun.
  • We will now look into what methods you can use to reduce the impact of the EXECUTE AS clause.
  • It's possible to do this in a procedure with an EXECUTE AS clause:
  • CREATE PROCEDURE some_sp WITH EXECUTE AS 'proxyuser' AS DECLARE @realuser sysname EXECUTE AS CALLER SELECT @realuser = SYSTEM_USER REVERT -- Do whatever requires extra privileges go
  • That is, with the EXECUTE AS CALLER statement, you revert to the context of the caller, and you can find out who actually called the procedure. Provided, that is, there were no impersonation on upper levels.
  • If the procedure is a longer one, and there is only one action that needs special privileges, for instance dynamic SQL, you can even do:
  • CREATE PROCEDURE someother_sp WITH EXECUTE AS 'proxyuser' AS DECLARE ... EXECUTE AS CALLER ... -- Here we need the powers of the proxy user REVERT EXEC sp_executesql @sql, ... -- Or something else which needs privs. EXECUTE AS CALLER -- Rest of the procedure
  • While this certainly is recommendable from the philosophy of not using more permissions than necessary, it takes more effort than just adding the EXECUTE AS clause in the beginning and run with it. It would be more reasonable to write:
  • CREATE PROCEDURE someother_sp AS DECLARE ... ... -- Here we need the powers of the proxy user EXECUTE AS USER = 'proxyuser' EXEC sp_executesql @sql, ... -- Or something else which needs privs. REVERT -- Rest of the procedure
  • Alas, this does not work An unprivileged user will get a permission error, as the rights to impersonate someone can not be given to a user through the body of a stored procedure, only the header. (Of course, by signing the procedure with a certificate you can grant that permission, but if you use certificates, you don't really need EXECUTE AS at all.)
  • There are many situations where EXECUTE AS CALLER does not help. If that dynamic SQL accesses a view with row-level security, it does not help to save the real user's name into a variable, as the call to SYSTEM_USER (or similar) is in the text of the view itself. The same applies if the dynamic SQL performs an update, and the auditing is based on a trigger or a default constraint. Furthermore, if a procedure sp1 with an EXECUTE AS clause calls sp2, sp2 cannot use EXECUTE AS CALLER to set its context to the caller of sp1, as the caller to sp2 is the user in the EXECUTE AS clause in sp1.
  • On top of that, EXECUTE AS CALLER requires a conscious action from the programmer. Someone who just heard about EXECUTE AS OWNER on the forums is not going to get through that extra hoop.
  • While SYSTEM_USER, USER, user_name() etc all are affected by EXECUTE AS, there is one function that returns the login that originally connected to SQL Server: original_login().
  • Thus, anywhere you have schemes for row-level security or code for auditing it's better to use original_login() rather thanSYSTEM_USER to be protected against the risk that EXECUTE AS leads to incorrect auditing or users getting access to data they are not entitled to see. At least as long as you are not using "application proxies", something I will return to in the next section.
  • If your row-level security and auditing schemes are based on the username in the database rather than the server-level login name, you are likely to ask for an original_user() only to find that there isn't such a function. In this case you will have to rework your scheme to use logins instead.
  • Why isn't there any original_user()? Actually, there is a good reason. Things get complicated with cross-database access. Say that a procedure sp1 in database A has an EXECUTE AS clause for user1, and sp1 invokes sp2 in database B to which user1 has access. sp1 is invoked by user2 that maps to login2, but login2 has no access to database B. Say now that sp2 calls this fictive original_user(), what should it return? user2 is flat wrong in the given context. NULL? Are your auditing columns nullable? Mine aren't.
  • If you are really paranoid and want to make sure that your procedure are not run with elevated privileges because the calling procedure has an EXECUTE AS clause, you could add this test to the beginning of your procedure:
  • IF SYSTEM_USER original_login() BEGIN RAISERROR('This procedure does not support impersonated users', 16, 1) RETURN 1 END
  • original_login() works as long as the users themselves log into SQL Server with their personal login. But consider the case of an "application proxy". That is, the application authenticates users outside SQL Server, and the proxy login issues EXECUTE AS (orSETUSER for a legacy application) on the behalf of the actual user. Guess what original_login() will return in this case? That's right, the name for the application's proxy login. Not a very useful piece of information. While SQL 2005 was still in beta, I submitted a Connect item that asked for a way to retrieve the full impersonation stack to address this situation. It hasn't happened yet.
  • One possible way out here is the command SET CONTEXT_INFO and the context_info() function. SET CONTEXT_INFO was added already in SQL 2000, but it may not be widely known. It sets a binary value of 128 bytes that you can retrieve with thecontext_info() function.
  • Here is how you would use it. When connecting for a user, the application would do something like:
  • DECLARE @contextinfo varbinary(128) SELECT @contextinfo = convert(varbinary(128), N'JoeCool') SET CONTEXT_INFO @contextinfo
  • A table with an auditing column could look like this:
  • CREATE TABLE audited (somedata int NOT NULL, moduser sysname NOT NULL CONSTRAINT def_moduser DEFAULT coalesce(convert(nvarchar(64), substring(context_info(), 1, charindex(0x0000, context_info()) - 1)), original_login()) ) go
  • The expression to get data from context_info() is surprisingly complex; this is because context_info() returns binary(128), so we need to strip the trailing zeroes. Despite the name, charindex() works on binary data too. We must specify 0x0000 to find where the zeroes start, since with nvarchar, every second byte is often 0 for data using the Latin alphabet.
  • On top of that, we use coalesce() with original_login() as a second argument to have a fallback alternative, in case SET CONTEXT_INFO never was issued, for instance because the action was performed by an administrator who logged in directly toSQL Server from SQL Server Management Studio.
  • I feel obliged to point out that the solution with SET CONTEXT_INFO is not entirely secure. If there are SQL injection holes in the application, a malicious user could inject a SET CONTEXT_INFO command to hide his identity. This could permit him to do actions anonymously, and to access data from row-level security schemes that he should not see.
  • One more thing to add about SET CONTEXT_INFO: normally the effect of a SET statement issued in a stored procedure is reverted when the procedure exits. SET CONTEXT_INFO is an exception to this rule, and the effect of SET CONTEXT_INFO is always global to the connection.
  • If you are a DBA who is not in the position that you can review all code that is deployed into the database (or a lead programmer/database architect who cannot review all code that is checked into the version-control system) and you are scared of the damage that EXECUTE AS could cause to your application, you may ask: is there a way to stop all this? After all, there is no need to use EXECUTE AS to grant permissions, when you can use certificates without side effects.
  • Microsoft touts SQL Server as "secure by default", so you would expect a knob to control whether the EXECUTE AS clause is available, and you would expect that knob to be in the OFF position by default. Not so. There is no knob at all. But you can implement your own.
  • If you are the permissive sort of person, you may be content to every once in a while run:
  • SELECT module = object_name(object_id), execute_as = CASE m.execute_as_principal_id WHEN -2 THEN 'OWNER' ELSE d.name END FROM sys.sql_modules m LEFT JOIN sys.database_principals d ON m.execute_as_principal_id = d.principal_id WHERE m.execute_as_principal_id IS NOT NULL
  • This displays which modules have been decorated with the EXECUTE AS clause and with which user name.
  • If you are the more evil sort of person, then you can put this DDL trigger in place:
  • CREATE TRIGGER stop_execute_as ON DATABASE FOR CREATE_PROCEDURE, ALTER_PROCEDURE, CREATE_FUNCTION, ALTER_FUNCTION, CREATE_TRIGGER, ALTER_TRIGGER AS DECLARE @eventdata xml, @schema sysname, @object_name sysname, @object_id int, @msg nvarchar(255)
  • -- Get the schema and name for the object created/altered. SELECT @eventdata = eventdata() SELECT @schema = C.value(N'SchemaName[1]', 'nvarchar(128)'), @object_name = C.value(N'ObjectName[1]', 'nvarchar(128)') FROM @eventdata.nodes('/EVENT_INSTANCE') AS E(C)
  • -- Find its object id. SELECT @object_id = o.object_id FROM sys.objects o JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE o.name = @object_name AND s.name = @schema
  • -- If we don't find it, it may be because the creator does not have -- have permission on the object. (Yes, this can happen.) IF @object_id IS NULL BEGIN SELECT @msg = 'Could not retrieve object id for [%s].[%s], operation aborted' RAISERROR(@msg, 16, 1, @schema, @object_name) ROLLBACK TRANSACTION RETURN END
  • -- Finally check that the catalog views whether the module has any -- EXECUTE AS clause. IF EXISTS (SELECT * FROM sys.sql_modules WHERE object_id = @object_id AND execute_as_principal_id IS NOT NULL) BEGIN ROLLBACK TRANSACTION SELECT @msg = 'Module [%s].[%s] has an EXECUTE AS clause. ' + 'This is not permitted in this database.' RAISERROR (@msg, 16, 1, @schema, @object_name) RETURN END go
  • The trigger first retrieves the schema and object names for the created object from the eventdata() function. This function returns an XML document, and we use XQuery to extract the data we need. Next we translate the object name to an id. We check that we are actually able to do this. (Since the owner of a procedure is the schema owner, it is possible to have a user that is permitted to create a procedure without being permitted to see the definition of it.) Finally there is the check that the module does not have any EXECUTE AS.
  • Variations of this theme include checking execute_as_principal_id for -2 (OWNER) and power users, or permit EXECUTE AS if the proxy user does not map to a login. (That is, a user created WITHOUT LOGIN.)
  • Would anyone be this evil? Well, if you have an auditing scheme that relies on SYSTEM_USER or similar function, and you don't want to rewrite your code right now, do you have any choice?
  • Note: If you are on SQL 2008, you may ask if this could be implemented with Policy-Based Management. It probably can, but I would not recommend that use you the On Prevent option in PBM, as PBM may silently decide to turn off checking if it deems your conditions to be too complex. (See this Connect bug for details.) Possibly you could use PBM to monitor the use of EXECUTE AS.
  • EXECUTE AS and Monitoring

  • As I mentioned, EXECUTE AS also has implications for the DBA who likes to monitor his system. Say that there is a procedure which has the heading:
  • CREATE PROCEDURE some_sp WITH EXECUTE AS 'proxyuser' AS
  • Say that the user Nisse runs this procedure, and there is a trace with captures the statements in this procedure. What will the column LoginName display? That depends. If proxyuser was created from a login with the same name, the value in LoginNamewill be proxyuser. If proxyuser was created WITHOUT LOGIN, the value will be a SID, that is, a value starts like S-1-9-3-913356... But in no case the name Nisse will be displayed.
  • This has some ugly ramifications. If you commonly apply filters on LoginName, EXECUTE AS can cause users to fall off the radar for the duration of the procedure with the clause. If you rely on tracing for auditing, EXECUTE AS can also result in the wrong person being credited/blamed for a certain action.
  • LoginName is not the only column that is affected, but also the column NTUserName, although this column does not always change. It changes if the impersonated user is a Windows user or a user created WITHOUT LOGIN, but not if the user is created from an SQL Server login. At least, that is what my quick testing indicates.
  • This also extends do DMVs like sys.dm_exec_sessions. The columns login_name and nt_user_name behaves likeLoginName and NTUserName in Profiler and reflect the name of the impersonated user. The same is true for sysprocessesetc.
  • Thankfully, there are alternatives. In Profiler you can use the column SessionLoginName. The value in this column corresponds to the value returned by the function original_login() and thus it will never change during the lifetime of the connection. The column is not visible by default, but you have to check the box Show all columns to find it. (Why SessionLoginName is not visible by default, while LoginName is? As I recall, SessionLoginName was added in SP2 of SQL 2005, and I guess Microsoft did not want to meddle with the existing templates.) You could define your own template, so that you don't have to remember to add it every time.
  • Likewise, in sys.dm_exec_sessions there is the column original_login_name, which is one of the columns at end of the table; it was added in SP2 of SQL 2005. In sysprocesses, there is no value corresponding to original_login(), but sysprocesses is a compatibility view, which Microsoft prefers us not to use.
  • So far we have looked at using EXECUTE AS to give permissions within a single database. What happens if you try to access other databases or perform an action that requires a server-level permission?
  • Answer: you run into a roadblock. Consider this procedure created in some other database than AdventureWorks:
  • CREATE PROCEDURE crossdb WITH EXECUTE AS OWNER AS SELECT COUNT(*) FROM AdventureWorks.Person.Address go EXEC crossdb
  • If you run this logged in as sa you get:
  • Server: Msg 916, Level 14, State 1, Procedure crossdb, Line 2 The server principal "sa" is not able to access the database "AdventureWorks" under the current security context.
  • Since sa usually can access everything, this comes as quite unexpected. But this is because there is a safeguard here. TheEXECUTE AS clause always impersonates a database user, never a server login. And when you impersonate a user, you are sandboxed into the current database, and you are denied any access outside that database. This applies to the EXECUTE ASclause in a procedure as well as the statement EXECUTE AS USER. (But not to EXECUTE AS LOGIN.)
  • The same is true for server-level permissions. If you try:
  • CREATE PROCEDURE reload_sp WITH EXECUTE AS OWNER AS TRUNCATE TABLE reloadable EXEC('BULK INSERT reloadable FROM ''C:\temp\reloadtest.csv'' WITH (FIELDTERMINATOR='','', ROWTERMINATOR=''\n'')') go EXEC reload_sp
  • Even if you are logged with sysadmin rights, you will get this error message:
  • Msg 4834, Level 16, State 4, Line 2 You do not have permission to use the bulk load statement.
  • To open the sandbox, you must open two doors. If the database is owned by a user with sysadmin permission, one of the doors are already open. The other door is this statement:
  • ALTER DATABASE db SET TRUSTWORTHY ON
  • If the database is trustworthy, and you impersonate user1 with the statement EXECUTE AS USER = 'user1'
  • or the clause EXECUTE AS 'user1'
  • in a stored procedure, you will be able to exercise any rights that user1 may have in other databases or on server level.
  • To set a database as trustworthy you need sysadmin rights. And this is by no means a step you should take casually. There are some scenarios where this setting is safe play, but there are also many where it opens a glaring hole in your server security. I will discuss this in detail, but to keep the focus of the main topic – granting permissions to stored procedures – I will first show how to use EXECUTE AS to grant bulk-copy permissions.
  • As with certificates, using EXECUTE AS to give bulk-copy permissions takes a little more work. The steps are:
  • - Create a proxy login, in the master database.
  • - Grant the proxy login ADMINISTER BULK OPERATIONS. Again in master.
  • - Mark the target database as trustworthy.
  • - Switch to the application database.
  • - Create a user for the proxy login.
  • - Grant the proxy user ALTER and INSERT on the target table.
  • - Add an EXECUTE AS clause to the procedure.
  • As there is not much new here, I will just make a few comments, before I give you a complete script with all steps and a test case.
  • Since ADMINISTER BULK OPERATIONS is a server-level permission, we need to create a full login in this case. It's a good idea to revoke the proxy login the right to connect to SQL, and I do this in the test script below.
  • As discussed in the previous section, we need to mark the database as trustworthy to break out from the sandbox.
  • Just like we did with certificates, we must put the BULK INSERT statement in dynamic SQL, because of a bug in SQL Server.
  • So here is the test script for using BULK INSERT with EXECUTE AS. (And as always, the introductory note on the examples applies):
  • USE master go -- Create a test file for bulkload. EXEC xp_cmdshell 'ECHO 978,123,234 > C:\temp\reloadtest.csv', no_output EXEC xp_cmdshell 'ECHO -98,13,85 >> C:\temp\reloadtest.csv', no_output go CREATE LOGIN testuser WITH PASSWORD = 'ExECaS=0=TeST' go -- Create the database to run the test in. CREATE DATABASE bulkcopytest go -- Mark the database as trustworthy. ALTER DATABASE bulkcopytest SET TRUSTWORTHY ON go -- Create a proxy login, which is to have the bulk-copy rights. CREATE LOGIN bulkproxy WITH PASSWORD = 'lkjSeF&hskldjh?löKDdf/jlk98sdfjälksdjg' go -- Grant rights for the proxy login and make it unable to login. GRANT ADMINISTER BULK OPERATIONS TO bulkproxy REVOKE CONNECT SQL FROM bulkproxy go -- Move to test database. USE bulkcopytest go -- Create the non-priv user and the proxy user. CREATE USER testuser CREATE USER bulkproxy go -- A test table. CREATE TABLE reloadable (a int NOT NULL, b int NOT NULL, c int NOT NULL) go -- Test procedure with BULK INSERT. CREATE PROCEDURE reload_sp WITH EXECUTE AS 'bulkproxy' AS TRUNCATE TABLE reloadable EXEC('BULK INSERT reloadable FROM ''C:\temp\reloadtest.csv'' WITH (FIELDTERMINATOR='','', ROWTERMINATOR=''\n'')') go -- Give test user right to execute them. GRANT EXECUTE ON reload_sp TO public go -- Grant the proxy user rights to truncate and insert to the test table. GRANT ALTER, INSERT ON reloadable TO bulkproxy go -- Insert some test data. If test succeeds, this data should disappear. INSERT reloadable (a, b, c) VALUES (12, 23, 34) go -- Switch to the test user. EXECUTE AS LOGIN = 'testuser' go -- Run the bulk load. EXEC reload_sp go -- Back to ourselves. REVERT go -- Verify that bulk load succeeded. SELECT a, b, c FROM reloadable go REVERT go -- Clean up. USE master DROP DATABASE bulkcopytest DROP LOGIN bulkproxy DROP LOGIN testuser EXEC xp_cmdshell 'DEL C:\temp\reloadtest.csv', no_output
  • Considerations on TRUSTWORTHY

  • Exactly how dangerous is TRUSTWORTHY? Permit me to approach this question in a somewhat roundabout way. For many years, this article just said that you should think twice before turning on TRUSTWORTHY, but did not go into details. Then one day, I got a mail from a reader who asked a question that got me thinking.
  • My correspondent had a problem. He wanted to grant access to BULK INSERT, and used my example for EXECUTE AS as a template, but he could not get it to work. There was a twist in his case, he wanted the database owner to be a plain server login, and not a member of sysadmin.
  • I sat down and played with my bulk-copy example and I was able to confirm his findings. I read through the topic Extending Database Impersonation by Using EXECUTE AS in Books Online and this was when I learnt that the sandbox has two doors that both must be open. Say there is a database A, and in this database we impersonate a user U – with the statement EXECUTE AS USER or the EXECUTE AS clause in a stored procedure. To be able to exercise the rights that the user U may have outside the database, the following conditions must be true.
  • 1.The database A must be TRUSTWORTHY.
  • 2a.For access to another database B, the owner of A must have been granted the permission AUTHENTICATE in the database B.
  • 2b.For actions that require server-level permissions, the owner of A must have been granted the permission AUTHENTICATE SERVER.
  • So did I answer to my correspondent that he should grant his database owner AUTHENTICATE SERVER? No. I had a nagging feeling that there was something hiding here, and after some thinking, I came to the realisation that this was just a different way to give the DB owner the possibility to do everything on the server – that is, the rights of sysadmin. How can it be? Consider this scenario:
  • Say that you are DBA for a server that hosts many databases, owned by various people in your organisation. One database owner, let's call him David B Owner, comes to you with this stored procedure to perform BULK INSERT. He now needs your help to get the server-level permissions for the procedure to work. We have already looked at how do this with certificates, and you have learnt that this way you can have full control what permissions you have granted to what code. If David changes his code, he has to come to you again so you can sign it anew.
  • But assume that David B Owner persuades you to instead take the route with EXECUTE AS. David has already written a procedure and tested it out on his personal server, and you are swamped with other things. And maybe you don't want David come to you again and again, every time he changes the procedure. After all, what damage can you do with ADMINISTER BULK OPERATIONSalone? Following the example in the previous section, you create the proxy login which you grant ADMINISTER BULK OPERATIONS, you make David's database trustworthy, and you grant David AUTHENTICATE SERVER. David merrily leaves your office. But what exactly did you do now? Did you in any way ensure that all David's database can do on server level is BULK INSERT?
  • Back at his desk, David runs this in his database:
  • CREATE USER [Domain\ServerDBA] -- That's you! go EXECUTE AS USER = 'Domain\ServerDBA'
  • That is, David creates a user for you in his database, and then he impersonates that user. Since he owns the database, he has full permissions to do anything in the database, including these two actions. As long as at least one of the two doors in the sandbox are closed, David can only play that he is you inside in his own database. But you were kind to open both doors to him, and now he has all powers on the server that you have. On top of all, auditing will give you the blame for what he is doing, unless auditing is based on original_login() and the similar columns in Profiler and the DMVs. When David is done, he can drop you as a user from the database to cover his tracks.
  • This is a classical example of privilege elevation, and the sandbox exists precisely to prevent this from happening by default.
  • In this last example, a non-privileged user was the database owner, but in many shops is customary to have sa or some dedicated SQL login as the database owner for all databases. (The problem with having individuals as database owners is that when people leave the company the DBA is not always informed when the user is dropped from the Active Directory, leaving theDBA with a database owned by an orphaned user.) But there are still people like Anna DeMin and David B Owner who are application admins or whatever, and they are member of db_owner in that database. In this scenario, what does it mean to set the database as trustworthy?
  • If the database is owned by sa (or some other user with sysadmin rights) the situation is just like above. Since sa is the owner, one of the doors of the sandbox is open from the start. If you make the database trustworthy, any person with db_owner rights can impersonate a server login with sysadmin rights just and do whatever he likes. What if the databases are owned by a generic login which has no other server permissions than owning all the databases? In this case, the door to server-level permission is closed, but the doors to all user databases are open. A malicious user with db_owner rights can do
  • EXECUTE AS USER = 'dbo'
  • and if the database is trustworthy, he can access all databases on the server owned by that login with full permission, which means that the user can read and write data he is not authorised to access.
  • In these examples I have assumed that the evil user is in the db_owner role. But db_owner is not required. More precisely, it's sufficient to have permission create users in the database and and have permission to impersonate users. Being member ofdb_securityadmin and db_accessadmin is sufficient. You should also not overlook the possibility that two users with supplementing permissions can work together.
  • You have now seen that TRUSTWORTHY is a switch that applied casually that can be utilised by malicious persons. But is this switch ever secure?
  • Certainly. When it comes to give plain users server-level permission through stored procedures, EXECUTE AS + TRUSTWORTHY is safe if all persons who have elevated permissions in the database also are members of sysadmin or have CONTROL SERVER. In this trivial case, there is no person who can use impersonation to elevate his permissions. This scenario is not unlikely on a server that is dedicated to a single application. However, keep in mind that one day you may be oblivious and grant a person you don't trust sysadmin rights to have db_owner permissions in that database. Maybe a support person for a vendor application. Maybe a junior DBA (who may prove to be less than junior when it comes to exploit security holes!) So while EXECUTE AS may seem simpler, I would say that for server-level access, you should always use certificates. Keep in mind that with certificates you have full control over what permissions you grant. Even if you don't want to review Anna's and David's bulk-insert procedures over and over again but instead give them the password to the certificate, the only permission they can ever abuse is ADMINISTER BULK OPERATIONS. Whereas with EXECUTE AS and a trustworthy database, there are no restrictions at all.
  • Cross-Database Access

  • In the previous section I showed that it is dangerous to make a database trustworthy, if all databases are owned by the same generic user. But if all databases have individual owners, it's a different matter. Note here that individual owners do not have to be physical persons, but it could be a generic login for each database. If this is the situation, there are a few scenarios for cross-database access where EXECUTE AS + TRUSTWORTHY may be perfectly acceptable.
  • Consider an application that consists of several databases all with the same owner, and there is a need for stored procedures to access data in more than one database. In this article we have looked three alternatives to address this situation: 1) Database chaining and the database option DB_CHAINING. 2) Certificates. 3) EXCUTE AS + Trustworthy. If the requirement is that every application database should be able to access all the other databases, then database chaining may be the simplest solution.
  • But say that there is only one database where there are stored procedures with cross-database access, and you don't want to permit access from the other databases. Since DB_CHAINING must be enabled for all databases, this rules out this option. The advantage with EXECUTE AS + TRUSTWORTHY over database chaining is that you can select which databases you make trustworthy. Of course, if you decide to use EXECUTE AS for cross-database access, you need to make sure that you can handle the consequences of impersonation and make sure that you don't rely on SYSTEM_USER et al, but only use original_login() orcontext_info(). If not, certificates are, as always, an option.
  • Here is a second scenario: there are two databases, A and B, which are part of different, but related, applications, and the databases have different owners. There is a need to access data in database B from A. To do this with EXECUTE AS, database A must be TRUSTWORTHY, and furthermore the database owner of A must be granted the permission AUTHENTICATE in database B. For this to be permissible, all users with db_owner or similar rights in database A must be entitled to see all data in database B, since they now can do:
  • CREATE USER owner_of_database_B go EXECUTE AS USER = 'owner_of_database_B'
  • They can now do whatever they want in database B. To some extent this is a matter about trust. If the owner of database B trusts all users in database B not to mess up his database, he can grant AUTHENTICATE to the owner of database A. After all, having indirect permission through AUTHENTICATE is from a legal point of view not the same as being added to the db_owner role in the database. Still owner of B is taking a risk, and personally, I say if there is sensitive data in the database he should not accept to grant AUTHENTICATE to the owner of A, but insist on certificate signing, and review all code that accesses his database.
  • Obviously, the point about trust can be made about server permissions as well. If you trust Anna DeMin and David B Owner, you can grant them AUTHENTICATE SERVER. But in my opinion, there is too much at stake here to even consider this.
  • I like to stress again, that a presumption for it to be acceptable to make a database TRUSTWORTHY is that the database is owned by a user specific to that database, or group of databases. As soon there is a generic owner who owns unrelated databases,TRUSTWORTHY cannot be considered permissible. (Unless there never are any users who are only db_owner in a subset of the databases.)
  • Starting Jobs

  • We looked previously at how we could make it possible for users of an application to start a certain job with help of certificates. The solution is somewhat dubious, since it requires you to counter-sign three procedures in msdb. Could this be done better withEXECUTE AS without compromising security? I think so. Here are the steps for a possible solution.
  • - The source database must have an individual owner.
  • - The source database must be TRUSTWORTHY.
  • - The database owner is added to msdb and granted AUTHENTICATE in that database.
  • - Create a login-less user jobstartuser in msdb and add this user to SQLAgentOperatorRole.
  • - You create a stored procedure in msdb to start the job in question. The procedure should have EXECUTE AS 'jobstartuser'
  • .
  • - The database owner is granted EXECUTE permission on this procedure.
  • - The database owner creates a stored procedure in his database with EXECUTE AS 'dbo'
  • that calls the procedure in msdb.
  • The reader may be shocked here, since we have learnt that if you own a trustworthy database and have AUTHENTICATE permission in another database, then you can get the power of the owner of that database by creating a user for him in your own database and then impersonate him. And yet I'm suggesting this? And with msdb, a system database?
  • Yes. You see, there is a special case. The owner of msdb is, by default, sa. And if you try any of:
  • CREATE USER sa CREATE USER Nisse FROM LOGIN sa
  • You will be told:
  • Msg 15405, Level 16, State 1, Line 1 Cannot use the special principal 'sa'.
  • You may ask: what happens if I create a user for someone I know is member of sysadmin and impersonate that user? The answer is that in this case, you will access msdb as guest. As long as that person is not an explicit member of msdb, that is. And there is a weakness with this solution. There is maybe little reason to add members of sysadmin to msdb. But what if there are operators or junior DBAs who are not sysadmin, but who are entitled to administer jobs? They have to be users in msdb, so they can be added to the various SQL Server Agent roles. And with AUTHENTICATE permission in msdb, the owner of database A can impersonate these guys and do things he should not be permitted to.
  • There is potentially a second problem with this solution. Who says that it is supported to put user procedures in msdb? Maybe it is, but I have not been able to find an answer in either direction. When I asked in our internal MVP forum, the only reply I got wasWhy don't you create the procedure in master? At first I did not see the point, as it would only serve to make the solution to be more complicated. Sure, no risk that the database owner would be able to impersonate operators in msdb, but instead he would have to be granted AUTHENTICATE in master.
  • But after some more thinking I realised that using an intermediate database was the right thing, but it should not be master, but a dedicated database. So here is the modified list of steps:
  • - The source database must have an individual owner.
  • - The source database must be TRUSTWORTHY.
  • - Create an intermediate database, call it jobstarter. This database MUST be owned by sa.
  • - Make jobstarter TRUSTWORTHY.
  • - Create a login jobstartuser who is to be the proxy user to start jobs. Deny this login the CONNECT SQL, that is, the right to log in to SQL Server.
  • - Add jobstartuser to msdb and make it member of the SQLAgentOperatorRole.
  • - Add jobstartuser to the jobstarter database.
  • - Add the owner of the source database to jobstarter, and grant him AUTHENTICATE.
  • - In jobstarter create a procedure that calls sp_start_job for the specific job. The procedure should have EXECUTE AS 'jobstartuser'
  • .
  • - Grant the database owner EXECUTE permission on the procedure.
  • - The database owner adds a procedure to his database with EXECUTE AS 'dbo'
  • that calls the procedure in jobstarter.
  • When I devised this solution, I debated with myself whether I should really have this jobstartuser. If you instead use EXECUTE AS OWNER in the procedure that calls sp_start_job, there is no need to create this extra login. Since I have advocated that you should never grant more permissions than needed, I chose to follow this line. But in this particular case, I cannot really blame you if you prefer EXECUTE AS OWNER. And you could argue that this is safer, since if jobstartuser is mistakenly granted permissions in jobstarter it should not have, this could lead to a security hole.
  • Before I show you an example script, I like to point out an important difference to the solution with certificates. In that solution, there is no code in msdb, nor is there any intermediate database. Instead the procedure that calls sp_start_job is in the source database. This is possible with certificates, since the DBA can have full control over what can be done with the ceritificate, for instance by dropping the private key. But when we use EXECUTE AS, the call to sp_start_job must be outside of reach for the database owner.
  • Here is a complete script that demonstrates the solution (the introductory remark on the example scripts applies as always). If you want to run this, you need to create a job called Testjob. It does not have to do anything particularly meaningful.
  • USE master go -- Create a login for a database owner as well plain test user. CREATE LOGIN databaseowner WITH PASSWORD = 'JoBS=tA7RTte5t' CREATE LOGIN testuser WITH PASSWORD = 'eXEc=a$=TeST' CREATE LOGIN jobstartuser WITH PASSWORD = 'No login !!' DENY CONNECT SQL TO jobstartuser go -- Create test database and set owner. We set the database trustworthy. CREATE DATABASE jobstarttest ALTER AUTHORIZATION ON DATABASE::jobstarttest TO databaseowner ALTER DATABASE jobstarttest SET TRUSTWORTHY ON go -- Create an intermediate database. This database *must* be owned by sa. -- This database should never have any non-sysadmin privileged users. CREATE DATABASE jobstarter ALTER AUTHORIZATION ON DATABASE::jobstarter TO sa ALTER DATABASE jobstarter SET TRUSTWORTHY ON go -- Next stop is msdb. go USE msdb go -- Create a user for jobstartuser and give permissions. CREATE USER jobstartuser EXEC sp_addrolemember SQLAgentOperatorRole, jobstartuser go -- Set up things the intermediate database. USE jobstarter go -- Create a user for the jobstartuser. CREATE USER jobstartuser go -- We add the database owner as a user and grant him AUTHENTICATE. CREATE USER databaseowner GRANT AUTHENTICATE TO databaseowner go -- Create a procedure to start a certain job. CREATE PROCEDURE start_this_job WITH EXECUTE AS 'jobstartuser' AS EXEC msdb..sp_start_job 'Testjob' go -- Permit the databaseowner to run this procedure. GRANT EXECUTE ON start_this_job TO databaseowner go -- Move to test database. USE jobstarttest go -- Create a database user for the test login as well as the proxyuser. CREATE USER testuser go -- Create a procedure that calls our start procedure in msdb. CREATE PROCEDURE start_our_job WITH EXECUTE AS 'dbo' AS EXEC jobstarter..start_this_job go -- Give test user right to execute the procedure. GRANT EXECUTE ON start_our_job TO testuser go -- Switch to the test user. EXECUTE AS LOGIN = 'testuser' go -- Start the job, this succeeds. EXEC start_our_job go -- Back to ourselves. REVERT go -- Clean up. go USE msdb go DROP USER jobstartuser go USE master go DROP DATABASE jobstarttest DROP DATABASE jobstarter DROP LOGIN testuser DROP LOGIN databaseowner DROP LOGIN jobstartuser
  • You may find this solution a bit too elaborate, and I can certainly agree. A better solution may to be use a mix of impersonation and certificate signing. Put the procedure start_this_job in msdb and use EXECUTE AS to get access to sp_start_job. But instead of making the source database TRUSTWORTHY, you use certificate signing to give permission to run start_this_job. This also relieves you of the requirement that the database must not have an individual owner.
  • As a final note: if you want to see which user in the source database that actually started the job, counter-signing the system procedures is the only choice. The auditing in msdb is performed through SYSTEM_USER so any impersonation breaks that.
  • Before we leave EXECUTE AS, there is one more side effect I have yet to discuss. This is a little more on the advanced side, and something I learnt from SQL Server MVP Adam Machanic.
  • In a CLR module, you can access the WindowsIdentity object. The main purpose for this is in assemblies that have been marked as EXTERNAL_ACCESS or UNSAFE where you want to access resources outside SQL Server with the Windows permissions of the actual user. To do this, you need to impersonate that user, or else the access will be through the service account for SQL Server.
  • As long as there has not been any impersonation through EXECUTE AS, SqlContext.WindowsIdentity.Name will return the domain and the Windows user name, if the user logged in through Windows authentication. For an SQL login, WindowsIdentity is Null, so access to SqlContext.WindowsIdentity.Name yields a Null exception.
  • But if there is an EXECUTE AS clause somewhere on the call stack, you can no longer retrieve the user name for the Windows user. In most cases, WindowsIdentity is Null. But, if the database was set as trustworthy, and the EXECUTE AS is for a user withsysadmin privileges, then WindowsIdentity.Name will return the name of the service account for SQL Server.
  • Other Methods

  • In this section I will cover three other methods to secure SQL Server. Sometimes I see people ask on the newsgroups and forumsHow can I grant access to an application? That is, they don't want the users to be able to access the tables directly from SSMSor Excel, but only from the application. The regular approach to achieve this is to use stored procedures, and we have already looked what possibilities they offer. But not all applications use stored procedures. In this section, I will briefly look at three solutions you can employ regardless whether you use stored procedures or not.
  • Application roles were added in SQL 7. The idea is that you create a role to which you assign the necessary privileges to run the application. The users have no permissions at all beyond the database access. The application calls the system proceduresp_setapprole to activate the role. To do this, the application must pass a password that can be obfuscated when sent over the wire.
  • Application roles may seem what you are looking for, but the password is a weak point. If you have a two-tier application, you cannever achieve a secure solution with application roles. The password has to be embedded in the application, or stored somewhere the user has read access. You can chop it into pieces and store the pieces in the four corners of the application, but at best that is security by obscurity. It's a different matter if you have a three-tier application. Then you can store the password on the middle tier somewhere the users do not have read access. You should still need to beware that anyone who can get access to the network wire to SQL Server may be able to eavesdrop and crack the password.
  • By default, when you activate an application role, you cannot back out of it. This has an effect on connection pooling; if you try to reuse a connection where an application role has been active, you will get an error. But you can get a cookie back fromsp_setapprole, which you then can pass to sp_unsetapprole before you disconnect.(Please see sp_setapprole in Books Online for the exact syntax.)
  • This also makes it possible for having several application roles with custom permissions for various tasks, similar to what we have discussed for certificates and EXECUTE AS. That is, you would set the application role, perform the SQL that needs special permissions, and then unset the role. (Note that you cannot call sp_setapprole from within a stored procedure; it must be called from the top-level scope.) But due to the password issue, it is not a solution that I recommend.
  • Since application roles are database entities, you cannot use them for things that require server-level permissions, for instance bulk load.
  • When you use application roles, functions that return login names – SYSTEM_USER, suser_sname() etc – still return the login name of the actual user. However, functions that return the database-level user name – USER, user_name() – return the name of the application role.
  • I have already touched at application proxies in several places, mainly in the sections on the EXECUTE AS statement and SET CONTEXT_INFO. Here I like to give just a few more remarks.
  • For an "application proxy" to be meaningful, the application must have at least three tiers. The middle tier authenticates the user and then connects to SQL Server. The same arrangement can be achieved with application roles, but with one difference: the application proxy can be a Windows login, so there is no password crossing the wire.
  • An interesting observation on SET CONTEXT_INFO is that it could serve as a full alternative to EXECUTE AS to impersonate the real user. All checks and auditing in the application that require knowledge about the real user would use context_info() to get this information. But as I discussed earlier, if there are holes in the application that permits for SQL injection, a malicious user could inject a SET CONTEXT_INFO to change his identity. For this reason, the EXECUTE AS statement with its NO REVERT and WITH COOKIE clauses appears as safer.
  • It is possible to set up a Remote Desktop connection so that a specific application is started, when the user connects. Furthermore, on Windows 2008, it is possible to set this up so that the user arrives directly to the login screen of the application, and the application appears as if it executes from his computer. That is, there is no desktop from the computer running Terminal Server. Please don't ask me how you do to set this up; I'm an SQL Server MVP, not a Windows MVP. But I've seen a demo of it.
  • If you have a two-tier application, you can use this to ensure that users can connect only through the application, and not through Access, Excel, Management Studio or whatever. You need to configure the network so that the SQL Server machine is not directly accessible from the users' computers, only from the computer running Terminal Server. One way to do this is to configure the firewall on the SQL Server machine to only accept connections from certain IP addresses.
  • Since this solution builds on things outside my realm, I cannot fully asses how secure it is. For instance, I don't know if there is any possibility for users to intercept the login process in Terminal Server. Nevertheless, it is an interesting option. Not the least if you have a two-tier application, you don't want to re-architect.
  • Final Words

  • Security is always a challenge. One aspect is that security and convenience rarely goes hand in hand. Better security often means more hassle.
  • But security is also a challenge, because the holes in your security scheme may not always be apparent. To work with security means that you constantly have to think around corners. Can this permission be exploited by a malicious user? Could there be privilege elevation? You cannot only consider the current situation, but you must also try to see into the future. Maybe your current setup is secure because of some assumptions that are true now. But what if those assumptions are not true to tomorrow?
  • In this article I have presented a number of solutions and suggestions, which I believe to be secure. But I cannot rule out that I've made a shortcut too many somewhere. By all means, if you apply any of my solutions in an area where security is top priority, you should make your own evaluation of the solution to assess whether there is a hole somewhere.
  • In this article we have looked at three different solutions to grant permissions through stored procedures: ownership chaining, certificates and EXECUTE AS.
  • Of these, ownership signing only works in a limited scenario, but a very common one, and ownership signing is what you will use 99 % of the time or even more.
  • In the situations where ownership signing is not sufficient, you can always use certificate signing to grant other permissions. With certificates you can have very tight control over what permissions you grant. Not the least is this important if you are a server DBAwho needs to grant server-level permission to users in application databases.
  • And then there is EXECUTE AS... As you have realised from this article, I am less than enthusiastic over EXECUTE AS. Everything you can do with EXECUTE AS you can do with certificates. (Save to cover up for an explicit DENY.) But it has to be admitted thatEXECUTE AS is simpler. So I think that EXECUTE AS is OK to grant database permissions under these circumstances:
  • - The application was designed with EXECUTE AS in mind. That is row-level security and auditing is based on original_login() orcontext_info().
  • - You use proxy users with specific permissions and don't descend to EXECUTE AS OWNER as the miracle cure.
  • From this follows that if you are a plain developer and need a solution to grant permissions beyond what is possible with ownership chaining, you cannot start using EXECUTE AS on your own initiative. You first need to discuss with your DBA or the chief designer of the application, so that you don't wreak havoc of something.
  • There are also some situations where EXECUTE AS is meaningful for cross-database access, and we have looked at some examples. The presumption is that the database has an individual owner, so that the effect of making it trustworthy is limited.
  • But when it comes to server-level permissions, you should be extremely conservative with using EXECUTE AS, since this requires the database to be trustworthy and the database owner to be granted AUTHENTICATE SERVER. Any user in that database with sufficient permission will be able to elevate his permission to the sysadmin role. You should have very good reasons not to use certificates here.
  • I like to thank SQL Server MVPs Dan Guzman, Martin Bell, Adam Machanic, Hugo Kornelis, Razvan Socol, Kent Tegels and Victor Isakov as well as Imran Mohamed and Jerry Horochowianka for submitting valuable suggestions for this article.If you have suggestions for improvements, corrections on contents, language or formatting, please mail me atesquel@sommarskog.se. If you have technical questions that any knowledgeable person could answer, I encourage you to post a question to the SQL Server Security forum on MSDN/Technet.
  • 2011-07-13 – A major overhaul of the article to reflect that five years had passed since the original publication. I have also added some quite important new material. Changes in summary:
  • - I wrote the original article when SQL 2005 was brand-new, it is no longer. I've revised the article to change that perspective a bit.
  • - Added a section on counter-signing stored procedures and added an example how to use this for starting jobs.
  • - Replaced the old section What About the Password? with a new section, Managing Certificates and Passwords for a better and deeper discussion on the security around certificates. There is also a script to show how you can use throw-away passwords when you deploy signed stored procedures for server-level permissions.
  • - Revised the section on the statement EXECUTE AS.
  • - Added a brief description of one more method – using Terminal Server.
  • 2011-01-11 – Corrected the expression to decode context_info(). The old expression would result in a number trailing NULcharacters in the character data. Thanks to Imran Mohamed for pointing out the error.
  • 2006-03-28 – Rewrote the section on asymmetric keys on suggestions from Razvan Socol.
  • 2006-02-19 – Original version.
0 Comments