Date: July 5, 2004
SQL Server 2000 Design and Implementation, Part XI -
Security...
This series of articles is focusing primarily on the physical data store Microsoft developers are most likely to use for their applications, SQL Server. The main reference for this series of articles is MCSE: SQL Server 2000 Design by Israel and Jones.
Date: July 5, 2004
This series of articles is focussing primarily on the physical data store Microsoft developers are most likely to use for their applications, SQLServer. The main reference for this series of articles is MCSE: SQLServer 2000 Design by Israel and Jones.
Here's a quick overview of what we've covered so far as well as what we shall be covering:
Article I: we started with developing a logical data model, before looking at how we go about implementing the physical database in SQLServer 2000.
Article II: creating and maintaining databases, in particular the files that comprise the database and how they can be split into filegroups and why they should be on occasion.
Article III: creating and maintaining tables - looking at what we can store in tables, and how we do so.
Article IV: data integrity - default values, check constraints and rules, primary keys, unique constraints, as well as foreign keys and relationships.
Article V: indexes and the related area of statistics.
Article VI: creating and maintaining the various database objects that exist- views, stored procedures, transactions, user defined functions and triggers. This concludes the look at the physical design per se. The remainder of the articles look at issues relating to data access.
Article VII: accessing data
Article VIII: modifying data
Article IX: importing and exporting data
Article X: locking
Article XI: security
Article XII: analysing and optimising data access
Thus far we've reached article XI: security.
There are two levels of security:
Whichever method is used for authentication once SQLServer receives the login name and password these are compared to the values stored in the Master.syslogins table and determines whether the user is successfully authenticated or not.
For someone who has logged into SQLServer, a user must be created in a database and mapped to a login so that data can be manipulated within that database. If access to another database is needed, then a different user must be created in the second database and mapped to the same login. Each database contains a Sysusers table that defines each user and the login to which they are mapped. When a user is added to the database, the server inserts their login ID into this table along with a unique user ID that is generated.
As more and more objects are created the administrative burden of maintaining security increases. Roles ease this burden in the same way that groups are used to manage NT users. The rights that are granted to roles are combined to determine their level of access with the DENY permission overriding other rights.
Enterprise manager can be used to administer roles. A number of stored procedures are also available: SP_ADDROLE, SP_DROPROLE, SP_ADROLEMEMEBER, SP_DROPROLEMEMEBER
SQLServer contains a second kind of role called an application role that allows limiting of access to a specific application. This allows the application to determine how a user can access or modify data with the database. Application roles are a little different: first, application roles have no members. Secondly, an application role is inactive by default. Once connected to SQLSever the application runs SP_SETAPP-ROLE to activate the role using the rolename and a password. Lastly, all permissions assigned to the current user are removed and only those permissions assigned to the application role are applied. Permissions are assigned to application roles in the same manner that they are assigned to users or user roles.
Next is the topic of object level security, i.e. controlling with more granularity what a user can do once logged in. There are a number of different types of rights that can be granted to a user or role via the GRANT command. The options partly depend on the type of object being considered. Similarly you can deny specific rights with the DENY command. See Books Online for the syntax of the GRANT and DENY commands.
All data is stored in a table in SQLServer, including system information. When a table is created only the owner of the table and the owner of the database are allowed to access the table. If other users need access they must be explicitly granted permissions. The standard table rights are configurable (SELECT, INSERT, UPDATE, DELETE), plus REFERENCES which allows a user to reference foreign keys to the object and also allows SCHEMABINDING references in view and functions.
Tables can also be used to limit access to data by storing the data in separate tables with different rights granted to different users. A vertical partition of a table would be an example. A similar effect can be achieved by limiting access to specific columns, which can be achieved via the GRANT/ DENY commands.
You can also control data access via stored procedures, triggers, user defined functions and views. Views can be used to limit data access in themselves but the same object permissions are also available as per tables.
Stored procedures provide an excellent security mechanism as they limit what users can do to the functionality provided by the stored procedures. The only security right that is assignable for stored procedures is the EXECUTE right which allows a user to execute the stored procedure, pass in parameters, receive parameters and results. User defined functions are very similar in the security context.
Triggers can implement a security plan in a number of ways. Since a trigger will execute whenever a data modification is made to a table, the trigger can contain code that checks business security rules. Triggers do not receive rights by themselves; rather, the rights to execute an INSERT, UPDATE or DELETE statement on a table implicitly allow the user to execute the associated trigger that has been defined for the table. The right to execute the trigger, however, does not extend any permission to other objects that may be referenced by the trigger. If a trigger modifies another table, the ownership chain must remain intact or the user must have rights to modify the table referenced by the trigger.
Ownership chains are a consideration as objects build on other objects: views contain tables, stored procedures access views, etc. Each of these objects implements its own security. SQLServer uses ownership chains to check the security of an object. An ownership chain is an implicit link between the owners of two objects that access one another. If the same user owns both objects, then an ownership chain is intact. If not, the ownership chain is broken. Ownership chains allow the original owner of the lowest level object to retain control over the object.
Examples:
Granting rights to a table
1 EXEC SP_ADDLOGIN 'Chris','secure'
2 EXEC SP_ADDUSER 'Chris'
3 GRANT SELECT ON <tablename> TO PUBLIC
1 Creates a new login.
2 Adds the user to the current database, by default as part of the public role.
3 Grant the role access to a specific database.
Granting rights to a selected column in a table
GRANT SELECT ON <tablename> (<columnname>) TO PUBLIC
Granting rights to other objects
GRANT SELECT ON <viewname> TO PUBLIC
GRANT EXECUTE ON <spname> TO PUBLIC
etc.
There we leave the topic of security. In the next article, the last article in the series, we look at how we can analyse and optimising data access in SQLServer.
MCSE: SQLServer 2000 Design
Israel and Jones
Sybex
MSDN
Books Online