Granting privileges to New database user

[Ainsley]

Hi All

I have installed oracle 9i on my system and need to access on my data tables to another users of database who is created right now.As i am running with DBA privilege, I have created another user without DBA privilege,how would I grant to the new user to access (select , insert, update, delete) to the tables of database.

thanks.

[Reegan]

Granting privileges to New user :

The user and privileges are closely related with each other.If you are using oracle database with DBA privilege and need to grant another user to access the object of database.

In this case, you have to create a new user and first assign him/her a new session privilege because that user can't login into the session.You can grant this type of privilege using this statement :

SQL> Grant Create session,resource to <name of the user>;

Then you can specify any privilege for his/her account.

[opaper]

TYpes of privileges:
The privileges are right to execute any statement and access the objects of another users or schema in database.It is categorized in two sections privileges provided by oracle database which are as follows:

1. System privileges
2. Object privileges

The system privileges allows to perform the task at system level.It has few privileges which is granted by DBA user.The name of the privileges are as follows :

#Create session(Used to login into new schema)
# create table
# create view
# create procedure
# sysdba
# sysoper

The Object privileges has a lot of privileges which is granted to access the objects of database. the list of those are :

select, insert, update, delete, alter, debug, flashback, on commit refresh, query,rewrite, references, all ....and etc.

[MindSpace]

Granting privilege to a user :

Before granting the privilege to any user of database,you need to confirm that what are the privileges and on which object would be granted to any user then after ready to grant and use this statement to grant the privilege.

Now I am going to show you a example demo which will grant select , insert, update, delete privilege to a user named "JOHN" using this statement :

SQL>GRANT select, update on <Object_name> to JOHN;

Where Object_name is the name of table,index,views or ........

[Modifier]

Granting ALL privileges to a user:

The ALL privilege is one of the privilege which contains the all the privileges for any object .It belongs to the category of Object privileges.After defining this ALL privilege to any user on a particular object.The user can SELECT,INSERT,UPDATE,DELETE on specified object.

Granting ALL privilege to a user :

SQL> GRANT ALL to <User_name>;