ALLBASE/SQL Reference Manual (36216-90216)
76 Chapter2
Using ALLBASE/SQL
Controlling Database Access
DBA Authority
When a DBEnvironment is configured, DBA authority is automatically given to the login
name of the DBECreator.
A user with DBA authority (also referred to as the DBA) has extensive control over data in
a DBEnvironment. The DBA can issue almost all the SQL statements and execute all the
programs that access the DBEnvironment. The two SQL statements that
only
a
DBECreator can issue are, START DBE NEWLOG and START DBE RECOVER. Some SQL
statements
only a DBA
can issue. Most of these statements are DBEnvironment-wide in
scope. For example, only DBAs can grant the special authorities (CONNECT, RESOURCE, and
DBA) and define DBEFiles and DBEFileSets. In addition, only a DBA can issue statements
that control objects owned by a class name; for example, only DBAs can drop or issue
grants for a table owned by a class name.
Grants
All authorities except OWNER authority can be
granted
by using the GRANT statement. The
GRANT statement gives authorities to individual users, to authorization groups, or to all
users.
The following grants authorize a user with a logOn name of WOLFGANG@DBMS to start
a DBE session and to retrieve rows from the table named Quotas. Wolfgang can also create
his own database because he is also granted RESOURCE authority.
GRANT CONNECT TO WOLFGANG@DBMS
GRANT SELECT ON Marketing.Quotas TO WOLFGANG@DBMS
GRANT RESOURCE TO WOLFGANGg@DBMS
The following grants authorize the group named Managers to start a DBE session and all
users to retrieve rows from the table Forecast:
GRANT CONNECT TO Managers
GRANT SELECT ON Marketing.Forecast TO PUBLIC
The REVOKE statement is used to eliminate authorities:
REVOKE RESOURCE FROM WOLFGANG@DBMS
DBAs can grant or revoke authorities. The only individuals entitled to grant and revoke
authorities are users or members of groups that
own
tables, views, or modules, or those
who have received grantable privileges, as described below. Individuals or members of
groups that own tables, views, or modules can issue grants for objects they own.
Grantable Privileges
If a grantor specifies the WITH GRANT OPTION clause when issuing the GRANT statement on
table and view authorities, the grantee receives not only the privilege, but the authority to
grant that same privilege, with or without the WITH GRANT OPTION, to another user. The
grantee is also entitled to revoke authorities he or she granted. This kind of privilege is
called a grantable privilege. The use of grantable privileges can result in chains of grants.
A cycle in a chain of grants is not allowed; that is, a user cannot be granted the same
authority more than once on an object. If a grant of authority causes a cycle, you will
receive an error message. The WITH GRANT OPTION clause cannot be specified when the