When you upgrade to 16.0, users who are granted authorities in the earlier version are automatically granted an equivalent compatibility role. If the user previously had the ability to administer the authority, he or she has the ability to administer the compatibility role in 16.0.
The naming convention for each compatibility role retains the original authority name, but is prefaced with "SYS_AUTH_" and suffixed with "_ROLE". For example, the authority BACKUP becomes the role SYS_AUTH_BACKUP_ROLE, authority RESOURCE becomes role SYS_AUTH_RESOURCES_ROLE, and so on.
You cannot modify compatibility roles. However, you can migrate them to a user-defined role, and then modify them. When you migrate a compatibility role to a user-defined role, all users who are granted the compatibility role are granted the new user-defined role. Once each underlying system privilege is granted to at least one other role, you can drop the compatibility role. To restore compatibility roles, use the CREATE ROLE statement.
SQL statements are backward compatibility to support applications that grant or revoke authorities. However, the old syntax is deprecated and SAP recommends that you changed your applications to use the new SQL syntax for roles.
This table shows each authority and its equivalent compatibility role.
| Authority | Compatibility Role | Description |
|---|---|---|
| BACKUP | SYS_AUTH_BACKUP_ROLE | Allows a user to back up databases and transaction logs with archive or image backups by using the BACKUP DATABASE statement or dbbackup utility. |
| DBA | SYS_AUTH_DBA_ROLE SYS_AUTH_SA_ROLE SYS_AUTH_SSO_ROLE |
Allows users to perform all possible privileged operations. Users
with the SYS_AUTH_DBA_ROLE role can create database objects and
assign ownership of these objects to other user IDs, change table
structures, create new user IDs, revoke permissions from users, back
up the database, and so on. Of the possible privileged operations that the SYS_AUTH_DBA_ROLE compatibility role can perform, the SYS_AUTH_SA_ROLE compatibility role allows the user to perform all database administration-related activities, such as creating tables, and backing up data. Of the possible privileged operations that the SYS_AUTH_DBA_ROLE compatibility role can perform, the SYS_AUTH_SSO_ROLE compatibility role allows the user to perform the security and access-related administration activities, such as creating users, and granting privileges on objects. |
| PROFILE | SYS_AUTH_PROFILE_ROLE | Allows a user to perform profiling, tracing, and diagnostic operations. |
| READCLIENTFILE | SYS_AUTH_READCLIENTFILE_ROLE | Allows a user to read files on the client computer, for example when loading data from a file on a client computer. |
| READFILE | SYS_AUTH_READFILE_ROLE | Allows a user to use the OPENSTRING clause in a SELECT statement to read a file. |
| REMOTE DBA | SYS_RUN_REPLICATION_ROLE system role SYS_REPLICATION_ADMIN_ROLE system role |
Allows a SQL Remote user to perform replication
activities by using the dbremote and
dbmisync utilities. It does not, however,
allow administration of replication. The SYS_REPLICATION_ADMIN_ROLE system role is provided for replication administration. |
| RESOURCE | SYS_AUTH_RESOURCE_ROLE | Allows a user to create database objects, such as tables, views, stored procedures, and triggers. |
| VALIDATE | SYS_AUTH_VALIDATE_ROLE | Allows a user to perform database, table, index, and checksum validation by using the VALIDATE statement or dbvalid utility. |
| WRITECLIENTFILE | SYS_AUTH_WRITECLIENTFILE_ROLE | Allows a user to write to files on a client computer, for example, when using the UNLOAD TABLE statement to write data to a client computer. |
| WRITEFILE | SYS_AUTH_WRITEFILE_ROLE | Allows a user to execute the xp_write_file system procedure. |
In an authority-based security model, there was no way to limit the grant if a user did not need all of the permissions vested in an authority. This resulted in users often being granted more permissions than necessary, a potential security concern. The role-based security model addresses this concern by allowing system privileges to be granted at a granular level.
Since the migration process ensures that all existing privileges are preserved at the user and group level, SAP recommends that, after migration, you review the compatibility role grants for each user and adjust membership as necessary.