Granting and revoking database-level authorities

This category of authorities allows a user to perform activities specific to a database. These activities are viewing/modifying data in the database, granting and revoking privileges to users, performing data load operations, running maintenance activities like collecting statistics, backups and restores, and so on.

Getting ready

• We need SYSADM authority to grant DBADM or SECADM authority

• We need SYSADM or DBADM authority to grant other database authorities

How to do it...

Let's see how we can grant and revoke database-level authorities. The process is the same for every type of database-level authority.

Granting database-level authorities

• All database-level authorities can be granted to groups and roles as well as to individual users except SECADM, which can only be granted to a user.

  Use the following command to grant any authority to user/role/group:

GRANT <authority_name> ON DATABASE <db_name> to USER/ROLE/GROUP <name>

• For...