Privilege-based users and their privileges (database privileges and object privileges) are added and managed using SQL statements — e.g., GRANT and REVOKE (see Statements for managing privilege-based users and privileges).
When a system catalog is first generated with support for privilege-based users, or when users are initialized, the system catalog will have
- two users: DBA and DBADMIN. Both have the DBA database privilege, which gives them read/write access to all tables and views. Additionally, these users can open the system catalog in the DBA program. (No other user can open the system catalog.) Note that we recommend against changing database permissions for these users.
- no explicit object privileges (the GENESIS_AUTHS table will be empty).
|
For security, change passwords for the initial set of users. |
To add, view, or manage privilege-based users and their privileges, do the following:
- Generate the system catalog using dbcreate with the -c and -a options (and any other options dbcreate will need to find files, generate to the correct location, etc.). The GENESIS_USERS and GENESIS_AUTHS tables/files will be set to their initial values: two users (DBA and DBADMIN) with DBA privileges, but no object privileges. For more information on dbcreate options and syntax, see dbcreate utility.
|
|
Using dbcreate with the -c and -a options will restore the initial state of GENESIS_USERS and GENESIS_AUTHS, eliminating any changes you've made to users and privileges, unless you also use the -n option for dbcreate. |
- To add privilege-based users, use a GRANT statement with the IDENTIFIED BY clause. See GRANT (database privileges).
- To assign privileges to users, use a GRANT statement. See GRANT (database privileges) and GRANT (object privileges).
- To revoke privileges, use a REVOKE statement. See REVOKE (database privileges) and REVOKE (object privileges).
- To view information on privilege-based users and their database-level privileges, query the GENESIS_USERS table — e.g., SELECT * FROM genesis_users. The GENESIS_USERS table has the following columns with information on privilege-based users:
U_NAME - user name
U_PASSWORD - password (encrypted)
U_DBA - Y or N indicating whether the user has the DBA database privilege
U_CONNECT - Y or N indicating whether the user has the CONNECT database privilege
U_RESOURCE - Y or N indicating whether the user has the RESOURCE database privilege
U_GROUP - Indicating whether the user is one of the initial DBA users (i.e., has a setting of 255)
See GRANT (database privileges) for information on the CONNECT, RESOURCE, and DBA privileges.
You can use the DBA program to view a list of privilege-based users, but the list includes little more than user names. To see this list, open the system catalog in DBA (see Opening the system catalog in DBA), and select Maintenance > Users from the menu.
- To view information on object-level privileges for users, query the GENESIS_AUTHS table. For example, SELECT * FROM genesis_auths. The GENESIS_AUTHS table has the following columns:
A_USER - the user that the privileges are assigned to
A_DATABASE - the name of the database
A_OWNER - the owner of the object (table or view)
A_TABLE - the table or view that the privilege is granted for
A_SELECT - Y or N indicating whether the user has permission to use SELECT statements with the table or view
A_INSERT - Y or N indicating whether the user has permission to use INSERT statements with the table or view
A_UPDATE - Y or N indicating whether the user has permission to use UPDATE statements with the table or view
A_DELETE - Y or N indicating whether the user has permission to use DELETE statements with the table or view
See GRANT (object privileges) for more information.
- To allow users to update a database table, set the table access level to an odd number. For more information, see Modifying table access levels.
A privilege-based user cannot be removed from the system catalog. However, you can make a privilege-based user inactive by revoking all database privileges for the user.
