Following is a list of product-wide additions introduced in version 16.0. For information about supported platforms and versions,
see ![[external link]](gif/external.gif)
http://www.sybase.com/detail?id=1061806.
New security model: Role-based access control (RBAC)SQL Anywhere 16.0 introduces a new role- and privileged-based security model to replace the former authorities and permissions security model. The new role-based security model provides you with granular control over the privileged tasks that users can perform, and simpler administration of access control.
For existing SQL Anywhere customers who are upgrading to version 16.0, backwards compatibility has been provided so that your applications will continue to function once your database has been upgraded. A special chapter has been provided for you to understand the difference between the models, what takes place automatically when you upgrade, and what you should consider doing if you have applications that use SQL Anywhere (such as updating your GRANT and REVOKE statement calls). See Upgrading to role-based security.
Tutorials are available here: Tutorial: Granting roles and privileges (Sybase Central), and Tutorial: Granting roles and privileges (SQL).
Here is a brief overview of new and changed features provided in support of the new role-based security model.
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 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.
New 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.
Automatic unlocking of user accounts The root_auto_lock_time (root login policy only) and auto_unlock_time login policy options allow you to determine the automatic unlocking time period for users locked out of the database due to failed login attempts. These options are available in the CREATE LOGIN POLICY and ALTER LOGIN POLICY statements. See Automatic unlocking of user accounts.
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.
| Catalog item | Change |
|---|---|
|
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 |
Changes to the SQL Anywhere plug-in to support roles and privileges Sybase Central has undergone many changes to support the new role-based security model. Be aware that performing privileged database tasks—such as creating a table—may require more privileges to perform in Sybase Central than it would using SQL statements. This is because Sybase Central performs additional privileged tasks while you are using it such as populating its folders (Views, Users, and so on) for display.
If you are unsure of the privileges required to perform a task, refer to the documentation for performing the task in Sybase Central.
LDAP user authentication supportSQL 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:
See Root login policy.
System procedures The following system procedures have been added or enhanced to support LDAP user authentication:
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:
SQL statements The following SQL statements have been added to support LDAP user authentication:
The following SQL statements have been enhanced to support LDAP user authentication:
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.
 |
Discuss this page in DocCommentXchange.
|
Copyright © 2014, SAP AG or an SAP affiliate company. - SAP Sybase SQL Anywhere 16.0 |