An overview of database permissions

Chapter 24. Managing User IDs and Permissions
An overview of database permissions

Proper management of user IDs and permissions is essential for users of a database to carry out their jobs effectively while maintaining the security and privacy of appropriate information within the database.

SQL Anywhere provides commands for assigning user IDs to new users of a database, granting and revoking permissions for database users, and finding out the current permissions of users.

Database permissions are assigned to user IDs. Throughout this chapter, the term user is used as a synonym for user ID. You should remember, however, that it is always on the basis of a particular user ID that permissions are granted and revoked.

Setting up individual user IDs

Even if there are no security concerns regarding a multiuser database, there are good reasons for setting up an individual user ID for each user, and the administrative overhead is very low if a group with the appropriate permissions is set up. Groups of users are discussed in this chapter. Among other reasons for using individual user IDs are the following:

The log translation utility can selectively extract the changes made by individual users from a transaction log. This is very useful when troubleshooting or piecing together what happened if data is incorrect.
The network server screen and the listing of connections in Sybase Central are both much more useful with individual user IDs, as you can tell which connections are which users.
Row locking messages (with the BLOCKING option set to OFF) are more informative.

DBA authority overview

When a SQL Anywhere database is created using the DBINIT tool, a single usable user ID is created. This first user ID is DBA and the password is initially set to SQL. The DBA user ID is automatically given DBA permissions, also called DBA authority, within the database. This level of permission enables the DBA user ID to carry out any activity in the database: create tables, change table structures, create new user IDs, revoke permissions from users, and so on.

Users with DBA authority

A user with DBA authority is referred to as the database administrator or database owner. In this chapter frequent reference is made to the database administrator, or the DBA . This is a shorthand for any user or users with DBA authority .

Although DBA authority may be granted or transferred to other user IDs, in this chapter it is assumed that the DBA user ID is the database administrator, and the abbreviation DBA is used interchangeably to mean both the DBA user ID and any user ID with DBA authority.

Adding new users

The DBA has the authority to add new users to the database. As users are added, they are also granted permissions to carry out tasks on the database. Some users may need to simply look at the database information using SQL queries, others may need to add information to the database, and others may need to modify the structure of the database itself. Although some of the responsibilities of the DBA may be handed over to other user IDs, the DBA is responsible for the overall management of the database by virtue of the DBA authority.

The DBA has authority to create database objects and assign ownership of these objects to other user IDs

See the syntax of the commands for creating database objects, in the chapter "Watcom-SQL Language Reference".

Resource authority overview

Resource authority is the permission to create database objects, such as tables, views, stored procedures, and triggers. Resource authority may be granted only by the DBA to other users.

In order to create a trigger, a user needs ALTER permissions on the table to which the trigger applies, in addition to RESOURCE authority.

Ownership permissions overview

The creator of a database object becomes the owner of that object. Ownership of a database object carries with it permissions to carry out actions on that object. These are not assigned to users in the same way that other permissions in this chapter are assigned.

Owners

A user who creates a new object within the database is called the owner of that object, and automatically has permission to carry out any operation on that object. The owner of a table may modify the structure of that table, for instance, or may grant permissions to other database users to update the information within the table.

The DBA has permission to modify any component within the database, and so could delete a table created by another user, for instance. The DBA has all the permissions regarding database objects that the owners of each object has.

The DBA is also able to create database objects for other users, and in this case the owner of an object is not the user ID that executed the CREATE statement. A use for this ability is discussed in "Groups without passwords". Despite this possibility, this chapter refers interchangeably to the owner and creator of database objects.

Table and views permissions overview

There are several distinct permissions that may be granted to user IDs concerning tables:

Permission Description

ALTER Permission to alter the structure of a table or create a trigger on a table

DELETE Permission to delete rows from a table or view

INSERT Permission to insert rows into a table or view

REFERENCES Permission to create indexes on a table, and to create foreign keys that reference a table

SELECT Permission to look at information in a table or view

UPDATE Permission to update rows in a table or view. This may be granted on a set of columns in a table only

ALL All the above permissions

Permission	Description
ALTER	Permission to alter the structure of a table or create a trigger on a table
DELETE	Permission to delete rows from a table or view
INSERT	Permission to insert rows into a table or view
REFERENCES	Permission to create indexes on a table, and to create foreign keys that reference a table
SELECT	Permission to look at information in a table or view
UPDATE	Permission to update rows in a table or view. This may be granted on a set of columns in a table only
ALL	All the above permissions

Convenient and secure access to data in each table is set by granting combinations of these permissions to different sets of users.

Procedures permissions overview

There is only one permission that may be granted on a procedure, and that is the EXECUTE permission to execute (or CALL) the procedure.

Group permissions overview

Setting permissions individually for each user of a database can be a time-consuming and error-prone process. For most databases, permission management based on groups, rather than on individual user IDs, is a much more efficient approach.

You can assign permissions to a group in exactly the same way as to an individual user. You can then assign membership in appropriate groups to each new user of the database, and they gain a set of permissions by virtue of their group membership.

Example

For example, you may create groups for different departments in a company database (sales, marketing, and so on) and assign these groups permissions. Each salesperson is made a member of the sales group, and automatically gains access to the appropriate areas of the database.

Any user ID can be a member of several groups, and inherits all permissions from each of the groups.