create service

Wraps the supplied SQL statement in a stored procedure with the specified name and parameters.

Syntax

create service service-name [secure security_options ] [, userpath path] 
			[, alias alias-name]
	type { xml | raw | soap } 
		[[(@parameter_name datatype [(length ) | (precision [, scale ])]
			[= default][output]
	[, @parameter_name datatype [(length ) | (precision [, scale ])]
			[= default][output]]...[)]]
as SQL_statements 
security_options ::= (security_option_item [security_option_item])

Parameters

Examples

Usage

Except for the following differences, the resulting stored procedure behaves the same as a stored procedure created with the create procedure command, following existing stored procedure rules for execution, replication, sp_helptext, and recompilation, and is executable from isql:

  • The resulting stored procedure can be dropped only with the drop service command, not the drop procedure command.

  • The syscomments table is populated with DDL necessary to re-create the create service command.

  • The specified service name cannot create a stored procedure group.

Note: To make a user-defined Web service available through the SAP ASE Web Services engine, you must use the deploy option of sp_webservices. However, the stored procedure for a user-defined Web service is accessible from isql, even if it has not been deployed.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

To use a Web service, you must be granted execute permission explicitly:

Permissions on Objects at

Description

Service creation

When you create a Web service, SAP ASE makes no permission checks on objects, such as tables and views, that are referenced by the service. Therefore, you can successfully create a Web service even though you do not have access to its objects. All permission checks occur when a user executes the Web service.

Web service execution

When the Web service is executed, permission checks on objects depend on whether the Web service and all referenced objects are owned by the same user.
  • If the Web service’s objects are owned by different users, the invoker must have been granted direct access to the objects. For example, if the Web service performs a select from a table that the user cannot access, the Web service execution fails.

  • If a Web service and its objects are owned by the same user, however, special rules apply. The invoker automatically has “implicit permission” to access the Web service’s objects even though the invoker could not access them directly. Without having to grant users direct access to your tables and views, you can give them restricted access with a stored procedure. In this way, a stored procedure can be a security mechanism. For example, invokers of the Web service might be able to access only certain rows and columns of your table.

    A detailed description of the rules for implicit permissions is discussed in the System Administration Guide.

The following describes permission checks for create service that differ based on your granular permissions settings.

SettingDescription
Enabled

With granular permissions enabled, you must have the create procedure privilege. You must have create any procedure privilege to use create service for other users.

Disabled

With granular permissions disabled, you must have the create procedure privilege, be the database owner, or a user with sa_role.

You must be a user with sa_role to use create rule for other users.

Auditing

Values in event and extrainfo columns of sysaudits are:

InformationValues
Event

11

Audit option

create

Command or access audited

create services

Information in extrainfo
  • Roles – current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – original login name, if set proxy is in effect