938 lines
20 KiB
HTML
938 lines
20 KiB
HTML
<HTML
|
|
><HEAD
|
|
><TITLE
|
|
>Managing a Database</TITLE
|
|
><META
|
|
NAME="GENERATOR"
|
|
CONTENT="Modular DocBook HTML Stylesheet Version 1.63
|
|
"><LINK
|
|
REL="HOME"
|
|
TITLE="Sybase Adaptive Server Anywhere for Linux HOWTO"
|
|
HREF="index.html"><LINK
|
|
REL="PREVIOUS"
|
|
TITLE="Backing up and Restoring a Database"
|
|
HREF="backup.html"><LINK
|
|
REL="NEXT"
|
|
TITLE="Where to get more information"
|
|
HREF="moreinfo.html"></HEAD
|
|
><BODY
|
|
CLASS="SECT1"
|
|
BGCOLOR="#FFFFFF"
|
|
TEXT="#000000"
|
|
LINK="#0000FF"
|
|
VLINK="#840084"
|
|
ALINK="#0000FF"
|
|
><DIV
|
|
CLASS="NAVHEADER"
|
|
><TABLE
|
|
WIDTH="100%"
|
|
BORDER="0"
|
|
CELLPADDING="0"
|
|
CELLSPACING="0"
|
|
><TR
|
|
><TH
|
|
COLSPAN="3"
|
|
ALIGN="center"
|
|
>Sybase Adaptive Server Anywhere for Linux HOWTO</TH
|
|
></TR
|
|
><TR
|
|
><TD
|
|
WIDTH="10%"
|
|
ALIGN="left"
|
|
VALIGN="bottom"
|
|
><A
|
|
HREF="backup.html"
|
|
>Prev</A
|
|
></TD
|
|
><TD
|
|
WIDTH="80%"
|
|
ALIGN="center"
|
|
VALIGN="bottom"
|
|
></TD
|
|
><TD
|
|
WIDTH="10%"
|
|
ALIGN="right"
|
|
VALIGN="bottom"
|
|
><A
|
|
HREF="moreinfo.html"
|
|
>Next</A
|
|
></TD
|
|
></TR
|
|
></TABLE
|
|
><HR
|
|
ALIGN="LEFT"
|
|
WIDTH="100%"></DIV
|
|
><DIV
|
|
CLASS="SECT1"
|
|
><H1
|
|
CLASS="SECT1"
|
|
><A
|
|
NAME="MANAGE"
|
|
>6. Managing a Database</A
|
|
></H1
|
|
><DIV
|
|
CLASS="SECT2"
|
|
><H2
|
|
CLASS="SECT2"
|
|
><A
|
|
NAME="AEN740"
|
|
>6.1. Tables</A
|
|
></H2
|
|
><P
|
|
>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: </P
|
|
><P
|
|
></P
|
|
><UL
|
|
><LI
|
|
><P
|
|
>There is no significance to the order
|
|
of rows and columns. </P
|
|
></LI
|
|
><LI
|
|
><P
|
|
>Each row contains exactly one value per column. </P
|
|
></LI
|
|
><LI
|
|
><P
|
|
>All values in a column are of the same type. </P
|
|
></LI
|
|
></UL
|
|
><P
|
|
>Here are some things to keep in mind when designing your database: </P
|
|
><P
|
|
></P
|
|
><UL
|
|
><LI
|
|
><P
|
|
>give every table a primary key </P
|
|
></LI
|
|
><LI
|
|
><P
|
|
>make sure that each table holds information about
|
|
one specific entity </P
|
|
></LI
|
|
><LI
|
|
><P
|
|
>foreign keys form the relationships between tables
|
|
(and therefore entities) </P
|
|
></LI
|
|
></UL
|
|
><DIV
|
|
CLASS="SECT3"
|
|
><H3
|
|
CLASS="SECT3"
|
|
><A
|
|
NAME="AEN758"
|
|
>6.1.1. Creating a Table</A
|
|
></H3
|
|
><P
|
|
>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. </P
|
|
><P
|
|
>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: </P
|
|
><TABLE
|
|
BORDER="0"
|
|
BGCOLOR="#E0E0E0"
|
|
WIDTH="100%"
|
|
><TR
|
|
><TD
|
|
><PRE
|
|
CLASS="PROGRAMLISTING"
|
|
><B
|
|
CLASS="COMMAND"
|
|
>CREATE TABLE owner.table-name
|
|
(column-name datatype [, column-name datatype]...)</B
|
|
></PRE
|
|
></TD
|
|
></TR
|
|
></TABLE
|
|
><P
|
|
>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. </P
|
|
><P
|
|
>See the SQL Data Types chapter of the Adaptive Server Anywhere
|
|
Reference for a list of the types available and their characteristics. </P
|
|
><P
|
|
>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: </P
|
|
><TABLE
|
|
BORDER="0"
|
|
BGCOLOR="#E0E0E0"
|
|
WIDTH="100%"
|
|
><TR
|
|
><TD
|
|
><PRE
|
|
CLASS="PROGRAMLISTING"
|
|
><B
|
|
CLASS="COMMAND"
|
|
>create table customer
|
|
(id integer not null primary key,
|
|
name char ( 35 ),
|
|
address char ( 35 ),
|
|
city_state_zip char ( 35 ),
|
|
phone char ( 12 )
|
|
) </B
|
|
></PRE
|
|
></TD
|
|
></TR
|
|
></TABLE
|
|
><P
|
|
>It's also important to add "not null" in the case of id, since
|
|
it's the primary key. </P
|
|
><P
|
|
>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! </P
|
|
><P
|
|
>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. </P
|
|
></DIV
|
|
><DIV
|
|
CLASS="SECT3"
|
|
><H3
|
|
CLASS="SECT3"
|
|
><A
|
|
NAME="AEN772"
|
|
>6.1.2. Making Alterations to Tables</A
|
|
></H3
|
|
><P
|
|
>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: </P
|
|
><P
|
|
></P
|
|
><UL
|
|
><LI
|
|
><P
|
|
>rename a table </P
|
|
></LI
|
|
><LI
|
|
><P
|
|
>add, remove, or rename columns </P
|
|
></LI
|
|
><LI
|
|
><P
|
|
>change the datatype, default value, or length of
|
|
a column </P
|
|
></LI
|
|
></UL
|
|
><P
|
|
>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. </P
|
|
><P
|
|
>To rename the customer table to cust: </P
|
|
><TABLE
|
|
BORDER="0"
|
|
BGCOLOR="#E0E0E0"
|
|
WIDTH="100%"
|
|
><TR
|
|
><TD
|
|
><PRE
|
|
CLASS="PROGRAMLISTING"
|
|
><B
|
|
CLASS="COMMAND"
|
|
>alter table customer
|
|
rename cust</B
|
|
></PRE
|
|
></TD
|
|
></TR
|
|
></TABLE
|
|
><P
|
|
>To add a company_name column to cust, with a maximum length
|
|
of 35 characters: </P
|
|
><TABLE
|
|
BORDER="0"
|
|
BGCOLOR="#E0E0E0"
|
|
WIDTH="100%"
|
|
><TR
|
|
><TD
|
|
><PRE
|
|
CLASS="PROGRAMLISTING"
|
|
><B
|
|
CLASS="COMMAND"
|
|
>alter table cust
|
|
add (company_name char (35) )</B
|
|
></PRE
|
|
></TD
|
|
></TR
|
|
></TABLE
|
|
><P
|
|
>To give company_name a default value of "n/a" : </P
|
|
><TABLE
|
|
BORDER="0"
|
|
BGCOLOR="#E0E0E0"
|
|
WIDTH="100%"
|
|
><TR
|
|
><TD
|
|
><PRE
|
|
CLASS="PROGRAMLISTING"
|
|
><B
|
|
CLASS="COMMAND"
|
|
>alter table cust
|
|
alter company_name set default 'n/a'</B
|
|
></PRE
|
|
></TD
|
|
></TR
|
|
></TABLE
|
|
></DIV
|
|
></DIV
|
|
><DIV
|
|
CLASS="SECT2"
|
|
><H2
|
|
CLASS="SECT2"
|
|
><A
|
|
NAME="AEN792"
|
|
>6.2. Users, permissions, and authorities</A
|
|
></H2
|
|
><P
|
|
>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. </P
|
|
><P
|
|
>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. </P
|
|
><DIV
|
|
CLASS="SECT3"
|
|
><H3
|
|
CLASS="SECT3"
|
|
><A
|
|
NAME="AEN796"
|
|
>6.2.1. User IDs</A
|
|
></H3
|
|
><DIV
|
|
CLASS="SECT4"
|
|
><H4
|
|
CLASS="SECT4"
|
|
><A
|
|
NAME="AEN798"
|
|
>6.2.1.1. Special user IDs</A
|
|
></H4
|
|
><P
|
|
>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.</P
|
|
><P
|
|
>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.</P
|
|
><P
|
|
>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.</P
|
|
><P
|
|
>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. </P
|
|
></DIV
|
|
><DIV
|
|
CLASS="SECT4"
|
|
><H4
|
|
CLASS="SECT4"
|
|
><A
|
|
NAME="AEN804"
|
|
>6.2.1.2. Creating new user IDs</A
|
|
></H4
|
|
><P
|
|
>The SQL statement to add a new user ID is GRANT CONNECT. </P
|
|
><P
|
|
>Syntax: </P
|
|
><TABLE
|
|
BORDER="0"
|
|
BGCOLOR="#E0E0E0"
|
|
WIDTH="100%"
|
|
><TR
|
|
><TD
|
|
><PRE
|
|
CLASS="PROGRAMLISTING"
|
|
><B
|
|
CLASS="COMMAND"
|
|
>GRANT CONNECT TO userid1
|
|
IDENTIFIED BY password1 </B
|
|
></PRE
|
|
></TD
|
|
></TR
|
|
></TABLE
|
|
><P
|
|
>To add a user ID with the name Mortimer, execute the following
|
|
SQL statement: </P
|
|
><TABLE
|
|
BORDER="0"
|
|
BGCOLOR="#E0E0E0"
|
|
WIDTH="100%"
|
|
><TR
|
|
><TD
|
|
><PRE
|
|
CLASS="PROGRAMLISTING"
|
|
><B
|
|
CLASS="COMMAND"
|
|
>grant connect to mortimer identified by
|
|
monkey </B
|
|
></PRE
|
|
></TD
|
|
></TR
|
|
></TABLE
|
|
></DIV
|
|
></DIV
|
|
><DIV
|
|
CLASS="SECT3"
|
|
><H3
|
|
CLASS="SECT3"
|
|
><A
|
|
NAME="AEN813"
|
|
>6.2.2. Permissions</A
|
|
></H3
|
|
><P
|
|
>This section explains permissions on tables that can be granted
|
|
to users. Permissions are granted on a user-by-user basis. </P
|
|
><P
|
|
>There are a few different table permissions that can be granted
|
|
to a user, and they are each granted separately. </P
|
|
><P
|
|
></P
|
|
><UL
|
|
><LI
|
|
><P
|
|
><EM
|
|
>SELECT</EM
|
|
> allows
|
|
the user to <EM
|
|
>read</EM
|
|
> data, and can be restricted
|
|
to particular columns. </P
|
|
></LI
|
|
><LI
|
|
><P
|
|
><EM
|
|
>INSERT</EM
|
|
> allows the user to <EM
|
|
>add</EM
|
|
> data. </P
|
|
></LI
|
|
><LI
|
|
><P
|
|
><EM
|
|
>UPDATE</EM
|
|
> allows the user to <EM
|
|
>change</EM
|
|
> data,
|
|
and can be restricted to particular columns. </P
|
|
></LI
|
|
><LI
|
|
><P
|
|
><EM
|
|
>DELETE</EM
|
|
> allows the user to <EM
|
|
>remove</EM
|
|
> data. </P
|
|
></LI
|
|
><LI
|
|
><P
|
|
><EM
|
|
>ALTER</EM
|
|
> allows the user to <EM
|
|
>modify
|
|
the structure</EM
|
|
> of a table. </P
|
|
></LI
|
|
><LI
|
|
><P
|
|
><EM
|
|
>REFERENCES</EM
|
|
> allows the user
|
|
to add indexes, primary keys, and foreign keys. </P
|
|
></LI
|
|
><LI
|
|
><P
|
|
><EM
|
|
>ALL</EM
|
|
> includes all the above
|
|
permissions. </P
|
|
></LI
|
|
></UL
|
|
><P
|
|
>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: </P
|
|
><TABLE
|
|
BORDER="0"
|
|
BGCOLOR="#E0E0E0"
|
|
WIDTH="100%"
|
|
><TR
|
|
><TD
|
|
><PRE
|
|
CLASS="PROGRAMLISTING"
|
|
><B
|
|
CLASS="COMMAND"
|
|
>GRANT [ SELECT (column-name, ...)
|
|
| INSERT
|
|
| UPDATE (column-name, ...)
|
|
| DELETE
|
|
| ALTER
|
|
| REFERENCES
|
|
| ALL ]
|
|
ON table-name
|
|
TO userid</B
|
|
></PRE
|
|
></TD
|
|
></TR
|
|
></TABLE
|
|
><P
|
|
>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. </P
|
|
><P
|
|
>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: </P
|
|
><P
|
|
><B
|
|
CLASS="COMMAND"
|
|
>grant select on banana_supply (type, quantity) to mortimer</B
|
|
></P
|
|
><P
|
|
>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. </P
|
|
><P
|
|
>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: </P
|
|
><TABLE
|
|
BORDER="0"
|
|
BGCOLOR="#E0E0E0"
|
|
WIDTH="100%"
|
|
><TR
|
|
><TD
|
|
><PRE
|
|
CLASS="PROGRAMLISTING"
|
|
><B
|
|
CLASS="COMMAND"
|
|
>grant select on banana_supply (type, quantity)
|
|
to mortimer
|
|
with grant option </B
|
|
></PRE
|
|
></TD
|
|
></TR
|
|
></TABLE
|
|
></DIV
|
|
><DIV
|
|
CLASS="SECT3"
|
|
><H3
|
|
CLASS="SECT3"
|
|
><A
|
|
NAME="AEN855"
|
|
>6.2.3. Authorities</A
|
|
></H3
|
|
><P
|
|
>An authority is a different level of permission. There are
|
|
two types of authority. </P
|
|
><DIV
|
|
CLASS="SECT4"
|
|
><H4
|
|
CLASS="SECT4"
|
|
><A
|
|
NAME="AEN858"
|
|
>6.2.3.1. RESOURCE authority</A
|
|
></H4
|
|
><P
|
|
>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: </P
|
|
><P
|
|
><B
|
|
CLASS="COMMAND"
|
|
>GRANT RESOURCE TO userid </B
|
|
></P
|
|
></DIV
|
|
><DIV
|
|
CLASS="SECT4"
|
|
><H4
|
|
CLASS="SECT4"
|
|
><A
|
|
NAME="AEN863"
|
|
>6.2.3.2. DBA authority</A
|
|
></H4
|
|
><P
|
|
>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. </P
|
|
><P
|
|
><B
|
|
CLASS="COMMAND"
|
|
>GRANT DBA TO userid </B
|
|
></P
|
|
></DIV
|
|
></DIV
|
|
><DIV
|
|
CLASS="SECT3"
|
|
><H3
|
|
CLASS="SECT3"
|
|
><A
|
|
NAME="AEN868"
|
|
>6.2.4. Removing Users and Revoking Permissions</A
|
|
></H3
|
|
><P
|
|
>The SQL statement to delete a user ID is REVOKE CONNECT. </P
|
|
><P
|
|
>Syntax: </P
|
|
><P
|
|
><B
|
|
CLASS="COMMAND"
|
|
>REVOKE CONNECT FROM userid [, userid ] </B
|
|
></P
|
|
><P
|
|
>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: </P
|
|
><P
|
|
><B
|
|
CLASS="COMMAND"
|
|
>revoke connect from mortimer, chestington</B
|
|
></P
|
|
><P
|
|
>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: </P
|
|
><P
|
|
><B
|
|
CLASS="COMMAND"
|
|
>revoke select on banana_supply (type, quantity) from
|
|
mortimer</B
|
|
></P
|
|
></DIV
|
|
><DIV
|
|
CLASS="SECT3"
|
|
><H3
|
|
CLASS="SECT3"
|
|
><A
|
|
NAME="AEN880"
|
|
>6.2.5. Changing Passwords</A
|
|
></H3
|
|
><P
|
|
>To change the password associated with a particular user ID,
|
|
use a GRANT CONNECT statement again: </P
|
|
><P
|
|
><B
|
|
CLASS="COMMAND"
|
|
>GRANT CONNECT TO userid IDENTIFIED BY newpassword</B
|
|
></P
|
|
><P
|
|
>For example, to change the DBA's password from "SQL" to "d0n13xw9,"
|
|
use this statement: </P
|
|
><P
|
|
><B
|
|
CLASS="COMMAND"
|
|
>grant connect to DBA identified by d0n13xw9</B
|
|
></P
|
|
></DIV
|
|
></DIV
|
|
><DIV
|
|
CLASS="SECT2"
|
|
><H2
|
|
CLASS="SECT2"
|
|
><A
|
|
NAME="AEN888"
|
|
>6.3. Making the database more secure</A
|
|
></H2
|
|
><P
|
|
>Some of the Adaptive Server Anywhere features you may wish
|
|
to use in building a secure environment for your data include the
|
|
following: </P
|
|
><P
|
|
></P
|
|
><UL
|
|
><LI
|
|
><P
|
|
><EM
|
|
>User identification and
|
|
authentication </EM
|
|
>control access to databases.</P
|
|
></LI
|
|
><LI
|
|
><P
|
|
><EM
|
|
>Permissions and authorities</EM
|
|
>,
|
|
which have already been explained in previous sections, control
|
|
the actions a user can carry out while connected to a database. </P
|
|
></LI
|
|
><LI
|
|
><P
|
|
><EM
|
|
>Views and stored procedures </EM
|
|
>allow
|
|
you to carefully tune the data a user can access and the operations
|
|
a user can execute. </P
|
|
></LI
|
|
><LI
|
|
><P
|
|
><EM
|
|
>Connection encryption</EM
|
|
> can prevent
|
|
unauthorized persons from snooping. </P
|
|
></LI
|
|
></UL
|
|
><P
|
|
>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: </P
|
|
><DIV
|
|
CLASS="TABLE"
|
|
><A
|
|
NAME="AEN905"
|
|
></A
|
|
><P
|
|
><B
|
|
>Table 8. </B
|
|
></P
|
|
><TABLE
|
|
BORDER="1"
|
|
CLASS="CALSTABLE"
|
|
><TBODY
|
|
><TR
|
|
><TD
|
|
WIDTH="328"
|
|
ALIGN="LEFT"
|
|
VALIGN="TOP"
|
|
><EM
|
|
>Chapter</EM
|
|
></TD
|
|
><TD
|
|
WIDTH="512"
|
|
ALIGN="LEFT"
|
|
VALIGN="TOP"
|
|
>Section</TD
|
|
></TR
|
|
><TR
|
|
><TD
|
|
WIDTH="328"
|
|
ALIGN="LEFT"
|
|
VALIGN="TOP"
|
|
><EM
|
|
>Using Procedures,
|
|
Triggers, and Batches</EM
|
|
></TD
|
|
><TD
|
|
WIDTH="512"
|
|
ALIGN="LEFT"
|
|
VALIGN="TOP"
|
|
>Benefits of procedures and triggers</TD
|
|
></TR
|
|
><TR
|
|
><TD
|
|
WIDTH="328"
|
|
ALIGN="LEFT"
|
|
VALIGN="TOP"
|
|
><EM
|
|
>Managing User IDs
|
|
and Permissions</EM
|
|
></TD
|
|
><TD
|
|
WIDTH="512"
|
|
ALIGN="LEFT"
|
|
VALIGN="TOP"
|
|
>Using views and procedures for
|
|
extra security</TD
|
|
></TR
|
|
></TBODY
|
|
></TABLE
|
|
></DIV
|
|
><DIV
|
|
CLASS="SECT3"
|
|
><H3
|
|
CLASS="SECT3"
|
|
><A
|
|
NAME="AEN923"
|
|
>6.3.1. Increasing password security</A
|
|
></H3
|
|
><P
|
|
>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: </P
|
|
><P
|
|
><B
|
|
CLASS="COMMAND"
|
|
>set option public.min_password_length = 8 </B
|
|
></P
|
|
><P
|
|
>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! </P
|
|
></DIV
|
|
><DIV
|
|
CLASS="SECT3"
|
|
><H3
|
|
CLASS="SECT3"
|
|
><A
|
|
NAME="AEN929"
|
|
>6.3.2. Views, procedures, and triggers</A
|
|
></H3
|
|
><P
|
|
>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. </P
|
|
><P
|
|
>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. </P
|
|
><P
|
|
>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.</P
|
|
><P
|
|
>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. </P
|
|
></DIV
|
|
><DIV
|
|
CLASS="SECT3"
|
|
><H3
|
|
CLASS="SECT3"
|
|
><A
|
|
NAME="AEN935"
|
|
>6.3.3. Encrypting client/server communications</A
|
|
></H3
|
|
><P
|
|
>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: </P
|
|
><P
|
|
><B
|
|
CLASS="COMMAND"
|
|
>dbsrv7 -e -x tcpip mydb.db </B
|
|
></P
|
|
><P
|
|
>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: </P
|
|
><P
|
|
><B
|
|
CLASS="COMMAND"
|
|
>"uid=mortimer;pwd=monkey;links=tcpip;eng=MyServer;dbf=mydb.db;enc=true"</B
|
|
></P
|
|
><P
|
|
>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" . </P
|
|
></DIV
|
|
></DIV
|
|
></DIV
|
|
><DIV
|
|
CLASS="NAVFOOTER"
|
|
><HR
|
|
ALIGN="LEFT"
|
|
WIDTH="100%"><TABLE
|
|
WIDTH="100%"
|
|
BORDER="0"
|
|
CELLPADDING="0"
|
|
CELLSPACING="0"
|
|
><TR
|
|
><TD
|
|
WIDTH="33%"
|
|
ALIGN="left"
|
|
VALIGN="top"
|
|
><A
|
|
HREF="backup.html"
|
|
>Prev</A
|
|
></TD
|
|
><TD
|
|
WIDTH="34%"
|
|
ALIGN="center"
|
|
VALIGN="top"
|
|
><A
|
|
HREF="index.html"
|
|
>Home</A
|
|
></TD
|
|
><TD
|
|
WIDTH="33%"
|
|
ALIGN="right"
|
|
VALIGN="top"
|
|
><A
|
|
HREF="moreinfo.html"
|
|
>Next</A
|
|
></TD
|
|
></TR
|
|
><TR
|
|
><TD
|
|
WIDTH="33%"
|
|
ALIGN="left"
|
|
VALIGN="top"
|
|
>Backing up and Restoring a Database</TD
|
|
><TD
|
|
WIDTH="34%"
|
|
ALIGN="center"
|
|
VALIGN="top"
|
|
> </TD
|
|
><TD
|
|
WIDTH="33%"
|
|
ALIGN="right"
|
|
VALIGN="top"
|
|
>Where to get more information</TD
|
|
></TR
|
|
></TABLE
|
|
></DIV
|
|
></BODY
|
|
></HTML
|
|
> |