Goodmornin,
pls can any one help me on this.
Am havin prob granting access 2 a new user of a database.Each time i run d query "Grant all to , " it says d login has no valid role,thus access denied by d dbo.
Please help .

The problem is that the user you are logged in as does not have the necessary to privilege to grant other users all privileges.
What you need to do is to connect as the sysdba (or contact your sysdba) to grant you (the user you are logged on as) "all privileges" with grant option or admin option, then you will be able to grant other users the specific or "all privileges"

Let me know if this resolves your problem

Thanx 4 d reply.
This is actually my 1st time of creating a login in an existing database,cos i was just given d responsibilty of a DBA.Though i av done several w same mthd n can't really say y dis is given me a prob.Even when i logged in as SA,its tel me dr is no user/role with this name.
Pls can u help on this.

Obviously, the user you want to grant these privileges does not exist!. You can check if it does by querying the dba_users to confirm. If it aint there, then create that user and try again.

Hello Zai,

Hope the work's coming on fine. I know you can grant a privilege or role. Personally, i don't think you can grant "all" to a user cause all is not a privilege, neither is it a role. If you want to give the person all the system and object priviledges, you can either grant DBA to the user , or create a role and grant all the priviledges (u want the person to have )to that role, then grant the role to the user.

Hope this helps you.

All the best

all these is seemin like film trick after so long

when will oracle wake up in naija?

the syntax to grant privileges or roles to a user:

1. sqlplus conn [username/password] @[database name] as SYSDBA

NOTE: YOU MUST BE CONNECTED AS A SYSDBA BEFORE YOU CAN GRANT ANY PRIVILEGE OR IF YOU ARE NOT A SYSDBA, YOU MUST HAVE BEEN GRANTED PRIVILEGES WITH ADMIN OPTION (THIS WILL ALLOW YOU TO GRANT OTHERS SYSTEM PRIVILEGES) OR WITH GRANT OPTION (TIHS WILL ALLOW YOU TO GRANT OTHERS OBJECT PRIVELEGES)

2. sqlplus> grant [privilege] | [privilege, privilege,, ] ON OBJECT
to user
NOTE: YOU MUST HAVE CREATED A USER BEFORE YOU GRANT A PRIVILEGE OTHERWISE YOU WILL RECEIVE AN ERROR

eg. sqlplus conn tom/tiger @buran as SYSDBA

, connected

sqlplus> grant SELECT, UPDATE, INSERT, DELETE ON HR
to ZAI
, grant succeded