Protecting information is an important part of any database. Users should not be allowed free access to all areas of the database, and they should not be capable of changing data at will. DBMaker provides several means of protecting data and managing access to data in its databases.

9.1 Security Management

Security management is important for restricting data access to only those users who require it. Security management in DBMaker is provided in several ways:

User accounts - uses user ID's and passwords to control access to the database.
Authority levels - controls actions that can be performed by different users.
Table privileges - lets users share some data while keeping other data private.
Nested groups - simplifies granting privileges by organizing users into groups.

Each user able to access the database is identified by a unique user ID and an optional password. All users are also given an authority level for their user accounts. These user authority levels specify who can access a database and what they can do. There are four authority levels in DBMaker: CONNECT, RESOURCE, DBA (Database Administrator) and SYSADM (System Administrator). There may be any number of CONNECT, RESOURCE and DBA user accounts, but there is only one SYSADM account. This account is reserved for the person who set up and maintains the database and user accounts.

Table privileges are used to control access to the data in the database. By using table privileges you can allow other users access to the data in the tables you create. You can use table permissions to allow other users to perform operations on the table data such as: view, insert, delete and update data in a table, create an index on the table, reference the table from other tables or alter the table by adding new columns. Table privileges can also be granted to PUBLIC. By using the PUBLIC keyword, the privileges on a table are granted to all users of the database.

9.2 User Authority Levels

Connect

To connect to a database, a user must first be granted connect authority. A user with connect authority has very limited access to the database. They cannot create any new objects, such as tables or views, in the database. They can only view or alter data in tables that have privileges granted to PUBLIC. They cannot alter or view any tables created by other users until table privileges have been granted to them by the owner of the table or a user with DBA authority. After table privileges have been granted, they may use any table privileges that have been granted to them or to PUBLIC.

Resource

Resource authority allows a user to create new tables in the database and drop any tables they previously created. As the owner of a table, they may also create and drop views and indices on tables they create. To allow the data in the tables they create to be shared, they may also grant and revoke table privileges on any tables they own to other users. They cannot alter or view any tables created by other users until table privileges have been granted to them or to PUBLIC by the owner of the table or a user with DBA authority.

DBA

DBA authority allows a much greater degree of control over the database. It gives a user all privileges on all tables and views in the database. A DBA can create new tables, as well as view, alter and grant table permissions on tables owned by others. A DBA can also create new database structures, such as tablespaces and data files. To control access for a number of users at a time, a DBA can also create and remove groups and add and remove users from those groups. However, a DBA user cannot change the authority level of a current user, grant new users permission to connect to the database, or change user passwords.

SYSADM

There is only one SYSADM for each database. The SYSADM ID is assigned by default to the user who created the database. The SYSADM has complete control over the database. He can add new tables to the database, view, alter and drop the tables of any user, grant table permissions for any tables and create new tablespaces and data files. Only the SYSADM can grant, revoke and change user authority levels or change a user's password.

9.3 Adding New Users

Only the SYSADM can grant CONNECT authority to add new users to a database. Therefore, to add new users to the database, you must log in as the SYSADM. If the database is not yet started, you must first start the database:

start db tutorial SYSADM password;

If the database is already started, connect to the database:

connect to tutorial SYSADM password;

Allowing Users Access to the Database

When you have connected to the database as the SYSADM, you are ready to add a new user. New users are added using the GRANT keyword. The GRANT command is used to specify user names, passwords and their authority levels. The syntax of the GRANT keyword is:

The user ID may contain letters or numbers, and may be a maximum of eight characters in length. If the first character of the user ID is a number, the entire user ID must be enclosed in double quotes (""). The user ID is also case sensitive, so it is possible to have two different user accounts with the names user and User.

Like the user ID, the password may also contain letters or numbers to a maximum of eight characters. The password must also be enclosed in double quotes if the first character is a number. The password is also case sensitive, so it must be entered exactly as it was first defined.

A new user must always be granted CONNECT authority before they can be promoted to a higher authority level, such as RESOURCE or DBA. When creating a new user account, you can choose to assign a password, or you can create the account with no password and let the user create their own password later. For security reasons, it is usually best to assign a temporary password when you create the account, and ask the user change it when they first log on. To create a new user account without a password, you can use any of the following:

grant connect to Judy; 
grant connect to Judy NULL; 
grant connect to Judy "";

To create a new user account with a password:

grant connect to Judy secret;

You should note that the user name is case sensitive. Every time a user logs on to the database, they must type the user name exactly as it appears. When you create new user accounts, it is best to format the user names the same way every time for consistency. For example, always use initial caps, always use all lower case, or always use all upper case.

