support new features:

 

ROLE-BASED SECURITY : 

    Overview of role-based security model   A complete set of system privileges and roles have been added to increase security by providing you precision over the capabilities you want your users to have. 

  For every privileged operation that can be performed in the system, a system privilege has been created. Roles combine privileges into logical groups. SQL Anywhere provides you with many predefined roles, 

  but you can also create your own roles. See User security (roles and privileges).

    If you are upgrading, authorities, permissions, and groups have been replaced with roles, privileges, and user-extended roles. See Upgrading to role-based security.

 

NEW PRIVILEGES:

  TRUNCATE object-level privilege   A new object-level privilege, TRUNCATE, has been added to allow a user to truncate a specified table or materialized view. This privilege did not exist as an object-level permission in previous releases of the software. See Object-level privileges.

  LOAD object-level privilege   A new object-level privilege, LOAD, has been added to allow a user to load a specific table. This privilege did not exist as an object-level permission in previous releases of the software. See Object-level privileges.

 

 

System procedures:   Following are the system procedures added in support of role-based security:

  sp_objectpermission system procedure   This procedure generates a report on the object privileges granted to the specified object, dbspace, role, or user name. You must rebuild existing databases to get this system procedure. See sp_objectpermission system procedure.

  sp_displayroles system procedure   Returns all roles granted to the specified system privilege, system role, user-defined role, or user name, or displays the entire hierarchy tree of roles. See sp_displayroles system procedure.

  sp_has_role system procedure   Returns whether the invoker of the procedure has been granted the specified system privilege or user-defined role. See sp_has_role system procedure.

  sp_proc_priv system procedure   Returns the list of system privileges required to run a procedure. See sp_proc_priv system procedure.

  sp_auth_sys_role_info system procedure   Returns the mapping of authorities from previous versions of SQL Anywhere to their corresponding compatibility roles. See sp_auth_sys_role_info system procedure.

  sp_sys_priv_role_info system procedure   Returns the mapping of system privileges to their underlying system roles. See sp_sys_priv_role_info system procedure.

 

Database options   Following are the database options added in support of role-based security:

  min_role_admins option   Sets the minimum number of administrators required for a role. See min_role_admins option.

  db_publisher option   This option stores the user ID of the database publisher. It can be set in the same way as other database options, but can also be set using the GRANT PUBLISH and REVOKE PUBLISH statements. See db_publisher option.

 

Database server options   The behavior of the following database server options has been changed to support role-based security:

  -gu database server option   Setting the value to DBA means that only a user with the SERVER OPERATOR privilege can create or drop databases. See -gu database server option.

  -gk database server option   Setting the value to DBA means that only a user with the SERVER OPERATOR privilege can shut down a database server with the dbstop utility. See -gk database server option.

  -gd database server option   Setting the value to DBA means that only a user with the SERVER OPERATOR privilege can start databases. See -gd database server option.

  -gl database server option   Setting the value to DBA means that only a user with the LOAD ANY TABLE or ALTER ANY TABLE privileges can execute the LOAD statement. The user must have the SELECT ANY TABLE privilege to execute the UNLOAD statement. See -gl database server option.

 

