Access to the execution of DDL operations can be controlled by granting and/or revoking DDL privileges for a given user. By default, solidDB® allows any user to execute DDL in their respective schema. In order to alleviate the potential risks involved, it is possible to revoke the DDL execution privileges for a given user.
Granting DDL privileges
The syntax for granting DDL privileges for a given user is:
GRANT DDL TO <user>
This operation will allow the specified user to execute any DDL operation within that user’s own schema. This is the default DDL privilege mode for all solidDB® users.
Revoking DDL privileges
The syntax for revoking DDL privileges from a given user is:
REVOKE DDL FROM <user>
This operation will prevent the specified user from executing any DDL operations within that user’s own schema. However, even if the user DDL operations have been revoked, the user is still able to execute the statement 'ALTER USER <user> IDENTIFIED BY <password>' in order to be able to change the password.
Note The DDL privileges granted/revoked for a user already logged into the database will become active only after the user has reconnected into the database. This means, in effect, that if a user’s DDL privileges have been altered by the database administrator then the active user needs to be disconnected and must reconnect to the database in order for the DDL privilege changes to take place.
If the user has been revoked of the DDL privileges, but the user has been granted (for example) the SYS_ADMIN_ROLE or the SYS_SYNC_ADMIN role, then these higher privileges precede the normal DDL privilege mode. Therefore, the user may still be able to execute the DDL operations that fall on these higher priority system roles.