SAND CDBMS SQL Reference Guide
SAND CDBMS SQL Database Objects and Language Elements

User Authorizations and Privileges

A newly-created SAND database contains two special authorization identifiers by default: DBA and PUBLIC. Other user authorizations are created by the user DBA (or users with DBA privileges), through execution of a CREATE AUTHORIZATION statement. A user authorization name (with an optional password) can be subsequently specified in a request to connect to the database. All authorization names must be unique within the database.

Users must have certain privileges in order to execute SQL statements involving objects in the database. The user DBA (a kind of database “super-user”) has unrestricted privileges to create, alter, manipulate, and drop any object in the database. The user DBA can grant these unrestricted privileges, called DBA privileges, to another user (though this capability should be exercised with caution). Furthermore, only a user with DBA privileges can execute the following commands:

• CREATE AUTHORIZATION
  
• DROP AUTHORIZATION
  
• SET TRANSACTION...ISOLATION LEVEL EXCLUSIVE
  
• SHUTDOWN

Non-DBA users, once created, can connect to the database and proceed to create new schemas (which they will own), and then tables, domains, and so on within them. A new user also receives ownership privileges on their default schema, if it did not exist prior to the creation of the user authorization (see the Schemas section for more details).

Ownership of a database object entails the ability to give privileges on the object to other users using the GRANT command; these privileges may furthermore be given WITH GRANT OPTION, which allows the grantee to give the privileges to yet another user.

The PUBLIC authorization has the same abilities as any other authorization, which means that initially anybody can connect to the database as user PUBLIC and query system views, create, alter, manipulate, and drop database objects within the public schema, and create new schemas. The sole difference between the PUBLIC authorization and a regular authorization is that any privileges granted to PUBLIC are also received by all other authorizations in the database.

The DBA, and users with DBA privileges, can access information about all authorizations in the database by querying the system table SYSTEM.AUTHORIZATIONS. Users without DBA privileges can access information about their own authorization by querying the system view PUBLIC.AUTHORIZATIONS. See Appendix C. SAND CDBMS System Tables/Views for more information about system tables and views.