Important topics in Oracle

General Topics in Oracle:       

1. How to create a user or schema in Oracle ?
2. How to GRANT permissions to an user ?
3. How to REVOKE permissions to an user ?

4. How to change the password ?

5. How to Lock an Account or User ?

6. How to unlock an account / user ?

7. How to DROP an account ?

8. How to view the User logged into the Server ?

Here are the Answers for the Above questions:

1. How to create a user/ schema in Oracle ?

Only DBA can create an User / Schema.

Syntax:

                Create user <username>  identified by <password>

     

2. How to GRANT permissions to an user ?

Grant has the following perceptions:

-          CONNECT                  Can create tables, views, functions, procedures.

-          RESOURCE                                Can create triggers, clusters etc..

-          DBA                              Can create users, grant permissions etc…

-          SELECT                        Can read the table

-          INSERT                        Can insert data into the table

-          UPDATE                      Can modify the data

-          DELETE                       Can delete the data

-          ALTER                          Can alter the data definition

-          REFERENCES            Can create constraints

-          INDEX                          Can create Indexes.

Ex:

                GRANT  CONNECT     to XXX;

                GRANT  RESOURCE   to XXX;

                GRANT  DBA                 to XXX;

                GRANT  SELECT,INSERT,UPDATE,DELETE          to XXX;

                GRANT  ALTER             to XXX;

                GRANT  REFERENCES     to XXX;

                GRANT  INDEX             to XXX;

      

3. How to REVOKE permissions to an user ?

Ex:

                REVOKE  CONNECT     to XXX;

                REVOKE  RESOURCE   to XXX;

                REVOKE  DBA              to XXX;

                REVOKE  SELECT,INSERT,UPDATE,DELETE          to XXX;

                REVOKE  ALTER             to XXX;

                REVOKE  REFERENCES     to XXX;

                REVOKE  INDEX                          to XXX;

     

4. How to change the password ?

Password can be changed by User and DBA.

ALTER    USER   XXX    IDENTIFIED    BY   PASSWORD;

      

5. How to Lock an account or user ?

ALTER    USER    XXX   ACCOUNT   LOCK ;

     

6. How to unlock an account or user ?

ALTER    USER    XXX   ACCOUNT   UNLOCK

      

7. How to DROP an account ?

- This permission is given only to DBA.

DROP   USER    XXX   [CASCADE]

Cascade is an Optional, its required when there are any tables/ views/ procedures etc.. created by the user.

     

8. How to view the User logged into the Server ?

SHOW    USER ;