New and changed SQL statements   Following are the SQL statements added or changed in support of role-based security:

  Changes to the SELECT statement   You can now specify a FOR JSON clause in a SELECT statement. The FOR JSON clause specifies that the result set is to be returned in JSON format. You can specify one of the following JSON modes:

  RAW   Allows you to return each row in the query result set as a flattened JSON representation.

  AUTO   Allows you to return the query result set as nested JSON objects based on query joins.

  EXPLICIT   Allows you to specify columns as simple values, objects, and nested hierarchical objects to produce uniform or heterogeneous arrays.

  See SELECT statement and Use of the FOR JSON clause to retrieve query results as JSON.

  Changes to the GRANT statement   The GRANT statement has been enhanced to allow granting of roles and privileges. See GRANT statement.

  The old syntax for granting authorities, permissions, and membership in groups is still supported but deprecated. See GRANT statement (authorities and groups) (deprecated).

  Changes to the REVOKE statement   The REVOKE statement has been enhanced to revoke roles and privileges. See REVOKE statement.

  The old syntax for granting authorities, permissions, and membership in groups is still supported but deprecated. See REVOKE statement (authorities and groups) (deprecated).

  New CREATE ROLE statement   Creates or replaces a role, converts a user to a role, or manages role administrators on a role. See CREATE ROLE statement..

  New ALTER ROLE statement   Migrates a compatibility role (roles that begin with SYS_AUTH_) to a user-defined role, then drops the compatibility role. See ALTER ROLE statement..

  New DROP ROLE statement   Removes a user-defined or a compatibility role from the database, or converts a user-extended role to a regular user. See DROP ROLE statement..

  New GRANT ROLE SYS_RUN_REPLICATION_ROLE statement   This statement grants the role required to run replication. See GRANT ROLE SYS_RUN_REPLICATION_ROLE statement [MobiLink] [SQL Remote].

  New GRANT ROLE SYS_REPLICATION_ADMIN_ROLE statement   This statement grants the role required to administer replication. See GRANT ROLE SYS_REPLICATION_ADMIN_ROLE statement [MobiLink] [SQL Remote].

  Changes to GRANT PUBLISH and REVOKE PUBLISH statements   Previously, the GRANT PUBLISH and REVOKE PUBLISH statements updated the database publisher information in the ISYSAUTHORITY system table. However, as part of role-based security, the database publisher user ID is now stored as the db_publisher database option. These statements now update the value of the db_publisher database option, instead of updating ISYSUSERAUTHORITY. See GRANT PUBLISH statement [SQL Remote] and REVOKE PUBLISH statement [SQL Remote].

 

Changes to the catalog   Following are the catalog changes made in support of role-based security. The changes are made to the catalog table, but since you can only access the corresponding system view, the view is mentioned as well.

  ISYSROLEGRANT system table / SYSROLEGRANT system view

New. Stores information about role membership and type of membership

  SYSROLEGRANTS consolidated view

New. Same as SYSROLEGRANT, but includes two additional columns: role_name, and grantee_name.

  ISYSROLEGRANTEXT system table / SYSROLEGRANTEXT system view

New. Stores the syntax extensions for SET USER and CHANGE PASSWORD privileges.

  SYSGROUP compatibility view

This view was previously a system view, but is now a compatibility view.

  SYSGROUPS compatibility view

This view was previously a consolidated view, but is now a compatibility view.

  SYSUSER system view

New column: dual_password

  SYSUSERAUTHORITY compatibility view (deprecated)

This view has changed from a system view to a compatibility view. The underlying ISYSAUTHORITY system table has been removed, but the view is retained for compatibility purposes.

  SYSTABLEPERM system view

New columns: loadauth and truncateauth

  SYSTABAUTH consolidated view

New columns: loadauth and truncateauth

 

 

Collapse/expand section LDAP user authentication support

  SQL Anywhere now provides support for LDAP user authentication. The following list provides information on what has been added or changed to support LDAP user authentication. See LDAP authentication.

  Changes to the root login policy   The following login policy options have been added to the root login policy to support LDAP user authentication:

    ldap_primary_server

    ldap_secondary_server

    ldap_auto_failback_period

    ldap_failover_to_std

    ldap_refresh_dn

  System procedures   The following system procedures have been added or enhanced to support LDAP user authentication:

    sa_get_ldapserver_status system procedure (new)

    sa_get_user_status system procedure (enhanced)

    New columns (user_dn, user_dn_cached_at, password_change_state, password_change_first_user, password_change_second_user) are reported by the sa_get_user_status system procedure.

  Database server and database options   The following database options have been added or enhanced to support LDAP user authentication:

    login_mode option

    trusted_certificates_file option

  SQL statements   The following SQL statements have been added to support LDAP user authentication:

    CREATE LDAP SERVER statement

    ALTER LDAP SERVER statement

    DROP LDAP SERVER statement

    VALIDATE LDAP SERVER statement

    The following SQL statements have been enhanced to support LDAP user authentication:

      ALTER USER statement (new REFRESH DN syntax)

      CREATE LOGIN POLICY statement (new login policy options)

      ALTER LOGIN POLICY statement (new login policy options)

      COMMENT statement (ability to comment on LDAP server configuration objects)

Catalog changes   The following catalog changes have been made to support LDAP user authentication:

  SYSLDAPSERVER system view (new)   The SYSLDAPSERVER system view contains one row for each LDAP SERVER object configured in the database. See SYSLDAPSERVER system view.

  SYSUSER system view   The SYSUSER system view has two new columns related to LDAP user authentication: user_dn and user_dn_cached_at. See SYSUSER system view.