6. Managing a Database

6.1.1. Creating a Table

When you first create a database in Adaptive Server Anywhere, the only tables it contains are the system tables. To create tables to hold your data, use either the CREATE TABLE statement in SQL or the Sybase Central Table Editor. You must have the DBA or RESOURCE authority to create a table, and you must have the DBA authority make another user its owner.

The CREATE TABLE statement has an extremely broad range of options that are documented in the Adaptive Server Anywhere Reference, so only a small subset of options are described here. The basic syntax is as follows:

CREATE TABLE owner.table-name
  (column-name datatype [, column-name datatype]...)

The "owner." portion before tablename is optional, and is used by a user with the DBA authority to make another user the owner of the new table. table-name and column-name, respectively, are the names of the table and its columns. Insert the words PRIMARY KEY after datatype to make it the primary key.

See the SQL Data Types chapter of the Adaptive Server Anywhere Reference for a list of the types available and their characteristics.

To create a table named customer with columns id, name, address, city_state_zip, and phone, with id as the primary key, for example, use the following CREATE TABLE statement:

create table customer 
 (id integer not null primary key, 
  name char ( 35 ), 
  address char ( 35 ), 
  city_state_zip char ( 35 ), 
  phone char ( 12 ) 
 ) 

It's also important to add "not null" in the case of id, since it's the primary key.

To create a table in Sybase Central, connect to your database and open its Tables folder. If you double-click "Add Table," Sybase Central Table Editor will be opened and using the button bar, you can set up the table as you wish. Hover the mouse pointer over each button to find out what it does. Don't forget to make a primary key before you close the Table Editor!

Some table creation options documented in the Adaptive Server Anywhere Reference but not here that you might be interested in include automatic incrementation (often used on the primary key), constraints, and foreign keys.

6.1.2. Making Alterations to Tables

You can make many kinds of changes to a table once it's been created. Some of the things you can do include the following:

• rename a table

• add, remove, or rename columns

• change the datatype, default value, or length of a column

As with creating tables, you can alter them through SQL or Sybase Central. To alter a table in SQL, you use the ALTER TABLE statement. ALTER TABLE has a great variety of options, which are described in detail in the Adaptive Server Anywhere Reference. You'll see a few basic examples here just to get you started.

To rename the customer table to cust:

alter table customer 
  rename cust

To add a company_name column to cust, with a maximum length of 35 characters:

alter table cust
  add (company_name char (35) )

To give company_name a default value of "n/a" :

alter table cust
  alter company_name set default 'n/a'

6.2.1. User IDs

GRANT CONNECT TO userid1 
  IDENTIFIED BY password1 

grant connect to mortimer identified by
monkey 

6.2.2. Permissions

This section explains permissions on tables that can be granted to users. Permissions are granted on a user-by-user basis.

There are a few different table permissions that can be granted to a user, and they are each granted separately.

• SELECT allows the user to read data, and can be restricted to particular columns.

• INSERT allows the user to add data.

• UPDATE allows the user to change data, and can be restricted to particular columns.

• DELETE allows the user to remove data.

• ALTER allows the user to modify the structure of a table.

• REFERENCES allows the user to add indexes, primary keys, and foreign keys.

• ALL includes all the above permissions.

With the exceptions of ALTER and REFERENCES, which apply to tables exclusively, the table permissions apply to both tables and views. The SQL syntax for granting permissions is as follows:

GRANT [ SELECT (column-name, ...) 
      | INSERT 
      | UPDATE (column-name, ...)
      | DELETE 
      | ALTER 
      | REFERENCES 
      | ALL                                          ] 
ON table-name 
TO userid

The user userid is given the specified permission(s) on the table identified by table-name. If the permissions granted include SELECT and/or UPDATE, they are granted only on the columns specified in column-name.

Let's say a list of available banana types is stored in the type and quantity columns of a table named banana_supply. To allow Mortimer to see a list of available banana types along with their quantities, use the following SQL statement:

grant select on banana_supply (type, quantity) to mortimer

When you grant a permission to a user, you have the option of granting him the ability to grant that same permission to others. To grant a user the permission to do so, add WITH GRANT OPTION to the end of your users GRANT statement when you're granting them their permissions.

To allow Mortimer to see a list of banana types available along with the quantities of each, as well as allowing him to grant others the same SELECT permission, use this SQL statement:

grant select on banana_supply (type, quantity)
to mortimer
      with grant option 

6.2.3. Authorities

An authority is a different level of permission. There are two types of authority.

6.2.4. Removing Users and Revoking Permissions

The SQL statement to delete a user ID is REVOKE CONNECT.

Syntax:

REVOKE CONNECT FROM userid [, userid ]

As suggested by the portions in square parentheses, it's possible to remove multiple user IDs in a single statement. For example, to remove the user IDs for Mortimer and Chestington, execute this statement:

revoke connect from mortimer, chestington

To revoke permissions or authorities given to a particular user, you take the original granting statement, replace the GRANT with REVOKE, and replace the TO with FROM. To take away Mortimer's permission to view the banana_supply table, for example, use this REVOKE statement:

revoke select on banana_supply (type, quantity) from mortimer

6.2.5. Changing Passwords

To change the password associated with a particular user ID, use a GRANT CONNECT statement again:

GRANT CONNECT TO userid IDENTIFIED BY newpassword

For example, to change the DBA's password from "SQL" to "d0n13xw9," use this statement:

grant connect to DBA identified by d0n13xw9

6.3.1. Increasing password security

By default, passwords can be any length. For greater security, you can enforce a minimum length on all new passwords, to make them more difficult to guess. You do this by setting the MIN_PASSWORD_LENGTH database option to a greater value. The following statement enforces a minimum password length of 8 characters:

set option public.min_password_length = 8

Check the "Changing Passwords" section of this document to learn how to change a user's password, and don't forget to change the DBA's password!

6.3.2. Views, procedures, and triggers

Views are useful when it is appropriate to give a user access to just one portion of a table. The portion can be defined in terms of rows or in terms of columns. For example, you may wish to prevent a group of users from seeing the quantity column of the banana_supply table, or you may wish to limit a user to see information on a particular type of banana.

While views restrict access based on the data, procedures and triggers restrict access based on the actions a user can take. Procedures and triggers store SQL statements in a database for use by all applications. They execute under the table permissions of the associated table's owner, regardless of the permissions of the user who either executes the procedure or fires the trigger.

Procedures are invoked by a CALL statement, and can take values as well as return them. Unlike procedures, however, triggers are can neither take values nor return them, and are invoked by insertions, updates, or deletions in the table it is associated with. Permissions are not associated with triggers. They execute when the action defined to fire them is performed, regardless of the user.

For strict security, you can prevent all access to the tables, and grant permission to users to execute certain stored procedures that carry out specific tasks. This approach strictly defines the manner in which the database can be modified.

6.3.3. Encrypting client/server communications

Encrypting client/server communications prevents third parties from reading messages being sent between the client and the server. It can be enabled from either the server side or the client side. To enable encryption from the server, use the -e option at server startup. For example, use the following command to start up the database server to accept encrypted connections to mydb.db over TCP/IP:

dbsrv7 -e -x tcpip mydb.db

To enable encryption from a particular client, use the ENC keyword in the connection string. For example, to encrypt a connection over TCP/IP to mydb.db, your connection string would appear as follows:

"uid=mortimer;pwd=monkey;links=tcpip;eng=MyServer;dbf=mydb.db;enc=true"

For more information about client/server communications encryption, look for the -e command-line option under "The database server" in the Adaptive Server Anywhere Reference Manual, and for "Encryption connection parameter" under "Connection parameters" .