Granting/Revoking Table Access
Accessing Tables of Other Users
Table is the most commonly used form of storing user data in Oracle. A record in a table is referred as a row. Rows may be stored in any order depending on the activity on the table. Columns for a row are stored in the order which they are defined, and any trailing NULL columns are not stored.
Oracle offers a pseudo-column ROWID to uniquely identify each row in the table. ROWID is not stored explicitly stored as a column value. It is used to locate a row.
In general, the naming conventions of a table should follow :
These conventions also apply to other types of Oracle objects such as index.
CREATE TABLE <tablename> (<column name> <column datatype> ...) ..
The syntax is simplified and for simplicity it does not include the optional clauses such as PCTFREE, PCTINCREASE, and STORAGE etc which instruct Oracle how to allocate space dynamically to the table data.
For example, to create an employee table with three columns, employee number (empno), employee name (name), and salary (sal), we first give the three columns a short name, and associate them with data types. empno is an integer and must not be empty (as defined by NOT NULL); name is a variable-length character string of maximum length 50 (as defined as VARCHAR2(50)) and must not be empty; sal is real number allowing digits and must not be empty. Furthermore we define the primary key of employee table to be empno. That is, given two empno values, they can uniquely identify other table columns. The command is as follows:
SQL> CREATE TABLE employee (
empno INTEGER NOT NULL,
name VARCHAR2(50) NOT NULL,
sal REAL NOT NULL,
primary key (empno));
Note that:
- It is required to associate a data type with a column name.
- It is optional to have NOT NULL clause.
- To define a fixed-length string, use CHAR(length). For example, CHAR(50).
- For a complete list of available data types in Oracle, refer Oracle Documentation on SQL Datatypes.
- It is optional to define the primary key clause. There must be a maximum of only ONE primary key per table.
- To define a primary key consisting of multiple columns, use primary key(column I, column2 ..).
- The table to be created must NOT exist in the current database.
- A NULL value is a value that is not assigned, or not available. A NULL value is NOT the same as a zero value, or a blank space.
You can create a table and insert rows into it with merging CREATE TABLE command with AS subquery option. It matches the number of specified columns to the number of subquery columns. If no column name is specified, then subquery column names and default values will be used in creating the table.
The syntax is
CREATE TABLE <table name> (<column name>, ...) AS sub-query;
For example,
SQL> CREATE TABLE dept20
AS
SELECT id, name, salary FROM employee WHERE dept_id=20;
The above example creates a table named DEPT20 with three columns ID, NAME, and SALARY (as default from the subquery results) and inserts into this table the rows retrieved from the subquery.
For example, to add a new column dept_id into an existing employee table,
SQL> ALTER TABLE employee ADD (dept_id NUMBER(3) NOT NULL);
To add multiple columns, use comma "," to separate columns.
SQL> ALTER TABLE employee ADD (dept_id NUMBER(3) NOT NULL, comment VARCHAR2(100) NOT NULL);
Note that the default values for these newly added columns are empty. To assign the values, use UPDATE commands and use PL/SQL procedures.
For example, the following command expands the length of the COMMENT column in employee table.
SQL> ALTER TABLE employee MODIFY (comment VARCHAR2(200));
For example,
SQL> ALTER TABLE employee DROP COLUMN comment CASCADE CONSTRAINTS;
Note that 1) you cannot drop a column which is primary key; 2) you cannot drop all table columns.
SQL> DROP TABLE <table name> [CASCADE CONSTRAINTS];
For example,
SQL> DROP TABLE employee CASCADE CONSTRAINTS;
Note:
If you want to remain the table structure definition while deleting all table rows and releasing the table space used, you can use the TRUNCATE command.
For example,
SQL> TRUNCATE TABLE employee;
Comments help users to understand the semantics of table and columns. Table owners can add comments to a table or a column by using COMMENT command.
For example, the following command adds a comment to table EMPLOYEE,
SQL> COMMENT ON TABLE employee IS "Employee information";
Comments can be viewed by selecting from the data dictionary views USER_COL_COMMENTS and USER_TAB_COMMENTS;
For example, the following command displays the current table columns and type definitions.
SQL> DESC employee;
SQL> SELECT * FROM user_tables;
By default tables are accessible by the owners only. To grant table access to others users, use GRANT command.
The syntax is
GRANT privilege [, ...] ON object [,
...] TO { PUBLIC | username }
A privilege is one of SELECT, INSERT, UPDATE, DELETE, and ALL.
PUBLIC is an Oracle reserved keyword representing all users.
For example, the following command grants public access to a table
named REF_POINTS.
SQL> GRANT SELECT ON ref_points TO PUBLIC;
If you want to revoke the access to your tables from others, use REVOKE command and the syntax is similar to GRANT.
For example, the following command revokes the earlier granted access.
SQL> REVOKE SELECT ON ref_points FROM PUBLIC;
Tables belonging to other users are not in the user's schema and thus users must use the owner's name as a prefix to the table.
The syntax is:
<owner name>.<table name>
For example, the following command selects a public-accessible table called REF_POINTS owned by user SCOTT,
SQL> SELECT * FROM scott.ref_points;
Sometimes it would be convenient (especially in your PL/SQL programs) to reference a table without prefixing the name of the owner. This can be achieved by the table owner creating a public synonym on the table.
For example, user SCOTT can execute the following command to create a public accessible synonym scott_ref_points:
SQL> CREATE PUBLIC SYNONYM scott_ref_points FOR scott.ref_points;
And other users can use this synonym to access table scott.ref_points, by the following command:
SQL> SELECT * FROM scott_ref_points;