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 :

SELECT	Read data
DELETE	Delete data
INSERT	Write new data
UPDATE	Modify existing data
REFERENCES	Create a table with a foreign key refers to the primary key
EXECUTE	Call stored procedures
ALL	SELECT, DELETE, INSERT, UPDATE and REFERENCES
ROLE	All 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

ROLE

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