BY : VARSHA AJITH Asst. Prof CSE DEPT JNEC
CONTENTS:  Create database (create tablespace)  Creating User  Managing Users  Granting privileges  Managing password  Removing privileges
DATABASE  Collection of coherent data  To create database we have : Syntax: CREATE DATABASE <database_name> Eg: CREATE DATABASE my_db;
EXAMPLE FOR CREATING DATABASE:  create database ora10 logfile group 1 ('D:oracledatabasesora10redo1.log') size 10M, group 2 ('D:oracledatabasesora10redo2.log') size 10M, group 3 ('D:oracledatabasesora10redo3.log') size 10M character set WE8ISO8859P1 national character set utf8 datafile 'D:oracledatabasesora10system.dbf' size 50M autoextend on next 10M maxsize unlimited extent management local sysaux datafile 'D:oracledatabasesora10sysaux.dbf' size 10M autoextend on next 10M maxsize unlimited undo tablespace undo datafile 'D:oracledatabasesora10undo.dbf' size 10M default temporary tablespace temp tempfile 'D:oracledatabasesora10temp.dbf' size 10M;
TABLESPACE The oracle database consist of one or more logical storage units called tablespaces. Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures that confirm to the operating system in which Oracle is running. Syntax: CREATE<tablespace name> DATAFILE 'C:oraclexeapporacleproduct10.2.0server<file name.dbf ’ SIZE 50M; Eg: Create tablespace te_cs DATAFILE ‘'C:oraclexeapporacleproduct10.2.0serverusr.dbf' ’SIZE 50M;
Create User The DBA creates user by executing CREATE USER statement. The user is someone who connects to the database if enough privilege is granted. Syntax: SQL> CREATE USER < username> -- (name of user to be created ) IDENTIFIED BY <password> -- (specifies that the user must login with this password) SQL> user created Eg: create user James identified by bob; (The user does not have privilege at this time, it has to be granted.These privileges determine what user can do at database level.)
Privileges A privilege is a right to execute an SQL statement or to access another user's object. In Oracle, there are two types of privileges  System Privileges  Object Privileges  System Privileges : are those through which the user can manage the performance of database actions. It is normally granted by DBA to users. Eg: Create Session,Create Table,Create user etc..  Object Privileges : allow access to objects or privileges on object, i.e. tables, table columns. tables,views etc..It includes alter,delete,insert,select update etc. (After creating the user, DBA grant specific system privileges to user)
GRANT : The DBA uses the GRANT statement to allocate system privileges to other user. Syntax: SQL> GRANT privilege [privilege…. … ] TO USER ; SQL> Grant succeeded Eg: Grant create session, create table, create view to James;
Object privileges vary from object to object.An owner has all privilege or specific privileges on object. SQL> GRANT object_priv [(column)] ON object TO user; SQL>GRANT select, insert ON emp TO James; SQL>GRANT select ,update (e_name,e_address) ON emp TO James;
CHANGE PASSWORD The DBA creates an account and initializes a password for every user.You can change password by using ALTER USER statement. Syntax: Alter USER <some user name> IDENTIFIED BY<New password> Eg: ALTER USER James IDENTIFIED BY sam
ROLE: A role is a named group of related privileges that can be granted to user.In other words, role is a predefined collection of previleges that are grouped together,thus privileges are easier to assign user. SQL> Create role custom; SQL> Grant create table, create view TO custom; SQL> Grant select, insert ON emp TO custom; Eg: Grant custom to James, Steve;
REVOKE REVOKE statement is used to remove privileges granted to other users.The privileges you specify are revoked from the users. Syntax: REVOKE [privilege.. …] ON object FROM user Eg:  REVOKE create session,create table from James;  REVOKE select ,insert ON emp FROM James
THANKS

