Managing users in database

[LaMarcus]

I am running with oracle 9i enterprise edition and can you explore me some more information about user management and related tools that should be provided by default from oracle and the different types of privileges that is given due to user creation.Just tell me about this also.I am a beginner in oracle and need to learn user management.

Please help me...

[Praetor]

If you are running with oracle enterprise edition and willing to know about user management. You should first know the use of related tools to manage the users.

If you are working as a user,there are some limited privileges would be given to you according your position and use of database.But if you are having the privilege of DBA, you are only the user of database that is gives all the command to manage the database.

[Katty]

A Database Administrator is responsible to create the user for database and specific privilege to use the database access.You can limit the access of resources for user before and after creation of their session using some protected statements.

The creation of user is done by this sentence :

SQL> CREATE USER <user_name> IDENTIFIED BY <password>
                   DEFAULT TABLESPACE <tablespace_name> 
                       QUOTA 100M ON <tablespace_name>;

After creation of the user, the user can use the 100 Mb space of allocated tablespace .

[kelfro]

Granting privilege :

You can grant the sufficient privileges to the user of database.After the creation of the user. The statement given below would be used to give the privilege to the user and one thing more, A normal user can give the permission to access on the database to another user if he/she is already permitted to give the access with sufficient privileges.

I am going to give the privilege with SELECT,INSERT,DELETE,UPDATE to the "John" user of the database which is created right now.

SQL> GRANT SELECT,INSERT,UPDATE,DELETE ON <Object_name> TO JOHN;

Where Object_name requires {table_name,index_name,view_name,etc...}

[Zecho]

You can remove the user of the database when the user is no longer needed for database. There are some option provided by oracle database which will revoke all the permission and contents held by user earlier .

Using this command, you can delete the user of database -

SQL> DROP USER <User_name> CASCADE;

Where the CASCADE option will remove all the object and contents of the database which is held by that user.