When you upgrade a database, users that were granted authorities in pre-16.0 databases are automatically granted an equivalent compatibility role for that authority. If a user had the ability to administer the previous authority, the user has the ability to administer the compatibility role.
For ease of transition, the naming convention for each compatibility role retains the original authority name, but prefaces it with "SYS_AUTH_" and suffixes it 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. Once each underlying system privilege has been granted to at least one other role, you can drop the original compatibility role. When you migrate a compatibility role to a user-defined role, all users that were granted the compatibility role are automatically granted the new user-defined role. The compatibility role is automatically dropped once it has been migrated. However, you can restore compatibility roles using the CREATE ROLE statement.
Backwards compatibility for SQL statements has been provided so applications that grant or revoke authorities continue to work. However, the old syntax is deprecated and you should consider changing your applications to use the new SQL syntax for roles.
The following table shows authorities and the compatibility roles they become when a database is upgraded.
| Pre-16.0 Authority | Equivalent Role | Description |
|---|---|---|
| BACKUP authority | SYS_AUTH_BACKUP_ROLE compatibility role | Allows a user to back up databases and transaction logs with archive or image backups by using the BACKUP statement or dbbackup utility. |
| DBA authority | SYS_AUTH_DBA_ROLE compatibility role SYS_AUTH_SA_ROLE compatibility role SYS_AUTH_SSO_ROLE compatibility 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 authority | SYS_AUTH_PROFILE_ROLE compatibility role | Allows a user to perform profiling, tracing, and diagnostic operations. |
| READCLIENTFILE authority | SYS_AUTH_READCLIENTFILE_ROLE compatibility role | Allows a user to read files on the client computer, for example when loading data from a file on a client computer. |
| READFILE authority | SYS_AUTH_READFILE_ROLE compatibility role | Allows a user to use the OPENSTRING clause in a SELECT statement to read a file. |
| REMOTE DBA authority | 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 utility, and a MobiLink user to perform
synchronization activities by using the dbmlsync utility. It does
not allow administration of replication, however. The SYS_REPLICATION_ADMIN_ROLE system role is provided for replication administration. |
| RESOURCE authority | SYS_AUTH_RESOURCE_ROLE compatibility role | Allows a user to create database objects, such as tables, views, stored procedures, and triggers. |
| VALIDATE authority | SYS_AUTH_VALIDATE_ROLE compatibility role | Allows a user to perform database, table, index, and checksum validation by using the VALIDATE statement or dbvalid utility. |
| WRITECLIENTFILE authority | SYS_AUTH_WRITECLIENTFILE_ROLE compatibility 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 authority | SYS_AUTH_WRITEFILE_ROLE compatibility role | Allows a user to execute the xp_write_file system procedure. |
With an authority-based security model, if a user did not need all of the permissions vested in an authority, there was no way to limit the grant. As a result, users were often granted more permissions than necessary, a potential security concern. The role-based security model addresses this concern, allowing privileges to be granted at a granular level.
Since the migration process ensures that all of a user's privileges are preserved during migration, it is strongly recommended that you review the compatibility role grants and of each user post-migration and adjust membership as necessary.