SQL Anywhere 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.

In pre-16.0 databases, authorities were database-level permissions. For example, a user with BACKUP authority could back up the database. Some authorities also bundled object-level permissions. For example, a user with PROFILE authority could perform application profiling and database tracing tasks, which involve using system procedures that aren't otherwise available for use. You could not create new authorities, alter the permissions they were comprised of, or drop them. You could grant administrative rights (WITH GRANT), but could not limit the grant to only being an administrator.

Now, roles replace authorities in functionality with the added benefit that you can create new roles, alter the privileges they comprise, and drop them. Switching to roles and privileges means you have more granular control over the privileges you want to grant to a user, and an easier way to grant them to other users. You can also grant the role to a user with administrative rights only, which means the user can grant and revoke the role, but cannot exercise the underlying privileges.

In pre-16.0 databases, permissions allowed you to create, modify, query, use, or delete database objects such as tables, views, and users. For example, you might have SELECT permission on a table.

Now, privileges replace permissions in functionality, with the added benefit that there are far more privileges than permissions. For every privileged operation that can be performed on a database object, there is a grantable privilege. You can grant privileges individually to users, or grant a role to them. The term permission has not gone away; however, it has changed slightly. Previously, the word permission meant a grantable capability. Now, the word permission means the result of an evaluation of whether an operation can be performed. For example, you have permission to alter the table if you are the owner or you have the ALTER ANY TABLE system privilege.

In pre-16.0 databases, groups were a collection of one or more users whose authorities and permissions were determined by what is set at the group level. A user was granted group status, and then other users were granted membership in that group.

Now, the group paradigm is achieved using user-extended roles. If you have a user with a set of privileges that you want to grant to other users, you can extend the user to become a user-extended role, and then grant that role to other users. The group paradigm can also be achieved by creating a standalone role that has the roles and privileges you want to grant to users, without being associated with a user ID.

When you upgrade a pre-16.0 database, the upgrade process automatically converts your existing authority, permission, and group hierarchy into an equivalent role, privilege, and user-extended role hierarchy. For every pre-16.0 authority, there is an equivalent compatibility role. These roles are easily identifiable in the database because their names start with SYS_AUTH. Compatibility roles contain the system privileges required for pre-16.0 users to perform the same operations they could perform using an authority.

 See also