xfODBC supports the following SQL statements, which manage privilege-based users and the privileges associated with them. For more information, see Managing privilege-based users and privileges.
- GRANT (database privileges)
- GRANT (object privileges)
- REVOKE (database privileges)
- REVOKE (object privileges)
- SET PASSWORD
|
GRANT, REVOKE, and SET PASSWORD are available only if the system catalog supports privilege-based users — i.e., only if the system catalog was generated using dbcreate with the -c and -a options and has the GENESIS_AUTHS and GENESIS_USERS tables and files. See Managing access with users, groups, and table settings for more information on privilege-based users. |
For links to more information on xfODBC's support for SQL, see Appendix B: SQL Support.
GRANT (database privileges)
The following form of the GRANT statement enables DBA users (users with the DBA database privilege) to add privilege-based users and to grant database privileges to privilege-based users.
GRANT privilege[,...] TO user [IDENTIFIED BY password]
where
privilege is a database privilege (CONNECT, RESOURCE, or DBA).
user is the user that the privileges will be granted to. A new privilege-based user is added if the "IDENTIFIED BY password" clause is used (and user does not already exist). The "IDENTIFIED BY password" clause is required to create a new user.
password is the password for a new user.
A GRANT statement can grant one or more of the following database privileges:
- CONNECT enables user to connect to the database and access tables with PUBLIC as the owner. A user with this privilege can also be granted object permissions to tables and views owned by other users. The CONNECT privilege does not enable a user to create a table or view.
- RESOURCE enables user to create tables and views and to be granted object privileges to tables and views owned by other users.
- DBA implicitly includes both CONNECT and RESOURCE privileges and enables user to read and modify all tables and views in the database.
User name (user) is not case sensitive when logging in or when used in a GRANT, REVOKE, or SET PASSWORD statement. If user is not in quotation marks when a user is created, the user name will be saved to the system catalog in all uppercase characters (but it can still be used in any combination of uppercase and lowercase characters to log in or with GRANT, REVOKE, or SET PASSWORD).
Password is case sensitive. If password is enclosed in quotation marks, case is preserved. If a password is not enclosed in quotation marks, it will be stored in all uppercase characters. Passwords enclosed in quotation marks can contain some special characters (e.g., "N#e-Wpas%"). For more information on passwords, see SET PASSWORD below.
|
Do not use colons (:) in passwords. If a password contains a colon, the user will not be able to log in until the password is changed to a valid password (with SET PASSWORD). |
Examples
The following example adds a user (user3), assigns it a password (some_password), and assigns it the CONNECT and RESOURCE database privileges. Because "some_password" is quoted, case will be preserved.
GRANT CONNECT,RESOURCE TO user3 IDENTIFIED BY "some_password"
The next example grants the DBA database privilege to the user (user3) created with the previous example GRANT statement:
GRANT DBA TO user3
GRANT (object privileges)
The following form of the GRANT statement enables a DBA user (user with the DBA database privilege) to grant object privileges to one or more privilege-based users.
GRANT privilege[,...]|ALL PRIVILEGES ON object TO user[,...]|PUBLIC
where
privilege is the type of object privilege to grant (the SQL statement that user can use with the specified table or view): SELECT, INSERT, UPDATE, or DELETE. To grant all privileges, specify ALL PRIVILEGES.
object is the name of the object (table or view) that granted privileges apply to. If you do not own the object, you must include the owner's name: owner_name.object.
user is the user that the privileges will be assigned to. PUBLIC denotes all users, present and future.
Privileges can cascade up — i.e., if you grant privileges for an object, those privileges may be granted automatically for other objects, as follows:
- If user owns an updatable view and is being granted privileges on its leaf underlying table (the base table wherein the data finally resides, regardless of any intervening tables or views), these privileges are granted for the view as well. There can be only one leaf underlying table for an updatable view.
- If user owns an updatable view that immediately references the table on which privileges are being granted (in other words, if the reference appears in the FROM clause without an intervening view), these privileges can also cascade up.
- If user owns a view, updatable or not, user already has the SELECT privilege on all tables referenced in its definition as well as on the view itself.
See CREATE VIEW for more information on views.
For each privilege that is granted, an entry is made in the GENESIS_AUTHS table in the system catalog. The entry includes the following information:
- The user (user) that was granted the privilege(s)
- The generated privilege(s)
- The table or view that the privilege(s) are for
Examples
The following example assigns all object privileges for a table (mytable) to a user (user3):
GRANT ALL PRIVILEGES ON mytable TO user3
The next example grants the SELECT object privilege for a table (sometable, which is owned by user3) to all users:
GRANT SELECT ON user3.sometable TO PUBLIC
REVOKE (database privileges)
The following form of the REVOKE statement enables a DBA user (a user with the DBA database privilege) to rescind database privileges that have been granted to a privilege-based user.
REVOKE privilege[,...] FROM user
where
privilege is CONNECT, RESOURCE, or DBA.
user is the user-based user that will lose the specified database privileges.
See GRANT (database privileges) for more information on these privileges, and note the following:
- Revoking the CONNECT privilege prevents the specified user from accessing the database.
- Revoking the RESOURCE privilege prevents the specified user from creating new tables and views.
- Revoking the DBA privilege does not revoke CONNECT or RESOURCE, and vice versa.
|
|
Be careful removing the DBA privilege. If there are no DBAs, the GRANT and REVOKE statements can no longer be used (so users can no longer be managed). We recommend against changing the permissions for the initial users (DBA and DBADMIN). |
Example
The following statement removes the DBA privilege from a user (user2), which may leave the user with no privileges at all.
REVOKE DBA FROM user2
REVOKE (object privileges)
The following form of the REVOKE statement enables a DBA user (a user with the DBA database privilege) to rescind object privileges granted to one or more privilege-based users. See GRANT (object privileges) for more information on these privileges.
REVOKE privilege[,...] ON object_name FROM PUBLIC|user[,...]
where
privilege is SELECT, INSERT, UPDATE, DELETE, or ALL PRIVILEGES (see the discussion below).
object is the name of the object (table or view) that the privileges apply to.
user is the user that the specified database privileges will be taken from. PUBLIC specifies all users, present and future.
Note that when a privilege is revoked, dependent privileges may also be revoked.
SET PASSWORD
The SET PASSWORD statement enables a privilege-based user to change his or her password and enables a DBA user (a user with the DBA database privilege) to change another user's password.
SET PASSWORD new_password old_password
or
SET PASSWORD new_password FOR user
where
new_password is the new password for the user.
old_password is the current password for the logged-in user.
user is a privilege-based user.
Passwords for privilege-based users can have a maximum of 30 characters. If they are unquoted, they will be stored as all uppercase characters, and they must follow the rules for identifiers (see Conventions, names, and identifier case). If they are quoted, they can contain special characters, except the colon (:) and the at sign (@), though they must still start with a letter. Note the following:
- Passwords are encoded using a SHA512 encoding scheme.
- If the old password is in mixed case, it must be quoted — e.g., “MyOldPassword”.
For example, the following changes the password for the currently logged-in user. The new password (mynewpassword) will be saved in all uppercase characters (MYNEWPASSWORD) because it is unquoted.
SET PASSWORD mynewpassword MYOLDPASSWORD
The next example changes the password for user2 to NewPasswordForUser. Because the password is quoted, it will retain its mixed case. For this statement, the logged-in user would need to have the DBA database privilege.
SET PASSWORD "NewPasswordForUser2" FOR user2
