When the SQL standard authorization mode is enabled, object owners
can use the GRANT and REVOKE SQL statements to set the user permissions for
specific database objects or for specific SQL actions.
The SQL standard authorization mode is a SQL2003 compatible access
control system. You enable the SQL standard authorization mode by setting the
gemfirexd.sql-authorization property to
TRUE.
While GemFire XD has a simpler database access mode which can be set to
provide users with
full,
read-only, or
no access authorization, this simpler access mode is
less appropriate for most client-server database configurations. When users or
applications issue SQL statements directly against the database, the SQL
authorization mode provides a more precise mechanism to limit the actions that
users can take on the database.
GRANT and REVOKE Privileges
The GRANT statement is used to grant specific permissions to users.
The REVOKE statement is used to revoke permissions. The grant and revoke
privileges are:
- DELETE
- EXECUTE
- INSERT
- SELECT
- REFERENCES
- TRIGGER
- UPDATE
When a table, view, function, or procedure is created, the person that creates the object is
referred to as the owner of the object. Only the object owner and the
JVM
owner have full privileges on the object. No other users have privileges
on the object until the object owner grants privileges to them.
Set Public and Individual User Privileges
The object owner can grant and revoke privileges for specific users or
for all users. The keyword PUBLIC is used to specify all users. When PUBLIC is
specified, the privileges affect all current and future users. The privileges
granted and revoked to PUBLIC and to individual users are independent. For
example, a SELECT privilege on table
t is granted to both PUBLIC and to the user
harry. The SELECT privilege is later revoked from user
harry, but user
harry has access to table
t through the PUBLIC privilege.
Note: When you create a view, trigger,
or constraint, GemFire XD first checks to determine if you have the required
privileges at the user-level. If you have the user-level privileges, the object
is created and is dependent on that user-level privilege. If you do not have
the required privileges at the user-level, GemFire XD checks to determine if you
have the required privileges at the PUBLIC level. If you have the PUBLIC level
privileges, the object is created and is dependent on that PUBLIC level
privilege. After the object is created, if the privilege on which the object
depends on is revoked, the object is automatically dropped. GemFire XD does not
try to determine if you have other privileges that can replace the privileges
that are being revoked.
- Example 1
- User
zhi creates table
t1 and grants SELECT privileges to user
harry on table
t1. User
zhi grants SELECT privileges to PUBLIC on table
t1. User
harry creates view
v1 with the statement SELECT * from
zhi.t1. The view depends on the user-level
privilege that user
harry has on
t1. Subsequently, user
zhi revokes SELECT privileges from user
harry on table
t1. As a result, the view
harry.v1 is dropped.
- Example 2
- User
anita creates table
t1 and grants SELECT privileges to PUBLIC. User
harry creates view
v1 with the statement SELECT * from
anita.t1. The view depends on the PUBLIC level
privilege that user
harry has on
t1 since user
harry does not have user-level privileges on
table
t1 when he creates the view
harry.v1. Subsequently, user
anita grants SELECT privileges to user
harry on table
anita.t1. The view
harry.v1 continues to depend on PUBLIC level
privilege that user
harry has on
t1. When user
anita revokes SELECT privileges from PUBLIC on
table
t1, the view
harry.v1 is dropped.
Set Permissions on Views, Triggers, and Constraints
Views, triggers, and constraints operate with the permissions of the
owner of the view, trigger, or constraint. For example, user
anita wants to create a view using the following
statement:
CREATE VIEW s.v(vc1,vc2,vc3)
AS SELECT t1.c1,t1.c2,f(t1.c3)
FROM t1 JOIN t2 ON t1.c1 = t2.c1
WHERE t2.c2 = 5
User
anita needs the following permissions to create the
view:
- Ownership of the schema
s, so that she can create something in the schema
- Ownership of the table
t1, so that she can allow others to see columns in
the table
- SELECT permission on
column
t2.c1 and column
t2.c2
- EXECUTE permission on
function
f
When the view is created, only user
anita has SELECT permission on it. User
anita can grant SELECT permission on any or all of the
columns of view
s.v to anyone, even to users that do not have SELECT
permission on
t1 or
t2, or EXECUTE permission on
f. User
anita grants SELECT permission on view
s.v to user
harry. When user
harry issues a SELECT statement on the view
s.v, GemFire XD checks to determine if user
harry has SELECT permission on
view
s.v. GemFire XD does not check to determine if user
harry has SELECT permission on
t1, or
t2, or EXECUTE permission on
f.
Permissions on triggers and constraints work the same way as
permissions on views. When a view, trigger, or constraint is created, GemFire XD
checks that the owner has the required permissions. Other users do not need to
have those permissions to perform actions on a view, trigger, or constraint.
If the required permissions are revoked from the owner of a view,
trigger, or constraint, the object is dropped as part of the REVOKE statement.
See also
GRANT
and
REVOKE.