Assigning Database Object Privileges- Users and Basic Security

Database object privileges allow you to access and manipulate other users’ objects. The types of database objects to which you can grant privileges include tables, views, materialized views, sequences, packages, functions, procedures, user-defined types, directories, and now with 23c schemas. To be able to grant object privileges, one of the following must be true:

•     You own the object.

•     You have been granted the object with GRANT OPTION.

•     You have the GRANT ANY OBJECT PRIVILEGE system privilege. This example grants object privileges (as the object owner) to the

The GRANT ALL statement is equivalent to granting INSERT, UPDATE, DELETE, and SELECT to an object. There are other privileges included in the ALL command such as ALTER, INDEX, REFERENCES, READ, ON COMMIT REFRESH, QUERY REWRITE, DEBUG, and FLASHBACK. The next statement is equivalent to the prior statement:

You can also grant INSERT and UPDATE privileges to tables, at the column level. The next example grants INSERT privileges to specific columns in the INVENTORY table:

If you need to take away object privileges, use the REVOKE statement. This example revokes DML privileges from the INV_MGMT_APP user:

Grouping and Assigning Privileges

A role is a database object that allows you to group together system or object privileges, or both, in a logical manner so that you can assign those privileges in one operation to a user. You can also grant roles to other roles, but be careful with nesting too many roles as it does make it difficult to manage.

Roles help you manage aspects of database security in that they provide a central object that has privileges assigned to it. You can subsequently assign the role to multiple users or other roles.
To create a role, connect to the database as a user that has the CREATE ROLE system privilege. Next, create a role and assign to it the system or object privileges that you want to group together. This example uses the CREATE ROLE statement to create the JR_DBA role:

The next several lines of SQL grant system privileges to the newly created role:

The users JSMITH and LWALKER can now perform tasks such as creating synonyms and views. To see the users to which a role is assigned, query the DBA_ROLE_PRIVS view:

To see the roles granted to your currently connected user, query from the USER_ROLE_ PRIVS view:

To revoke a privilege from a role, use the REVOKE command:

Similarly, use the REVOKE command to remove a role from a user:

Note Unlike other database objects, roles don’t have owners.a role is defined by the privileges assigned to it.