Granting schema and database object privileges

Related topics

An Oracle database object privilege is a permission granted to an Oracle database user or role to perform some action on a database object. These object privileges include SELECT, INSERT, UPDATE, DELETE on tables and views and EXECUTE on procedures, functions, packages, and Java objects. They can be granted using Oracle Portal or using Oracle commands.

A schema-level privilege is granted to a Oracle Portal user or group to perform some action on a schema, for example, inserting rows in any table in the schema or modifying the definition of objects in the schema.

You can grant privileges at the object or schema level. Object-level privileges are granted on an object-by-object basis and apply only to Oracle database user accounts or roles. Schema level privileges are granted on a schema-by-schema basis and apply to Oracle Portal users or groups.

To grant schema-level privileges, you must have either:

To grant privileges on a database object, you must have Manage privileges on the schema where the object is stored.

 

To grant an object-level privilege using the database objects navigator:

  1. In the Oracle Portal Navigator, navigate to the list of schemas.

  2. The Name column on the Database Objects tab contains a list of all schemas in the database on which you have View or higher access privileges. In the Name column, scroll down to the schema containing the database object you want to find.

  3. Click the schema Name. The Name column updates with a list of objects stored in the schema you selected.

  4. Scroll down to the database object you want to find.

    Note To find an object, you can also enter the name of the database object in the Find field at the top of the page and click Go.

  5. The Actions column displays all actions you can perform on the object.

  6. Click Grant Access.

  7. In the User/Role field, enter the name of the database user or role to whom you want to grant privileges. Click to search for users or role. Only Oracle database user accounts and roles are shown in the list

    If you enter Public, all Oracle database user accounts will be given the access privileges you choose in the next step.

  8. Choose one or more database object privileges in the list next to the User/Role field.

    The privileges in the list are based on the type of object on which you are granting privileges. For example, for tables or views, ALTER, DELETE, INDEX, INSERT and other privileges display. For procedures/function/packages, EXECUTE displays.

  9. Select the with Grant Option checkbox if you want the user or role to be able to grant object level privileges to other Oracle database users or roles.

  10. Click Grant.

  11. The database user or role you entered now appears in the Revoke Privileges list at the bottom of the page with the object access privilege you granted. Privileges granted with the With Grant Option display as bold text in the list.

  12. (Optional) Click next to a database user or role to revoke their privileges.

 

To grant schema-level privileges to a Oracle Portal user or group:

  1.  Follow steps 1-4 above. The Actions column displays all actions you can perform on the schema.

  2. In the Actions column, click Grant Access. The Privilege Manager page displays.

  3. In the Grantee field, enter the name of the Oracle Portal user or group of users to whom you want to grant schema-level privileges. Click to search for users or groups.

  4. Choose an access privilege in the list next to the grantee; for example, Manage, Grant, Insert, or View.

  5. Click Add to List. The Oracle Portal user or group you entered in Grantee now appears in the Modify Privilege list at the bottom of the page with the access privilege you granted.

  6. (Optional) To modify a schema-level access privilege, choose a new privilege next to the Oracle Portal user or group in the Modify Privilege list.

  7. (Optional) Click next to an Oracle Portal user or group to revoke their privileges.

  8. Click Apply.

 

     

Notes

  • Use the Grant Manager page to grant privileges on a single object to one or more Oracle database schemas or roles. Use the Grant tab of the User Manager to grant privileges on more than one object to a single Oracle database schema.

  • The Grant Manager page display check boxes based on the type of object on which you are granting privileges. For tables or views, the SELECT, INSERT, UPDATE, and DELETE check boxes display. For procedures/function/packages, the EXECUTE check box displays. For Sequences, the SELECT check box displays.

  • You can add or remove several privileges at a time.

  • The privilege system is hierarchical and does not allow more than one privilege level to be granted per grantee, per object.

Related topics

 

Back to top