DBA authority overview

When a database is created, a single usable user ID is created. This first user ID is DBA and the password is initially set to SQL. The DBA user ID is automatically given 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.

NoteTo ensure database security, the DBA needs to change the password from the default of SQL to a new value.

Users with DBA authority

A user with DBA authority is referred to as the database administrator or database owner. In this chapter, frequent reference is made to the database administrator, or the DBA. This is shorthand for any user or users with DBA authority.

Although DBA authority may be granted or transferred to other user IDs, in this chapter it is assumed that the DBA user ID is the database administrator, and the abbreviation DBA is used interchangeably to mean both the DBA user ID and any user ID with DBA authority.

WARNING! Never drop the DBA user for a multiplex database. Doing so makes the database invalid.

Example

The following example shows how to give non-DBA users the ability to execute commands that require DBA privileges. This example creates a policy that lets a non-DBA user (user1) perform 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 has the authority to add new users to the database. As users are added, they are also granted permissions to carry out tasks on the database. Some users may need to simply look at the database information using SQL queries, others may need to add information to the database, and others may need to modify the structure of the database itself. Although some of the responsibilities of the DBA 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.

See the syntax of the commands for creating database objects, Chapter 3, “SQL Language Elements,” in Sybase IQ Reference Manual.

DBA user ID in case sensitive databases

User IDs and passwords are actually objects in the database. Password case sensitivity follows that of the data by default. For this reason, you must enter the password for the user ID DBA (SQL by default) in uppercase if:

For case insensitive databases (CASE IGNORE or PASSWORD CASE IGNORE) you can enter this user ID and password in either uppercase or lowercase.