SQL Anywhere Studio Security Guide
  Restrictions and Other Security Concerns

Revoking DBA authority

This page describes operation of Adaptive Server Anywhere in a manner equivalent to a C2-security-certified configuration. It does not provide general-purpose information on the topic.

Since the engine does not generally allow you to revoke DBA authority from a user while that user is connected to the database, the easiest way to revoke DBA authority is simply to wait until the user has disconnected, and then issue a REVOKE DBA statement.

However, it may be necessary to immediately revoke DBA authority from a user who is currently connected to the database, before the user has a chance to do anything else. Assume for this example you are trying to revoke DBA authority from user fred.

To revoke DBA authority from a connected user

1. Connect to the same database as a different user with DBA authority.

   For example, use a user ID other than fred.

2. Disable connections to the server by executing the following statement:

   CALL sa_server_option('disable_connections', 'ON')

   This prevents fred from connecting again once his existing connections have been dropped.

3. List all the connections to the database by executing the following statement:

   CALL sa_conn_info( )

4. Write down the value of the Number column for each row containing fred in the Userid column.

5. For each connection number you wrote down in step 4, execute the following statement:

   DROP CONNECTION number

   This immediately drops each connection, rolling back any uncommitted transactions. Note that any transactions committed by fred, as well as any DDLs executed by fred before the DROP statement was executed, are not rolled back and must be manually undone.

6. Execute the following SQL statement:

   REVOKE DBA FROM fred

7. Re-enable connections to the server by executing the following statement:

   CALL sa_server_option('disable_connections', 'OFF')