In this lesson you grant and revoke the privileges to run DELETE,
INSERT, SELECT, and UPDATE statements on the EMPLOYEE table by using the Privileges
page of the Properties view.
When you select a table in the Data Project Explorer, the Properties
view displays the different properties of the table on several different pages.
The Privileges page lets you set up privileges for users.
In this lesson,
you will grant to PUBLIC the privilege to query the table with a SELECT statement.
You will also grant and revoke an UPDATE privilege.
To grant and revoke
privileges on the EMPLOYEE table:
- Open the Privilege page of the Properties view:
- In the Data Project Explorer, select the EMPLOYEE table.
- In the Properties view, select the Privileges page.
Figure 1. The Privileges page of the Properties view
- Grant a privilege:
- Click the yellow plus symbol on the page to open the Grant Privilege
window.
- In the Grantee field, select PUBLIC.
- In the Privilege field, select SELECT and
click OK.
The table in the Privileges page now has this row:
Figure 2. Row showing the SELECT privilege for user ID PUBLIC
- Use the Grant Privilege window to grant the UPDATE privilege to
the user ID TEST.
The table in the
Privileges page has a row for the TEST grantee.
Figure 3. Row showing the UPDATE privilege for the user ID TEST
- Type Ctrl+S to save your physical data model.
- Run the GRANT statements at the data source:
- Right-click the EMPLOYEE table and open the Generate DDL wizard.
- Deselect all of the check boxes for statements except the GRANT
statements check box and the Fully qualified names check
box. Then, click Next and Next again.
- Select the Run DDL on server check box
and click Next.
- Select the connection to your data server and click Next.
- Click Finish.
Check
the Data Output view to find out whether the GRANT statements ran successfully.
You can also select the EMPLOYEE table on the data server and look at the
Privileges page of the Properties view to confirm that the privileges
were granted.
- Remove a privilege.
Now, you realize that
you do not need to grant the UPDATE privilege to the user ID TEST after
all. So, you need to revoke the privilege. To revoke a privilege:
- In the Data Project Explorer, select the EMPLOYEE table and
then open the Privileges page of the Properties view.
- Select the Revoke check box in the row
for the user ID TEST.
Tip:
You
cannot revoke a privilege by selecting a row and then clicking the red X symbol
at the top of the Privileges page. That action would delete the privilege
from the model. If the privilege no longer existed in the model, the Generate
DDL wizard would have no object to base a REVOKE statement on.
- Save the physical data model by typing CTRL+S.
- Run the REVOKE statement at the data source:
- In the Data Project Explorer, right-click the EMPLOYEE table
and open the Generate DDL wizard.
- Deselect all of the check boxes for statements except the GRANT
statements check box and the Fully qualified names check
box. Then, click Next and Next again.
The Preview DDL pane, contains the REVOKE statement that
you want, but also contains a GRANT statement. When you select the GRANT statements
on the previous page of the wizard, a statement is generated for each row
of the table on the Privileges page.
You can choose only to open
the generated DDL in an editor and remove the GRANT statement, or you can
run the DDL as it is because the GRANT statement will not matter.
- Select the Run DDL on server check box
and then finish the wizard.
As before, you can check the Data Output view to see whether the
REVOKE statement ran successfully. You can also select the EMPLOYEE table
on the data server and look at the Privileges page of the Properties view.
Now,
you can return to the Privileges page of the Properties view for the EMPLOYEE
table in your project and delete the privilege that you revoked.
Other
pages that are available in the Properties view are:
- General page
- Displays the name of the table, the data source, and the setting for the
Data Capture flag.
- Columns page
- Lists the columns in the table.
- Source Information
- Displays information about the data source.
- Documentation
- Lets you add comments to the table.
Other pages are specific to the data source that the table
is mapped to. Two additional pages are not used by Classic Data Architect,
although they appear as part of the Eclipse platform that Classic Data Architect
is built on. Those pages are Relationships and Annotation.