Create user Syntax :
Code:
create (user name)
identified by (password);
Oracle Object privileges
Object privileges assign the right to perform a particular operation on a specific object. Here are some examples of object privilege assignment:
Code:
grant select, insert on customer to fred, mary, joe;
grant insert on order_table to update_role;
grant all on customer to fred;
grant select on customer_view to mary;
As you can see, the direct assignment of object privileges requires specific grants for every object to every user in the Oracle database. If you have a schema with 100 tables and 1,000 users, it would require 100,000 individual grant statements to assign security.
Oracle System privileges
System privileges cover many areas of access in a broad brush, with grants such as select any table. Examples of system privilege grants include:
Code:
grant create any cluster to customer_role;
grant select any table to fred;
grant create any table to public;
grant create tablespace to dba_role;
Obviously, system privileges should be used only in cases where security isn't important, because a single grant statement could remove all security from the table.
Oracle Role-based security
Role security allows you to gather related grants into a collection. Since the role is a predefined collection of privileges that are grouped together, privileges are easier to assign to users, as in this example:
Code:
create role all_customer;
grant select, update on customer to all_customer;
grant select on item_table to all_customer;
grant all_customer to fred, mary, joe;
The benefits of role-based security are obvious, because role-based security allows you to define sets of access rules and then assign them to the appropriate classes of users.
However, unlike VPD security, it isn't possible to implement sophisticated rules for data access. With grants, users either have access to the table, or they do not.
Design for Oracle grant security
If you choose to implement grant security for your Oracle database, you must do some careful up-front planning to ensure that each role is carefully designed to cover access for a specific class of users without overlapping other roles. The steps for implementing grant security are:
- Define roles for all known classes of users.
- Define access rules for each role.
- Define all row-level and column-level restrictions.
- Create views for all data access.
- Assign the views to the roles.
- Assign the roles to the users.