Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SAP Sybase SQL Anywhere 16.0 » SQL Anywhere Server - Database Administration » User and database security » User security (roles and privileges) » Upgrading to role-based security


What happened to authorities, permissions, and groups?

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.


You can use a SQL Anywhere 16.0 database server with a pre-16.0 database. When you do, full backwards compatibility is provided for that database, and its security model is not changed.

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