DBA Authority Overview

When you create a database, a single user ID is created.

The password for DBA is initially set to "sql" by default. To override the default user name or password, use the CREATE DATABASE statement with the DBA USER or DBA PASSWORD clause. The DBA user ID is automatically granted DBA permissions, also called DBA authority, within the database. This level of permission enables the DBA user ID to carry out any activity in the database: create tables, change table structures, create new user IDs, revoke permissions from users, and so on.

Note: To ensure database security and accountability, avoid using generic names like "dba" as the first user ID. Use a real user's login name with a strong password instead.

Users with DBA Authority

Although DBA authority may be granted or transferred to other user IDs, Sybase IQ documentation assumes that the DBA user ID is the database administrator, and the abbreviation DBA means both the DBA user ID and any user ID with DBA authority.

To guard against loss of password by the active DBA user, create one or more extra DBA accounts (with a randomly generated user name and password) and lock up those credentials. If the active DBA password is lost, use one of the extra credentials to log in to that DBA account, and reset the original account password.

Warning! Do not drop the DBA user for a multiplex database. Doing so makes the database invalid.

Example

This example shows how to give non-DBA users the ability to execute commands that require DBA privileges. It creates a policy that lets a non-DBA user (user1) perform a backup.

CREATE PROCEDURE "DBA".do_backup()
BEGIN
   BACKUP DATABASE
      CRC ON
      ATTENDED OFF
      BLOCK FACTOR 4
      FULL
      TO 'fileA' SIZE 2000
      TO 'fileB' SIZE 2000
      TO 'fileC' SIZE 2000;
END;
GRANT EXECUTE ON "DBA".do_backup TO user1;

Adding New Users

The DBA can add new users to the database. New users are also granted permissions to carry out tasks on the database. While some users only need to look at the database information using SQL queries, others may need to add information to the database, and others may need to modify the database structure. Although DBA responsibilities may be handed over to other user IDs, the DBA is responsible for the overall management of the database by virtue of the DBA authority.

The DBA has authority to create database objects and assign ownership of these objects to other user IDs.

DBA User ID in Case-Sensitive Databases

User IDs and passwords are actually database objects. For details about case-sensitivity of user IDs and passwords, see Reference: Building Blocks, Tables, and Procedures.