Data Vault Administrator Guide

6.4.3 HotFix 1
- 6.5.1
- 6.5 HotFix 1
- 6.5
- 6.4.4
- 6.4.3

Back Next

DBA Privileges

The DBA role gives a user all privileges over all objects in the database. Only a user with DBA privileges can execute the following SQL commands:

CREATE AUTHORIZATION: Creates a database user authorization identifier, which consists of a user name and optional password. When you form the CREATE AUTHORIZATION statement, you can also assign the new user to a default schema. After you commit the CREATE AUTHORIZATION statement, you can connect to the database with the new user.
The user name argument must be unique in the database. The user name can be up to 128 characters long and cannot be "PUBLIC" or "DBA."
The password argument is optional. If you include a password argument in the CREATE AUTHORIZATION statement, the password must be preceded by
PASSWORD
. The password can be up to 128 characters long and is case-sensitive. You must enter the password every time the user connects to the database.
If you do not include a password argument in the CREATE AUTHORIZATION statement, the password has a null value and you will not need to enter a password when you connect to the database.
Optionally, you can assign the user to a default schema. If you include a DEFAULT SCHEMA clause in the CREATE AUTHORIZATION statement, the specified schema is always the initial schema for the user when the user connects to the database. If you do not include a DEFAULT SCHEMA clause, the initial schema for the user is the PUBLIC schema.
If you specify a schema in the DEFAULT SCHEMA clause that does not already exist in the database, Data Vault creates the schema and assigns the new user OWNER privileges with the GRANT option on the schema. If the DEFAULT SCHEMA you specify already exists in the database, Data Vault assigns the new user OWNER privileges without the GRANT option.
Example
The following statement creates a user authorization called user1
with the password pass
:
CREATE AUTHORIZATION user1 PASSWORD 'pass';
The following statement creates the user authorization u1
with a null password, and makes the default schema for the user vip
:
CREATE AUTHORIZATION u1 DEFAULT SCHEMA vip;
DROP AUTHORIZATION: Drops the specified user authorization and all associated privileges. If any database objects belong to the user authorization that you try to drop, the DROP command will fail. You cannot drop the DBA or PUBLIC users.
Example
The following statement drops the user authorization user1
from the database:
DROP AUTHORIZATION user1;
ALTER AUTHORIZATION...SET PASSWORD: Changes the password for a non-DBA user. The user name you specify in the ALTER AUTHORIZATION statement must exist in the database. The password you enter can be up to 128 characters long and is case-sensitive.
Example
The following statement sets the password for user1
to "pass123":
ALTER AUTHORIZATION user1 SET PASSWORD pass123;; Do not use the ALTER AUTHORIZATION statement to change the DBA user password.
ALTER AUTHORIZATION...SET DEFAULT SCHEMA: Changes the default schema for a non-DBA user. If you specify a schema in the DEFAULT SCHEMA clause that does not exist in the database, Data Vault creates the schema and assigns the user OWNER privileges with the GRANT option on the schema. If the DEFAULT SCHEMA you specify exists in the database but the user does not own it, Data Vault assigns the user OWNER privileges without the GRANT option on the schema.
Example
The following statement changes the default schema for user1
to vip
:
ALTER AUTHORIZATION user1 SET DEFAULT SCHEMA vip;
SHUTDOWN: Shuts down the Data Vault service.
SET TRANSACTION...ISOLATION LEVEL EXCLUSIVE

Rename Saved Search

Table of Contents

Data Vault Administrator Guide

Data Vault Administrator Guide

DBA Privileges

DBA Privileges