SQL AUTHORIZATION

What is SQL AUTHORIZATION?

Specifies the user or role that will own the database object being created, most commonly a schema or view.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL AUTHORIZATION:

SQL AUTHORIZATION Full Explanation

AUTHORIZATION is a reserved SQL keyword used as a clause inside certain Data Definition Language (DDL) statements to assign ownership of the newly created object to a specific database principal (user or role). In Standard SQL and in several major database engines, AUTHORIZATION appears chiefly in CREATE SCHEMA and CREATE VIEW statements.1. CREATE SCHEMA – When you supply AUTHORIZATION, the schema is created under the named principal, regardless of the login that executed the statement, provided the caller has sufficient rights. This allows a database administrator to create schemas for other users without switching sessions.2. CREATE VIEW – In the SQL standard, AUTHORIZATION can also appear in CREATE VIEW to record who is considered the definer of the view for privilege-checking. (Support varies by product.)AUTHORIZATION does not grant privileges; it simply sets ownership. Owners implicitly receive full privileges on their objects. Dropping or altering the object later is subject to normal ownership rules.Caveats:- The specified principal must already exist.- In most systems, only superusers or principals with CREATE ANY SCHEMA (or equivalent) can create a schema owned by someone else.- Confusing AUTHORIZATION with GRANT leads to incorrect security assumptions; GRANT is still needed to let others use the object.- Some dialects (e.g., PostgreSQL) limit AUTHORIZATION use to CREATE SCHEMA, while others (e.g., SQL Server) also allow it in CREATE SCHEMA ... AUTHORIZATION and ALTER AUTHORIZATION statements.

SQL AUTHORIZATION Syntax

-- CREATE SCHEMA
CREATE SCHEMA [schema_name] AUTHORIZATION principal_name;

-- CREATE VIEW (standard SQL, limited engine support)
CREATE VIEW view_name AS
SELECT ...
AUTHORIZATION principal_name;

SQL AUTHORIZATION Parameters

schema_name - STRING Optional in many dialects. Name of the schema being created. If omitted, the principal name is often reused.

Example Queries Using SQL AUTHORIZATION

-- Create a schema owned by the role analytics
CREATE SCHEMA reporting AUTHORIZATION analytics;

-- In SQL Server, create a schema without naming it separately
CREATE SCHEMA AUTHORIZATION app_user;

-- (Standard SQL) Create a view owned by a specific user
CREATE VIEW monthly_sales AS
SELECT customer_id, SUM(amount) AS total
FROM sales
GROUP BY customer_id
AUTHORIZATION sales_mgr;

Expected Output Using SQL AUTHORIZATION

A new schema (or view) is created
Its owner column in the system catalog is set to the specified principal
That principal receives implicit full control over the object

Use Cases with SQL AUTHORIZATION

Pre-creating schemas for application roles during database provisioning.
Separating ownership from deployment account to enforce least privilege.
Migrating objects between owners without changing the executing login.
Ensuring views run with definer rights in engines that honor CREATE VIEW ... AUTHORIZATION.

Common Mistakes with SQL AUTHORIZATION

Using AUTHORIZATION as a standalone statement – it must be part of another DDL command.
Assuming AUTHORIZATION grants usage rights to other users – it only sets ownership.
Misspelling or referencing a non-existent principal, which raises an error.
Forgetting required privileges; ordinary users cannot assign ownership to others without elevated rights.

First Introduced In

SQL-92

Frequently Asked Questions

What privileges are required to use AUTHORIZATION?

Typically you need elevated rights: database owner, superuser, or CREATE ANY SCHEMA permission. Without these, specifying another principal raises an error.

Does AUTHORIZATION automatically grant privileges to others?

No. The clause only sets the owner. To let additional users use the schema or view, issue explicit GRANT statements.

Can I change the owner later?

Yes, many dialects offer ALTER AUTHORIZATION or ALTER SCHEMA ... TRANSFER OWNERSHIP commands. You must be the current owner or a superuser.

Is AUTHORIZATION supported in MySQL?

No. MySQL ignores this clause or raises a syntax error. Use GRANT and ALTER statements instead to manage ownership.