When you create a table with a CREATE TABLE statement, you become the owner of that table and you have full privileges for the table (such as SELECT, INSERT, UPDATE, DELETE, GRANT, REVOKE, etc.). Nobody else has any privileges on your newly created table. If others are to be given access to the table, you must explicitly grant privileges to them using the GRANT statement.When you create a view with a CREATE VIEW statement, you own the view, but you can not create the view unless you already have SELECT privileges on all source tables for the view. Since you already have SELECT privileges on the view source tables, you now have SELECT privileges on the view you created. You do not have INSERT, UPDATE, and DELETE privileges for the view you created unless you have previously been granted INSERT, UPDATE, and DELETE privileges for the source tables of your view.

SELECT privileges on a VIEW can be granted to others without giving them any privileges on the source tables of the VIEW. You can effectively limit the columns and/or rows in the view for security reasons, denying other users access to the source table columns and rows. A view can also combine columns from two or more source tables, making the columns appear to be in one table.

If a user must be able to update data in a VIEW, then that user must be given INSERT, UPDATE, and/or DELETE privileges to the source tables for the VIEW.

How does SQL know to associate your user-ID with the statement or program being executed?When you sign-on to an interactive SQL computer system, you generally enter a user-ID and a password. Once you have signed-on to the system, your user-ID has been established until you sign-off or cancel the computer session.

On a database server, when you sign-on, your user-ID is established by the work station you are using.
If you use a query program or a forms-based system, again your sign-on has established your user-ID.

When several people have identical needs, you can create a user-ID for a user group. If everyone in the payroll department can perform the same commands on the PAYROLL_TBL, then they can be given the same user-ID and password or they can be assigned their own user-ID and password.

For example:
Users from the payroll department may have user-IDs of PRUSER.
Users from the accounts payable department may have user-IDs of APUSER.
The president of the company may have a user-ID of WILLIAMD.
The database administrator may have a user-ID of K3R7CFC or DBA1.
> DB2 allows a single user to have a primary user-ID, and multiple secondary user-IDs.

> SQL Server and Sybase allow a single user to have both a single user-ID and a group user-ID.

This starts Data Control Language (DCL) commands. See Table Creation and SQL is DML, DDL, & DCL.
While a DataBase Administrator (DBA) will normally perform the DDL and DCL functions, you need to know how they work so that you can convey your needs to the DBA.Security is a major part of the RDBMS systems. SQL makes access to data very easy, so access must be scrutinized carefully for company and corporate databases. Database objects must be entered by an individual called a user and, to do this, they must be given maintenance privileges.

Each user of an SQL database is assigned a user-ID. This is a short name that identifies this individual as a person or as a member of a group. You must have a user-ID before you can access the database. The user-ID with its associated privileges determines what the user can or can not do. In a production database, a database administrator (DBA) assigns the user-ID.

The SQL standard refers to the authorization-ID. Here we refer to the user-ID as the identification used to obtain authorization through privileges to access database objects.

> The SQL1 standard allowed the user-ID to be up to 18 characters without spaces.

> DB2 allows a user-ID of up to 8 characters and uses the MVS/TSO user name.

> Informix and Ingres use the host computer’s operating system UNIX user name.

> SQL Server and Sybase allow a user-ID of up to 30 characters.

> For portability between systems, it is suggested that the user-ID be between 1 and 8 characters.

Format:DROP SCHEMA schema_name {RESTRICT | CASCADE};
This command will remove the schema.

If RESTRICT is specified, the schema will not be deleted if all objects in the schema have not been dropped.

If CASCADE is specified, the schema will be deleted with all objects contained in the schema.
Example:

DROP SCHEMA CBT_INC CASCADE;

Format:

          CREATE SCHEMA schema_name [user_id]
                [DEFAULT CHARACTER SET char_set_name];

A schema is a set of database objects that is owned by a user called a schema owner.
The schema owner has authority to give privileges to other users.
Example:

          CREATE SCHEMA CBT_INC USER1;

> All RDBMS vendors have their own parameters for defining a schema.

When you use the CREATE TABLE statement, you are the table’s owner with SELECT, INSERT, UPDATE, DELETE, and any other privileges allowed by your RDBMS system.You can not use the CREATE VIEW statement without having at least a SELECT privilege on each of the source tables for the view, so you automatically have the SELECT privilege on the view. If you already have the INSERT, UPDATE, or DELETE privileges on the source or base table, then these privileges are automatically given to you on the view by the RDBMS system.

There are other complications that are illustrated in the examples in our course.

Format:

          DROP ALIAS alias_name;

                                     or

          DROP SYNONYM synonym_name;

This command will remove the alias or synonym.
Example:

          DROP SYNONYM PILOTS;

> Aliases and synonyms are not an SQL standard.

> DB2, Informix, and Oracle implement ALIAS and SYNONYM.

> MySQL does not implement ALIAS or SYNONYM.

Format:

          CREATE [PUBLIC | PRIVATE] ALIAS alias_name
             FOR {table_name | view_name};

or

          CREATE [PUBLIC | PRIVATE] SYNONYM synonym_name
             FOR {table_name | view_name};

An alias or synonym produces another name for a table or view.
The normal use of an alias or symonym is to avoid having to qualify another user’s table or view.
An alias or synonym simplifies the SQL statement and make it appear to be your own table.

A PRIVATE alias or synonym can only be accessed by the creator of the alias or synonym.
If unspecified, PRIVATE is the default.

A PUBLIC alias or synonym is accessible to all users of the database.
Normally only a Database Administrator or a privileged security user can create with PUBLIC.

Example:

          CREATE SYNONYM PILOTS FOR USER1.PILOT_TBL;

> Aliases and synonyms are not an SQL standard.

> DB2, Informix, and Oracle implement ALIAS and SYNONYM.

> MySQL does not implement ALIAS or SYNONYM.

DB2, Oracle, PostgreSQL Format:

          DROP INDEX index_name;

Access, MySQL Format:

          DROP INDEX index_name
            ON table_name;

SQL Server Format:

          DROP INDEX table_name.index_name;

Because an index is completely separate from its associated table, it can be deleted at any time without any effect on the base table other than performance issues. All SQL code will continue to function normally.

Most RDBMS systems have tools for measuring performance. If a table is small, or the data is infrequently referenced, you may choose to remove the index.

The SQL standard does not mention indices so vendors do their own implementation.

Format:

          CREATE {UNIQUE} INDEX index_name
              ON table_name (
                   column_name1 {ASC | DESC},
                   column_name2 {ASC | DESC},
                   ...);

A simple index contains only a single column.

A composite index contains multiple columns from a single table.
The index applies to all the columns in the index definition.
These columns do not have to be adjacent in the table.
Instead, they can be taken from any column in any order.
The order in the index definition is important and is defined left to right, going from major to minor sort order.

Your can create multiple indices on the same table.

ASC means ascending sort order, which is the default and does not need to be specified.
DESC means descending sort order and must be specified to reverse the sequence.
UNIQUE forces the combination of indexed columns to be unique.
> Access, MySQL, and SQL Server index names must be unique within a table.

> DB2, Oracle, and PostgreSQL index names must be unique within the database.