There is a group that exists by default in all DBMSs—the PUBLIC group, or role. PUBLIC is basically a variable that equates to anyone connected to the database; therefore, any right granted to PUBLIC is granted to everyone with a database connection. There are cases in which all users require a certain privilege. For example, in an Oracle database, all users must be able to execute the stored procedures DBMS_PIPE and DBMS_LOCK. You should, therefore, grant rights to execute these stored procedures to the PUBLIC role.
Sometimes, high-level privileges are given to PUBLIC by default when the database is created. However, for security reasons, granting privileges to PUBLIC should be used only when absolutely necessary.
The following are a few tips for grouping users in the DBMS:
- Create separate groups (roles) for system and object privileges. This enables the DBAs to manage privileges for the system roles and data owners to grant privileges to the object roles exclusively.
- Choose a naming convention that reflects each type of group (role) for easy reference. For example, for a group that will be able to edit all the land base data, you could name the group LANDBASE_EDITORS.
- Grant privileges directly to the ArcSDE administrator user and grant privileges via groups (roles) for all other users.
The ArcSDE administrator user is a unique entity. In most cases, only one such user may exist for any database, and it is not part of a larger logical group of users. (The only exception to this is if you are using multiple geodatabases stored in the same Oracle database; in this case, you may have a few users that need ArcSDE administrator-type privileges.) Experienced database administrators consider it good design to grant privileges directly to such application administrator accounts. By contrast, accounts for end users should receive privileges through groups that represent their job description, project responsibilities, or other logical classification within the organization.
- Avoid mixing roles with directly granted privileges for end user accounts.
When end user accounts receive privileges through both roles and direct grants, a well-planned security model can quickly devolve into an unmanageable mess, requiring considerable time and effort to restore to an organized state. Set policies for data owners to follow when granting access to their schema objects.
In the rare case that an end user has truly unique security requirements, consider granting some privileges directly to avoid complicating the role-based security model. Document these cases; they should be the exception rather than the rule.