Oracle SQL Tutorial – System and User Privileges

Hi everyone, i want to show you schema subject, system and user privileges. it seems easy but required for dba


Schemas are called database objects aggregated together. A schema contains tables, procedures, sequences,synonyms, index and database links. Each of users has a schema, each schema has the same name as the user name. When a user is created, the schema with the same name is created.

While schemas logically hold users’ objects, their physical counterparts are tablespace.

System Privileges:

The Database administrator has a multiple authority that users can grant in SQL language. A database administrator is the user with the highest authority in a database.

  • Creating Nnew Users
  • Droping Users
  • Droping Tables
  • Backup Tables

Creating Users:

In SQL, the user is created on the database side. A normal user does not have the authorized to create users on the system.

Basic syntax ;

  • DEFAULT TABLESPACE “USERS” specify the user’s storage in tablespace.
  • TEMPORARY TABLESPACE “TEMP” specify we specify the user’s temp tablespace.
  • PROFILE “DEFAULT” which profile will be included


Granting System Rights To the User:

A user can be granted the following rights by the database administrator, for example

  • CREATE SESSION Required to connect to the database.
  • CREATE TABLE  Required to create table on database.
  • CREATE SEQUENCE Required for objects that generate ascending or descending sequential numeric values in the database.
  • CREATE VIEW Allows us to extract data from multiple tables in the database as if it were a single table.
  • CREATE PROCEDURE Required to create procedure in the database


GRANT  "authority nameto "user name";


Database Role Concept :

What is the role ?

Role consist of authorize and aims to facilitate authority management and dynamic. We can assign the necessary privileges under a role to users.  When the privileges under the role change, this affects all users with that role.

Role creation :

In SQL language, if you want to create a role, the following steps are followed.

CREATE ROLE role_name

If we want to give the role we created to a user ,

GRANT role_name to user_name


Object Authority :

Object rights are privileges granted to users to perform a specific action on a particular table, view, or procedure.


GRANT object_priv [(columns)]

ON   object

TO  {user | role | PUBLIC} 


Granting object rights to other users :

In SQL, the rights of a user can be granted to different users if requested.

For example, If we want to give the query right in the Customers table to other users



Public Parameter :

All rights of the users table are used when you want to give other users in bulk.

For example Lazy’s products table, if we want other users to view,



Revocation of object rights :

In sql, REVOKE will revoke rights granted to other users.  Also, the rights defined with WITH GRANT OPTION are revoked.


REVOKE {privilege [, privilege...] | ALL} ON object FROM {user [,user] | role | PUBLIC } [CASCADE CONSTRAINTS];

We have come to the end of this article, I hope everything is descriptive See you in the next article



Deniz Parlak

Hi, I'm a Computer Engineering student in Dumlupınar University.I will graduate next year. I did an internship TurkNet Telecominication Company in 2018 June- 2018 September. I had experience Linux/Unix system, SQL Server Management , SSRS, Mysql Database Management,Troubleshooting performance issues in Linux. I also interested Oracle database and technologies. I want to work as Oracle DBA after my University. I am practicing Unix, Exadata, Orace Cloud, Oracle RAC, Dataguard, EBS, Oracle BI and etc and i hope the articles will be helpful. Any questions, or support please write email

Leave a Reply

Your email address will not be published. Required fields are marked *