Adding Multiple Users

You can also create new user accounts for multiple users at the same time. The syntax is the same as for adding one user, but you specify multiple user names and passwords on the command line. To add two new users without assigning passwords, you can use any of:

grant connect to Tom, Judy; 
grant connect to Tom "", Judy "";    
grant connect to Tom NULL, Judy NULL;

To add multiple new users with passwords, you would enter:

grant connect to Tom secret1, Judy secret2;

9.4 Promoting a User's Authority Level

The GRANT keyword is also used to promote an existing user's authority level. The procedure is the same as granting CONNECT authority to a new user. Because a user may have already have a password for their user account, you cannot specify a new password. (This would make their account inaccessible to them until you told them the new password.) To promote a user from CONNECT to RESOURCE or DBA authority:

grant resource to Judy; 
grant DBA to Judy;

You should note that granting DBA authority to a user does not automatically give them RESOURCE authority. This becomes important when you are demoting a users authority level. If you want to give a user both RESOURCE and DBA authority, you can use the grant command twice: once to grant the RESOURCE authority level, and once to grant the DBA authority level.

Promoting Multiple Users

You can also promote multiple users at the same time. The syntax is the same as for promoting one user, but you specify multiple user names on the command line. However, you must be promoting the users to the same authority level. It is not possible to promote multiple users to different authority levels with one command. To promote two new users:

grant RESOURCE to Tom, Judy; 
grant DBA to Tom, Judy;

9.5 Demoting a User's Authority Level

Demoting a user's authority level is similar to promoting a user's authority level, but you use the REVOKE keyword instead. The syntax of the REVOKE keyword is as follows:

When you revoke an authority level from a user, that user drops to the next lower authority level they have been granted. Consider the following example: A new user is created with CONNECT authority. That user is then granted DBA authority, without being granted RESOURCE authority. After DBA authority is revoked from the user, they will only have CONNECT authority. To demote a user from DBA to the next lower authority level they have been given:

revoke DBA from Judy;

To demote a user from RESOURCE to CONNECT authority:

revoke RESOURCE from Judy;

If a user was first granted RESOURCE and then DBA authority, you must revoke both DBA and RESOURCE authority to demote the user to CONNECT authority. To demote a user from DBA authority to CONNECT authority:

revoke DBA from Judy; 
revoke RESOURCE from Judy;

9.6 Removing Existing Users

The REVOKE keyword is also used to remove an existing user. The procedure for removing an existing user is the same as for demoting a users authority level. Once a user has had CONNECT authority revoked, that user is removed from the list of people who can connect to the database. To remove an existing user from a database:

revoke CONNECT from Judy;

9.7 Setting and Changing Passwords

Usually, the SYSADM assigns a temporary password to a new user when creating their user account. If the user wants to change this password, or it the SYSADM did not assign a temporary password, the user can change the password or set a new password by using the ALTER PASSWORD command. The SYSADM can also assign a new password to a user.

This would most commonly be used only if the user forgot their password and could no longer access the database because of this. The syntax of the ALTER PASSWORD command is:

If the user doesn't have a password, and they want to create a password, they can do one of the following:

alter password NULL to newpass;    
alter password "" to newpass;

If the user was assigned a temporary password by the SYSADM, and they want to change the password for security reasons, they should enter:

alter password X9elx4 to newpass;

A user can remove a password by changing their current password to NULL. To change a password to NULL, you can enter:

alter password oldpass to NULL;    
alter password oldpass to "";

The SYSADM can also change or remove a user's password. No user other than the SYSADM can change another user's password. To change a user's password, the SYSADM does not have to know the user's existing password. He uses a slightly different form of the command from that shown above:

alter password of Judy to newpass;    
alter password of Judy to NULL; 
alter password of Judy to "";

9.8 Using Groups to Control Access Privileges

When a database becomes very large and has many users, it becomes more and more difficult to grant user privileges on an individual basis. To solve this problem, DBMaker allows you to place users who require access to the same data into groups. Using groups, you can collect together all users who require the same database privileges to simplify authorization management. Table privileges can be granted or revoked for all users in the group at the same time, rights to a number of users at the same time, simplifying authorization management.

9.9 Creating a Group

To create a new group, you use the CREATE GROUP command. The syntax of the CREATE GROUP command is:

The group name may contain letters or numbers. The group name can be any length, but only the first eight characters will be used. Because of this, you must be careful when naming groups. For example, both the groups CompanyEmployees and CompanyExecutives will have the same significant characters, CompanyE. Trying to create the second group with the same significant characters will result in an error.

