GRANT

The GRANT statement creates new user IDs and gives permissions to specific users in a secured database.

Syntax

GRANT CREATETAB | CREATEVIEW | CREATESP TO public-or-user-or-
group-name [ , user-or-group-name ]... 
GRANT LOGIN TO user-password [ , user-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 [ ( table-column-name [ , table-column-name ]... ) ] 
| DELETE [ ( table-column-name [ , table-column-name ]... ) ] 
| INSERT [ ( table-column-name [ , table-column-name ]... ) ] 
| REFERENCES [ (table-column-name [ , table-column-name ]...)] 
| 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-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 arguments are extensions to the core SQL grammar. You can use the GRANT statement to grant privileges for CREATE TABLE, CREATE VIEW, and CREATE PROCEDURE, and to create a user account with password as a member of an already created group.

The following table shows the syntax for a given action:

Note that 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 user password to create a user. Optionally, you may specify the group to which you want the user to belong. The group must already exist. You cannot create a group with the GRANT LOGIN statement.

Constraints

The following constraints apply to permissions:

By Object Type

ALL Key Word

For example, if you were to issue GRANT ALL ON * to User1, User1 would have all permissions listed in the table above.

If you were to issue GRANT ALL ON VIEW myview1 TO User2, User2 would have 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 non-alphanumeric characters.

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

Owner Name

An owner name is a password required to gain access to a Btrieve file. There is no relation between an owner name and any system user name or database user name. You should think of an owner name as a simple file password.

If you have a Btrieve owner name set on a file that is a table in a secure ODBC 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.

If a user tries to access a table through ODBC 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 automatically has SELECT rights on this table without specifically granting himself/herself the SELECT rights with the 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.

See the following table for a comparison of the characteristics between trusted and non-trusted objects.

Examples

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.

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 specifying 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. 

The user name and password here only refer to Pervasive PSQL databases and are not related to user names and passwords set at the operating system level. Pervasive PSQL user names, groups, and passwords can also be set through the Pervasive 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 pervasive_dev

GRANT LOGIN TO "Jerry Gentry":123456, travisk:abcdef in 
group pervasive_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.

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.

See Also

CREATE GROUP

CREATE PROCEDURE

CREATE VIEW

DROP GROUP

REVOKE

SET SECURITY

System Stored Procedures