Grant Privileges on Tables
You can grant users various privileges to tables. These privileges can be any combination of select, insert, update, delete, references, alter, and index. Below is an explanation of what each privilege means.
Privilege Description Select Ability to query the table with a select statement. Insert Ability to add new rows to the table with the insert statement. Update Ability to update rows in the table with the update statement. Delete Ability to delete rows from the table with the delete statement. References Ability to create a constraint that refers to the table. Alter Ability to change the table definition with the alter table statement. Index Ability to create an index on the table with the create index statement.
The syntax for granting privileges on a table is:
grant privileges on object to user;
For example, if you wanted to grant select, insert, update,
and delete privileges on a table called suppliers to a user name smithj,
you would execute the following statement:grant select, insert, update, delete on suppliers to smithj;You can also use the all keyword to indicate that you wish all permissions to be granted. For example:
grant all on suppliers to smithj;If you wanted to grant select access on your table to all users, you could grant the privileges to the public keyword. For example:
grant select on suppliers to public;
Revoke Privileges on Tables
Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can execute a revoke command. You can revoke any combination of select, insert, update, delete, references, alter, and index.The syntax for revoking privileges on a table is:
revoke privileges on object from user;
For example, if you wanted to revoke delete privileges on a
table called suppliers from a user named anderson, you would execute the
following statement:revoke delete on suppliers from anderson;If you wanted to revoke all privileges on a table, you could use the all keyword. For example:
revoke all on suppliers from anderson;If you had granted privileges to public (all users) and you wanted to revoke these privileges, you could execute the following statement:
revoke all on suppliers from public;
Grant Privileges on Functions/Procedures
When dealing with functions and procedures, you can grant users the ability to execute these functions and procedures. The Execute privilege is explained below:The syntax for granting execute privileges on a function/procedure is:
Privilege Description Execute Ability to compile the function/procedure.
Ability to execute the function/procedure directly.
grant execute on object to user;
For example, if you had a function called Find_Value and you
wanted to grant execute access to the user named smithj, you would
execute the following statement:grant execute on Find_Value to smithj;If you wanted to grant all users the ability to execute this function, you would execute the following:
grant execute on Find_Value to public;
Revoke Privileges on Functions/Procedures
Once you have granted execute privileges on a function or procedure, you may need to revoke these privileges from a user. To do this, you can execute a revoke command.The syntax for the revoking privileges on a function or procedure is:
revoke execute on object from user;
If you wanted to revoke execute privileges on a function
called Find_Value from a user named anderson, you would execute the
following statement:revoke execute on Find_Value from anderson;If you had granted privileges to public (all users) and you wanted to revoke these privileges, you could execute the following statement:
revoke execute on Find_Value from public;
Data Control Language (DCL) Statements
Data Control
Language Statements are used to grant privileges on tables, views, sequences,
synonyms, procedures to other users or roles.
The DCL statements
are
GRANT :Use
to grant privileges to other users or roles.
REVOKE :Use
to take back privileges granted to other users and roles.
Privileges are of
two types :
-
System Privileges
-
Object privileges
System Privileges
are normally granted by a DBA to users. Examples of system privileges are CREATE
SESSION, CREATE TABLE, CREATE USER etc.
Object privileges
means privileges on objects such as tables, views, synonyms, procedure. These
are granted by owner of the object.
Object Privileges
are
ALTER |
Change the table
definition with the
ALTER
TABLE statement. |
DELETE |
Remove rows from
the table with the
DELETE statement.
Note:
You must grant the
SELECT privilege on the
table along with the
DELETE privilege. |
INDEX |
Create an index on
the table with the
CREATE
INDEX statement. |
INSERT |
Add new rows to
the table with the
INSERT statement. |
REFERENCES |
Create a
constraint that refers to the table. You cannot grant this privilege to
a role.
|
SELECT |
Query the table
with the
SELECT statement. |
UPDATE |
Change data in the
table with the
UPDATE statement. |
|
Note:
You must grant the
SELECT privilege on the
table along with the
UPDATE privilege. |
Grant
Grant is use to
grant privileges on tables, view, procedure to other users or roles
Examples
Suppose you own emp
table. Now you want to grant select,update,insert privilege on this table to
other user “SAMI”.
grant select, update, insert on emp to sami;
Suppose you want to
grant all privileges on emp table to sami. Then
grant all on emp to sami;
Suppose you want to
grant select privilege on emp to all other users of the database. Then
grant select on emp to public;
Suppose you want to
grant update and insert privilege on only certain columns not on all the columns
then include the column names in grant statement. For example you want to grant
update privilege on ename column only and insert privilege on empno and ename
columns only. Then give the following statement
grant update (ename),insert (empno, ename) on emp to
sami;
To grant select
statement on emp table to sami and to make sami be able further pass on this
privilege you have to give WITH GRANT OPTION clause in GRANT statement like
this.
grant select on emp to sami with grant option;
REVOKE
Use to revoke
privileges already granted to other users.
For example to
revoke select, update, insert privilege you have granted to Sami then give the
following statement.
revoke select, update, insert on emp from sami;
To revoke select
statement on emp granted to public give the following command.
revoke select on emp from
public;
To revoke update
privilege on ename column and insert privilege on empno and ename columns give
the following revoke statement.
revoke update, insert on emp
from sami;
Note :You
cannot take back column level privileges. Suppose you just want to take back
insert privilege on ename column then you have to first take back the whole
insert privilege and then grant privilege on empno column.
ROLES
A role is a group of Privileges. A role is very handy in managing privileges,
Particularly in such situation when number of users should have the same set of
privileges.
For example you have four users :Sami, Scott, Ashi, Tanya in the database. To
these users you want to grant select ,update privilege on emp table,
select,delete privilege on dept table. To do this first create a role by giving
the following statement
create role clerks
Then grant privileges to this role.
grant select,update on emp to
clerks;
grant select,delete on dept to
clerks;
Now grant this clerks role to users like this
grant clerks to sami, scott,
ashi, tanya ;
Now Sami, Scott, Ashi and Tanya have all the privileges granted on clerks role.
Suppose after one month you want grant delete on privilege on emp table all
these users then just grant this privilege to clerks role and automatically all
the users will have the privilege.
grant delete on emp to clerks;
If you want to take back update privilege on emp table from these users just
take it back from clerks role.
revoke update on emp from
clerks;
To Drop a role
Drop role clerks;
LISTING INFORMATION ABOUT PRIVILEGES
To see which table privileges are granted by you to other users.
SELECT * FROM
USER_TAB_PRIVS_MADE
To see which table privileges are granted to you by other users
SELECT * FROM
USER_TAB_PRIVS_RECD;
To see which column level privileges are granted by you to other users.
SELECT * FROM
USER_COL_PRIVS_MADE
To see which column level privileges are granted to you by other users
SELECT * FROM
USER_COL_PRIVS_RECD;
To see which privileges are granted to roles
SELECT * FROM USER_ROLE_PRIVS;
No comments:
Post a Comment