Permissions in Oracle

The Oracle database, there are two types of permissions:

• System privileges - the right to perform a particular action or perform certain actions on the specific type of objects
• Object permissions - the right to perform a specified action on a certain subject

System privileges

There are over 80 different system privileges. In order to facilitate the work of administrators permissions are grouped into so-called. roles. Predefined roles are:

• CONNECT - ability to connect to Oracle
• RESOURCE - the ability to create tables, sequences, indexes and other objects
• DBA - the possibility of setting up other users, access to restricted sites of other members

Granting System

To grant permissions to use the GRANT command:

GRANT privilege [, privilege ...] is the user;
GRANT command gives new powers to the user. Powers given subsequent command GRANT accumulate.

Revoking system privileges

To receive permission REVOKE command is used:

REVOKE privilege [, privilege ...] FROM user;
REVOKE command receives the specified permission to the user, leaving the other unchanged.

Granting privileges

By default, you may not transfer the powers conferred on him by another user. To be able to do so, while giving it powers of the administrator must give them the option WITH ADMIN OPTION.

GRANT privilege [, privilege ...]
The user
WITH ADMIN OPTION;

Object Permissions

The owner of an object is the user who creates. If the user does not provide rights to your property, it is only he and the administrator proficient in this object.

Object privileges define user rights to the object of another user.

Granting Object

Object permissions give the command:

GRANT privilege [, privilege ...]
ON object
TO user [, user];

The following table shows the most frequently broadcast rights object.

• SELECT - selecting data from a table or view
• INSERT - inserting rows into a table or view
• UPDATE - modifications to the rows or columns of a table or indeterminate perspective
• DELETE - deleting rows from a table or view
• ALTER - change in the definition of table columns
• INDEX - indexing table
• REFERENCES - a reference to a table in the foreign keys
• ALL - all right
• EXECUTE - the right to perform the procedures, functions and packages

Example

Scott to give the user the option to your emp table, the user Adams wrote:

GRANT SELECT
ON emp
TO Scott;

Granting privileges

By default, you may not transfer the powers conferred on him by another user object. To be able to do this, permission must be given the option WITH GRANT OPTION.

GRANT privilege [, privilege ...]
ON object
The user
WITH GRANT OPTION;

Example

Scott to give the user the option to your table emp with the law of transfer of this right away, you Adams wrote:

GRANT SELECT
ON emp
TO Scott
WITH GRANT OPTION;

PUBLIC type preference

To transfer the right to object to all database users, I use the PUBLIC:

GRANT SELECT
ON emp
TO PUBLIC;

Revoking permission object

To receive permission, use the command REVOKE object:

REVOKE privilege [, privilege ...]
ON object
FROM user;

Actions on objects of another user

Another user to the object referenced is as follows:

user_name.object_name

Example

To select everything from the emp table by Adams, write:

SELECT *
FROM Adams.emp;

To facilitate reference to an object, you can create synonyms for them.