| Restricted User Role [message #158931] |
Wed, 15 February 2006 02:20  |
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  |
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 |
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 |
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!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
