ALLBASE/SQL Reference Manual
> Chapter 2 Using ALLBASE/SQLControlling Database Access |
||||||||||||||||||||||||
|
AuthoritiesALLBASE/SQL has the following several kinds of authorities:
Obtaining AuthorizationYou obtain authority by the following methods:
DBA AuthorityWhen 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. GrantsAll 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@DBMSThe 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 PUBLICThe REVOKE statement is used to eliminate authorities: REVOKE RESOURCE FROM WOLFGANG@DBMSDBAs 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 PrivilegesIf 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 grantee is a group. The following statement grants UPDATE authority to Amanda, who can then grant that authority to individual users or a class: GRANT UPDATE ON Marketing.Forecast TO AMANDA@DBMS WITH GRANT OPTION;Users with a grantable privilege can only revoke privileges they have granted and chains they have caused. To revoke the privilege given to the grantee and any subsequent grantees in a chain, the grantor must use the CASCADE option of the REVOKE statement. Owners can revoke any privilege on their object, but to revoke a privilege that has been given to subsequent grantees, the CASCADE option must be used. The DBA does not have to use the CASCADE option to revoke a grantable privilege from a user. However, if CASCADE is not used, that privilege is removed from the specified grantee only, not from the subsequent chain of grants. Then, an orphaned privilege is created. An orphaned privilege can be given a parent by the DBA with the BY clause of the GRANT statement. For more information on orphaned privileges, refer to "Using the WITH GRANT OPTION Clause" in the chapter "Database Creation and Security" in the ALLBASE/SQL Database Administration Guide. OwnershipThe following six objects have owners associated with them:
SELECT PartNumber FROM PurchDB.PartsThe system views belong to special owners named SYSTEM and CATALOG. Therefore when you refer to one of the system views, you must specify that name: SELECT * FROM System.Table or SELECT * FROM Catalog.Table Default Owner RulesIn several statements, when a name is specified, such as table name, rule name, group name, or index name, specification of the owner name is optional. The method of determining the default owner when no owner is specified is as follows:
Ownership PrivilegesThe following summarizes the privileges that extend to users or members of groups that own objects:
Authorization GroupsAn authorization group is a named collection of users or other groups. The CREATE GROUP statement is used to define groups, and the ADD TO GROUP statement is used to associate individuals or other groups with the group. The GRANT statement assigns authorities to a group. All three statements are used in the following example: CREATE GROUP PurchManagers ADD MARGUERITE@RYAN, RON@HART, SHARON@MULDOON TO GROUP PurchManagers GRANT SELECT on PurchDB.Parts TO PurchManagersAny member of the group PurchManagers can select data from table PurchDB.Parts. Authorization groups have several advantages as described here:
ClassesA class is a special category of owner that is neither a conventional DBEUserID nor a group. You may wish to assign ownership of objects to a class when you do not want any individual or group to have automatic access to them. With class ownership, the DBA controls all authorities, because objects that belong to a class can be created and maintained only by the DBA. For a class to be useful, its class name must be different from the name of any existing DBEUserID or group name. A DBA can create a class by doing one of the following:
CREATE TABLE PurchDB.Parts (PartNumber CHAR(16) NOT NULL, PartName CHAR(30), SalesPrice DECIMAL(10,2)) IN WarehFS;After creating objects owned by the class, you must grant the specific authorities you wish users or groups to have. Suppose you have a group PurStaff consisting of DBEUserIDs for members of the Purchasing department. You could grant authorities to the group as follows: GRANT SELECT, UPDATE ON PurchDB.Parts to PurStaff; Differences between Groups and ClassesYou create a group explicitly by using the CREATE GROUP statement. You create a class implicitly by creating objects that use the class name as the owner name. A group has members, all of which have the privileges the group has. For example, if a user is a member of the group Sales, then that user can drop or alter objects owned by Sales. A class does not have members, nor can it use any authorities, although you can grant them if you wish. This can be useful in a scenario in which you want to preassign ownership of objects to a DBEUserID which has no logon ID on your system.
|