Manage Privileges for Database Users - Oracle

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.

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"

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 table, or execute an individual function, package, or procedure are object privileges as opposed to the ability to execute any procedure or select any table, which are system-level privileges.

What is given can be taken away. In order to take privileges away from a user, we use the REVOKE command and the syntax is very similar to the syntax we use when issuing a grant. Here are two examples of a REVOKE operation:

REVOKE INSERT ON "TABLE_NAME" FROM "NEWUSER";
REVOKE EXECUTE ON "TABLE_NAME" FROM "NEWUSER";

When you think of the number of privileges that need to be managed in situations where you have thousands of database objects as well as thousands of users, you quickly realize that it would be nice to organize the privileges into groups that can be easily managed. This is where roles come into play.

A “role” is used to group privileges together into a predefined group that can be granted to users. So, rather than granting object and system privileges individually to every user in your system, you can grant them to a role, which in turn is granted to the user.

Some special roles are created by Oracle through the install process or by running Oracle-supplied scripts. The DBA, Connect, Resource, Imp_Full_Database, and Select_Catalog_Role are some examples of roles that are supplied by Oracle and should not be changed.

Create and Grant a Role Roles are created using the create statement in the same manner as creating users. We can also revoke privileges from roles and drop roles when they are no longer needed. Roles can also be granted to other roles. You can see an example of this next where the Oracle role CONNECT is granted to the newly created role TESTROLE, along with a system and object privilege.

CREATE ROLE "TESTROLE”;
GRANT CONNECT TO "TESTROLE"
GRANT EXECUTE ANY PROCEDURE TO "TESTROLE"
GRANT SELECT ON "table_name" TO "TESTROLE"

The new role can then be granted to a user as shown next, where “testrole” is granted to user “Testuser.”

Grant “testrole” to “Testuser”;

The “TESTROLE” is then dropped since it is no longer required.

DROP ROLE "TESTROLE";

Now that we’ve created users and roles, we can fine-tune our management of these by implementing some user policies through profiles,which we will explore next.

A profile can be used to implement a password management policy, as well as limit resources for a user. When you created the user NEWUSER earlier, a password was supplied along with the DEFAULT profile. Using this DEFAULT profile, the user never needs to change their password and there are no limits placed on any system resources. You can create new profiles to implement your corporate password policies in Oracle. For example, you can specify the number of days after which a user must change their password. You can also establish a policy where a password cannot be reused within a certain period of time and must contain a certain number of changes. A function can be used to ensure that a complex password is created by the user. For example, you may require that a password be more than eight characters long, use alpha, numeric, and special characters, and that it does not repeat a character more than twice. This can all be implemented in a function. An account can be locked after a specified number of login attempts and can remain locked for the number of days defined in the profile.

System limits for a user can also be implemented by a profile. These include limiting system resources such as those for CPU, connect, and idle time as well as the number of sessions employed by the user, limits on reads, and the SGA used. You should note, however, that the Database Resource Manager is the preferred way to limit resources and that you should use profiles to manage passwords.

The following is an example of the creation of a new policy that will lock an account after three failed login attempts and will keep the account locked indefinitely. The password needs to be changed every 60 days and the new password will be verified by your custom function COMPLEX_PASSWORD. The old password cannot be reused for 120 days.

CREATE PROFILE "NEWPOLICY"
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_LIFE_TIME 60
PASSWORD_REUSE_TIME 120
PASSWORD_VERIFY_FUNCTION COMPLEX_PASSWORD

Now, let’s grant this policy to user NEWUSER:

ALTER USER NEWUSER PROFILE NEWPOLICY;

In Conclusion

As you have seen in this chapter, there is a great deal that a DBA needs to be aware of to properly manage a database. The good news is that you will have tools such as OEM to help you. Do your best to keep your environment as simple as you possibly can! You will be glad that you did as your overall database environment continues to grow.