Developer Reference : Data Access Methods : SQL Engine Reference : SQL Syntax Reference : GRANT
 
GRANT
In a secured database, use the GRANT statement to manage access permissions for tables, views, and stored procedures. GRANT can give users rights to these permissions, can create new users, and can assign the users to existing user groups. If needed, use CREATE GROUP to create a new group before using GRANT.
The following topics cover use of GRANT statements:
GRANT LOGIN TO
Constraints on Permissions
GRANT and Data Security
Syntax
GRANT CREATETAB | CREATEVIEW | CREATESP TO public-or-user-or-group-name [ , user-or-group-name ]...
 
GRANT LOGIN TO user_and_password [ , user_and_password ]... [ IN GROUP group-name ]
 
GRANT permission ON < * | [ TABLE ] table-name [ owner-name ] | VIEW view-name | PROCEDURE stored_procedure-name >
TO user-or-group-name [ , user-or-group-name ]...
 
* ::= all of the objects (that is, all tables, views, and stored procedures)
 
permission ::= ALL
| ALTER
| DELETE
| INSERT [ ( table-column-name [ , table-column-name ]... ) ]
| REFERENCES
| SELECT [ ( table-column-name [ , table-column-name ]... ) ]
| UPDATE [ ( table-column-name [ , table-column-name ]... ) ]
| EXECUTE
 
table-name ::= user-defined table-name
 
owner-name ::= user-defined owner name
 
view-name ::= user-defined view-name
 
stored-procedure-name ::= user-defined stored_procedure-name
 
user_and_password ::= user-name [ : ] password
 
public-or-user-or-group-name ::= PUBLIC | user-or-group-name
 
user-or-group-name ::= user-name | group-name
 
user-name ::= user-defined user name
 
table-column-name ::= user-defined column name (tables only)
Remarks
CREATETAB, CREATESP, CREATEVIEW, and LOGIN TO keywords are extensions to the SQL grammar. You can use the GRANT statement to grant privileges for CREATE TABLE, CREATE VIEW, and CREATE PROCEDURE. The following table lists the syntax for a given action.
To GRANT Privileges for This Action
Use This Keyword 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 privileges 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 on Permissions
The following constraints apply to permissions on objects:
By Object Type
ALL Keyword
By Object Type
Table 24 Permissions Applicable To Object Type  
Permission
Table1
View1
Stored Procedure
CREATETAB
* 
 
 
CREATEVIEW
 
* 
 
CREATESP
 
 
* 
ALTER2
* 
* 
* 
DELETE
* 
* 
 
INSERT
* 
* 
 
REFERENCES
* 
 
 
SELECT
* 
* 
 
UPDATE
* 
* 
 
EXECUTE3
 
 
* 
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
Table
View
Stored Procedure
ALTER1
* 
* 
* 
DELETE
* 
* 
 
INSERT
* 
* 
 
REFERENCES
* 
 
 
SELECT
* 
* 
 
UPDATE
* 
* 
 
