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

SCHEMA :

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 ;

CREATE USER "USERNAME" PROFILE "DEFAULT" IDENTIFIED BY PASSWORD DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP";
  • 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

Syntax,

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.

Syntax,

GRANT object_priv [(columns)]

ON   object

TO  {user | role | PUBLIC} 


[WITH GRANT OPTION];

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

SQL> GRANT SELECT ON CUSTOMERS TO Micheal

 

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,

SQL> GRANT SELECT ON Lazy.products TO PUBLIC

 

Revocation of object rights :

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

Syntax,

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

I am a technical author on IT Tutorial. I am working on Oracle and Mysql databases, I also work on Datawarehouse and Big Data. If you need help, please contact deniz.parlak@yahoo.com.