Microsoft.NET

……………………………………………….Expertise in .NET Technologies

Data Control Language (DCL) – Part XV

Posted by Ravi Varma Thumati on October 15, 2009

Data control language provides users with privilege commands. The owner of the database object has the authority over them.

Following commands are categorized as Data Control commands

  • Grant command
  • Revoke command

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:

Privilege

Description

Execute

Ability to compile the function/procedure.
Ability to execute the function/procedure directly.

The syntax for granting execute privileges on a function/procedure is:

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;

Roles

A role is a set or group of privileges that can be granted to users or another role. This is a great way for database administrators to save time and effort.

Creating a Role

To create a role, you must have CREATE ROLE system privileges.

The syntax for creating a role is:

CREATE ROLE role_name
[ NOT IDENTIFIED |
IDENTIFIED {BY password | USING [schema.] package | EXTERNALLY | GLOBALLY } ;

Note: If both the NOT IDENTIFIED and IDENTIFIED phrases are omitted in the CREATE ROLE statement, the role will be created as a NOT IDENTIFIED role.

The role_name phrase is the name of the new role that you are creating. This is how you will refer to the grouping of privileges.

The NOT IDENTIFIED phrase means that the role is immediately enabled. No password is required to enable the role.

The IDENTIFIED phrase means that a user must be authorized by a specified method before the role is enabled.

The BY password phrase means that a user must supply a password to enable the role.

The USING package phrase means that you are creating an application role – a role that is enabled only by applications using an authorized package.

The EXTERNALLY phrase means that a user must be authorized by an external service to enable the role. An external service can be an operating system or third-party service.

The GLOBALLY phrase means that a user must be authorized by the enterprise directory service to enable the role.

For example:

CREATE ROLE test_role;

This first example creates a role called test_role.

CREATE ROLE test_role
IDENTIFIED BY test123;

This second example creates the same role called test_role, but now it is password protected with the password of test123.

Grant Privileges (on Tables) to Roles

You can grant roles 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 role_name

For example, if you wanted to grant select, insert, update, and delete privileges on a table called suppliers to a role named test_role, you would execute the following statement:

grant select, insert, update, delete on suppliers to test_role;

You can also use the all keyword to indicate that you wish all permissions to be granted. For example:

grant all on suppliers to test_role;

Revoke Privileges (on Tables) to Roles

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 role_name;

For example, if you wanted to revoke delete privileges on a table called suppliers from a role named test_role, you would execute the following statement:

revoke delete on suppliers from test_role;

If you wanted to revoke all privileges on a table, you could use the all keyword. For example:

revoke all on suppliers from test_role;

Grant Privileges (on Functions/Procedures) to Roles

When dealing with functions and procedures, you can grant roles the ability to execute these functions and procedures. The Execute privilege is explained below:

Privilege

Description

Execute

Ability to compile the function/procedure.
Ability to execute the function/procedure directly.

The syntax for granting execute privileges on a function/procedure is:

grant execute on object to role_name;

For example, if you had a function called Find_Value and you wanted to grant execute access to the role named test_role, you would execute the following statement:

grant execute on Find_Value to test_role;

Revoke Privileges (on Functions/Procedures) to Roles

Once you have granted execute privileges on a function or procedure, you may need to revoke these privileges from a role. 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 role_name;

If you wanted to revoke execute privileges on a function called Find_Value from a role named test_role, you would execute the following statement:

revoke execute on Find_Value from test_role;

Granting the Role to a User

Now, that you’ve created the role and assigned the privileges to the role, you’ll need to grant the role to specific users.

The syntax to grant a role to a user is:

GRANT role_name TO user_name;

For example:

GRANT test_role to smithj;

This example would grant the role called test_role to the user named smithj.

The SET ROLE statement

The SET ROLE statement allows you to enable or disable a role for a current session.

When a user logs into Oracle, all default roles are enabled, but non-default roles must be enabled with the SET ROLE statement.

The syntax for the SET ROLE statement is:

SET ROLE
( role_name [ IDENTIFIED BY password ]
| ALL [EXCEPT role1, role2, … ]
| NONE );

The role_name phrase is the name of the role that you wish to enable.

The IDENTIFIED BY password phrase is the password for the role to enable it. If the role does not have a password, this phrase can be omitted.

The ALL phrase means that all roles should be enabled for this current session, except those listed in the EXCEPT phrase.

The NONE phrase disables all roles for the current session. (including all default roles)

For example:

SET ROLE test_role IDENTIFIED BY test123;

This example would enable the role called test_role with a password of test123.

Setting a role as DEFAULT Role

A default role means that the role is always enabled for the current session at logon. It is not necessary to issue the SET ROLE statement. To set a role as a DEFAULT role, you need to issue the ALTER USER statement.

The syntax for setting a role as a DEFAULT role is:

ALTER USER user_name
DEFAULT ROLE
( role_name
| ALL [EXCEPT role1, role2, … ]
| NONE );

The user_name phrase is the name of the user whose role you are setting as DEFAULT.

The role_name phrase is the name of the role that you wish to set as DEFAULT.

The ALL phrase means that all roles should be enabled as DEFAULT, except those listed in the EXCEPT phrase.

The NONE phrase disables all roles as DEFAULT.

For example:

ALTER USER smithj
DEFAULT ROLE
test_role;

This example would set the role called test_role as a DEFAULT role for the user named smithj.

ALTER USER smithj
DEFAULT ROLE
ALL;

This example would set all roles assigned to smithj as DEFAULT.

ALTER USER smithj
DEFAULT ROLE
ALL EXCEPT test_role;

This example would set all roles assigned to smithj as DEFAULT, except for the role called test_role.

Dropping a Role

It is also possible to drop a role. The syntax for dropping a role is:

DROP ROLE role_name;

For example:

DROP ROLE test_role;

This drop statement would drop the role called test_role that we defined earlier.

Change a user’s password in Oracle

Question:  How do I change the password for a user in Oracle?

Answer:  To change a user’s password in Oracle, you need to execute the alter user command.

The syntax for changing a password is:

alter user user_name identified by new_password;

user_name is the user whose password you wish to change.

new_password is the new password to assign.

For example:

If you wanted to reset the password for a user named smithj, and you wanted to set the new password to autumn, you would run the following command:

alter user smithj identified by autumn;

Advertisements

5 Responses to “Data Control Language (DCL) – Part XV”

  1. dbametrix said

    Hi,

    Very interesting. Nice article and discussion.
    Thanks a lot for same.

    Regards,
    Gitesh Trivedi
    http://www.dbametrix.com

  2. sailu said

    Excellent topic it is!

  3. sailu said

    Sir ,

    you was mention Default word..! this word may be used at table space level and admin roles level according to my sence..! when i was saw your article ..! just i want to know what is the default table space? how it will effect on tables? and also genunely company’s will not provide that much level to go that level? how we will know ? who are schema level? may i know is it possible to know like that? if it has chance how to create roles? and their permissions? and if we create like that roles..! what are the permissions he have? please dont mind sir! this is my curiacity!
    …sailu

  4. sailu said

    Sir,

    Sir your article’s so nice..!we are watching your efforts on that! we dont have that much time totally watching!
    but it is too good! here your commitment is excellent to give best your levels!
    sir
    just i want according to your blog in Data base concepts
    at one page level between Db2,mysql,sqlserver,oracle..! if it is possible to give from basic levels to all at one page!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: