|
   
12.
Security Management
 12.1
Security Policies
12.2
Database Authority
12.3
Object Privileges
12.4
Security System Catalog
12. Security Management
This chapter
provides guidelines on how to set up the security policies of a DBMaker
database, and includes information on setting security on a database,
setting the authority level of users, and setting table privileges.
12.1 Security Policies
For use
in an isolated environment, DBMaker provides an option to turn off security
management. To achieve this, users can set DB_SECUR=0 in the dmconfig.ini
file. With security management turned off DBMaker won't check for any
security violations, which reduces user and system overhead. However,
this option can only be used when creating a new database using the
CREATE DB command. Once a database has been created, security checking
policies cannot be changed unless the database is unloaded and recreated.
DBMaker
provides two kinds of security:
Database authority - determines who can log on to DBMaker and the
actions they can perform.
Object privileges - controls access rights for DBMaker objects.
DBMaker objects include tables, columns, views, domains and synonyms.
12.2 Database Authority
Database
authority is used to determine who can access a database and what they
can do. DBMaker controls database access with user names and
passwords. DBMaker has four classes of users as shown in Table
12-1.
The SYSADM
is the most powerful user in DBMaker. There can be only one SYSADM for
every database. The SYSADM can grant DBA, RESOURCE or CONNECT authority
to users, and also has all the privileges of the DBA authority level
on the database.
Users with
DBA authority level have all privileges on all objects in the database
and can grant, change, or revoke object privileges for any user in the
database except the SYSADM and other DBA users. They can also create
new resources like tablespaces and files, and do database administration
operations like starting/terminating databases and backing up databases.
Users with
RESOURCE authority are allowed to create new tables or views, and to
grant privileges on his/her own tables to other users.
Users with
only CONNECT authority can access objects that they have been granted
privileges for, but cannot create new tables or views. They may also
select information from the system tables.
The authority
levels are hierarchical as shown below:
Figure
12-1: DBMaker database authority level hierarchy.
|
Level
|
Privileges
|
|
SYSADM
|
Can grant and revoke security authority levels to all users (but
not the SYSADM authority level).
Can change the passwords of all users.
Has all the privileges of the DBA authority level.
|
|
DBA
|
Has all privileges on all tables except the SYSTEM tables.
Can grant/change/revoke object privileges of all users/groups.
Can add/remove users from groups.
Has privileges on database administration commands such as starting
or terminating a database, creating/dropping/ altering a tablespace,
and backing up a database.
Has all the privileges of the CONNECT and RESOURCE authority levels.
|
|
RESOURCE
|
Can create and drop tables, views, domains and synonyms. (Can
only drop tables, views, domains and synonyms created by the user.)
Can grant/revoke owned table/view privileges to other users.
Has any table privileges granted to the user.
Has all the privileges of the CONNECT authority level.
|
|
CONNECT
|
Can log on to the database.
Can select the SYSTEM tables.
Has any table privileges granted to the user.
This authority level must be granted before the other authority
levels can be granted.
|
Table
12-1: DBMaker database authority levels
Managing Users
DBMaker
provides several SQL commands for managing users. These commands allow
you to add a new user to and remove existing users from a database,
set or change user passwords, and change the authority levels granted
to users.
Adding a User
The SYSADM
must assign each user a user name and a password by using the SQL GRANT
(database authority) command before an user can log on to DBMaker.
The GRANT
command grants authority levels to users. Only the SYSADM can grant
authority levels to other users. The SYSADM authority level cannot be
granted to other users. As a result, for each database there is only
one user with the SYSADM user name and SYSADM authority level. The SYSADM
is also the default user who creates the database. This user name cannot
be changed. The only thing that can be changed for the SYSADM user name
is its password.
The SYSADM
can grant CONNECT, RESOURCE and DBA authorities to other users. If you
use the GRANT command to grant RESOURCE or DBA authority to a user,
it does not take effect until the next time the user connects.
The SYSADM
can grant CONNECT authority to a user with a password. If the SYSADM
doesn't specify the password, it means that user doesn't need a password
to log on to database. A password can be any valid SQL identifier which
is not longer than eight bytes.
Example
The following
example gives CONNECT authority level and the password jeff123to
user Jeff:
dmSQL> GRANT CONNECT TO Jeff jeff123;
|
Example
The following
example increases the authority level of user Jeff to RESOURCE:
dmSQL> GRANT RESOURCE TO Jeff;
|
Example
This example
increases the authority level of user Jeff to DBA:
dmSQL> GRANT DBA TO Jeff;
|
Changing a Password
The SQL
ALTER PASSWORD command can be used to change a user's password. The
syntax of this command is shown in the following figure:
There are
two ways this command can be used in DBMaker:
A user can change his/her own password with the ALTER PASSWORD old_password
TO new_password command. The old_password must match the
original password stored in the database.
The SYSADM can change any user's password with the ALTER PASSWORD OF
user_name TO new_password command. It is not necessary
for the SYSADM to know the old password of other users.
Example
The example
below (executed by user Jeff) changes the password of user Jeff
from no password to xyz@#:
dmSQL> ALTER PASSWORD NULL TO "xyz@#";
|
Example
The example
below (executed by the SYSADM) changes the password of user Jeff
to xyz@#:
dmSQL> ALTER PASSWORD OF Jeff TO "xyz@#";
|
Removing a User or Changing a User's Authority Level
The SQL
REVOKE (database authority) command can remove a database authority
level from a user. The syntax for this command is shown in the following
figure:
The
SYSADM can revoke authority levels as shown in Table 12-2.
If you
revoke a user's RESOURCE or DBA authority, it does not take effect until
the next time this user connects to the database.
Example
The example
below shows how to revoke DBA authority from user Jeff.
dmSQL> REVOKE DBA FROM Jeff;
|
After executing
this command, Jeff no longer has DBA authority, but still has
CONNECT authority. To remove Jeff's CONNECT authority (taking
away the ability to log on), enter the following command:
dmSQL> REVOKE CONNECT FROM Jeff;
|
|
Privilege
|
Description
|
|
DBA
|
Revoking this authority means this user can no longer create or
drop tables, or grant or revoke privileges from other users.
If this user had also been granted RESOURCE privilege, the user
will keep it. Otherwise, this user will retain only CONNECT authority.
All tables, views, domains and synonyms created by this user remain
in the database.
|
|
RESOURCE
|
Revoking this authority means this user can no longer create or
drop tables.
But if this user had also been granted DBA privilege, the user
will keep it. Otherwise this user will retain only CONNECT authority.
All tables, views, domains and synonyms created by this user remain
in the database.
|
|
CONNECT
|
Revoking this authority means this user can no longer log on to
the database.
All privileges owned by this user on tables and views will be
revoked.
All tables, views, domains and synonyms created by this user remain
in database.
|
Table
12-2: Description of revoking DBMaker database authority level
Managing Groups
To simplify
authorization management, you can use a group to collect together several
users and/or other groups. You can then grant database privileges to
all members of a group at the same time with one command. Though a group
is different from a user, you can treat it as a user. If you grant some
privileges to a group, all members of the group will have those privileges.
Only users
with SYSADM or DBA authority levels can use the statements introduced
below.
Creating Groups
The CREATE
GROUP statement is used to create a new group. The syntax for this command
is shown in the following figure:
The group
identification (group name) is a name by which a group is known
to DBMaker. The group name can't be SYSTEM, PUBLIC, GROUP or any existing
user name or group name.
Example
The example
below is used to create a new group named COMMITEE.
dmSQL> CREATE GROUP COMMITEE;
|
Adding Members to Groups
After creating
a new group, users can be added to the group using the ADD ... TO GROUP
command. The syntax for this command is shown in the following figure:
A group
cannot be added as a new member of itself. Members of a group can be
any existing user name or group name.
Example
A DBA can
use the following commands to add a user Jeff and a group RD
to another group COMMITEE and grant the SELECT privileges on
the table CASEMaker.EMPLOYEE to the group COMMITEE.
dmSQL> ADD Jeff, RD TO GROUP COMMITEE;
dmSQL> GRANT SELECT ON CASEMaker.EMPLOYEE TO COMMITEE;
|
All members
of COMMITEE will have the privilege to SELECT table CASEMaker.EMPLOYEE.
Removing Members from Groups
The REMOVE
... FROM GROUP command can be used to remove users from a specified
group. The syntax for this command is shown in the following figure:
The members
removed from the group will lose all privileges granted to the specified
group, but will retain privileges granted to them directly.
Example
The example
below describes how to remove user Jeff from group COMMITEE
whose members have SELECT privilege on the table CASEMaker.EMPLOYEE.
dmSQL> REMOVE Jeff FROM GROUP COMMITEE;
|
After this
command is executed, user Jeff will be removed from the group
COMMITEE and lose SELECT privilege on the table CASEMaker.EMPLOYEE.
Dropping Groups
The DROP
GROUP command will drop a specified group from a database, so that all
members of this group will lose the privileges granted to this group.
The syntax for this command is shown in the following figure:
Example
The following
command will drop the group COMMITTEE from the database. All
its members will lose the privileges granted to this group.
dmSQL> DROP GROUP COMMITEE;
|
12.3 Object Privileges
An object
is any of the following items in a database: tables, views, columns
in tables/views, domains or synonyms. DBMaker provides security management
on objects, which enables users to GRANT or REVOKE object privileges
from other users. However, when a domain is created all users in the
database can reference it by default, but only the creator can drop
the domain. Synonyms do not have privileges. The privileges for a synonym
are based on its base table. Please refer to the chapter Managing
Schema Objects for a detailed definition of views, domains and synonyms.
Granting Object Privileges
The user
who creates an object is the owner of the object and has all privileges
to it. An owner can also grant privileges on the object to other users
by using the SQL GRANT (object privileges) command. The syntax for this
command is shown in the following figure:
A user
with DBA authority can grant privileges on any table or view in a database,
regardless of whether they are the owner or not. A user with the RESOURCE
authority (but without DBA authority) can grant privileges only on tables
or views created by that user.
All privileges
supported by DBMaker are described in Table 12-3.
INSERT,
UPDATE, and DELETE privileges should be scrupulously controlled to prevent
the corruption of information in the database. ALTER and INDEX privileges
should be restricted to developers.
UPDATE,
INSERT, and REFERENCE privileges can be restricted to some specific
columns. Each column name must be unqualified and be in every
table identified in the ON clause.
|
Privilege
|
Description
|
|
SELECT
|
Allows
users to select data from a table/view.
|
|
INSERT
|
Allows
users to insert rows into a table/view or (optionally) insert
only into specified columns.
|
|
DELETE
|
Allows
users to delete rows from a table/view.
|
|
UPDATE
|
Allows
users to update a table/view or (optionally) update only specified
columns.
|
|
INDEX
|
Allows
users to create or drop indexes on a table.
|
|
ALTER
|
Allows
users to alter the definition of a table.
|
|
REFERENCE
|
Allows
users to create a foreign key on a source table that references
a primary key on a destination table/view.
|
|
ALL
[PRIVILEGES]
|
Allows
users to exercise all above privileges for a table or view. (PRIVILEGES
is an optional keyword.)
|
Table
12-3: Description of granting DBMaker table level privileges
The user
name in the GRANT command is a user who has at least CONNECT authority.
The group name is a group name created using the CREATE GROUP command.
The keyword PUBLIC means all current and future users. Granting a privilege
to PUBLIC means that all current and future users will have the specified
privileges on a table.
Example
Jeff executes
the GRANT command below to give Cathy the privilege to read data
in table EMP_INFO which was created by Jeff.
dmSQL> GRANT SELECT ON EMP_INFO TO Cathy;
|
Example
A DBA can
execute the GRANT command below to give Cathy the privilege to
read the data in table EMP_INFO which was created by Jeff.
dmSQL> GRANT SELECT ON Jeff.EMP_INFO TO Cathy;
|
Example
In this
example, a DBA gives INSERT and UPDATE privileges only on the PHONENO
column of table EMP_INFO to Cathy. This means Cathy
has no privilege to delete information from this column.
dmSQL> GRANT INSERT, UPDATE (PHONENO) ON Jeff.EMP_INFO TO Cathy;
|
Example
The PUBLIC
keyword in the example below is used to permit any user in the database
to read data in table Jeff.EMP_INFO.
dmSQL> GRANT SELECT ON Jeff.EMP_INFO TO PUBLIC;
|
Revoking Object Privileges
The REVOKE
(object privileges) command revokes privileges granted to a user at
an earlier time. The syntax for this command is shown in the figure
on the following page.
The privileges
in the REVOKE (object privileges) command are the same as those for
the GRANT (object privileges) command. In the diagram, the user name
represents an authorized user in the database, the group name represents
a group of users, and the PUBLIC keyword represents all users in the
database
Example
In following
examples, Cathy and Jeff are authorized users in the database
and group1 and group2 are groups in the database. The
following command revokes SELECT privilege on the table EMP_INFO
from user Cathy.
dmSQL> REVOKE SELECT ON EMP_INFO FROM Cathy;
|
The following
command revokes SELECT privilege on Jeff's table EMP_INFO
from user Cathy.
dmSQL> REVOKE SELECT on Jeff.EMP_INFO FROM Cathy;
|
The
REVOKE (object privileges) command
The following
command revokes UPDATE privileges on the column PHONENO in Jeff's
table EMP_INFO from the group named group1.
dmSQL> REVOKE UPDATE (PHONENO) on Jeff.EMP_INFO FROM group1;
|
The following
command revokes all privileges granted to PUBLIC on table EMP_INFO.
However, if a user was directly granted any privileges on this table,
they will keep them.
dmSQL> REVOKE ALL ON EMP_INFO FROM PUBLIC;
|
The following
command revokes INSERT, UPDATE, and SELECT privileges on table EMP_INFO
from user Jeff and all users in the group named group2.
dmSQL> REVOKE INSERT, UPDATE, SELECT ON EMP_INFO FROM Jeff, group2;
|
12.4 Security System Catalog
All information
about authority levels, privileges and groups is recorded in the following
system catalogs:
SYSAUTHUSER - authority level of each user.
SYSAUTHTABLE - privileges on tables.
SYSAUTHCOL - columns of a table to which a user has been restricted
for INSERT, UPDATE and REFERENCE privileges.
SYSGROUP - group name, group creator and number of group members.
SYSMEMBER - members of a group.
The security
system catalogs are owned by SYSTEM. No user (including SYSADM) can
modify the system catalogs. Please see Appendix B for more details
on the DBMaker system catalogs.
|
