Filter
Top Products
CHAPTER 10 Managing User IDs and Permissions
359
• You should give users with DBA authority two user IDs, one with DBA
authority and one without, so that they connect as DBA only when
necessary.
• RESOURCE authority allows the user to create new database objects,
such as tables, views, indexes, or procedures.
Granting permissions on tables and views
You can assign a set of permissions on individual tables and views. Users can
be granted combinations of these permissions to define their access to a table
or view.
Combinations of
permissions
• The ALTER (permission to alter the structure of a table) and
REFERENCES (permission to create indexes and to create unenforced
foreign keys) permissions grant the authority to modify the database
schema, and so will not be assigned to most users. These permissions do
not apply to views.
• The DELETE, INSERT, and UPDATE permissions grant the authority to
modify the data in a table or view. The DELETE, INSERT, and UPDATE
permissions grant the authority to modify the data in a table or view. Of
these, the UPDATE permission may be restricted to a set of columns in the
table or view.
• The SELECT permission grants authority to look at data in a table or view,
but does not give permission to alter it.
• ALL permission grants all the above permissions.
Example
All table and view permissions are granted in a very similar fashion. You can
grant permission to M_Haneef to delete rows from the table named
sample_table as follows:
1 Connect to the database as a user with DBA authority, or as the owner of
sample_table.
2 Type and execute the SQL statement:
GRANT DELETE
ON sample_table
TO M_Haneef
You can grant permission to M_Haneef to update the column_1 and column_2
columns only in the table named sample_table as follows: