4D Doc Center : GRANT

Home

4D v14

GRANT



	GRANT[READ \| READ_WRITE \| ALL] ON sql_name TO sql_name

Description

The GRANT command can be used to set the access rights associated with the sql_name schema (first parameter). These rights will be assigned to the group of 4D users designated by the second sql_name parameter.

The READ, READ_WRITE and ALL keywords can be used to set the access rights allowed for the table:

READ establishes Read-only access (data). By default: <Everybody>
READ_WRITE establishes Read/Write access (data). By default: <Everybody>
ALL establishes full access mode (data and structure). By default: <Nobody>

Note that each type of access is set separately from the others. More specifically, if you assign only the READ access rights to one group, this will not have any effect since the group as well as all the others will continue to benefit from READ_WRITE access (assigned to all groups by default). To set READ access, you must call the GRANT command twice (see example 2).

Access control only applies to external connections. The SQL code executed within 4D via the Begin SQL/End SQL tags or commands such as SQL EXECUTE still have full access.

Compatibility Note: During the conversion of an older database to version 11.3 or higher, the global access rights (as set on the SQL page of the application Preferences) are transferred to the default schema.

The second sql_name parameter must contain the name of a group of 4D users to which you want to assign access rights to the schema. This group must exist in the 4D database.

Note: 4D allows group names to include spaces and/or accented characters that are not accepted by standard SQL. In this case, you must put the name between the [ and ] characters. For example: GRANT READ ON [my schema] TO [the admins!]

Only the database Designer and Administrator can modify schemas.

Note regarding referential integrity

4D ensures the principle of referential integrity independently from access rights. For example, suppose that you have two tables, Table1 and Table2, connected by a Many-to-One type relation (Table2 -> Table1). Table1 belongs to schema S1 and Table2 to schema S2. A user that has access rights to schema S1 but not to S2 can delete records in Table1. In this case, in order to respect the principles of referential integrity, all the records of Table2 that are related to records deleted from Table1 will also be deleted.

Example 1

You want to allow read/write access to data of the MySchema1 schema to the "Power_Users" group:

GRANT READ_WRITE ON MySchema1 TO POWER_USERS

Example 2

You want to allow read-only access to the "Readers" group. This case requires assigning at least one group with READ_WRITE access rights (here it is "Admins") so that it is no longer assigned to all groups by default:

GRANT READ ON MySchema2 TO Readers /*Assignment of read-only access */
GRANT READ_WRITE ON MySchema2 TO Admins /*Stop read-write access to all*/