All data in relational databases is held in tables. Each column is assigned a data type, and each row of a table holds a value for each column. The following are true for any table in a relational database:
There is no significance to the order of rows and columns.
Each row contains exactly one value per column.
All values in a column are of the same type.
Here are some things to keep in mind when designing your database:
give every table a primary key
make sure that each table holds information about one specific entity
foreign keys form the relationships between tables (and therefore entities)
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.
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' |
NOTE: Before putting an Adaptive Server Anywhere database into serious usage, your first order of business as the database administrator (DBA) should be to change the DBA password from the default password, "SQL." For details on how to do this, see section 6.2.5.
This section describes the user IDs that are created for each database, briefly describes how to create new user IDs, and goes over some of the ways you can use user IDs to control outsiders access of data. For more information on user IDs, groups, and permissions, see the Managing User IDs and Permissions chapter of the Adaptive Server Anywhere User's Guide.
When Adaptive Server Anywhere databases are initialized, two groups and two user IDs are created. The two groups created are SYS and PUBLIC. The two user IDs created are DBA and dbo.
SYS is a user as well as a group, but no one can connect to the database using the user ID SYS. SYS owns the system tables and the system views, and only SYS can update the system tables.
PUBLIC is a member of the SYS group, and has only SELECT permissions on most system tables and system views. Since new user IDs are, by default, members of PUBLIC, you should revoke PUBLIC's membership in SYS if you want new users to have no permissions by default.
The DBA user can directly modify any part of an Adaptive Server Anywhere database except the system tables. This is why it's important to change the default DBA password from "SQL." You should be cautious when giving DBA authority to a user (see the DBA Authority section below). If a user needs DBA authority, s/he should be given DBA authority, rather than the DBA's password.
The SQL statement to add a new user ID is GRANT CONNECT.
Syntax:
GRANT CONNECT TO userid1 IDENTIFIED BY password1 |
To add a user ID with the name Mortimer, execute the following SQL statement:
grant connect to mortimer identified by monkey |
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
|
An authority is a different level of permission. There are two types of authority.
A user with the RESOURCE authority can create and drop database objects such as tables, views, stored procedures, and functions. The RESOURCE authority also allows the user to create and remove user IDs and passwords. To give userid the RESOURCE authority, execute the following SQL statement:
GRANT RESOURCE TO userid
A user with the DBA authority can perform any database operation, and automatically has all permissions on all tables, except the system tables. The DBA can create and remove user IDs and passwords, grant RESOURCE and DBA authority, and unload and reload the database.
GRANT DBA TO userid
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
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
Some of the Adaptive Server Anywhere features you may wish to use in building a secure environment for your data include the following:
User identification and authentication control access to databases.
Permissions and authorities, which have already been explained in previous sections, control the actions a user can carry out while connected to a database.
Views and stored procedures allow you to carefully tune the data a user can access and the operations a user can execute.
Connection encryption can prevent unauthorized persons from snooping.
Some of these features have already been mentioned in this HOWTO, and some of them will be elaborated upon in the following sections. While the concepts of triggers, procedures, and views will be introduced so you can decide if and how you'll use them, their implementation won't be discussed. You can find indepth information on them, as well as details on their implementation, in the sections of the Adaptive Server Anywhere User's Guide listed below:
Table 8.
| Chapter | Section |
| Using Procedures, Triggers, and Batches | Benefits of procedures and triggers |
| Managing User IDs and Permissions | Using views and procedures for extra 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!
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.
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" .