Changes to the GRANT statement syntax

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.

Inheritance behavior for former authorities

In pre-16.0 databases, DBA, REMOTE DBA, BACKUP, RESOURCE, and VALIDATE authorities were non-inheritable in the case where they were assigned to a user acting as a group. Members of that group did not inherit the capabilities of the authority.

When you upgrade a pre-16.0 database, the equivalent role is automatically granted (for example, SYS_AUTH_BACKUP_ROLE is granted instead of BACKUP authority), and the WITH NO SYSTEM PRIVILEGE INHERITANCE clause is specified to ensure that inheritance behavior remains consistent with previous releases. Members of the group do not inherit the privileges that the role provides.

Likewise, if you continue to use the deprecated grant syntax to grant DBA, REMOTE DBA, BACKUP, RESOURCE, and VALIDATE, the old behavior of non-inheritance is maintained. That is, the equivalent role is granted and the WITH NO SYSTEM PRIVILEGE INHERITANCE clause is specified. This is done automatically.

In pre-16.0 databases, users that were granted DBA and REMOTE DBA authorities automatically could grant them to others. The WITH ADMIN OPTION clause in the new syntax recommended below ensures that administration rights behavior remains consistent with previous releases.

Deprecated syntax	New syntax
GRANT CONNECT TO username [ IDENTIFIED BY ] pwd	No change.
GRANT GROUP TO user	CREATE OR REPLACE ROLE groupname FOR USER userid
GRANT MEMBERSHIP IN GROUP groupname [,...] TO grantee [,...]	GRANT ROLE groupname [,...] TO grantee [,...]
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 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
GRANT PROFILE TO grantee [,...]	GRANT ROLE SYS_AUTH_PROFILE_ROLE TO TO grantee [,...]
GRANT READCLIENTFILE TO grantee [,...]	GRANT ROLE SYS_AUTH_READCLIENTFILE_ROLE TO TO grantee [,...]
GRANT READFILE TO grantee [,...]	GRANT ROLE SYS_AUTH_READFILE_ROLE TO TO grantee [,...]
GRANT WRITECLIENTFILE TO grantee [,...]	GRANT ROLE SYS_AUTH_WRITECLIENTFILE_ROLE TO 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
GRANT permission [,...] ON [ owner.]object-name TO grantee [,...] permission : ALL [ PRIVILEGES ] \| ALTER \| DELETE \| INSERT \| REFERENCES [ ( column-name, ...) ] \| SELECT [ ( column-name, ... ) ] \| UPDATE [ ( column-name, ... ) ]	No change.
GRANT EXECUTE ON [ owner.]{ proc \| user-def-funct } TO grantee [,...]	No change.
GRANT INTEGRATED LOGIN TO user-profile-name [,...] AS USER user	No change.
GRANT KERBEROS LOGIN TO userid [,...] AS USER user	No change.
GRANT CREATE ON dbspacename [,...] TO grantee [,...]	No change.

Changes to the GRANT statement syntax

Inheritance behavior for former authorities

See also