SQL User Access Levels - dummies

By Allen G. Taylor

SQL offers different levels of access depending on the user’s role to provide solid security for your database through limited access. SQL provides controlled access to these nine database-management functions:

  • Creating, seeing, modifying, and deleting: These functions correspond to the INSERT, SELECT, UPDATE, and DELETE operations.

  • Referencing: Using the REFERENCES keyword involves applying referential integrity constraints to a table that depends on another table in the database.

  • Using: The USAGE keyword pertains to domains, character sets, collations, and translations.

  • Defining new data types: You deal with user-defined type names with the UNDER keyword.

  • Responding to an event: The use of the TRIGGER keyword causes an SQL statement or statement block to be executed whenever a predetermined event occurs.

  • Executing: Using the EXECUTE keyword causes a routine to be executed.

The database administrator

In most installations with more than a few users, the supreme database authority is the database administrator (DBA). The DBA has all rights and privileges to all aspects of the database. With all that power at your disposal, you can easily mess up your database and destroy thousands of hours of work. DBAs must think clearly and carefully about the consequences of every action they perform.

The DBA not only has all rights to the database, but also controls the rights that other users have. Thus, highly trusted individuals can access more functions — and, perhaps, more tables — than can the majority of users.

A surefire way to become a DBA is to install the database management system. The person that installs a database is automatically a DBA. The installation manual gives you an account, or login, and a password. That login identifies you as a specially privileged user.

Sometimes, the system calls this privileged user the DBA, sometimes the system administrator, and sometimes the super user. As your first official act after logging in, you should change your password from the default to a secret one of your own.

If you don’t change the password, then anyone who reads the manual can also log in with full DBA privileges. After you change the password, only people who know the new password can log in as DBA. You should share the new DBA password with only a small number of highly trusted people.

After all, a falling meteor could strike you tomorrow; you could win the lottery; or you may become unavailable to the company in some other way. Your colleagues must be able to carry on in your absence. Anyone who knows the DBA login and password becomes the DBA after using that information to access the system.

If you have DBA privileges, log in as DBA only if you need to perform a specific task that requires DBA privileges. After you finish, log out. For routine work, log in by using your own personal login ID and password. This approach may prevent you from making mistakes that have serious consequences for other users’ tables (as well as for your own).

Database object owners

Another class of privileged user, along with the DBA, is the database object owner. Tables and views, for example, are database objects. Any user who creates such an object can specify its owner. A table owner enjoys every possible privilege associated with that table, including the privilege to grant access to the table to other people.

Because you can base views on underlying tables, someone other than a table’s owner can create a view based on that table. However, the view owner only receives privileges that he or she has for the underlying table. Bottom line: A user can’t circumvent the protection on another user’s table simply by creating a view based on that table.

The public

In network terms, “the public” consists of all users who are not specially privileged users (that is, either DBAs or object owners) and to whom a privileged user hasn’t specifically granted access rights. If a privileged user grants certain access rights to PUBLIC, then everyone who can access the system gains those rights.

In most installations, a hierarchy of user privilege exists, in which the DBA stands at the highest level and the public at the lowest.