Database administration commands

  • 1.
    BY : VARSHAAJITH Asst. Prof CSE DEPT JNEC
  • 2.
    CONTENTS:  Create database(create tablespace)  Creating User  Managing Users  Granting privileges  Managing password  Removing privileges
  • 3.
    DATABASE  Collection ofcoherent data  To create database we have : Syntax: CREATE DATABASE <database_name> Eg: CREATE DATABASE my_db;
  • 4.
    EXAMPLE FOR CREATINGDATABASE:  create database ora10 logfile group 1 ('D:oracledatabasesora10redo1.log') size 10M, group 2 ('D:oracledatabasesora10redo2.log') size 10M, group 3 ('D:oracledatabasesora10redo3.log') size 10M character set WE8ISO8859P1 national character set utf8 datafile 'D:oracledatabasesora10system.dbf' size 50M autoextend on next 10M maxsize unlimited extent management local sysaux datafile 'D:oracledatabasesora10sysaux.dbf' size 10M autoextend on next 10M maxsize unlimited undo tablespace undo datafile 'D:oracledatabasesora10undo.dbf' size 10M default temporary tablespace temp tempfile 'D:oracledatabasesora10temp.dbf' size 10M;
  • 5.
    TABLESPACE The oracle databaseconsist of one or more logical storage units called tablespaces. Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures that confirm to the operating system in which Oracle is running. Syntax: CREATE<tablespace name> DATAFILE 'C:oraclexeapporacleproduct10.2.0server<file name.dbf ’ SIZE 50M; Eg: Create tablespace te_cs DATAFILE ‘'C:oraclexeapporacleproduct10.2.0serverusr.dbf' ’SIZE 50M;
  • 6.
    Create User The DBAcreates user by executing CREATE USER statement. The user is someone who connects to the database if enough privilege is granted. Syntax: SQL> CREATE USER < username> -- (name of user to be created ) IDENTIFIED BY <password> -- (specifies that the user must login with this password) SQL> user created Eg: create user James identified by bob; (The user does not have privilege at this time, it has to be granted.These privileges determine what user can do at database level.)
  • 7.
    Privileges A privilege isa right to execute an SQL statement or to access another user's object. In Oracle, there are two types of privileges  System Privileges  Object Privileges  System Privileges : are those through which the user can manage the performance of database actions. It is normally granted by DBA to users. Eg: Create Session,Create Table,Create user etc..  Object Privileges : allow access to objects or privileges on object, i.e. tables, table columns. tables,views etc..It includes alter,delete,insert,select update etc. (After creating the user, DBA grant specific system privileges to user)
  • 8.
    GRANT : The DBAuses the GRANT statement to allocate system privileges to other user. Syntax: SQL> GRANT privilege [privilege…. … ] TO USER ; SQL> Grant succeeded Eg: Grant create session, create table, create view to James;
  • 9.
    Object privileges varyfrom object to object.An owner has all privilege or specific privileges on object. SQL> GRANT object_priv [(column)] ON object TO user; SQL>GRANT select, insert ON emp TO James; SQL>GRANT select ,update (e_name,e_address) ON emp TO James;
  • 10.
    CHANGE PASSWORD The DBAcreates an account and initializes a password for every user.You can change password by using ALTER USER statement. Syntax: Alter USER <some user name> IDENTIFIED BY<New password> Eg: ALTER USER James IDENTIFIED BY sam
  • 11.
    ROLE: A role isa named group of related privileges that can be granted to user.In other words, role is a predefined collection of previleges that are grouped together,thus privileges are easier to assign user. SQL> Create role custom; SQL> Grant create table, create view TO custom; SQL> Grant select, insert ON emp TO custom; Eg: Grant custom to James, Steve;
  • 12.
    REVOKE REVOKE statement isused to remove privileges granted to other users.The privileges you specify are revoked from the users. Syntax: REVOKE [privilege.. …] ON object FROM user Eg:  REVOKE create session,create table from James;  REVOKE select ,insert ON emp FROM James
  • 13.