Resource role in oracle

What is the difference between granting such roles as CREATE TABLE, CREATE TRIGGER, CREATE PROCEDURE to开发者_如何学JAVA user directly and via the role resource. Is it good to grant these role via resource or directly to the user schema?

The RESOURCE role almost certainly contains more privileges than you actually want to grant a user and it probably doesn't include a number of privileges that you do want to grant. So you would generally never grant users the RESOURCE role in a production system. Instead, you'd create roles appropriate to your environment (i.e. a DEVELOPER_ROLE, an ANALYST_ROLE, etc.), grant appropriate privileges to those roles, and then grant those roles to the appropriate users.

The only time that it matters whether a privilege has been granted directly to a user or via a role is if the user is trying to exercise the privilege through a stored procedure. If I've been granted the CREATE TABLE privilege through a role, I can create all the tables I want interactively. But if I try to write a stored procedure that creates a table using dynamic SQL, I'll get an error. Since you never want developers creating objects at runtime, however, this isn't generally a problem. If you really wanted to allow this, however, you'd have to grant the owner of the procedure the CREATE TABLE privilege directly.