IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
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 name" to "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_nameIf 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
