If you have applications that use the pre-16.0 GRANT statement syntax for authorities, permissions, and groups, you should modify them to use the updated syntax for roles and privileges. The table below shows you what the statements should be changed to. Use of the old GRANT syntax for authorities, permissions, and groups is supported, but deprecated.
In pre-16.0 databases, DBA, REMOTE DBA, RESOURCE, and VALIDATE authorities were non-inheritable. When your database is upgraded, the WITH NO SYSTEM PRIVILEGE INHERITANCE clause is specified to ensure that inheritance behavior remains consistent with previous releases.
Also, in pre-16.0 databases, users that were granted DBA and REMOTE DBA authorities automatically could grant them to others. The WITH ADMIN clause in the new syntax ensures that administration rights behavior remains consistent with previous releases.
| Pre-16.0 Syntax | New Syntax |
|---|---|
| GRANT DBA TO <grantee>[,...] |
GRANT ROLE SYS_AUTH_DBA_ROLE TO <grantee> [,...] WITH ADMIN OPTION WITH NO SYSTEM PRIVILEGE INHERITANCE |
| GRANT REMOTE DBA TO <grantee>[,...] |
GRANT ROLE SYS_RUN_REPLICATION_ROLE TO <grantee> [,...] WITH NO ADMIN OPTION WITH NO SYSTEM PRIVILEGE INHERITANCE |
| GRANT BACKUP TO <grantee>[,...] |
GRANT ROLE SYS_AUTH_BACKUP_ROLE TO <grantee> [,...] WITH NO SYSTEM PRIVILEGE INHERITANCE |
| GRANT RESOURCE TO <grantee>[,...] |
GRANT ROLE SYS_AUTH_RESOURCE_ROLE TO <grantee> [,...] WITH NO SYSTEM PRIVILEGE INHERITANCE |
| GRANT VALIDATE TO <grantee>[,...] |
GRANT ROLE SYS_AUTH_VALIDATE_ROLE TO <grantee> [,...] WITH NO SYSTEM PRIVILEGE INHERITANCE |
| Pre-16.0 SYNTAX | NEW SYNTAX |
|---|---|
| GRANT Multiplex Admin TO <grantee> [,...] |
GRANT ROLE SYS_AUTH_MULTIPLEX_ADMIN_ROLE TO <grantee> [,...] |
| GRANT Operator TO <grantee> [,...] | GRANT ROLE SYS_AUTH_OPERATOR_ROLE TO <grantee> [,...] |
| GRANT Perms Admin TO <grantee> [,...] | GRANT ROLE SYS_AUTH_PERMS_ADMIN_ROLE TO <grantee> [,...] |
| GRANT PROFILE TO <grantee> [,...] | GRANT ROLE SYS_AUTH_PROFILE_ROLE TO <grantee> [,...] |
| GRANT READCLIENTFILE TO <grantee> [,...] | GRANT ROLE SYS_AUTH_READCLIENTFILE_ROLE TO <grantee> [,...] |
| GRANT READFILE TO <grantee> [,...] | GRANT ROLE SYS_AUTH_READFILE_ROLE TO <grantee> [,...] |
| GRANT Space Admin TO <grantee> [,...] | GRANT ROLE SYS_AUTH_SPACE_ADMIN_ROLE TO <grantee> [,...] |
| GRANT Spatial Admin TO <grantee> [,...] | GRANT ROLE SYS_AUTH_SPATIAL_ADMIN_ROLE TO <grantee> [,...] |
| GRANT WRITECLIENTFILE TO <grantee> [,...] | GRANT ROLE SYS_AUTH_WRITECLIENTFILE_ROLE TO <grantee> [,...] |
| GRANT WRITEFILE TO <grantee> [,...] | GRANT ROLE SYS_AUTH_WRITEFILE_ROLE TO <grantee> [,...] |
|
GRANT CONNECT TO <username> [ IDENTIFIED BY <pwd> ] |
No change |
| GRANT GROUP TO <user> |
CREATE OR REPLACE <rolename> FOR USER <user> |
|
GRANT MEMBERSHIP IN GROUP <groupname>[,...] TO <grantee>[,...] |
GRANT ROLE <groupname>[,...] TO <grantee>[,...] |
| GRANT PUBLISH TO <grantee> | No change. However, you can also set the new PUBLIC option,
db_publisher: SET OPTION PUBLIC.db_publisher=<grantee_id> |
|
GRANT <permission>[,...] ON [ owner.]object-name TO <grantee>[,...] [ WITH GRANT OPTION ] <permission>: ALL [ PRIVILEGES ] | ALTER | DELETE | INSERT | REFERENCES [ ( column-name, ...) ] | SELECT [ ( column-name, ... ) ] | UPDATE [ ( column-name, ... ) ] |
No Change |
|
GRANT EXECUTE ON [owner.]{ procedure-name | user-defined-function } TO <grantee>[,...] |
No Change |
|
GRANT INTEGRATED LOGIN TO <user-profile-name>[,...] AS USER <user> |
No Change |
|
GRANT KERBEROS LOGIN TO client-Kerberos-principal [, …] AS USER <user> |
No Change |
|
GRANT CREATE ON <dbspacename> [,...] TO <grantee> [,...] |
No Change |