SQL query to grant permission

I want sql querys for following scenarios

Owner of the compant wants to create 5 users,3 in following fashion
1) 2 -Must have authority of admin
2) 2- Normal user
3) 1 - User whose password is blocked for 15 days

What kind of users you are talking about ?

Administrator
Normal User
Normal User whose account is blocked for 15 days
any scenario you can consider
Example -:College database
Teacher has right to upddate and delete marks and many other right like admin
student can only log in and can see results and raise objection.

I believe what debasisdas is asking (as I would) about kind of user is:
e.g.
Windows user
or
Database user
or
Application user
or
???

Its application user

Then you just need a column in your APP_USERS table for user type.
Also, from a schema design perspective, you should have a USER_TYPES table.

USER_TYPES fields
USER_TYPE_ID INTEGER PK,
USER_TYPE_DESCRIPTION VARCHAR2(100)

APP_USERS or whatever you have called it
All your current fields
USER_TYPE_ID INTEGER FK to USER_TYPES

Now you simply add the USER_TYPE_ID field to your SQL and use an if..then or case statement in your
application code to determine what the user can do.

Edit
-----
I forgot, you will also need a DATE_ADDED field if you need to deal with 15 day time periods.
Clive

Thanks

What if its database user? Do I need to use grant command to assign rights.

For any normal user, of course.
You will have to consult the documentation (or other responses here) for the specifics of any super users
like schema owners, administrators etc.

Clive

with your reply.
Can you elaborate in simple words?

I will try
However, it looks as if you could benefit from reading some of the Oracle documentation on users and grants.

(all subject to being corrected as I am going from memory)
BASIC
Any user who creates an object (TABLE, PROCEDURE, VIEW, SEQUENCE etc.) is the
only user who can access that object unless that user GRANTS permission to other users by name or to PUBLIC.
Permissions can also be granular. e.g. You could grant someone SELECT and UPDATE permission on a table but not INSERT and DELETE.

As I understand it, there are also certain super users like DBA and system manager who have rights over everything whether they created it or not.

There is a lot more to the whole subject.
Lots of rules I have not mentioned and things I have mentioned that probably have all sorts of additional variants.

For your purposes it is probably sufficient to know that you have to grant permission of some sort on objects you create for any other regular user to be able to access them.

Clive