Friday, 26 February 2010

Access Privileges ( Interbase Tutorial # 14 )

So far we have learnt how to develop a database. But only the owner of the database ( in this case NEW_USER ) and SYSDBA can access the database. Some other users may use the database. We can grant some privileges to these users, so they can retrieve or manipulate the table.

Before we start learning about access privileges, you need to create a new user. You can see how to create a new user in my first tutorial. Let’s name the new user OTHER_USER, and you can give any password you want for this user.

Now there are three users in your interbase server : SYSDBA, NEW_USER and OTHER_USER. SYSDBA can access the database created by NEW_USER, but OTHER_USER can’t. If you want OTHER_USER to retrieve or manipulate your database, you have to give some privileges to this user.

You can see the available access privileges in the table below :

SELECTRead data
DELETEDelete data
INSERTWrite new data
UPDATEModify existing data
REFERENCESCreate a table with a foreign key refers to the primary key
EXECUTECall stored procedures
ROLEAll privileges assigned to the ROLE

Giving Access privileges using GRANT

The syntax is :

    GRANT privilege(s) ON table TO user

Example : We want to give SELECT privilege on ‘employee’ table to OTHER_USER, so OTHER_USER can retrieve the database. The statement will be :

    grant select on employee to other_user

With Grant Option

We can use WITH GRANT OPTION clause to allow the user to give the privileges we’ve given to other users.

Example : 

    grant select, delete on employee to other_user
    with grant option

Revoking Privileges

We can also revoke the privileges we’ve given to a user. The syntax is :

    REVOKE privilege(s) ON table FROM user

Example : We want to revoke SELECT privilege on ‘employee’ table from OTHER_USER. The statement will be :

    revoke select on employee from other_user


If you want to give one or more privileges to some user using GRANT, you will type a lot of GRANT statement. You can create a ROLE to reduce the statement you type. A ROLE can contain one or more privileges. Follow these steps below to create and grant a ROLE to users :

- Define a ROLE 
   The syntax : CREATE ROLE role_name
- Inserting privileges to the ROLE
   The syntax : GRANT privileges(s) ON table TO role_name
- Granting ROLE to users
   GRANT role_name TO user(s)

Example :

    create ROLE myrole

    grant select, update, insert, delete on employee to myrole

    grant myrole to other_user

Go to Previous Tutorial or Next Tutorial


Post a Comment

These links are part of a pay per click advertising program called Infolinks. Infolinks is an In Text advertising service; they take my text and create links within it. If you hover your mouse over these double underlined links, you will see a small dialog box containing an advertisement related to the text. You can choose to move the mouse away and go on with your browsing, or to click on the box and visit the relevant ad. Click here to learn more about Infolinks Double Underline Link Ads.