Security considerations: User IDs

By assigning user IDs and passwords, the database administrator controls who has access to a database. By granting privileges to each user ID, the database administrator controls which tasks each user can perform when connected to the database.

Privilege scheme is based on user IDs

When a user logs on to the database, they have access to all database objects that meet any of the following criteria:

objects the user created
objects to which the user has received explicit privilege
objects to which a group the user belongs to received explicit privilege

The user cannot access any database object that does not meet these criteria. In short, users can access only the objects they own or objects to which they explicitly received access privileges.

Roles and privileges

You can control the tasks users can perform on database objects (such as creating, modifying, executing, updating, and so on), and the administrative tasks (such as backing up, profiling, and so on) that a user can perform, by granting roles and privileges.

You grant roles and privileges using the GRANT statement.

The REVOKE statement is the opposite of the GRANT statement—any role or privilege that GRANT has explicitly given, REVOKE can take away. Revoking CONNECT from a user removes the user from the database, including all objects owned by that user.

Integrated logins

Integrated logins allow users to use a single login name and password to log onto both your Windows operating system and onto a database. An external login name is associated with a database user ID. When you attempt an integrated login, you log onto the operating system by giving both a login name and password. The operating system then tells the server who you are, and the server logs you in as the associated database user ID. No additional login name or password are required.

When using integrated logins, leaving the user profile Guest enabled with a blank password can permit unrestricted access to a database that is hosted by the server that accepts integrated logins. Literally any user can log in to the server using any login ID and any password because they are logged in by default to the Guest user profile.