Granted privileges can be revoked. The owner of the data and the users who passed privileges granted to them to other users are authorized to do that.
The following statement issued by the user 'travel20' revokes the update right on the table 'hotel' from the user 'travel22':
REVOKE UPDATE ON hotel FROM travel22
'travel20', for example, does no longer want that 'travel10' has all privileges and the authorization to grant them to other users. The REVOKE statement has the effect that all privileges that 'travel10' granted on the table 'hotel' to other users are automatically revoked from the users. In our example, the entire authorization for 'hotel' is revoked from the usergroup:
REVOKE ALL ON hotel FROM travel10
User and usergroup can be removed from the database. The DBA who installed the user or usergroup is authorized to do so.
DROP USER travel22
DROP USERGROUP travel10group
DROP USER and DROP USERGROUP remove the specified user entry from the database catalog. They implicitly remove all the rights associated with this entry (password, privileges), any existing private data and rights granted for it and - for usergroups - all pertinent users.
Organizational modifications in the application 'travel agency' can be carried out with the relevant authorization statements. The user 'travel21' becomes DBA in order to be able to install users of his own.
1) Altering the user mode:
ALTER USER travel21 DBA
This statement must be performed by the SYSDBA, because he is the only one who can create DBAs. It is also possible to alter the status of a usergroup.
2) Altering the password:
- by the SYSDBA using
ALTER PASSWORD travel10 TO f4ffg
- by the respective user using
ALTER PASSWORD t10 f4ffg
3) Revoking granted privileges
REVOKE SELECT ON customer FROM PUBLIC
REVOKE INSERT, DELETE ON customer FROM travel10group
REVOKE ALL ON hotel FROM travel10
4) Granting privileges:
GRANT INDEX, REFERENCES, ALTER ON hotel TO travel21