Ownership of objectsRick Vicik and Todd Needham
Created: March 20, 1992
ABSTRACT
This article discusses suggested conventions to use when creating a front-end tool for SQL Server. Because many front-end tools can access a single SQL Server at any one time, using conventions may prevent contention among the front-ends accessing the server.
A client-server system may contain a single back-end (server) with multiple front-ends (clients). SQL Server follows this model and allows developers to write front-ends that satisfy the needs of users, while using the SQL back-end to store and to retrieve data. A single application may be one of several applications accessing the server at any time; therefore, some conventions should be followed to develop well-behaved front-ends. “Well-behaved” refers to the ability of a front-end application to coexist on the same SQL Server with other front-ends and with user-written applications.
The major areas of contention between multiple front-ends accessing SQL Server are:
Ownership of objects
External system tables (private data-dictionary tables)
Stored procedures, rules, and defaults
Trigger procedures
Obtaining SQL Server catalog information
Sign-on conventions
If a front-end creates database objects (such as tables, stored procedures, triggers, or rules) that will be used by the front-end itself rather than by other applications, the objects should be created under a username that has the same name as the front-end. If a front-end creates objects that are part of an application, those objects should be created under the username of the application developer (current signed-on user).
Doing so eliminates the possibility of name collisions among different front-ends, allows front-ends to use whatever naming convention they choose, and makes it easy for the database administrator to identify and manage objects created by front-ends.
Front-ends may add their own tables to each database they access. These tables store forms, reports, and data-dictionary information, eliminating the need for the front-end to store such information in files external to the database. Other front-ends must be able to differentiate these tables from user tables. For example, if the Maxform front-end adds tables to store its data-dictionary information, the Superfront front-end should not include Maxform’s data-dictionary tables when it lists user tables.
To differentiate such tables from those created by users or by other front-ends, use the following conventions:
1.Create all external system tables belonging to a particular front-end under a username with the same name as the front-end. For example, Maxform should create its tables under the username Maxform so that it is obvious who owns them.
2.All external system tables should have a value of ‘S’ in the type column of their sysobjects row. This makes it easy for other applications to eliminate nonuser tables when presenting a list of tables.
A stored procedure such as sp_set_table_type should be used to change the type column in the sysobjects table to ‘S’. If the following procedure does not exist in the master database, it should be added by the creator of the SQL Server front-end.
CREATE PROCEDURE sp_set_table_type (@name varchar(255))
AS UPDATE sysobjects
SET type='S'
WHERE id=OBJECT_ID( @name )
The input parameter must include the owner name as well as the object name, if it is different from that of the current signed-on user (for example, maxform.dictionary).
The allow updates configuration parameter must be set before this procedure is created so that it has permission to alter system tables; after the procedure is created, allow updates can be reset to its original value.
Create all stored procedures, rules, and defaults that are specific to a front-end under the username for that front-end. Stored procedures, rules, defaults, and so on that are part of an application should be created under the developer’s username, in the same way tables specific to an application are created.
For example, a front-end may dynamically create a stored procedure to access a table when that table is accessed repeatedly, to avoid parsing the SQL statement repeatedly. The front-end can use its own naming convention for this procedure because object names are qualified by the user who created them.
The text of an existing stored procedure, rule, or default can be obtained as follows:
sp_helptext 'ownername.objectname'
When replacing an existing stored procedure, the existing procedure must first be dropped. Because BEGIN TRAN or COMMIT TRAN statements cannot be used with CREATE or DROP statements, the safest approach is to create the replacement procedure under a temporary name, drop the original, and rename the replacement. Unfortunately, the object name in the CREATE statement is then inconsistent with the new name of the object. Rules and defaults that are bound to columns must be unbound before they can be dropped.
Front-ends must also be prepared to handle the error messages produced by rules attached to columns or datatypes, especially when accessing tables created by others.
SQL Server allows only one trigger (such as insert, update, or delete) per table operation. Because each front-end application can install its own triggers, the following conventions segregate each front-end’s trigger logic:
1.If a trigger procedure already exists for the table operation, front-ends should add their trigger logic to the end. If the trigger does not exist, front-ends should create the trigger under a username that is the same as the front-end and should use the following naming convention:
<tablename>_<operation>_TR
2.Each section of trigger logic should begin with two comment lines, the first of which should always be:
/* Foreign key constraint */
or:
/* Other constraint */
For foreign keys, the second comment line should have the form:*
/* Foreign key (col1, col2, ... ) references tablename col1,
col2, ... */
For primary keys referenced by foreign keys, the second comment line should have the form:
/* col1, col2, ... Referenced by foreign key tablename (col1, col2,
...)
on [update][delete][cascade][restrict][nullify] */
The format is similar to the syntax proposed in the ANSI Standard X3.135-1989 for defining foreign key constraints in data definition language (DDL) statements.
Separate actions should be implemented in separate sections. For example, if a delete trigger must enforce cascade delete on two other tables, two sections should be added, each with an appropriate comment line.
There are no restrictions on the format of the second comment line. The text of an existing trigger can be obtained as follows:
sp_helptext 'ownername.triggername'
When a trigger is created on a table that already has a trigger, the existing trigger is automatically replaced. If the replacement contains an error, the original trigger remains.
Trigger sections should execute ROLLBACK, RAISERROR, and RETURN statements if a fatal error is encountered. ROLLBACK is necessary because the trigger receives control after the modifications have actually been performed; RAISERROR causes the client application to receive a FAIL status; RETURN terminates trigger execution.
Two front-ends may install trigger logic that is redundant or conflicting on a logical level. For example, two front-ends may attempt to enforce referential integrity between a pair of tables or may attempt to create sequential row numbers. Avoiding redundancy by examining and decoding trigger logic is a difficult problem and cannot be handled without manual intervention. The purpose of this comment convention is to simplify that task.
As with rules, front-ends also must be prepared to handle the error messages produced by triggers, especially when accessing tables created by others.
Developers should use the catalog stored procedure interface to obtain catalog information whenever possible to ensure compatibility with future releases, gateways, and so on. This interface is available in SQL Server version 4.2 and later. These stored procedures can be retrofitted to earlier releases of SQL Server.
The catalog stored procedure definitions in this section are geared toward applications programmers or developers of tools for users. Rather than presenting all the catalog information for a database environment, the stored procedures present a filtered view of the database by presenting information on database objects accessible by the current user. The defined catalog stored procedures are:
sp_tables: Lists the queryable objects (tables, views) available in the database management system (DBMS) environment.
sp_columns: Describes the column metadata for a single table.
sp_stored_procedure: Lists the executable objects (such as stored procedures) available in the DBMS environment.
sp_sproc_columns: Describes the metadata for stored procedure parameters and results set columns for a single stored procedure.
sp_indexes: Describes the indexes for a single table.
sp_pkeys: Describes the logical primary key for a single table.
sp_fkeys: Describes the primary key/foreign key relationships between a group of tables.
sp_table_privileges: Describes the permission information for a single table.
sp_column_privileges: Describes the column permission information for a single table.
sp_server_info: Lists the configuration and capabilities information for a gateway.
SQL Server uses a unique combination of login identification and user identification (username) to control sign-on security. Login identification applies to the entire SQL Server; username is specific to each database. In most cases, there is a one-to-one correspondence between usernames and login identifications, but a login identification may also be assigned to a default database by the system administrator. It is possible to alias a particular login identification to a different username within a database. Permissions are controlled within each database by a GRANT or a REVOKE scheme based on the username within the database. The group to which a username belongs also affects permissions. The special username ‘guest’ allows login identifications that have no corresponding username to gain access to a database.
Front-ends must specify the servername, login identification, and password in order to connect to a SQL Server. Parameters such as a database name, an application name, and so on are optional login specifications.
The servername can be obtained from an environment parameter, an initialization file, or the user. General-purpose applications should use the MicrosoftÒ LAN Manager NetServerEnum application programming interface (API) to get the names of all the servers on the network, filter out those that are not SQL Servers using the appropriate server status flag bit, and allow the user to select from the resulting list of servernames.
Login identification and password are normally solicited from the user, but the front-end should also permit the username to be extracted from the network, eliminating a redundant sign-on and providing greater security integration.
Front-ends should provide the capability to set the optional connection parameters, such as the database name and the application name. The application name should be set to the name of the front-end as the default (for example, Maxform), but the designer of the application should be able to override it where appropriate (for example, Payroll). Similarly, if the Payroll application can run only against the financial database, the database parameter should be specified by the application, not by the user. A more general-purpose application might present a list of available databases to the user for selection.
*This comment is shown on two lines because of spacing restrictions in this document.