ElevateDB supports most of the SQL security model that is specified in the SQL 2003 standard. This includes support for users and roles (authorizations), as well as the granting and revoking of privileges on database objects. However, ElevateDB only allows users that have been granted the special system-created Administrators role to create, alter, or drop users and roles, or grant or revoke privileges for either. The SQL statements that apply to user security in ElevateDB are as follows:

   CREATE USER
   ALTER USER
   DROP USER
   RENAME USER
   CREATE ROLE
   ALTER ROLE
   DROP ROLE
   RENAME ROLE
   GRANT ROLES
   REVOKE ROLES
   GRANT PRIVILEGES
   REVOKE PRIVILEGES

Users and Roles

---

ElevateDB supports the creation of both users and roles, and both are considered authorizations in that they can be granted privileges on database objects. Roles can be granted to users, which allows for easier administration of the privileges for a given application and/or database by organizing the granting and revoking of privileges based upon the tasks required by a certain group of users. For example, in a point-of-sale application there would possibly be the following roles:

Cashiers
Managers

which would be created using the following SQL statements:

CREATE ROLE Cashiers DESCRIPTION 'Store Cashiers'

CREATE ROLE Managers DESCRIPTION 'Store Managers'

One could then grant privileges on the various database objects to these roles instead of directly to the users like this:

GRANT SELECT, INSERT ON Transactions
TO Cashiers

GRANT SELECT, INSERT, UPDATE, DELETE ON Inventory
TO Managers

Finally, granting these roles to new users can be done using the GRANT ROLE statement:

CREATE USER Jenny PASSWORD '34IJT199'
DESCRIPTION 'Jenny Myers'

GRANT Cashiers TO Jenny

Default Users and Roles

---

There are two system-created users and two system created roles in every ElevateDB configuration. They are as follows:

System User

The System user is created automatically for each new ElevateDB configuration, and cannot be dropped or altered. The System user is used in the following contexts:


Administrator User

The Administrator user is created automatically for each new ElevateDB configuration, and can be dropped and altered. The default password for the Administrator user is:

EDBDefault (case-sensitive)

The default Administrator user is automatically granted the system-created Administrators role (see below).

Administrators Role

The Administrators role is created automatically for each new ElevateDB configuration, and cannot be dropped or altered. Only users that have been granted the Administrators role can execute the following statements:

   CREATE USER
   ALTER USER
   DROP USER
   RENAME USER
   CREATE ROLE
   ALTER ROLE
   DROP ROLE
   RENAME ROLE
   GRANT ROLES
   REVOKE ROLES
   GRANT PRIVILEGES
   REVOKE PRIVILEGES
   CREATE DATABASE
   ALTER DATABASE
   DROP DATABASE
   RENAME DATABASE
   CREATE JOB
   ALTER JOB
   DROP JOB
   RENAME JOB
   CREATE STORE
   ALTER STORE
   DROP STORE
   RENAME STORE
   CREATE MODULE
   ALTER MODULE
   DROP MODULE
   RENAME MODULE
   CREATE MIGRATOR
   ALTER MIGRATOR
   DROP MIGRATOR
   RENAME MIGRATOR
   CREATE TEXT FILTER
   ALTER TEXT FILTER
   DROP TEXT FILTER
   RENAME TEXT FILTER
   CREATE WORD GENERATOR
   ALTER WORD GENERATOR
   DROP WORD GENERATOR
   RENAME WORD GENERATOR
   DISCONNECT SERVER SESSION
   REMOVE SERVER SESSION


Public Role

The Public role is created automatically for each new ElevateDB configuration, and cannot be dropped or altered. By default, all users are automatically granted the Public role, but the role can be revoked at any time as necessary.

Privileges

---

The GRANT PRIVILEGES and REVOKE PRIVILEGES statements can be used by any user that has been granted the Administrators role, and are used to specify the database object privileges that are available to the various users and/or roles that are defined in the configuration. The following table shows which privileges may be granted for the various database objects:


As you can see, the privileges granted on a given object usually dictate whether another object contained within the object can be accessed or altered in some way. For example, a user or role must have been granted CREATE privileges on a given table in order for that user or role to be able to use the CREATE TRIGGER statement to create a new trigger on the table.