Granting and revoking privileges on the object

[Cayden]

I have a stored procedure which can delete some specified tables from the database.After creating the procedure,I grant it to access to some user of database and I don't remember to whom I had given the privileges and may be that user can do anything without my permission and now I need release those privileges from him/her.Tell how would i find and release that privilege.

[Praetor]

If you have granted the privilege to particular users and don't remember to whom you had given this privilege.

To find out,that to whom you have given this privilege some privilege you need to have like you have to access on some data dictionary object which is permitted by the database administrator but some views are also available for the normal user which is described below can help you to find out the user to whom and on which object you had given the privilege.

The views are as follows:

USER_TAB_PRIVS
ALL_TAB_PRIVS
ROLE_TAB_PRIVS
DBA_TAB_PRIVS

USER_ROLE_PRIVS
ALL_ROLE_PRIVS
ROLE_ROLE_PRIVS
DBA_ROLE_PRIVS

To execute all the views,you need to have some special privilege and if you are running as a normal user then you can use the view which has USER and ALL as a prefix of their name.

[Katty]

Granting privileges on the object

To grant the privileges,you have some objects to grant and the object should be created by you and you should be the owner of that object.

To grant a particular object to another user of the database is depends on the name of the privileges like what type of operation you are going to allow him.

To allow the select and update operation on a object for a particular user of the database.you have enter the below command:

GRANT SELECT,UPDATE on MY_TABLE to MARTIN;

Where MY_TABLE is the name of the object and the user is MARTIN.

[kelfro]

When we allow a user to make some desired or allowed operation for a time specified.he can do any operation and whenever the owner want to cancel the permitted privileges,the owner can release that privileges again.

The statements which is used to allow the privileges to a user.the vice versa statement is being used to revoke or release the permission on the object from the database user.

The statement used to revoke the privileges would be as follows:

REVOKE SELECT,UPDATE ON MY_TABLE FROM MARTIN;

[Zecho]

With grant option with Grant statement

The admin option is used to show that the privileges which is granted to particular user can be passed to different users of the database.Just add the with grant option word in the last of the grant statement that will take the following form:

GRANT SELECT,UPDATE ON MY_TABLE FROM MARTIN WITH GRANT OPTION;

Now the martin user can pass the same privilege (select,update) on the my_table object to another user of the database.

[Reegan]

The user to which the owner has permitted the privileges can revoke the same privilege whenever the owner need to revoke.

If the owner will revoke the privilege from the permitted user and wouldn't mention the last parameter then the privileges would be incomplete and the statement can occur the error message or the privileges would be still available with the next granted user.The revoke statement would take the following form :

REVOKE SELECT,UPDATE ON MY_TABLE FROM MARTIN WITH GRANT OPTION

The permission of allowing to other database user to access on this MY_TABLE object would be revoked and no further transaction can be committed with this object.