ORACLE not only provides several diff. objects for
customized database management but also it supports user concept or schema
concept to protect those objects from invalid users.
No user can be created or manipulated from a
general user rather than user creation and user manipulation is otherwise known
as DBA user.
SYNTAX TO CREATE AN USER
Create user <u_name>
Identified by <pword>;
SYNTAX
TO DELETE AN EXITING USER:
Drop user<u_name>;
Above command is required to delete an empty user.
Drop user<u_name> cascade;
Above command is required to delete a filled user.
SYNTAX
TO LOCK OR UNLOCK AN USER:
Alter user<u_name>
Account lock/unlock;
SYNTAX
TO CHANGE THE PASSWORD OF AN USER:
Alter user <u_name>
IDENTIFIED BY <NEWP_NAME>;
Once
create a user in ORACLE database. User can write the following code
SQL>select usernames from DBA_USERS;
NOTE :
DBA_USERS is a supper user dictionary which holds
the information regarding an user type. General user dictionary starts with a
keyword user. Whereas supper user dictionary starts with a keyword DBA_
NOTE:
Connect or conn is a SQL*PLUS command responsible
to jump from one user to another without closing.
--> User
can able to change the password of a user using PASSWORD SQL*PLUS command.
WORKING WITH PRIVILEGE:
Whenever a user is created in ORCLE DB user is not
going to be activated unless and until some privilege are assigned in it.
1.
SYSTEM PRIVILEGE
2.
OBECT PRIVILEGE
System privileges are those which are granted
individual objects what can be done by the object and what cannot be.
The frequently used system privileges are
Create
table
Create
view
Similarly
synonym, sequence, type, index and procedure.
NOTE:
Among the above system privileges create session
is privilege is the important privilege which is responsible to activate a user
by creating session ID.
Frequently used object privileges are select,
update, insert, delete, execute.
Above privilege can either be assigned to an user
or object likewise revoked from an user or object.
To
do so DCL provides two commands such as
1.
Grant
2.
Revoke
Note:
Revoke create session is also helpful in locking
the user
SYNTAX
TO GRANT SYTEM PRIVILEGE TO NA USER
GRANT SP1, SP2……N
TO <U_NAME> |ROLE;
SYNTAX TO REVOKE SYSTEM PRIVILEGE FROM AN USER:
REVOKE SP1, SP2,………….N
FROM <U_NAME>|ROLE;
SYNTAX
TO OBJECT PRIVILEGE TO AN USER:
GRANT OP1,OP2,……………..N
ON<O_NAME>
TO<U-NAME>
SYNTAX
TO REVOKE OBJECT PRIVILEGE TO AN USER:
REVOKE OP1,OP2,……N
FROM <U_NAME>
NOTE:
system
privilege are those privileges which are granted or revoked only from supper
user where as object privilege are those which are granted or revoked from that
user who (‘’’’’’’’’’’’’’’’) the object.
Exm:-
SQL>
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO OURORISSA;
SQL>
REVOKE CREATE SESSION FROM ORISSA;
Above
command can only be executed from the system user.
WORKING WITH ROLE:
Role is basically a container which contains
system privilege to be granted to one user or several other users. Role can
only be created in system user and only contain system privileges.
SQL>
CREATE ROLE OURROLE;
Above
e.g. will create a role physically in system user.
SQL>
grant create session, create table, create view to ourrole
Above
eg will assign the privileges to ourrole.
