Re: DBA - Question - USER privilegs

Nandakumar wrote:
>
> What are the privileges that one user (yyy) should possess if he has to
> ONLY VIEW certain tables and views (owned by user xxx) and execute some
> sub programs?.
>
> 1) User yyyy should not be able to create any table in the DB.

Grant create session to yyy
(not connect, since connect is a role that includes rights to create tables etc.)

>
> 2) User should not be able to use any DML except select.

for each table the user should be able to do a select from:

   grant select on xxx.thetablename to yyy  

> 3) Some of the user-defined sub programs (owned by xxxx) that 'yyyy'
> will invoke, use dynamic pl/sql. So 'yyyy' should be able to execute
> some of the sub programs from oracle standard packages, like DBMS_SQL
> etc.

I think you got that wrong, and I think this will make you pleased: 'yyyy' only need execution-privileges on xxxx's objects!

xxxx is making stored objects, which yyyy will be executing. It is sufficient that _xxxx_ has the necessary privileges on tables etc granted directly to xxxx (or to public), yyyy _only need execute privilege on xxxx's objects!_
('dynamic sql is executed with the rights of the user, not the owner' means :
SYS is the owner of dbms_sql. But when objects created by xxxx use dbms_sql, the dynamic SQL will be executed with the rights of xxxx, not with the rights of SYS. So if yyyy executes an object created by xxxx which again calls dbms_sql, then the dynamic SQL will be executed with the privileges of xxxx)

Hence:
grant whatever necessary to xxxx.
Create objects owned by xxxx.
for each of xxxx's objects yyyy should be able to execute:

   grant execute on xxxx.theobjectname to yyyy

So what you should do is instead of granting from xxxx to yyyy you create a role, say 'NORMALUSER' and grant from xxxx to that role. Then, when you create users yyyy, zzzz etc, you just give them the role 'NORMALUSER'. Then they can execute and do the selects you described.

Regards,
Njål Received on Wed Aug 25 1999 - 04:05:54 CDT