GRANT


To GRANT Privileges for This Action	Use This Syntax with GRANT
CREATE TABLE	CREATETAB
CREATE VIEW	CREATEVIEW
CREATE PROCEDURE	CREATESP
LOGIN AS GROUP MEMBER	LOGIN TO

CREATETAB, CREATEVIEW, and CREATESP must be explicitly granted. These permissions are not included as part of a GRANT ALL statement.

GRANT LOGIN TO

GRANT LOGIN TO creates a user and allows that user to access the secured database. You must specify a user name and password to create a user. Optionally, you may specify an existing group for the new user. If needed, use CREATE GROUP to create a new group before using GRANT LOGIN TO.

Constraints

The following constraints apply to permissions:

•

By Object Type

•

ALL Keyword

By Object Type

Table 24

Permissions Applicable To Object Type

1 Columns can be specified only for tables. Permissions for a view can be granted only to the entire view, not to single columns.

2 To drop a table, view, or stored procedure, a user must have ALTER permission on that object. Trusted views and stored procedures can be dropped only by the Master user.

3 EXECUTE applies only to stored procedures. A stored procedure can be executed with either a CALL or an EXECUTE statement. The procedure can be trusted or non-trusted. See Trusted and Non-Trusted Objects.

ALL Keyword

Table 25

Permissions Granted with ALL by Object Type

Permission Included by ALL

1To drop a table, view, or stored procedure, a user must have ALTER permission on that object. Trusted views and stored procedures can be dropped only by the Master user.

For example, if you issue GRANT ALL ON * to User1, then User1 has all permissions listed in the table.

If you issue GRANT ALL ON VIEW myview1 TO User2, then User2 has ALTER, DELETE, INSERT, UPDATE, and SELECT permissions on myview1.

Users and Groups

Relational security is based on the existence of a default user named Master who has full access to the database when security is turned on. When you turn security on, you will be required to specify a password for the Master user.

Security must be turned on to perform this statement.

The Master user can create groups and other users using the GRANT LOGIN TO, CREATE USER or CREATE GROUP commands and define data access permissions for these groups and users.

If you want to grant the same permissions to all users, you can grant them to the PUBLIC group. All users inherit the default permissions assigned to the PUBLIC group.

Note: If you wish to use groups, you must set up the groups before creating users.

User name and password must be enclosed in double quotes if they contain spaces or other nonalphanumeric characters.

For further general information about users and groups, see Master User and Users and Groups in Advanced Operations Guide.

Owner Names

An owner name is a string of bytes that unlock access to a Btrieve file. Owner names have no connection with any system user name or relational database user name. You should think of an owner name as a file password. Owner names are case-sensitive.

If you have a Btrieve owner name set on a file that is a table in a secure SQL database, the Master user of the ODBC database must use the owner name in any GRANT statement to grant privileges on the given table to any user, including the Master user.

After the GRANT statement containing the owner name has been issued for a given user, that user can access the specified table by logging into the database, without specifying the owner name each time. Also note that the SET OWNER statement allows you to specify one or more owner names for use during the current database connection. See SET OWNER.

If a user tries to access a table through an SQL access method that has a Btrieve owner name, the access will not be allowed unless the Master user has granted privileges on the table to the user, with the correct owner name in the GRANT statement.

If a table has an owner name with the Read-Only attribute, the Master user has SELECT rights on this table without specifically granting other SELECT rights for that owner name.

Permissions on Views and Stored Procedures

Views and stored procedures can be trusted or non-trusted, depending on how you want to handle the permissions for the objects referenced by the view or stored procedure.

Trusted and Non-Trusted Objects

Views and stored procedures reference objects, such as tables, other views or other stored procedures. Granting permissions on every referenced object could become highly time consuming depending on the number of objects and users. A simpler approach for many situations is the concept of a trusted view or stored procedure.

A trusted view or stored procedure is one that can be executed without having to explicitly set permissions for each referenced object. For example, if trusted view myview1 references tables t1 and t2, the Master user can grant a user permissions on myview1 without having to grant the user permissions on t1 and t2.

A non-trusted view or stored procedure is one that cannot be executed without having to explicitly set permissions for each referenced object.

The following table compares characteristics between trusted and non-trusted objects.

Table 26

Characteristics of Trusted and Non-trusted Views and Stored Procedures

Object

Characteristic

Notes

Trusted view or trusted stored procedure

Requires V2 metadata

See PSQL Metadata.

Requires WITH EXECUTE AS ‘MASTER’ clause in CREATE statement

See CREATE VIEW and CREATE PROCEDURE.

Only Master user can create the object

See Master User in Advanced Operations Guide.

Only Master user can delete the object

See DROP VIEW and DROP PROCEDURE

