Starting with ESD #3, Sybase IQ 12.6 restricts permissions by default.
Prior to ESD #3, Sybase IQ allowed query servers to create and drop users, groups, and group memberships and to change permissions on objects created by write servers. This caused conflicts with permissions set by write servers.
Now only write servers can:
Modify TABLE permissions (select, insert, delete, update, references and alter) on tables owned by the write server.
Modify EXECUTE permission on stored procedures and functions owned by the write server. For related information, see “Multiplex DDL restrictions [CR 367460]”.
Execute GRANT/REVOKE of DBA, RESOURCE, GROUP, or MEMBERSHIP.
Create a user (GRANT CONNECT TO... where the user does not exist).
Drop a user (REVOKE CONNECT FROM...).
The following objects, when created on a write server, are owned by that server and cannot be dropped or altered on a query server by default:
Tables
Views
Indexes
Data types
Messages
Constraints
Procedures
Functions
The following, when created, altered, or dropped on a write server, propagate to query servers:
IQ base tables, IQ global temporary tables, and indexes on either IQ base or global temporary tables
IQ referential integrity constraints and IQ check constraints
User-defined data types (domains)
Messages
Users and groups
Permissions
Views
Stored procedures and functions
The following are permitted on query servers:
CREATE, ALTER, and DELETE EVENT
Changing a user password via GRANT CONNECT TO… IDENTIFIED BY… when the user already exists
The database options MPX_GLOBAL_TABLE_PRIV and MPX_LOCAL_SPEC_PRIV, described in the following sections, let you override the new permission restrictions.
Sybase strongly recommends that you create users domains
and messages on the write server only, to avoid static collisions.
For details, see “Setting multiplex permissions [CR 404004].”
Lets a query server grant and revoke permissions on objects created by the write server.
ON, OFF
DBA permissions are required to set this option. Can be set only for the PUBLIC group. Takes effect immediately.
OFF
To enable this option, set it ON. Setting MPX_GLOBAL_TABLE_PRIV ON allows grant and revoke of table and execute permissions of write server objects on a query server.
“New database option MPX_LOCAL_SPEC_PRIV.”
This option was omitted from the chapter “Database
Options” in the Sybase IQ Reference Manual.
Lets a query server create and drop users, groups, and group memberships.
0 to 63
A bit mask indicating the corresponding special privileges to be granted and revoked on a query server:
0x01 = DBA
0x02 = create user via GRANT CONNECT and drop user via REVOKE CONNECT
0x04 = RESOURCE
0x10 = GROUP
0x20 = MEMBERSHIP
To
combine two or more privileges, add the bit masks in hexadecimal
(base 16), then convert to decimal to determine the value for the
option. For example, to combine RESOURCE and GROUP privileges
on a database, use the formula 4 + 16
(10 in base 16) = 20 and set MPX_LOCAL_SPEC_PRIV to
20.
To allow all privileges, you must set all bits. To do this, set MPX_LOCAL_SPEC_PRIV to 63.
Can be set only for the PUBLIC group. Takes effect immediately.
0
To enable the MPX_LOCAL_SPEC_PRIV option, set it to the appropriate value between 1 and 63 (See Allowed values.) DBA permissions are required to set this option. This option takes effect immediately.
“New database option MPX_GLOBAL_TABLE_PRIV”.
This option was omitted from the chapter “Database
Options” in the Sybase IQ Reference Manual.
