Security & Ownership

Standard SQL "grant" and "revoke" statements are implemented in Sybase. For the rest of this section, "grants" will refer to both grants and revokes.

Grants are database specific. They can only affect database users and objects within a database. Remember that your database login (server user id or suid) is different from your database username (uid).

Sybase also uses grants to control access to certain database level commands such as create table, create procedure, drop table, etc. We will not concern ourselves with these types of grants.

Groups in Sybase

Groups are a means by which a set of users in a database can be collectively referred to.

Groups are used with grants to simplify administration.

A database user can be a member of only one group at a time.

Sybase grants can be issued against groups instead of individual users. For example:

use mydb sp_addgroup mygroup grant select on mytable to mygroup

As users come and go, they merely need to be added or removed from the proper groups in the database. The actual grant commands don't need to be re-run.

The default group is "public". All users in a database are always implicit members of the "public" group. This is true even if the database users has been explicitly made a member of another group.

Ownership of Database Objects

Database tables, stored procedures, etc. are owned by users in a database.

Remember that the syntax for referring to a table, view, or stored procedure is:

database.owner.object

Note that this syntax permits two different database users create different objects with the same object name. For example:

/* database user joe */ create table mytable( f1 int) select * from mydb.joe.mytable /* database user bob */ create table mytable(f1 int) select * from mydb.bob.mytable

There is a special user in each database called the database owner or "dbo". The dbo has all privileges within his own database.

The database owner (dbo) can also own objects. In most production environments, the dbo owns the tables and stored procedures in a database.

Defaults for Referencing Objects

When referencing an object via database.owner.object_name, the following defaults apply:

Since most production environments have objects owned by dbo, most regular database users will default to accessing dbo owned objects.

Grants with Views and Stored Procedures

There are different ways for a database user to be granted access to a database table. For purposes of this discussion, we will assume that the database objects are owned by the dbo.

The dbo can just grant access to the user (or the user's group).

The dbo can create a view on the table and let the user only access the view (this is standard SQL).

The dbo can create a stored procedure which accesses the table. The dbo can then grant execute access on the stored procedure to the database user. The user will be allowed to run the stored procedure and get any results it sends back, but the user will not be allowed to access the table directly.

For example:

/* the dbo would do the following */ create table orders ... create proc orders_stored_proc as ... grant execute to orders_stored_proc to ourgroup /* the database user would be able to do */ exec orders_stored_proc

This sort of stored procedure shell is useful when it is necessary to transform the user input or output in ways not permitted by an SQL view.

(to be added) figure - grants with views and stored procedures

Next chapter.