Database permissions and authorities overview

Proper management of user IDs and permissions is essential in a data warehouse. It allows users to carry out their jobs effectively, while maintaining the security and privacy of appropriate information within the database.

Use SQL statements to assign user IDs to new users of a database, to grant and revoke permissions for database users, and to display the current permissions of users.

A permission grants the ability to create, modify, query, use, or delete database objects such as tables, views, users, and so on. An authority grants the ability to perform a task at the database level, such as backing up the database.

Database permissions are assigned to user IDs. Throughout this chapter, the term user serves as a synonym for user ID. Remember, however, that permissions are granted and revoked for each user ID.

Setting up individual user IDs

Even if there are no security concerns regarding a multiuser database, there are good reasons for setting up an individual user ID for each user. The administrative overhead for individual user IDs is very low if a group with the appropriate permissions is set up. Groups of users are discussed later in this chapter.

Among the reasons for using individual user IDs are the following:

While all permissions are inheritable (from the groups to which the user belongs), only some authorities are inheritable.

Except for DBA, which has full administrative privileges, each authority has permissions to perform certain types of tasks. See “Using procedures for tailored security”.