The group name is also case sensitive, so names companyExecutives and CompanyExecutives will create two different groups, companyE and CompanyE. To avoid either of these situations, it is suggested that you limit group names to eight or less characters, and avoid long descriptive names.

To create a group of all the people who work in marketing, you would type:

create group marketing;

There is now a group called marketing that you can add all of the people in the marketing department to. Then privileges can be granted to all users in this group on objects that they all need access to.

9.10 Adding Members to a Group

To add a user to a group, you use the ADD TO GROUP command:

You can add users one at a time, or you can add several at once. To add one user named Judy to the group SalesRep:

add Judy to group SalesRep;

To add the users Judy, Jeff and Trent to the SalesRep group:

add Judy, Jeff, Trent to group SalesRep;

9.11 Removing Members from a Group

To remove a user from a group, you use the REMOVE FROM GROUP command:

The REMOVE GROUP command can be used for removing a single user, or, for removing multiple users at the same time. To remove one user named Judy from the group SalesRep:

remove Judy from group SalesRep;

To remove the userS Judy, Jeff and Trent from the group SalesRep:

remove Judy, Jeff, Trent from group SalesRep;

9.12 Dropping a Group

If a group of users is empty, or is no longer necessary, you can drop the group you created by using the DROP GROUP command. The syntax of this command is:

However, before a group can be removed, it must be empty. If the group is not yet empty, you must remove all users using the REMOVE FROM GROUP command above. To drop the group named SalesRep, first ensure that it is empty, then type:

drop group SalesRep;

9.13 Nested Groups

To further enhance the functionality of groups, nested groups are permitted. To create a nested group, simply use a group name as a user id, and use the ADD TO GROUP command to add that group to another group. To remove the nested group from another group, use the REMOVE FROM GROUP command. To create a group NYSales and add it to the SalesRep group, then remove it, you would type:

create group NYSales; 
add NYSales to group SalesRep; 
remove NYSales from group SalesRep;

9.14 Table Level Privileges

As the owner of a table or a user with DBA authority, you control access to tables through seven table privileges. Four of the privileges allow you to view and alter the data in a table: SELECT, INSERT, DELETE and UPDATE. The three remaining privileges allow you to create indexes (INDEX), change the table definition (ALTER), and place referential integrity constraints on the table (REFERENCE). Table privileges can also be used to allow changes to be made only to specified columns in a table.

Select

SELECT privilege allows you to view any data in a table or view. SELECT privilege may be granted to any user by the table owner or any user with DBA authority.

Insert

INSERT privilege allows you to insert new data into a table. INSERT privilege may be granted for an entire table, or only for specified columns. To grant INSERT privilege only on specified columns, include a column name list after the INSERT keyword. INSERT privilege may be granted to any user by the table owner or any user with DBA authority.

Delete

DELETE privilege allows you to delete data from a table. DELETE privilege may be granted to any user by the table owner or any user with DBA authority.

Update

UPDATE privilege allows you to update any of the values in a table. UPDATE privilege may be granted for an entire table, or only for specified columns. To grant UPDATE privilege only on specified columns, include a column name list after the UPDATE keyword. UPDATE privilege may be granted to any user by the table owner or any user with DBA authority.

Index

INDEX privilege allows you to create an index on a table. INDEX privilege may be granted to any user by the table owner or any user with DBA authority.

Alter

ALTER privilege allows you to alter the table definition of a table. ALTER privilege may be granted to any user by the table owner or any user with DBA authority.

Reference

REFERENCE privilege allows you to create foreign keys to a table. REFERENCE privilege may be granted for an entire table, or only for specified columns. To grant REFERENCE privilege only on specified columns, include a column name list after the REFERENCE keyword. REFERENCE privilege may be granted to any user by the table owner or any user with DBA authority.

9.15 Granting Table Privileges

You may grant table privileges to any user if you are the owner of the table, or a user with DBA authority. Some of the privileges can also be granted on specified columns, not only on the entire table. Privileges on tables are given with the grant command:

By looking at the syntax diagram above, you can see that you cannot grant privileges on a table and on specified columns in that table at the same time. If you wish to grant some privileges on the entire table, and some privileges on specified columns, you must use two commands.

Privileges can be granted to a single user, groups of users if they belong to a group, and to all users by using the PUBLIC keyword.

Granting Privileges on Entire Tables

To grant SELECT privilege on a table named SalesRep to the user named Judy:

grant select on SalesRep to Judy;

It is also possible to grant more than one privilege at the same time. To do this, simply list the privileges you want to grant on the command line, separated with a comma. To grant SELECT and UPDATE privileges on table SalesRep to Judy:

grant select, update on SalesRep to Judy;

