Example 1: Redeclaring a database to make ACL changes take effect
This example illustrates that GRANT and REVOKE statements do not
take effect until you attach to the database again.
SQL> -- Display the ACL for the EMPLOYEES table:
SQL> SHOW PROTECTION ON TABLE EMPLOYEES;
Protection on Table EMPLOYEES
(IDENTIFIER=[sql,warring],ACCESS=SELECT+INSERT+UPDATE+DELETE+SHOW+CREATE+
ALTER+DROP+DBCTRL+DBADM+REFERENCES)
(IDENTIFIER=[*,*],ACCESS=SELECT+INSERT+UPDATE+DELETE+ALTER+DROP)
SQL>
SQL> -- User warring, the owner of the database, denies
SQL> -- herself INSERT access to the EMPLOYEES table:
SQL> REVOKE INSERT ON TABLE EMPLOYEES FROM warring;
SQL> COMMIT;
SQL>
SQL> -- The SHOW PROTECTION statement displays the change
SQL> -- (INSERT is no longer part of the ACL entry
SQL> -- for warring):
SQL> SHOW PROTECTION ON TABLE EMPLOYEES;
Protection on Table EMPLOYEES
(IDENTIFIER=[sql,warring],ACCESS=SELECT+UPDATE+DELETE+SHOW+CREATE+ALTER+
DROP+DBCTRL+DBADM+REFERENCES)
(IDENTIFIER=[*,*],ACCESS=SELECT+INSERT+UPDATE+DELETE+ALTER+DROP)
SQL>
SQL> -- But the change is not yet effective.
SQL> -- User warring can still store rows in the EMPLOYEES table:
SQL> INSERT INTO EMPLOYEES (EMPLOYEE_ID) VALUES ('99999');
1 row inserted
SQL> SELECT EMPLOYEE_ID
cont> FROM EMPLOYEES
cont> WHERE EMPLOYEE_ID = '99999';
EMPLOYEE_ID
99999
1 row selected
SQL> ROLLBACK;
SQL>
SQL> -- To make the ACL change take effect, issue another ATTACH statement
SQL> -- to override the current declaration:
SQL> ATTACH 'FILENAME personnel';
This database context has already been declared.
Would you like to override this declaration (No)? Y
SQL>
SQL> -- Now warring cannot insert new rows into the EMPLOYEES table:
SQL> INSERT INTO EMPLOYEES (EMPLOYEE_ID) VALUES ("99999");
%RDB-E-NO_PRIV, privilege denied by database facility
SQL>
SQL> -- A GRANT statement gives all privileges back to warring:
SQL> GRANT ALL ON TABLE EMPLOYEES TO warring;
SQL> COMMIT;
Example 2: Creating an ACL with an SQL command file
The following SQL command file creates an ACL for the default
database by specifying the default alias RDB$DBHANDLE. It uses
two general guidelines for ordering ACL entries:
o The less restrictive the user identifier, the lower on the
list that ACL should go.
o The more powerful the privilege, the higher on the list that
ACL should go.
Because SQL reads the list from top to bottom, you should place
entries with more specific identifiers earlier, and those with
more general ones later. For example, if you place the entry with
the most general user identifier, [*,*], first in the list, all
users match it, and Oracle Rdb grants or denies all the access
rights specified there to all users.
Similarly, if you place the general entry [admin,*] before
the specific entry [admin,ford], SQL matches user [admin,ford]
with [admin,*] and denies the access rights INSERT, UPDATE, and
DELETE, which user [admin,ford] needs.
-- Database Administrator -- needs all privileges.
--
GRANT ALL
ON DATABASE ALIAS RDB$DBHANDLE
TO [group2,adams]
POSITION 1;
-- Assistant -- needs to be able to use data definition statements.
--
GRANT SELECT,CREATE,ALTER,DROP
ON DATABASE ALIAS RDB$DBHANDLE
TO [group2,clark]
POSITION 2;
-- Operator -- needs to be able to perform database maintenance tasks.
--
GRANT SELECT, ALTER, DBADM
ON DATABASE ALIAS RDB$DBHANDLE
TO [group2,lawrence]
POSITION 3;
-- Security Administrator -- needs to specify and show security events
-- audited for a database and review the audit trail.
--
GRANT SECURITY
ON DATABASE ALIAS RDB$DBHANDLE
TO [group2,davis]
POSITION 4;
-- Manager -- needs to be able to use all data manipulation statements.
--
GRANT SELECT,INSERT,UPDATE,DELETE
ON DATABASE ALIAS RDB$DBHANDLE
TO [admin,smith]
POSITION 5;
-- Secretary -- needs to be able to read, write, and delete data.
-- No access to data definition or maintenance.
--
GRANT SELECT,INSERT,UPDATE,DELETE
ON DATABASE ALIAS RDB$DBHANDLE
TO [admin,ford]
POSITION 6;
-- Programmers -- need to perform data definition and data manipulation
-- on some tables and constraints to test application programs.
--
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,ALTER,DROP,REFERENCES
ON DATABASE ALIAS RDB$DBHANDLE
TO PROGRAMMERS
POSITION 7;
-- Clerks -- need to be able only to read data. No access to modify, erase,
-- store, data definition, or maintenance statements.
--
GRANT SELECT
ON DATABASE ALIAS RDB$DBHANDLE
TO [admin,*]
POSITION 8;
-- Deny access to all users not explicitly granted access to the database.
--
REVOKE ALL
ON DATABASE ALIAS RDB$DBHANDLE
FROM PUBLIC
POSITION 9;
Example 3: Granting column access and denying table access
You need the REFERENCES privilege to define constraints that
affect a particular column. You need the UPDATE privilege to
update data in a column. A user with the UPDATE privilege for
a table automatically receives the UPDATE privilege for all
columns in that table. To update a column, you must have the
UPDATE privilege either for the column or for the table. However,
a database administrator can restrict UPDATE privileges by
defining them only for columns users should be able to update,
and then removing the UPDATE privilege from the table entry.
Because current salary is sensitive information, you might want
to restrict the ability to update this amount.
The following example prevents user [admin,ford] from updating
any column in the SALARY_HISTORY table except SALARY_START and
SALARY_END. For instance, user [admin,ford] cannot update the
SALARY_AMOUNT column.
SQL> GRANT UPDATE ON COLUMN SALARY_HISTORY.SALARY_START
cont> TO [admin,ford];
SQL> GRANT UPDATE ON COLUMN SALARY_HISTORY.SALARY_END
cont> TO [admin,ford];
SQL> --
SQL> REVOKE UPDATE ON TABLE SALARY_HISTORY FROM [admin,ford];
SQL> --
SQL> COMMIT;
SQL> --
SQL> SHOW PROTECTION ON TABLE SALARY_HISTORY;
Protection on Table SALARY_HISTORY
(IDENTIFIER=[grp2,jones],ACCESS=SELECT+INSERT+UPDATE+DELETE+SHOW+CREATE+
ALTER+DROP+DBCTRL+DBADM+REFERENCES+SECURITY+DISTRIBTRAN)
(IDENTIFIER=[*,*],ACCESS=NONE)
SQL> --
SQL> SHOW PROTECTION ON COLUMN SALARY_HISTORY.SALARY_START;
Protection on Column SALARY_HISTORY.SALARY_START
(IDENTIFIER=[admin,ford],ACCESS=UPDATE)
(IDENTIFIER=[*,*],ACCESS=NONE)
Example 4: Granting SELECT Privilege to All Users for a Sequence
SQL> SHOW PROTECTION ON SEQUENCE EMPID
Protection on Sequence EMPID
(IDENTIFIER=[RDB,STRAUTS],ACCESS=SELECT+SHOW+ALTER+DROP+DBCTRL)
(IDENTIFIER=[*,*],ACCESS=NONE)
SQL> GRANT SELECT ON SEQUENCE EMPID TO PUBLIC;
SQL> SHOW PROTECTION ON SEQUENCE EMPID;
Protection on Sequence EMPID
(IDENTIFIER=[RDB,STRAUTS],ACCESS=SELECT+SHOW+ALTER+DROP+DBCTRL)
(IDENTIFIER=[*,*],ACCESS=SELECT)
Example 5: Granting INSERT ON TABLE Privilege to a Role
SQL> SHOW PROTECTION ON TABLE JOBS
Protection on Table JOBS
(IDENTIFIER=[250,254],ACCESS=SELECT+INSERT+UPDATE+DELETE+SHOW+CREATE+ALTER+
DROP+DBCTRL+DBADM+REFERENCES)
(IDENTIFIER=PUBLIC,ACCESS=SELECT+INSERT+UPDATE+DELETE+SHOW+CREATE+ALTER+DROP
+DBADM+REFERENCES)
SQL> CREATE ROLE ADMINISTRATOR;
SQL> GRANT INSERT ON TABLE JOBS TO ADMINISTRATOR AFTER [250,254];
SQL> SHOW PROTECTION ON TABLE JOBS
Protection on Table JOBS
(IDENTIFIER=[250,254],ACCESS=SELECT+INSERT+UPDATE+DELETE+SHOW+CREATE+ALTER+
DROP+DBCTRL+DBADM+REFERENCES)
(IDENTIFIER=ADMINISTRATOR,ACCESS=INSERT)
(IDENTIFIER=PUBLIC,ACCESS=SELECT+INSERT+UPDATE+DELETE+SHOW+CREATE+ALTER+DROP
+DBADM+REFERENCES)
Example 6: Allowing All Access to a User
SQL> -- Allow all access to user JAIN
SQL> GRANT SELECT ON DATABASE ALIAS * to jain;
SQL> GRANT SELECT ON TABLE * to jain;
SQL> GRANT EXECUTE ON MODULE * to jain;
SQL> GRANT EXECUTE ON PROCEDURE * to jain;
SQL> GRANT EXECUTE ON FUNCTION * to jain;
Example 7: Automatically Creating a User While Granting
Privileges
SQL> ATTACH 'FILENAME MF_PERSONNEL.RDB';
SQL> SHOW USERS
Users in database with filename mf_personnel.rdb
tsmith
jstuart
SQL> GRANT ALL ON DATABASE ALIAS RDB$DBHANDLE TO CDAY;
%RDB-W-META_WARN, metadata successfully updated with the reported warning
-RDMS-W-PRFCREATED, some users or roles were created
SQL> SHOW USERS
Users in database with filename mf_personnel.rdb
tsmith
jstuart
cday