Changes in Inheritance Behavior

In SAP Sybase IQ versions earlier than 16.0, when you granted the DBA, REMOTE DBA, BACKUP, RESOURCE, and VALIDATE authorities to a group, the underlying permissions were not inherited by members of the group.

In 16.0, the default behavior when granting SYS_AUTH_DBA_ROLE, SYS_RUN_REPLICATION_ROLE, SYS_AUTH_BACKUP_ROLE, SYS_AUTH_RESOURCE_ROLE, and SYS_AUTH_VALIDATE_ROLE (formerly DBA, REMOTE DBA, BACKUP, RESOURCE, and VALIDATE) to a user-defined role allows members of the user-defined role to inherit the underlying system privileges of the role. The WITH NO SYSTEM INHERITANCE clause retains the non-inheritance behavior.

To retain the non-inheritance behavior of these roles after upgrading, include the WITH NO SYSTEM PRIVILEGE INHERITANCE clause in the GRANT ROLE statement, if you have applications that you are changing to use the new GRANT syntax, and want to preserve the non-inheritance behavior.

Note: The WITH NO SYSTEM PRIVILEGE INHERITANCE clause is supported only with these specific roles; any other use results in an error.

For example, you grant User1 the ALTER ANY OBJECT system privilege. You extend User1 to become a role and grant User1 to User2. You want to grant the SYS_AUTH_DBA_ROLE role to User1, but you do not want User2 to inherit the system privileges vested to SYS_AUTH_DBA_ROLE. Execute:

GRANT ROLE SYS_AUTH_DBA_ROLE TO User1 WITH NO SYSTEM PRIVILEGE INHERITANCE;