Role-based security replaces the authority-based security model used in versions of
SAP Sybase IQ earlier than 16.0.
What Happened to Authorities, Permissions, and Groups?
SAP Sybase IQ 16.0 introduces a role-based security model. Whereas before you had authorities, permissions, object-level permissions, and groups, you now have roles, system privileges, object-level privileges, and user-extended roles.
Authorities Become Compatibility Roles
When you upgrade a database, users that were granted authorities in pre-16.0 databases are automatically granted an equivalent compatibility role for that authority. If a user had the ability to administer the previous authority, the user has the ability to administer the compatibility role.
Permissions Become Privileges
In pre-16.0 databases, there were object-level permissions such as ALTER and SELECT for tables and views, and so on. While statements that grant or revoke these permissions still work, these permissions are now referred to as privileges, but retain the same name.
Groups Become Roles
During the upgrade of a pre-16.0 database, each group is converted to a user-extended role of the same name. Members of the original group are automatically granted the new role and all of its underlying privileges. Authorities and object-level permissions that were granted to the original group are converted to their equivalent roles and system privileges and granted to the user-extended role.
Change to Concept of a Super-User (DBA Authority)
In pre-16.0 databases, you could create a super-user by granting them DBA authority. Users with DBA authority could perform any privileged task in the system. When you upgrade your database, any users that had DBA authority gets the SYS_AUTH_DBA_ROLE compatibility role, and automatically receives exercise and administration rights for all roles and privileges that are present at the time of upgrade.
Changes to the GRANT Statement Syntax
If you have applications that use the pre-16.0 GRANT statement syntax for authorities, permissions, and groups, you should modify them to use the updated syntax for roles and privileges. The table below shows you what the statements should be changed to. Use of the old GRANT syntax for authorities, permissions, and groups is supported, but deprecated.
Changes to the REVOKE Statement Syntax
If you have applications that use the pre-16.0 REVOKE statement syntax for authorities, permissions, and groups, you should modify them to use the updated syntax for roles and privileges. The table below shows you what the statements should be changed to. Use of the old REVOKE syntax for authorities, permissions, and groups is supported but deprecated.
Changes to REMOTE DBA
In pre-16.0 databases, REMOTE DBA authority allowed a user to perform replication and synchronization operations using dbremote and dbmlsync.
Changes in administering the database publisher
In pre-16.0 databases, the database publisher was controlled by granting the PUBLISH authority by using the GRANT PUBLISH and REVOKE PUBLISH statements. The current publisher could be determined by querying the CURRENT PUBLISHER special value.
Changes to System Procedures that Perform Privileged Operations
As part of the enhanced security of role-based security, the way in which privileged system procedures run has changed. Pre-16.0, a privileged system procedure ran with the privileges of its owner, typically dbo, and is referred to as the SYSTEM PROCEDURE DEFINER model. With 16.0, privileged system procedures run with the privileges of the person executing it, and is referred to as the SYSTEM PROCEDURE INVOKER model.
Grant Compatibility Roles
Granting a compatibility role is semantically equivalent to granting each of its underlying system privileges and roles.
Dropping a Compatibility Role
All compatibility roles, with the exception of SYS_AUTH_SA_ROLE and SYS_AUTH_SSO_ROLE can be dropped. SYS_AUTH_SA_ROLE and SYS_AUTH_SSO_ROLE are dropped automatically when SYS_AUTH_DBA_ROLE is dropped.
Re-creating Compatibility Roles
To re-create dropped compatibility roles, use the CREATE ROLE statement and specify the compatibility role name.
DBO System Role in a Multiplex Environment
By default, the DBO system role is granted the SYS_AUTH_DBA_ROLE compatibility role, ensure that the DBO system role is granted all privileges necessary to execute multiplex management stored procedures.
Backward Compatibility in SAP Sybase IQ 16.0
Grant and revoke syntax for role-based security differs significantly from authority-based security. However, SAP Sybase IQ 16.0 is fully backward compatible with authority-based syntax.