|
|
In the following sections, you'll learn how you can use SQL Server's built-in stored procedures to add new users and change their passwords. You'll then see how you can use stored procedures and SQL statements to change the permissions of a particular user—that is, how you can determine what they can and can't do within a particular database.
With SQL Server, you can create a new user with the sp_addlogin stored procedure. You need to provide a username and optionally a password. There are more optional parameters; please consult SQL Server Books Online for the complete syntax:
| Tip |
For aesthetic purposes, we use proper casing for usernames, but database usernames are case insensitive. This is true for all databases covered in this book. |
You can change the user's password with the sp_password stored procedure. It takes as parameters the old password, the new password, and the username. If no username is supplied, the logged-in user is assumed by default. The following example illustrates how you can change Alice's simple password to a more complicated one:
EXEC sp_password 'simplepassword', 'complicatedpassword', 'Alice'
You can remove a user by using the sp_droplogin stored procedure, which takes as a parameter the username you want to delete. You can remove the Alice account using the following command:
EXEC sp_droplogin 'Alice'
This statement will drop the Alice account.
| Note |
In order to test the following statements, you'll need to keep the Alice account around. |
Note that SQL Server also supports an alternative mode of authentication, known as Integrated Security, where access to the database is linked to Windows authentication (local user accounts or domain user accounts). If you're using Windows authentication, you can refer to domain accounts or local accounts in SQL statements in the same way as database user accounts except that you can't create a new user account in this manner.
To refer to domain users, you can use the syntax DOMAIN\UserName. Local accounts are referred to using the name of the machine you're using, for example, MyMachine\Administrator. If you're using Windows authentication, you'll need to have access to a user called Alice for these examples. You can create a local system user called Alice using the Computer Management console. For the following examples, you can use MachineName/Alice as your username.
| Tip |
SQL Server also supports mixed mode authentication, where both Windows authentication and SQL Server authentication are used. |
Once you've added a new database account, you can start playing with its rights and permissions. By default, the new account doesn't have any permissions.
For a start, you can grant the new user permission to access a particular database. You can do this with the sp_grantdbaccess stored procedure. Giving users access to a database allows them to connect to the database, but it doesn't give any specific permissions to individual objects.
If you're logged in with the sa account (or an account that has the rights to create databases), you can do this little experiment using Query Analyzer:
CREATE DATABASE TestDatabase GO USE TestDatabase GO CREATE TABLE TestTable (TestRow INT); GO EXEC sp_grantdbaccess 'Alice'
Note that the sp_grantdbaccess stored procedure always works with the current database. In this example, you first create a new database named TestDatabase. When you connect to the database, you create a new data table and then you grant Alice access to the database.
Now, if you log in using the Alice account you'll be able to connect to TestDatabase (by calling USE TestDatabase), but you won't be able to read any rows from TestTable. An attempt to read TestTable generates a SELECT permission denied error.
You can easily reverse this action and revoke Alice's access to TestDatabase using the sp_revokedbaccess stored procedure (you need to be logged on as a user with administrative privileges again):
USE TestDatabase GO EXEC sp_revokedbaccess 'Alice'
If you execute this command and then log in with Alice's account, TestDatabase will not be visible to you. An attempt to call USE TestDatabase would generate an error: Server user 'Alice' is not a valid user in database 'TestDatabase'.
| Note |
For now, just make sure Alice does have access to TestDatabase so you'll be able to experiment further. |
After you've given the user access to the database itself, you'll most likely want to give the user access to objects inside the database—as you've seen, users can't access anything unless they are explicitly given the right to do so.
For fine-tuning user permissions, SQL Server provides you with the SQL-99 commands GRANT and REVOKE. You also have an additional command named DENY. Here you'll see how to use these commands to control object-level access.
The basic syntax for GRANT when setting object-level permissions is as follows:
GRANT <privilege type> ON <resource> TO <username>
You can assign six privileges to SQL Server objects:
SELECT, INSERT, UPDATE, and DELETE are the four rights that apply to data tables and views. Users having the SELECT permission on a data table will be able to read information from that table by using the SELECT statement. Users can have a mix of these permissions—for example, a user can be allowed to insert new rows into a table but can be forbidden to read data from the table.
REFERENCES allow a user to insert rows into a table that has a FOREIGN KEY constraint to another table where the user doesn't have SELECT permissions. This permission was implemented because inserting rows int a table having FOREIGN KEY constraints can give you tips about the data in other tables on which you may not have SELECT rights.
EXECUTE allows the user to run the specified stored procedure.
If you want to give Alice SELECT access to a table named TestTable, you type the following:
If you want to give Alice the right to pass along the permissions she receives (using GRANT), you can use the WITH GRANT OPTION parameter of GRANT. This allows her to grant SELECT access to TestTable to other users:
GRANT SELECT ON TestTable TO Alice WITH GRANT OPTION
DENY is the opposite of GRANT: It prevents the user from accessing the targeted object. DENY overrides any previously issued GRANT statements and overrides any permissions the user may have because of role memberships.
REVOKE eliminates the effect of previously issued GRANT or DENY statements. After revoking all individual permissions, the user will revert to having the default set of permissions specified by any roles of which they're a member.
To remove the SELECT permission on TestTable from Alice, you can call either DENY or REVOKE. REVOKE nullifies the effect of the GRANT statement, which was initially issued because Alice couldn't access TestTable. DENY is even tougher and explicitly denies Alice from accessing TestTable. Let's use REVOKE now:
REVOKE SELECT ON TestTable FROM Alice
If you granted access using WITH GRANT OPTION, you must use CASCADE when revoking the permission. This will remove the SELECT permission from all the accounts Alice granted this permission to:
REVOKE SELECT ON TestTable FROM Alice CASCADE
| Tip |
Keep in mind that GRANT, REVOKE, and DENY allow you to control access down to column level. |
SQL Server has a number of predefined roles (for a full list, you should consult the SQL Server Books Online). Here you'll focus on the commands that create new roles and assign them to database users.
To create a new SQL Server role, you use the sp_addrole stored procedure, which takes as a parameter the name of the new role. While logged in with a system administrator account, create a new role named TestRole:
After adding the role, let's assign the permission to perform SELECT on TestTable to it. You do this using the GRANT command, where instead of a username to assign the permission, you specify the name of a role:
GRANT SELECT ON TestTable TO TestRole
Okay, so you have a role that has an associated permission. In this example, you're testing a role with a single permission, but remember that you can add any number of permissions to TestRole. You can also assign DENY permissions to a role—so you can have roles that are allowed to perform some actions but that are prevented from performing other tasks.
After creating a role, it's time to assign the role to the Alice account. The sp_addrolemember stored procedure take care of that:
EXEC sp_addrolemember TestRole, Alice
Now it's time to see if Alice has gotten her rights to access TestTable again. Log in using the Alice account, select TestDatabase to make it your current database, and make a query on TestTable. It should run without problems:
SELECT * FROM TestTable
| Note |
DENY is always more powerful than GRANT. If you explicitly deny SELECT access on TestTable to Alice, she won't be able to read TestTable, even if she's a member of TestRole. Also, if you explicitly granted to Alice SELECT access on TestTable but assigned her a role that denies the same privilege, Alice couldn't access TestTable. |
You can use the sp_droprolemember stored procedure to remove a role from a user account. Type the following command when logged in using an administrator account on TestDatabase:
EXEC sp_droprolemember TestRole, Alice
After executing this command, Alice can't access TestTable anymore. You can drop roles from the database using sp_droprole:
GRANT, DENY, and REVOKE also deal with the statement-level permissions. With two exceptions, these rights allow the user to create new database objects. The statement-level permissions supported by SQL Server are as follows:
CREATE DATABASE
CREATE DEFAULT
CREATE FUNCTION
CREATE PROCEDURE
CREATE RULE
CREATE TABLE
CREATE VIEW
BACKUP DATABASE
BACKUP LOG
As an example, the following command gives Alice the right to create databases:
GRANT CREATE DATABASE TO Alice
Note that when you issue this command you need to have the master database currently selected, and Alice needs to be granted login permission to the master database before the administrator can run this command. As with object-level permissions, you use WITH GRANT OPTION if you want the grantee to be able to give the received permissions to other users as well.
It's also possible to grant a user the right to create tables within the current database. Make sure the database you want to give access to is your currently selected database and execute a GRANT CREATE TABLE statement, just like in the following example:
USE TestDatabase GRANT CREATE TABLE TO Alice
Now you can log in with the Alice account and attempt to create a new table in TestDatabase—you'll see; it works.
|
|