Home » RDBMS Server » Security » Restricted User Role
Restricted User Role [message #158931] Wed, 15 February 2006 02:20 Go to next message
asangapradeep
Messages: 128
Registered: October 2005
Location: UK
Senior Member
Hi,
I want to create a role when assigned to users would allow them only to do select and insert operations on the tables. (Either using direct SQL statements or via triggers, functions and stored procedures.) table altering operations or drop tables will not be allowed.

What are the roles and system privileges I must use to create this new role?

Thank you

Re: Restricted User Role [message #158967 is a reply to message #158931] Wed, 15 February 2006 04:43 Go to previous messageGo to next message
ramsat
Messages: 49
Registered: November 2005
Member
Hai,


this is how u can achieve

Creating a role
---------------
create role myrole;

Granting select and insert only to role-myrole
----------------------------------------------
grant select on any table to myrole;
grant insert on any table to myrole;



Creating a user and assigning him the permisssion
--------------------------------------------------
create user newuser identified by newuser;
grant connect to newuser;
grant myrole to newuser;

Thats it

Bye!

Ramesh
Re: Restricted User Role [message #158985 is a reply to message #158967] Wed, 15 February 2006 05:38 Go to previous messageGo to next message
asangapradeep
Messages: 128
Registered: October 2005
Location: UK
Senior Member
Hi Ramesh,

Thanks for your quick reply. I’ve tried this but it doesn't work as I expected. It doesn't allow users to create any table but still users can drop a table. I want to prevent such operation as dropping a table or altering a table. Any help on that?

thank you
Re: Restricted User Role [message #158994 is a reply to message #158985] Wed, 15 February 2006 06:36 Go to previous message
ramsat
Messages: 49
Registered: November 2005
Member
Hai,

A little change will help in this case




while creating the role for my role use the below

sql> grant connect,select any table,insert any table to myrole;

and then use

SQL> drop user x;

User dropped.

SQL> create user x identified by x;

User created.

SQL> grant myrole to x;

Grant succeeded.


if the user already available having table created then he may have a chance of droping as the objects of his schema is given pernmmission


come out if u have any doubt


Thanks
Bye!
Previous Topic: Problem with aud$ table
Next Topic: Oracle secure views in PL/SQL
Goto Forum:
  


Current Time: Mon Dec 06 21:22:49 CST 2021