GRANT

---

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

Syntax

GRANT CREATETAB TO public-or-user-or-group-name [ , user-or-group-name ]... 
 
GRANT LOGIN TO user-password [ , user-password ]... [ IN GROUP group-name ] 
 
GRANT table-privilege ON [ TABLE ] < * | table-name [ owner-name ]> 
TO user-or-group-name [ , user-or-group-name ]... 
 
table-privilege ::=	   ALL 
|  SELECT [ ( column-name [ , column-name ]... ) ] 
|  UPDATE [ ( column-name [ , column-name ]... ) ] 
|  INSERT [ ( column-name [ , column-name ]... ) ] 
|  DELETE 
|  ALTER 
|  REFERENCES 

 
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-name 
 
owner-name ::= user-defined-name 

Remarks

CREATETAB and LOGIN arguments are extensions to the core SQL grammar.

Column-level rights are only valid for the SELECT, UPDATE, and INSERT privileges. If you attempt to set column level rights for the DELETE, ALTER or REFERENCES privileges, you will receive status code 8098.

---

Note
ANSI SQL 3 permits column lists for INSERT, ALTER, REFERENCES, SELECT and UPDATE.

---

Database-wide Permissions

User permissions can be assigned at the database level, by using the asterisk instead of a table name in a GRANT statement. This feature allows user privileges to be assigned to all tables in a given database.

When you grant database-wide privileges using *, the privileges apply to all tables in a given database except the system tables. Since privileges are additive, these database-level privileges take precedence over any table-level rights

These rights also apply to any tables that are created later by any user having the CREATE TABLE right. If the user creates a table, then full access rights to the table are granted to that user separate from the database rights. If later you revoke database privileges from that user, the user still has rights to the tables they created. The table rights must be revoked separately.

Only the Master user can perform GRANT commands. You cannot use the database-wide (*) syntax when specifying an owner name.

For more information on the default database and how to secure Btrieve files, see the following sections in Advanced Operations Guide:

• Chapter 1, Pervasive.SQL Databases
• Chapter 7, Pervasive.SQL Security

Users and Groups

Security is based on the existence of a default user named "Master" who has full access to the database when security is first turned on.

---

Caution
If you turn on security, be sure to specify a password with a significant length, at least five characters.

---

The Master user can create groups and other users and define sets of data access privileges for these groups and users.

If you want to grant the same level of access to all users and avoid having to set up individual groups and users, you can grant the desired level of access to PUBLIC. The default group PUBLIC is created when you secure a database and represents any user connecting with or without a password. Any right granted to PUBLIC is granted also to all valid users. No user can have access rights lower than that of PUBLIC.

---

Note
If you wish to use groups, you must set up the groups before creating users. You cannot add a user to a group after you have already created the user.

---

You can use the Users node in PCC to perform these tasks. You can also use GRANT and REVOKE statements to perform these tasks.

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

See Pervasive.SQL User's Guide and Chapter 7, Pervasive.SQL Security in Advanced Operations Guide for further information about users and groups.

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.

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 dannyd for table Class.

GRANT ALL on Class to dannyd 

This statement grants the ALTER privilege to user debieq.

GRANT ALTER on Class TO debieq 

The following statement gives INSERT privileges to keithv and miked on 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 example grants CREATE TABLE rights to users aideenw and punitas

GRANT  CREATETAB TO aideenw, punitas 

---

This next statement grants login rights to a user named ravi and specifies his password as "password." The semi-colon separating the username and password is required if the password does not start with a letter.

GRANT LOGIN TO ravi:password 

The user name and password refer to Pervasive.SQL databases and are not related to user names and passwords set at level of the operating system. Pervasive.SQL user names, groups, and passwords are set through the Pervasive 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)  

See Also

REVOKE

SET SECURITY

CREATE GROUP

DROP GROUP