CRITICAL SKILL 3.10 Manage Privileges for Database Users

Team Fly

Page 115

CRITICAL SKILL 3.10
Manage Privileges for Database Users

Creating a user in Oracle has accomplished the first part of user setup and that is authentication. We have a user ID and password and have authorized this user to use an Oracle database. Once the user logs in, however, they will not be able to do very much because they will not have privileges that allow them to access any objects. This leads us to the second step of setting up a user: authorization. In order to authorize a user to perform their tasks, we need to grant access.

Grant Authority

You now need to give permission to the user to do things in Oracle. Actions like accessing a table or executing a procedure or running a utility require you to ''grant" the authority to that user. When you perform a grant, you can specify four things:

The user that is being granted the authority.

The object that is being granted. Examples of these are a table, procedure, or role.

The type of access being granted, such as select, insert, update, or delete on a table, or execute on a procedure, function, or package.

Whether this user has authority to then grant the same authority to other users. By default, they do not, but this can be added by using the With Grant option.

Here are two examples that grant a user "NEWUSER" access to a table and then to a package.

GRANT SELECT ON "TABLE_NAME" TO "NEWUSER" WITH GRANT OPTION;
GRANT INSERT ON "TABLE_NAME" TO "NEWUSER" WITH GRANT OPTION;
GRANT EXECUTE ON "PROCEDURE_NAME" TO "NEWUSER"

Types of Grants

There are two types of grants that can be given to a user: system privileges and object privileges.

System privileges are predefined Oracle privileges granting authority to overall system objects rather than individual ones. The ability to perform a create tablespace, alter system, and back up any table are just a few examples of some system-level privileges that can be granted to a user.

Object privileges are a lower-level authority where a named object is granted to a user. So, the ability to perform an operation on a particular

Team Fly