USER, PRIVILLEGES and ROLES

USER, ROLES & PRIVILEGES

Oracle provides exclusive security features to safeguard your information from both unauthorized viewing and intentional or inadvertent damage. This security is provided by granting or revoking privileges on a person by person and privilege by privilege basis and is in addition to any security your computer system already has.

Every Oracle user has a name and password and owns any tables, views and other resources that he creates. An Oracle role is a set of privileges. You can grant or revoke specific privileges to roles and then assign roles to the appropriate user. A user can also grant privileges directly to other users.

Users :

The Oracle system comes with two users already created System and Sys. You log on to the system user to create other users, since system user has that privilege. The create user command is used to create a new user. After creating a user we can change the password of the user or the memory allocation option or tablespace by using alter user command and it has the following syntax.

Syntax : Alter user UNAME identified by PWD Quota unlimited/Nk on System;

Like create user command, alter user command must also be given only from SYSTEM user. There is another command to change the password of the user from within the same user to which the password has to be changed. That command is Password. To change the password by using this command, user must know the old password.

Syntax : Password

We can delete a user by using drop user command and has the following syntax.

Syntax : Drop user Uname [cascade];

Privileges :

To connect to the database or to create any objects within the database or to access the data owned by another user, a user must have a corresponding permission. These permissions are called PRIVILEGES.

Database system privileges let you execute specific sets of commands. The “create table“ privilege, for example, lets you create tables. The “Grant Any Privilege” privilege allows you to grant any system privilege.

Database object privileges give you the ability to perform some operation on various objects. The “Delete” privilege, for example, lets you delete rows from tables and views. The “Select” privilege allows you to Query with a Select from tables, views, sequences and snapshots.

Granting Privileges :

Grant command is used to grant any system privilege or role to another user, to another role, or to public. The grant command has the following syntax for granting system privileges or role. System privileges can be granted to any user from System User only.

Syntax : Grant SystemPrivilege or Role, SystemPrivilege or Role, …
To Username/Role, Username/Role, …
[With Admin Option];

Example : Grant Create User, Alter User, Drop User to Ramesh;

After executing the above statement, user Ramesh can create a new user or alter an existing user or can delete an existing user from the database. The “with Admin Option” clause permits grantee to grant the Privilege/Role on to other user or role. If the above grant statement is written with “with admin option” at the end, then user Ramesh can grant “Create User”, “Alter User” and “Drop User” Privileges to other users, without connecting to system user.

To grant object privileges the grant command has the following syntax.

Syntax : Grant ObjectPrivilege, ObjectPrivilege,… on TableName
To UserName/Role, Username/Role, …
[ With grant Option ];

For example, if you want to grant select, insert and update permissions on student table of your user to scott, then following statement is used.

Example : Grant Select, Insert, Update(Fee) on Student to Scott;

The above grant statement grants select, insert and updation of FEE column permissions on student table to user Scott. This means that Scott can perform selection, insertion on student table and he can update only fee column of student table.

The “with Grant Option“ clause permits grantee to grant the Privilege on to other user or role. If the above grant statement is written with “with grant option” at the end, then user scott can grant “Select”, “Insert” and “Update” Privileges on student table to other users, without connecting to your user.

Instead of granting specific privileges to specific users, we can grant all privileges on a table as follows.

Example : Grant ALL on Student to Scott;

Instead of granting specific privileges to specific users, we can grant specific privileges or all privileges on object or system privileges to all users in the database at a time by using keyword “PUBLIC“ in place of Username. Following grant statement grants all permissions on student table in your user to all users in the database.

Example : Grant ALL on Student to Public;

Revoking Privileges :

Revoke is used to revoke privileges from a user or role. Revoke command has the following syntax for revoking System privileges. System privileges can be revoked from System user or the user who grants those permissions.

Syntax : Revoke SystemPrivilege/Role, SystemPrivilege/Role,…
From UserName/Role, UserName/Role…;

Example : Revoke Create user, Alter user, Drop user from Ramesh;

After executing the above revoke statement, user Ramesh can not create a new user or alter an existing user or drop an existing user.

Revoke command has following syntax for revoking Object privileges.

Syntax : Revoke ObjectPrivilege, ObjectPrivilege, …
on TableName
From Username/Role,Username/Role,…;

Example 1 : Revoke Select, Insert, Update(Fee) on Student From Scott;
Example 2 : Revoke All on Student From Public;

Example 1 revokes select, insert and updation of fee column privileges on student table from user Scott. Example 2 revokes all permissions on student table from all users in the database.


Roles

A role is nothing but a set of privileges. Using following syntax creates a role.

Syntax : Create Role RoleName;

Example : Create Role TEST;

Granting Privileges On A Role :

Example 1 : Grant Select on Student to TEST;
Example 2 : Grant Select, Insert, on Marks to TEST;
Example 3 : Grant ALL on EMP to TEST;

Above Grant statements grant different permissions on different users to the role TEST. You can also grant system privileges to the same role.

Granting A Role To A User :

Example : Grant TEST to Scott;

Above grant statement grants all permissions of Role TEST to the user Scott.

Revoking A Role From A User :

Example : Revoke TEST from Scott;

Above grant statement revokes all permissions of Role Test from User Scott.

Revoking Privileges From A Role :

Example : Revoke Select, Insert on Marks From TEST;

This statement revokes select and insert privileges on Marks table from Role TEST.

Deleting A Role : A Role can be deleted from database by using drop role command and has following syntax.

Syntax : Drop Role RoleName;

Example : Drop Role TEST;

Standard Roles
Oracle provides standard roles for compatibility with previous versions.

The Connect Role : Occasional users, particularly those who do not create tables will usually be given only the connect role. Connect is the simple privilege to use Oracle at all. This right becomes meaningful with the addition of access to specific tables belonging to other users, and the privilege to select, insert, update and delete rows from these tables as each of these rights are granted.

The Resource Role : More sophisticated and regular users of the database may be granted the resource role. Resource role gives users the additional rights to create their own tables, sequences, procedures, triggers and clusters.

The DBA Role : The DBA role has all the system privileges including unlimited space quotas and the ability to grant all privileges to other users.

Synonym : Synonym is nothing but an alias name to a table and is generally used when we have to access a table of another user. Prior to creating a synonym on another users table you must have privilege on that table.

Syntax : Create [ Public ] Synonym Synonymname for Username.TableName;

Example : Create Synonym SS For Scott.Student;

Above example creates a synonym with name SS for student table in user scott. Now you can use SS to access student table of scott instead of scott.student. Public key word will create public synonym that can be used by all users in the database those have permissions on student table of scott.

As other objects, a synonym can also be deleted. Syntax for deleting a synonym is as follows

Syntax : Drop [ Public ] Synonym Sname;

Example : Drop Synonym SS;

If we use public key word to create a synonym then to delete that synonym we must use the key word public.