Modules with Special Permissions

This topic describes how you can assign special permissions to stored procedures etc and how the DBA can handle such modules.

Contents:
   Introduction
   The Macros $DBPERM and $SERVERPERM
   Situations Where you Need Special Permissions 
   Considerations when Moving or Copying Databases
   Information for the DBA
      Objects Created by AbaPerls
      Stored Procedures for the DBA

Introduction

The traditional way of packaging permissions in SQL Server is ownership chaining. If you write a stored procedure that manipulates data in a table, users who run your stored procedure do not need direct permission to access the tables, as long as the procedure and tables have the same owner. However, this model is limited to SELECT, INSERT, DELETE, UPDATE and MERGE statements. It does extend to other commands, nor does it apply to dynamic SQL, but in these situations the user's own permissions apply.

SQL Server 2005 introduced a new model to address this: you can sign the module with a certificate, create a user from the certificate and then grant the certificate the permissions needed for the action. In this way, you can package the permission in a procedure with full control over what the user may do. AbaPerls makes it very easy to use this technique: all you need is to specify a Preppis macro in your file. If the procedure requires some a server-level permission, AbaPerls permits you to deploy the procedure even if you do not have server-level permissions yourself. The DBA can later run a stored procedure that uses the information that AbaPerls has stored to get the permissions and required certificates in place. Procedures available to the DBA are covered in the section Information for the DBA last on this page.

(If you want a detailed discussion on ownership signing and certificate signing, I refer you in the article Granting Permissions through Stored Procedures on my web site. This article also discusses an alternative technique using the EXECUTE AS clause. AbaPerls does not permit use of this clause.)

Note that this functionality requires that the ABAPERLS subsystem is present in the database.

The Macros $DBPERM and $SERVERPERM

To grant special permissions through a stored procedure, you can use the $DBPERM and $SERVERPERM macros. You use $DBPERM for database-level permissions and $SERVERPERM for server-level permissions. They have a similar syntax:

$DBPERM permission-string
$SERVERPERM permission-string

permission-string is a string of one or more permissions as accepted by the GRANT statement. That is the string is passed to GRANT without manipulation. For instance, the macro:

$DBPERM ALTER ON bankholidays

results in the GRANT statement:

GRANT ALTER ON bankholidays TO [ABAPERLS$cert_dbperm_test_sp_55B39DCA-7F5E-4368-8146-2E3DEBB0BC87]

(The long username is an example of the usernames that AbaPerls creates from certificates.)

AbaPerls performs no syntax check on the permission-string; any syntax error will yield an error from SQL Server.

There can be several $DBPERM and $SERVERPERM macros in a file, each resulting in a GRANT statement. The macros can appear anywhere in the file, but it is customary to put them after the final go.

Normally, you can only define one object per file, but .tri and .vtri files can define multiple triggers. In this case the special permissions are granted to all triggers.

When there is one or more $DBPERM or $SERVERPERM macro in a file, AbaPerls creates a certificate and a user associated with the certificate in the current database for each module in the file. In case of $SERVERPERM, AbaPerls also creates the certificate in the master database as well as a login connected to the certificate The user and the login exist only as bridge for granting the permissions; it is not possible to connect to SQL Server with them.

AbaPerls assumes that you have db_owner rights. In case you don't have rights to grant the permission, you will not be able to load the procedure, but get an error.

On the other hand, when you use $SERVERPERM, AbaPerls checks if you have the permission CONTROL SERVER. If you have not, AbaPerls produces the informational message You cannot add server-level permissions, because you do not have CONTROL SERVER permission and the procedure will load successfully. The DBA will have to complete the installation by running any of the procedures described in the section Information for the DBA.

Situations Where you Need Special Permissions 

Here follows some typical use cases of the $DBPERM and $SERVERPERM macros. Note: The topic of permissions in SQL Server and their granularity is complex, and it is beyond the scope of this manual to detail exactly how you should apply a permission. The list below just gives the name of the permission, and you will need to determine yourself how to apply it to your specific case.

Database-level

Server-level

Considerations when Moving or Copying Databases

Normally, each module has its own certificate. If a module with server-level permission appears in multiple databases, there will be one certificate in master for every database the module appears in. When a module is changed, the old certificate is thrown away, the module is re-signed, and permissions granted anew. In this way, there can be no interference between different modules or different databases.

However, there is one situation where this does not hold true, and that is if you backup a database and restore it under a different name on the same server. If there are modules with server-level permissions, these modules will now share the certificate in master. The best way to resolve this is to run the procedure ap_spp_reload_database_sp (discussed in the next section) in the copy; this will recreate all certificates and re-grant all permissions. This will not affect the original database, because AbaPerls stores information in the certificate that makes it possible for AbaPerls to tie the certificate to the source database and object.

If you don't reload special permissions in the restored copy and go on changing modules in the original, server-level permissions may disappear for modules in the copy. On the other hand, the original can never be affected by changes in the restored copy.

When you restore a backup on a different server, there will be no server-level permissions, so in this case you must always run ap_spp_reload_database_sp.

Information for the DBA

The information in this section pertains to both modules with special permissions and privileged CLR assemblies. (Further information about privileged assemblies is on the CLR page.)

Objects Created by AbaPerls

The $DBPERM permission results in two objects being created in the user database:

When someone uses $SERVERPERM, this also results in the following objects:

For a privileged assembly, AbaPerls creates the following objects in the master database.

If the database is part of an availability group, AbaPerls attempts to run the commands it executes in master, in the master database on all nodes in the availability group. To achieve this aim, AbaPerls sets up a temporary linked server of which the name is ABAPERLS$ followed by a GUID. The linked server is redefined for each node, and then dropped. AbaPerls batches these commands and performs it as one of the last actions of ABASQL, DBBUILD or an update script generated by DBUPDGEN.

Stored Procedures for the DBA

There are three stored procedures you can run to grant permissions to modules and assemblies, depending if you want to grant permission for a single object, all objects in a database or all databases on a server.

All three procedures have these two parameters:

@report   bit =  1
@exec     bit =  0

The procedures generate dynamic SQL to perform their actions, and they can print the generated SQL and/or execute it. The default is to print only. This permits the DBA to review the script to ascertain that no illegal actions have sneaked in, and he can then execute the very same script. However, note this:

These are the procedures:

ap_spp_grant_perms_sp Grant the permissions for a single object; this can be a stored procedure, function etc or an assembly. Permissions are reloaded in the current database as well in the master database if the module needs server-level permissions. Old certificates and users are dropped. Note that this procedure does not run the commands on other nodes in an availability group!
   The procedure accepts three arguments @objname, @report and @exec. @objname is mandatory.
ap_spp_reload_database_sp Reloads the permissions for all modules that require special permissions in the current database. The procedure also deletes all AbaPerls certificates and users in the user database as well as all AbaPerls certificates and their logins for the database in master. The procedure does not delete any asymmetric keys for assemblies. The procedure only deletes certificates that have names starting with ABAPERLS$. If the database is part of an availability group, the master database on each node in the AG is reloaded.
   The procedure accepts two parameters, @report and @exec.
ap_spp_reload_server_sp Runs ap_spp_reload_database_sp for all databases on the server where this database exists. The procedure also drops all logins, asymmetric keys and certificates in master of which the name starts with the string ABAPERLS$.
   The procedure accepts two parameters, @report and @exec.