EXECUTE
 
 
* 
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.
GRANT and Data Security
The following topics provide cover particular uses of GRANT to manage data security:
Granting Privileges to Users and Groups
Granting Access Using Owner Names
Granting Privileges to 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 manage data access for these groups and users.
If you want to grant the same privileges to all users, you can grant them to the PUBLIC group. All users inherit the default privileges 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, and Assigning Permissions Tasks in Zen User’s Guide.
Granting Access Using Owner Names
An owner name is a string of bytes that unlocks access to a Btrieve file. Btrieve owner names have no connection with any operating system or database user name but rather serve as a file access password. For more information, see Owner Names.
If a Btrieve file that serves as a table in a secure SQL database has an owner name, the database Master user must provide that owner name in a GRANT statement to authorize access to the table, including for the Master user itself.
After the Master user has executed a GRANT statement for a user, that user can access the table, without having to give the owner name, simply by logging into the database. This authorization lasts for the duration of the current database connection. Also note that the SET OWNER statement allows you to specify one or more owner names for the connection session. See SET OWNER.
If a user tries to run SQL commands on a table that has an owner name, access is refused unless the Master user has granted rights to the table for that user by using the owner name in a GRANT statement.
If a table has an owner name with the read-only setting chosen, all users have SELECT rights on the table.
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 permissions for myview1 without having to grant them for 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
Requires WITH EXECUTE AS ‘MASTER’ clause in CREATE statement
Only Master user can create the object
See Master User in Advanced Operations Guide.
Only Master user can delete the object
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 permissions to them.
GRANT and REVOKE statements applicable to object
See also REVOKE.
Object can exist in a secured or in an unsecured database
See Zen Security in Advanced Operations Guide.
Changing a trusted object to a non-trusted one (or vice versa) requires deletion then recreation of object
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 privilege. 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 for 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 for 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 Zen Security 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 privileges 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 of these permissions to user dannyd for table Class.
GRANT ALL on Class to dannyd
============ 
The following statement grants ALTER permission to user debieq for table Class.
GRANT ALTER on Class TO debieq
============ 
The following statement gives INSERT permission 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 permission to keithv and miked for table Class.
GRANT INSERT ON Class TO keithv, miked
============ 
The following statement grants INSERT permission 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 refer only to Zen databases and are not related to user names and passwords used for operating system or network authentication. Zen user names, groups, and passwords can also be set through Zen Control Center (ZenCC).
The following example grants login rights to users named dannyd and rgarcia and specifies their passwords as password and 1234567 respectively.
GRANT LOGIN TO dannyd:password,rgarcia: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 rgarcia with password abcdef. It also adds them to the group zen_dev
GRANT LOGIN TO "Jerry Gentry":123456, rgarcia:abcdef in group zen_dev
============ 
The Master user has all rights on a table that does not have an owner name. To grant permissions on a table that has a Btrieve owner name, the Master user must supply the correct owner name in the GRANT statement.
The following example grants the SELECT right to the user Master on table t1 that has a Btrieve owner name of abcd.
GRANT SELECT ON t1 'abcd' TO Master
You can set an owner name on a table using Function Executor or the Maintenance utility under the Tools menu in ZenCC. For more information, see Owner Names in Advanced Operations Guide.
============ 
After the Master user performs the following set of SQL statements, the user jsmith has SELECT access to all tables in the current database. The user also has DELETE access to tab1 and UPDATE access to tab2.
GRANT DELETE ON tab1 TO jsmith
GRANT SELECT ON * TO jsmith
GRANT UPDATE ON tab2 TO jsmith
If the following statement is performed later by any user with CREATE TABLE privileges, the user jsmith will have SELECT access to the newly created table.
CREATE TABLE tab3 (col1 INT)
============ 
GRANT CREATETAB TO user1
============ 
GRANT CREATESP TO user1
============ 
The following example grants EXECUTE permissions on stored procedure cal_rtrn_rate to all users.
GRANT EXECUTE ON PROCEDURE cal_rtrn_rate TO PUBLIC
============ 
The following example shows how members of the group Accounting can update only the salary column in the employee table (employee is part of the Demodata sample database).
Assume that the following stored procedure exists:
CREATE PROCEDURE employee_proc_upd(in :EmpID integer, in :Salary money) WITH EXECUTE AS 'Master';
BEGIN
UPDATE employee SET Salary = :Salary WHERE EmployeeID = :Empid;
END
GRANT EXECUTE ON PROCEDURE employee_proc_upd TO Accounting
Note that users belonging to group Accounting cannot update other columns in the Employee table because permissions were granted only for the stored procedure and the stored procedure updates only the salary column.
============ 
The following example assumes that you have enabled security on the Demodata sample database and added a user named USAcctsMgr. You now want to grant SELECT rights to the ID column in table Person to that user. Use the following statement.
GRANT SELECT ( ID ) ON Person TO 'USAcctsMgr'
See Also