Table of Contents
- Microsoft SQL Server Defined
- Microsoft SQL Server Features
Microsoft SQL Server Administration
- The DBA Survival Guide: The 10 Minute SQL Server Overview
- Preparing (or Tuning) a Windows System for SQL Server, Part 1
- Preparing (or Tuning) a Windows System for SQL Server, Part 2
- Installing SQL Server
- Upgrading SQL Server
- SQL Server 2000 Management Tools
- SQL Server 2005 Management Tools
- SQL Server 2008 Management Tools
- SQL Azure Tools
- Automating Tasks with SQL Server Agent
- Run Operating System Commands in SQL Agent using PowerShell
- Automating Tasks Without SQL Server Agent
- Storage – SQL Server I/O
- Service Packs, Hotfixes and Cumulative Upgrades
- Tracking SQL Server Information with Error and Event Logs
- Change Management
- SQL Server Metadata, Part One
- SQL Server Meta-Data, Part Two
- Monitoring - SQL Server 2005 Dynamic Views and Functions
- Monitoring - Performance Monitor
- Unattended Performance Monitoring for SQL Server
- Monitoring - User-Defined Performance Counters
- Monitoring: SQL Server Activity Monitor
- SQL Server Instances
- DBCC Commands
- SQL Server and Mail
- Database Maintenance Checklist
- The Maintenance Wizard: SQL Server 2000 and Earlier
- The Maintenance Wizard: SQL Server 2005 (SP2) and Later
- The Web Assistant Wizard
- Creating Web Pages from SQL Server
- SQL Server Security
- Securing the SQL Server Platform, Part 1
- Securing the SQL Server Platform, Part 2
- SQL Server Security: Users and other Principals
- SQL Server Security – Roles
- SQL Server Security: Objects (Securables)
- Security: Using the Command Line
- SQL Server Security - Encrypting Connections
- SQL Server Security: Encrypting Data
- SQL Server Security Audit
- High Availability - SQL Server Clustering
- SQL Server Configuration, Part 1
- SQL Server Configuration, Part 2
- Database Configuration Options
- 32- vs 64-bit Computing for SQL Server
- SQL Server and Memory
- Performance Tuning: Introduction to Indexes
- Statistical Indexes
- Backup and Recovery
- Backup and Recovery Examples, Part One
- Backup and Recovery Examples, Part Two: Transferring Databases to Another System (Even Without Backups)
- SQL Profiler - Reverse Engineering An Application
- SQL Trace
- SQL Server Alerts
- Files and Filegroups
- Full-Text Indexes
- Read-Only Data
- SQL Server Locks
- Monitoring Locking and Deadlocking
- Controlling Locks in SQL Server
- SQL Server Policy-Based Management, Part One
- SQL Server Policy-Based Management, Part Two
- SQL Server Policy-Based Management, Part Three
- Microsoft SQL Server Programming
- Performance Tuning
- Practical Applications
- Professional Development
- Application Architecture Assessments
- Business Intelligence
- Tips and Troubleshooting
- Additional Resources
SQL Server Security: Objects (Securables)
Last updated Mar 28, 2003.
I'm continuing a previous discussion of SQL Server Security in this series, where I've already described security in general, on the specific platform, as well as a graphical step-by-step tutorial on how to create users and groups of users called "roles" in the last tutorial.
I've been comparing a SQL Server setup to a bank building, and the users in a database to the patrons of a bank. For instance, in my bank account, the company I work for is allowed to put money into one of my accounts, but they can't see any of the other accounts. Not only that, they are only allowed to deposit money — they can't take any out. Credit companies, on the other hand, can inquire about my balances but can neither put money in nor take money out. Different patrons in the bank setting have different rights and privileges.
It's the same with the database, but you're working with users and objects (like tables) instead of patrons and accounts. Users have different kinds of access to different objects in the database.
In fact, the database objects aren't the only things that you can secure. You can create rights and privileges for everything from the server itself down to each database and each object that they contain. Microsoft calls these items "securables," although I'll mostly refer to each object by its individual name. In this tutorial I'll focus only on database objects.
There are multiple objects in the database that you can secure. Here's a partial list of the SQL Server securables for a database:
- Message Type
- Fulltext Catalog
- Application role
- Remote Service Binding
- Symmetric Key
- Asymmetric Key
- XML Schema Collection
As you can see, there are quite a few things you can secure in SQL Server.
I'm going to focus on just two objects to keep this tutorial simple: tables and stored procedures. The reason for that is that the way you work with a table is similar to the way you work with any "static" object. The way you work with a stored procedure is similar to the way you work with most any "code" objects. Of course there are exceptions, but I'll focus on just these two to keep the tutorial simple.
Also, although you can set the permissions for these objects graphically, I'll show you the three basic commands you need to set the security in Transact-SQL (T-SQL). This will keep the tutorial consistent, and most of the time you'll work with scripts in your own databases. Although creating one or two users is easier in the graphical tools, it can be more efficient to work with commands when you're setting up security.
Remember from the last two tutorials that I'm not just talking about users for object access, but any "thing" you've allowed in the database. Microsoft calls users, groups and the like "principals." You will more than likely create groups of users (roles) that you'll give access to various objects. That simplifies your security a great deal.
As I mentioned, there are only three basic commands you need to learn for security: GRANT, DENY and REVOKE. The first two commands are fairly straightforward. GRANT allows users (or any principal) to do something with an object. DENY prevents a principal from doing something with an object. REVOKE, on the other hand, removes the permissions from a user, unless they belong to a role where they do have the right to work with the object. I'll explain this with an example or two.
I'll create a simple database to work with called "PermissionsExample," add a table, and then add a couple of users. You can use this script to create that whole setup by copying this code (Don't do this on a production system — the passwords are far too weak!):
/* Set up the database */ CREATE DATABASE PermissionsTest GO USE PermissionsTest GO /* Create two tables */ CREATE TABLE AccountInfo ( AccountID int , AccountName varchar(50) , AccountLocation varchar(50) ) GO CREATE TABLE AccountBalance ( AccountID int , CurrentBalance money , CurrentBalanceDate datetime ) GO /* Fill the tables with data */ INSERT INTO AccountInfo VALUES (1, 'Checking', 'Tampa') INSERT INTO AccountInfo VALUES (2, 'Savings', 'Florence') GO INSERT INTO AccountBalance VALUES (1, 500, '12/05/1985') INSERT INTO AccountBalance VALUES (2, 1000, '12/05/1985') GO /* Create two users to work with */ EXEC sp_addlogin 'Buck', 'password', 'PermissionsTest' EXEC sp_grantdbaccess 'Buck', 'Buck' EXEC sp_addlogin 'Marjorie', 'password', 'PermissionsTest' EXEC sp_grantdbaccess 'Marjorie', 'Marjorie' GO
That script creates a database, adds two tables, a view and a stored procedure. It then creates two SQL Server logins, and gives them access to a database. All this you've seen in earlier tutorials. Note that in newer versions of SQL Server the proper commands for creating a server login and database user are CREATE LOGIN and CREATE USER respectively - but the two stored procedures I show here are still in use as of this writing. Either will create a SQL Server login for you - assuming that your Instance of SQL Server is set to allow both Windows and SQL Server Logins (sometimes called “Mixed Authentication).
So at that point, what can the users do? Well, they can log in to the server and access the database with a "USE PermissionsTest" statement. That's it.
To allow Buck or Marjorie to access data in a table or other object, I'll need to use the GRANT statement. At this point it's Important to think about what you want these users to be able to do.
To get that right, you need three pieces of information: Who you want to allow into the object, the object you want them to work with, and what level of access you want them to have.
You can let users view the data using the SELECT statement, put new data in using the INSERT statement, delete data using the DELETE statement, or change one value to another using the UPDATE statement. Here's the general format:
GRANT some permission (statement) ON some object TO some principal
Now all I have to do in this example is plug in the permissions, objects and principals to allow the user to do what they need. I'll allow Buck the ability to read data from the AccountInfo table:
GRANT SELECT ON AccountInfo TO Buck GO
The interesting thing is that you can combine permissions in a single GRANT:
GRANT SELECT, INSERT, DELETE, UPDATE ON AccountInfo TO Buck GO
Since that's a lot of typing, Transact-SQL has a single command to give those kinds of permissions at one time, by specifying ALL instead of the permissions. Here's how you could do that:
GRANT ALL ON AccountInfo TO Buck GO
Now Buck can see, enter, remove and alter data in the AccountInfo table. There's one other part to the GRANT command we need to look at. You may want to allow Buck to be able to give other people permission to this table. You can do that by adding the WITH GRANT option to the end of the GRANT statement, like this:
GRANT ALL ON AccountInfo TO Buck WITH GRANT OPTION GO
So far, Buck can see and manipulate all rows in the AccountInfo Table. Let's allow Buck to see everything in the AccountBalance table:
GRANT SELECT ON AccountBalance TO Buck GO
Now Buck has different rights on different objects, but Marjorie can't do anything. Assume that you want to allow Marjorie to look at both account information and balances, but only for the Melbourne location. You can't grant permissions only on certain rows in both tables, but you can create a view that shows only that data and then grant Marjorie permission to use the view. In fact, this is a common use-case for views. Here's an example:
/* Create a view showing the account balances only for the Melbourne accounts */ CREATE VIEW MelbourneAccounts AS SELECT a.AccountName , a.AccountLocation , b.CurrentBalance FROM AccountInfo a INNER JOIN AccountBalance b ON a.AccountID = b.AccountID WHERE a.AccountLocation = 'Melbourne' GO /* Grant Marjorie permissions to the view */ GRANT SELECT ON MelbourneAccounts TO Marjorie GO
Keep in mind that in this sample I granted Marjorie access to the view, but a view is just a SELECT statement — it doesn't hold any real data. I never granted Marjorie access to either of the two tables underneath (often called base tables). The view still works for Marjorie because of something called a permissions "chain." That means if the same user creates one object that depends on another, like a view on a table, permissions only have to be granted on the "parent" or top object, like the view.
Now Marjorie can query the view and get all of the rows that have Melbourne information.
So far no one can update any values in the AccountBalance table. I've decided that I want to allow Buck to update the current date in that table, but nothing else. To do that I can create a stored procedure that will work similar to the view, but only allow the user to do what I want by writing the appropriate code in the stored procedure. It uses the same permission chains to make this work. Here's an example:
/* Create a Stored Procedure that updates the account Balance Date */ CREATE PROCEDURE UpdateBalanceDate @NewDate datetime AS UPDATE AccountBalance SET CurrentBalanceDate = '01/31/2006' WHERE CurrentBalanceDate = '12/05/1985' GO
The only thing that is different about this set of statements is that the user doesn't access this program using a SELECT or INSERT statement, because this object is a program object. In this case I grant the EXECUTE statement to Buck:
GRANT EXECUTE ON UpdateBalanceDate TO Buck GO
Now Buck can execute the stored procedure, and the data is changed on his behalf. Of course, to truly make this useful, I'd allow Buck to send the date he wants as a variable — this one simply makes all the dates recorded here the same, at least the ones that meet the WHERE criteria.
In future tutorials I'll expand on these statements to show you how to build more complex security features.