To grant all table privileges to a user, you can explicitly list all the keywords on the command line, or you can use the ALL keyword provided by DBMaker. To grant all table privileges (SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX AND REFERENCE) on table SalesRep to user Judy:

grant all on SalesRep to Judy;

Privileges can also be granted to more than one user at a time. To grant privileges to multiple users, you specify multiple user names, separated by commas, on the command line. For example, to grant SELECT and UPDATE privilege on table SalesRep to users Judy and Jeff:

grant select, update on SalesRep to Judy,Jeff;

If you want to grant privileges to a group of users, or multiple groups, replace the user names on the command line with the group names. To grant SELECT and UPDATE privileges on table SalesRep to the groups Personnel and SalesMgr:

grant select, update on SalesRep to Personnel, SalesMgr;

You should note that it is not possible to grant privileges on multiple tables at the same time.

Granting Privileges on Specified Columns

It is possible to grant the INSERT, UPDATE and REFERENCE privileges only on specific columns. If you are granting privileges on columns, it is not possible to grant other privileges on the entire table in the same command. To grant INSERT privilege on the column Name of the SalesRep table to user Judy:

grant INSERT (Name) on SalesRep to Judy;

To grant INSERT privilege on more than one column, list the columns separated by commas:

grant INSERT (Name, Age, RepOffice, Title) on SalesRep to Judy;

You can also grant more than one privilege with one command. However, all the privileges you grant must act on the same columns. DBMaker does not support granting privileges on different columns in a single command. To grant UPDATE, INSERT and REFERENCE privileges on columns Name and Age of the SalesRep table to user Judy:

grant INSERT, UPDATE, REFERENCE (Name, Age) on SalesRep to Judy;

Granting privileges on columns can also be applied to multiple users and groups of users in the same way as granting privileges on tables. You list the user or group names on the command line, separated by commas.

9.16 Revoking Table Privileges

You may revoke table privileges from any user if you are the owner of the table, or a user with DBA authority. Some of the privileges can also be revoked from specified columns, not only on the entire table. Privileges on tables are removed with the REVOKE command:

By looking at the syntax diagram above, you can see that you cannot revoke privileges on a table and on specified columns in that table at the same time. If you wish to revoke some privileges on the entire table, and some privileges on specified columns, you must use two commands.

Privileges can be revoked from a single user, groups of users if they belong to a group, and to all users by using the PUBLIC keyword.

Revoking Privileges on Entire Tables

To revoke SELECT privilege on a table named SalesRep to the user named Judy:

revoke select on SalesRep to Judy;

It is also possible to revoke more than one privilege at the same time. To do this, simply list the privileges you want to revoke on the command line, separated with a comma. To revoke SELECT and UPDATE privileges on table SalesRep to Judy:

revoke select, update on SalesRep to Judy;

To revoke all table privileges from a user, you can explicitly list all the keywords on the command line, or you can use the ALL keyword provided by DBMaker. To revoke all table privileges (SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX AND REFERENCE) on table SalesRep from user Judy:

revoke all on SalesRep to Judy;

Privileges can also be revoked from more than one user at a time. To revoke privileges from multiple users, you specify multiple user names, separated by commas, on the command line. For example, to revoke SELECT and UPDATE privilege from table SalesRep from users Judy and Jeff:

revoke select, update on SalesRep to Judy,Jeff;

If you want to revoke privileges from a group of users, or multiple groups, replace the user names on the command line with the group names. To revoke SELECT and UPDATE privileges on table SalesRep from the groups Personnel and SalesMgr:

revoke select, update on SalesRep to Personnel,SalesMgr;

You should note that it is not possible to grant privileges on multiple tables at the same time.

Granting Privileges on Specified Columns

It is possible to revoke the INSERT, UPDATE and REFERENCE privileges only on specific columns. If you are revoking privileges on columns, it is not possible to revoke other privileges on the entire table in the same command. To revoke INSERT privilege on the column Name of the SalesRep table from user Judy:

revoke INSERT (Name) on SalesRep to Judy;

To revoke INSERT privilege on more than one column, list the columns separated by commas:

revoke INSERT (Name, Age, RepOffice, Title) on SalesRep to Judy;

You can also revoke more than one privilege with one command. However, all the privileges you revoke must act on the same columns. DBMaker does not support revoking privileges on different columns in a single command. To revoke UPDATE, INSERT and REFERENCE privileges on columns Name and Age of the SalesRep table from user Judy:

revoke INSERT, UPDATE, REFERENCE (Name, Age) on SalesRep from Judy;

Revoking privileges on columns can also be applied to multiple users and groups of users in the same way as revoking privileges on tables. You list the user or group names on the command line, separated by commas.