How to create User in Oracle & assign grant revoke privileges

CREATE USER user in Oracle

Controlling User Access

In a multiple-user environment, you want to maintain security of the database access and use. With Oracle server database security, you can do the following:

Control database access.

Give access to specific objects in the database.

Confirm given and received privileges with the Oracle data dictionary.

Create synonyms for database objects.

Database security can be classified into two categories: system security and data security. System security covers access and use of the database at the system level such as the username and password, the disk space allocated to users, and the system operations that users can perform. Database security covers access and use of the database objects and the actions that those users can have on the objects.

Privileges

Privileges are the right to execute particular SQL statements. The database administrator (DBA) is a high-level user with the ability to create users and grant users access to the database and its objects. Users require system privileges to gain access to the database and object privileges to manipulate the content of the objects in the database. Users can also be given the privilege to grant additional privileges to other users or to roles, which are named groups of related privileges.

Schemas

A schema is a collection of objects such as tables, views, and sequences. The schema is owned by a database user and has the same name as that user.

System Privileges

More than 100 distinct system privileges are available for users and roles. System privileges typically are provided by the database administrator.

Typical DBA Privileges

CREATE USER user                

IDENTIFIED BY   password;

Creating a User

The DBA creates a user by executing the CREATE USER statement. The user does not have any privileges at this point. The DBA can then grant privileges to that user. These privileges determine what the user can do at the database level

CREATE USER hema

IDENTIFIED BY   hema

MANAGING SCHEMA OBJECT

OBJECTIVES:

·         Altering table

·         Adding constraint

·         Setting column unused

·         Creating index at time of table creation

·         Flashback technology

v  ALTERING TABLE:

      ADDING COLUMN:

Alter table <table name> add (<column name> <datatype> );

For eg:

SQL> alter table t45 add(employee_id number);

Table altered.

      DROPPING COLUMN:

Alter table <table name> drop column <column name>;

For eg:

SQL> alter table t45 drop column dat1;

Table altered.

      ADDING  CONSTRAINT:

SQL> ALTER TABLE N3 MODIFY ID  CONSTRAINT PK_ID PRIMARY KEY;

Table altered.

OR

SQL> ALTER TABLE N3 ADD CONSTRAINT EMAIL_UQ UNIQUE(EMAIL_ID);

Table altered.

      DROPPING CONSTRAINT:

      SQL> ALTER TABLE N3 DROP CONSTRAINT EMAIL_UQ ;

      Table altered.

ON DELETE CASCADE:

SQL> ALTER TABLE N3 ADD CONSTRAINT DEPT_FK FOREIGN KEY(DEPT_ID) REFERENCES DEPARTMENTS(DEPARTMENT_ID) ON DELETE CASCADE;

Table altered.

DEFERRRING CONSTRAINT:

·         DEFERRABLE  AND  NON-DEFERRABLE

·         INITIALLY DEFERRED AND  INITIALLY IMMEDIATE

DEFERRABLE  INITIALLY DEFERRED:

SQL> ALTER TABLE N3 ADD CONSTRAINT PK_ID PRIMARY KEY(ID) DEFERRABLE INITIALLY DEFERRED;

Table altered.

(CHEKS AFTER COMMIT)

NOTE:BY DEFAULT ALL CONSTRAINT, IF NOT SPECIFIED ,ARE INITIALLY IMMEDIATE.

TO SET A PERTICULER CONSTRAINT IMMEDIATE FOR A SESSION BEING:

SQL> SET CONSTRAINT PK_ID IMMEDIATE;

Constraint set.

TO SET ALL CONSTRAINTS IMMEDIATE FOR A SESSION BEING:

SQL> ALTER SESSION SET CONSTRAINTS=IMMEDIATE;

Session altered.

TO ALTER SEQUENCE:

ALTER SEQUENCE EMPLOYEES_SEQ INCREMENT BY 10;

SET UNUSED:

SQL> ALTER TABLE N3 SET UNUSED COLUMN DATE1;

Table altered.

NOTE:

ONCE U UNUSED A COLUMN, U CAN’T SEE IN DESC COMMAND,ALSO U CAN DROP THAT COLUMN AND ADD COLUMN WITH SAME NAME.

SQL> DESC N3;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 ID                                                 NUMBER

 EMAIL_ID                                           VARCHAR2(20)

 DEPT_ID                                            NUMBER

SQL> ALTER TABLE N3 ADD DATE1 DATE;

Table altered.

DROPPING UNUSED COLUMN

ALTER TABLE N4 DROP UNUSED COLUMN;

CREATING INDEX AT TIME OF TABLE CREATION

SQL> CREATE TABLE N4(ID NUMBER PRIMARY KEY USING INDEX (CREATE INDEX IND123 ON N4(ID)),NAME VARCHAR2(20));

Table created.

SQL> SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME='N4';

CONSTRAINT_NAME

------------------------------

SYS_C005613

SQL> SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME='N4';

INDEX_NAME

------------------------------

IND123

FLASHBACK TECHNOLOGY:

SQL> DROP TABLE N3;

Table dropped.

SQL> SELECT * FROM RECYCLEBIN WHERE ORIGINAL_NAME='N3';

OBJECT_NAME                    ORIGINAL_NAME                    OPERATION

------------------------------ -------------------------------- ---------

TYPE                      TS_NAME                        CREATETIME

------------------------- ------------------------------ -------------------

DROPTIME               DROPSCN PARTITION_NAME                   CAN CAN

------------------- ---------- -------------------------------- --- ---

   RELATED BASE_OBJECT PURGE_OBJECT      SPACE

---------- ----------- ------------ ----------

BIN$R5uBkInYSsS4IT5jaZWaBg==$0 N3                               DROP

TABLE                     USERS                          2012-04-17:10:34:49

2012-05-03:10:00:25    2342497                                  YES YES

     53369       53369        53369          8

SQL> FLASHBACK TABLE N3 TO BEFORE DROP;

Flashback complete.

SQL> SELECT * FROM RECYCLEBIN WHERE ORIGINAL_NAME='N3';

no rows selected

NOTE:

RECYCLEBIN EXIST ONLY FORM NON-SYSTEM TABLESPACES.

FOR NORMAL USER THERE IS PRIVATE RECYCLEBIN .