A privilege is a right to execute a particular type of SQL statement or to access another user’s object. The Oracle database enables you to control what the users can and cannot do in the database.
Privileges are divided into two categories:
System privileges: Each system privilege allows a user to perform a particular database operation or class of database operations. For example, the privilege to create tablespaces is a system privilege. System privileges can be granted by the administrator or by someone who has been given explicit permission to administer the privilege.
Object privileges: Object privileges allow a user to perform a particular action on a specific object, such as a table, view, sequence, procedure, function, or package. Without specific permission, users can access only their own objects. Object privileges can be granted by the owner of an object, by the administrator, or by someone who has been explicitly given permission to grant privileges on the object. For example, in our demo ERPUSER will own the tables and other users will require read, write object privileges on ERPUSER tables.
Grant insert on myable1 to user1;
Grant insert on myable1 to user5
Create tablespace erp_data datafile ‘/u01/app/oradata/db11lin/erpdata01.dbf’ size 100M autoextend on;
Create user erpuser identified by welcome1 default tablespace erp_data temporary tablespace temp;
Grant connect, resource to erpuser;
Create user user1 identified by welcome1 default tablespace erp_data temporary tablespace temp;
Create table erpuser.mytable1 ( sno number(5), sname varchar2(20));
We can create a table for any user in this format if we have dba privileges. [owner.tablename]
Insert into erpuser.mytable1 values(100, ‘by user1’);