In a database environment, traditional security mechanisms either allow an operation to take place or not, depending on permissions granted to a principal. This is not a very flexible security model, and often requires an administrator to perform or initiate a number of operations that businesses would prefer to other personnel perform. As a result, administrator permissions are often given to more users than is desirable, which compromises security.
One attempt to provide more flexibility is based upon Data Definition Language (DDL) triggers. DDL triggers can be used to perform administrative tasks in the database such as auditing and regulating database operations without needing an administrator each time. DDL triggers are “after” triggers, in that they are based upon taking some action with respect to a database transaction, and then either committing the transaction or rolling back the transaction.
As a result, this is not very effective with respect to security for certain types of operations. For example, backing up a database creates a new file; once backed up, the work is already done, and the file exists with its information disclosed and open to exploit. Additionally, as the operation does not go through the hardened security kernel for permission evaluation, it is more likely to be vulnerable to attack. Even if an error is raised that triggers a security-related policy action, security has already been compromised.
This Summary is provided to introduce a selection of representative concepts in a simplified form that are further described below in the Detailed Description. This Summary is not intended to identify key features or essential features of the claimed subject matter, nor is it intended to be used in any way that would limit the scope of the claimed subject matter.
Briefly, various aspects of the subject matter described herein are directed towards temporarily granting permissions that may allow a database operation to be performed by executing a “before” security policy trigger evaluated prior to executing a database statement or the like. The before security policy trigger evaluates data associated with the database statement against criteria specified therein to determine whether a security context associated with the database statement is to be modified, e.g., by adding a role or secondary principal in association with the security context. The security context is evaluated by the existing security system to determine whether to execute the database statement. The security context is reverted after the successful or unsuccessful execution of the database statement.
In one implementation, a database engine receives an event notification from a module such as a function before an operation is to be executed. A policy trigger associated with that module evaluates information associated with the event notification to determine whether a set of one or more criteria is met by the information, and if so, to take action that modifies a security context. For example, the policy trigger may return a true evaluation result that modifies the security context, no evaluation result that leaves the security context unchanged, or a false evaluation result that raises an error and prevents any execution attempt. If not false, the security system evaluates the security context with respect to a statement for execution to determine whether to allow or deny execution of the statement. If modified, the security context is reverted to its prior state before any modification.
Other advantages may become apparent from the following detailed description when taken in conjunction with the drawings.
The present invention is illustrated by way of example and not limited in the accompanying figures in which like reference numerals indicate similar elements and in which:
Various aspects of the technology described herein are generally directed towards providing a security policy trigger (or more simply “policy trigger” or “trigger”) that is evaluated prior to executing a database operation (comprising a statement or multiple statements of a database language). In general the trigger comprises logic that evaluates one or more criteria, which if the trigger evaluates to true, results in modifying a security context associated with that operation. In one implementation, the security context is modified by adding a secondary principal or role to an entity (e.g., user) token; the role can be used to grant or deny additional permissions for the duration of execution of the operation. Once the operation associated with the policy trigger is completed, whether successful or not, the modified security context is reverted, e.g., the added role is removed from the user token. If the trigger evaluates the one or more criteria to false, an error may be raised to prevent the operation.
While some of the examples described herein are directed towards Structured Query Language (SQL) statements, it is understood that the technology described herein may be implemented in any suitable database language. Further, as will be understood, languages such as C# and/or the .Net framework may benefit from using the technology described herein. As such, the present invention is not limited to any particular embodiments, aspects, concepts, structures, functionalities or examples described herein. Rather, any of the embodiments, aspects, concepts, structures, functionalities or examples described herein are non-limiting, and the present invention may be used various ways that provide benefits and advantages in computing and programming operations in general.
A security system 110 is represented in
As described herein, the desired flexibility is provided via policy triggers 1121-112n, each of which contain logic that may be executed with respect to an operation (a statement or set of statements, particularly if corresponding to a function's operation) before that operation is executed. Depending on how an administrator configures a system, any operation may have zero, one, or more than one policy trigger associated with it.
In one aspect, a policy trigger may dynamically modify the user's security context (affecting permissions) for this operation, whereby authorization through the normal security code paths is performed, but with different permissions than the user normally has. In one example implementation, a policy trigger is a conditional EXECUTE AS clause, which based on some trigger-like functionality, adds a role membership to the execution context. This allows privileged execution; however it does not affect identity as reported in SQL Audit, sp_who, and so forth. Such triggers may be defined on system DDL.
Because the normal security code paths are used, the named role needs to have permission to do the desired operation; the security system 110/policy checks to determine whether the operation is allowed or not. After the operation completes, the user's security context (e.g., role membership) is reverted to its previous state.
Administrators define the policy triggers and associate one or more of them with any function/operation/statement to which the administrator wants to apply the policy trigger or triggers; this is represented in
Turning to invoking the policy trigger or triggers for a given operation, in the example of
Along with the before event notification, the function provides data such as relevant flags and/or parameters, e.g., in an event object. One example is a location (e.g., of a network storage volume) to which the database will be backed up. Another example may identify or provide a ticket. In general and as exemplified below, if a policy trigger exists for this statement, the policy trigger evaluates the data to determine if criteria in the policy trigger is met, and if so, returns a “True” evaluation result to the security system. Note that for purposes of brevity hereinafter, only one policy trigger is described for each function that has one, however it is understood that more than one policy trigger may exist for a function/operation/statement, with suitable logic combining the results in some way (e.g., all need to be “True” or any one may be “True” in order to modify the security context of the user invoking the function).
If the policy trigger returns true, the security context is modified, such as by adding a role in one implementation.
The function then issues the statement 124, which is associated with the modified security context. As with any statement, status indicative of success or failure is returned to the caller, as represented by block 126.
Regardless of success or failure, the function fires an after event notification 128. This is used to revert the security context of the user (or other entity) to its pre-modified state.
As mentioned above, adding a role may grant or deny additional permissions for the duration of statement execution (which may be more than one statement). Thus, the policy trigger may be used to deny an operation that the user is otherwise able to perform. As can be readily appreciated, this provides for highly flexible policy, e.g., to not allow backup or restore operations during normal business hours regardless of the caller's other permissions. Note that in some database systems, membership in certain roles trumps any grants/denies/revokes.
Further note that a policy trigger need not return “True” (or “False” as described below), whereby execution of the statement will be attempted with the normal security context of the user. Alternatively, the policy trigger may return a “False” result, which raises an error. Returning an error can be used to prevent an operation the user would normally have permission to execute.
By way of example, consider an IT staff member assigned through a work ticket system to restore a database on a production system. The staff member has a login on a SQL server instance, but no permissions. An administrator has defined an example policy trigger for this operation as:
When the staff member logs in, he or she executes:
As represented in
More particularly, in the above example, there are two sets of criteria (IF statements) that are evaluated, as represented in
Returning to
Conversely, if “False” was returned as evaluated at step 212, an error is raised (step 214). This may be used to prevent any further attempt to execute the operation.
In the event that a “True” or nothing (not a “False”/error raised state) was returned, step 216 executes the RESTORE statement. If “True” at step 208, the permission checks pass due to step 210's having added the SYSADMIN membership. Otherwise, the permission checks will not pass, (because in this example, the staff member had no permissions; note that in other scenarios, the permission checks may or may not pass depending on the user's non-modified security context).
Step 220 represents the function firing the after event notification. After execution completes, either through success or failure, the security context is reverted to its prior state as represented by step 222. Note that, reverting context is known technology that handles removing only what was added, if anything; however in other implementations, step 222 may include logic that only reverts the security context if it had been changed via step 210.
The function may provide more than one statement that execute with the added role. In other words, the function can perform an operation having more than one statement, which are each executed before sending the after event notification. Thus, the temporary role may be active for a single statement, or a set of statements, such as an entire module. However, note that in one implementation, reverting the security context happens automatically as part of the language runtime that is handling the execution of these functions, and thus the reverting operation may be on a per-statement basis so as to not allow the user to have an elevated context for more than a single statement.
In this manner, a user may temporarily obtain a new role for purposes of performing an operation. The role may be used to allow or deny the operation. Further, normal privileges may be revoked by a “False” evaluation result, e.g., if the user is already of member of SYSADMIN, the policy trigger may recognize this and return “False” which raises an error to prevent SYSADMINs from executing without permission.
Thus, instead of current systems that deny an operation if policy is not satisfied, which often result in granting permissions to users to perform certain but also allow them to do more than intended, the technology described herein provides a mechanism to securely elevate permissions based on policy, and only temporarily for the purpose of a certain operation. This means that users do not have elevated permissions outside of the policy, whereby users cannot disable or manipulate the triggers. In other words, by elevating permissions on demand (rather than denying if policy is violated), the need for administrator membership is reduced, which avoids users being able to bypass the triggers used to enforce the policy. If a user still finds a way around the triggers, no additional harm can be done because the user needs the trigger to successfully execute in order to obtain the permissions.
Note that various ways other than adding roles may be used to elevate permissions. For example, it is feasible to use a security trigger to change the security context by temporarily changing the user's identity. Notwithstanding, roles have the benefit of not changing the user identity, whereby audits and the like can determine what the user actually did.
As another alternative, current SQL (Microsoft® SQL Server) technology provides the capability to sign modules within the database, such as stored procedures, functions, triggers, or assemblies; (note that DDL triggers cannot be signed in one implementation). Select permissions may be granted to a login (e.g., user) associated with the certificate.
The invention is operational with numerous other general purpose or special purpose computing system environments or configurations. Examples of well known computing systems, environments, and/or configurations that may be suitable for use with the invention include, but are not limited to: personal computers, server computers, hand-held or laptop devices, tablet devices, multiprocessor systems, microprocessor-based systems, set top boxes, programmable consumer electronics, network PCs, minicomputers, mainframe computers, distributed computing environments that include any of the above systems or devices, and the like.
The invention may be described in the general context of computer-executable instructions, such as program modules, being executed by a computer. Generally, program modules include routines, programs, objects, components, data structures, and so forth, which perform particular tasks or implement particular abstract data types. The invention may also be practiced in distributed computing environments where tasks are performed by remote processing devices that are linked through a communications network. In a distributed computing environment, program modules may be located in local and/or remote computer storage media including memory storage devices.
With reference to
The computer 410 typically includes a variety of computer-readable media. Computer-readable media can be any available media that can be accessed by the computer 410 and includes both volatile and nonvolatile media, and removable and non-removable media. By way of example, and not limitation, computer-readable media may comprise computer storage media and communication media. Computer storage media includes volatile and nonvolatile, removable and non-removable media implemented in any method or technology for storage of information such as computer-readable instructions, data structures, program modules or other data. Computer storage media includes, but is not limited to, RAM, ROM, EEPROM, flash memory or other memory technology, CD-ROM, digital versatile disks (DVD) or other optical disk storage, magnetic cassettes, magnetic tape, magnetic disk storage or other magnetic storage devices, or any other medium which can be used to store the desired information and which can accessed by the computer 410. Communication media typically embodies computer-readable instructions, data structures, program modules or other data in a modulated data signal such as a carrier wave or other transport mechanism and includes any information delivery media. The term “modulated data signal” means a signal that has one or more of its characteristics set or changed in such a manner as to encode information in the signal. By way of example, and not limitation, communication media includes wired media such as a wired network or direct-wired connection, and wireless media such as acoustic, RF, infrared and other wireless media. Combinations of the any of the above may also be included within the scope of computer-readable media.
The system memory 430 includes computer storage media in the form of volatile and/or nonvolatile memory such as read only memory (ROM) 431 and random access memory (RAM) 432. A basic input/output system 433 (BIOS), containing the basic routines that help to transfer information between elements within computer 410, such as during start-up, is typically stored in ROM 431. RAM 432 typically contains data and/or program modules that are immediately accessible to and/or presently being operated on by processing unit 420. By way of example, and not limitation,
The computer 410 may also include other removable/non-removable, volatile/nonvolatile computer storage media. By way of example only,
The drives and their associated computer storage media, described above and illustrated in
The computer 410 may operate in a networked environment using logical connections to one or more remote computers, such as a remote computer 480. The remote computer 480 may be a personal computer, a server, a router, a network PC, a peer device or other common network node, and typically includes many or all of the elements described above relative to the computer 410, although only a memory storage device 481 has been illustrated in
When used in a LAN networking environment, the computer 410 is connected to the LAN 471 through a network interface or adapter 470. When used in a WAN networking environment, the computer 410 typically includes a modem 472 or other means for establishing communications over the WAN 473, such as the Internet. The modem 472, which may be internal or external, may be connected to the system bus 421 via the user input interface 460 or other appropriate mechanism. A wireless networking component 474 such as comprising an interface and antenna may be coupled through a suitable device such as an access point or peer computer to a WAN or LAN. In a networked environment, program modules depicted relative to the computer 410, or portions thereof, may be stored in the remote memory storage device. By way of example, and not limitation,
An auxiliary subsystem 499 (e.g., for auxiliary display of content) may be connected via the user interface 460 to allow data such as program content, system status and event notifications to be provided to the user, even if the main portions of the computer system are in a low power state. The auxiliary subsystem 499 may be connected to the modem 472 and/or network interface 470 to allow communication between these systems while the main processing unit 420 is in a low power state.
While the invention is susceptible to various modifications and alternative constructions, certain illustrated embodiments thereof are shown in the drawings and have been described above in detail. It should be understood, however, that there is no intention to limit the invention to the specific forms disclosed, but on the contrary, the intention is to cover all modifications, alternative constructions, and equivalents failing within the spirit and scope of the invention.