Master user must grant object permissions to other users

By default, only the Master user can access trusted views or stored procedures and must grant users permissions to them.

GRANT and REVOKE statements applicable to object

See also REVOKE.

Object can exist in a secured or in an un-secured database

See Security Models and Concepts in Advanced Operations Guide.

Changing a trusted object to a non-trusted one (or vice versa) requires deletion then recreation of object8

The ALTER statement for a view or stored procedure cannot be used to add or remove the trusted characteristic of the object. If you need to change a trusted object to a non-trusted one, you must first delete the object then recreate it without the WITH EXECUTE AS ‘MASTER’ clause. Similarly, if you need to change a non-trusted object to a trusted one, you must first delete the object then recreate it with the WITH EXECUTE AS ‘MASTER’ clause.

Non-trusted view or non-trusted stored procedures

Any user can create the object

User must be granted CREATEVIEW or CREATESP permission. See Remarks.

Any user can delete the object

User must be granted ALTER permission on the view or stored procedure. See GRANT.

ALTER permission required to delete the object

ALTER permission is also required to delete a table. Note that, by default, only the Master user can delete trusted objects. Users (other than Master) who did not create the view or stored procedure must be granted ALTER permissions to delete the view or stored procedure.

All users, by default, have all permissions to the object

For V2 metadata, if an unsecured database contains non-trusted objects, all permissions for the non-trusted objects are automatically granted to PUBLIC if security is enabled on the database.

User executing the view or stored procedure needs permissions on the objects referenced by the view or stored procedure

The user must also have permissions on the top-most object. That is, on the view or stored procedure that references the other objects.

GRANT and REVOKE statements applicable to object

See GRANT and REVOKE.

Object can exist in a secured or in an unsecured database

See Security Models and Concepts in Advanced Operations Guide.

Changing a trusted object to a non-trusted one (or vice versa) requires deletion then recreation of object

Same as above for trusted view or trusted stored procedure.

Examples

This section provides a number of examples of GRANT.

A GRANT ALL statement grants the INSERT, UPDATE, ALTER, SELECT, DELETE and REFERENCES rights to the specified user or group. In addition, the user or group is granted the CREATE TABLE right for the dictionary. The following statement grants all these privileges to user dannyd for table Class.

GRANT ALL on Class to dannyd

============

The following statement grants the ALTER privilege to user debieq for table Class.

GRANT ALTER on Class TO debieq

============

The following statement gives INSERT privileges to keithv and miked for table Class. The table has an owner name of winsvr644AdminGrp.

GRANT INSERT ON Class winsvr644AdminGrp TO keithv, miked

============

The following statement gives INSERT privileges to keithv and miked for table Class.

GRANT INSERT ON Class TO keithv, miked

============

The following statement grants INSERT privileges on two columns, First_name and Last_name, in the Person table to users keithv and brendanb

GRANT INSERT(First_name,last_name) ON Person to keithv,brendanb

============

The following statement grants CREATE TABLE rights to users aideenw and punitas

GRANT CREATETAB TO aideenw, punitas

============

The following GRANT LOGIN TO statement grants login rights to a user named ravi and specifies his password as password.

GRANT LOGIN TO ravi:password

Note: If the a user account that is granted login rights using the GRANT LOGIN TO statement does not currently exist, then it is created.

If GRANT LOGIN is used in a stored procedure, you must separate the user name and password with a space character and not with the colon character. The colon character is used to identify local variables in a stored procedure.

The user name and password here only refer to PSQL databases and are not related to user names and passwords set at the operating system level. PSQL user names, groups, and passwords can also be set through the PSQL Control Center (PCC).

The following example grants login rights to users named dannyd and travisk and specifies their passwords as password and 1234567 respectively.

GRANT LOGIN TO dannyd:password,travisk:1234567

If there are spaces in a name you may use double quotes as in the following example. This statement grants login rights to user named Jerry Gentry and Punita and specifies their password as sun and moon respectively

GRANT LOGIN TO "Jerry Gentry":sun, Punita:moon

The following example grants the login rights to a user named Jerry Gentry with password 123456 and a user named travisk with password abcdef. It also adds them to the group psql_dev

GRANT LOGIN TO "Jerry Gentry":123456, travisk:abcdef in group psql_dev

============

To grant privileges on a table that has a Btrieve owner name, the Master user has to supply the correct owner name in the GRANT statement.

The following example grants the SELECT rights to the Master user on table t1 that has a Btrieve owner name of abcd.

GRANT SELECT ON t1 'abcd' TO Master

The Master user has all rights on a table that does not have an owner name. You can set an owner name on a table with the Maintenance utility. The Btrieve owner name is case sensitive.

============