Database Users and Their Privileges 

Database administrators can create additional database users and grant privileges.

Prerequisites

You require the demo data for the SQL Tutorial.

Start the query tool SQL Studio as database administrator MONA with password RED and log on to the demo database instance DEMODB.

Creating Database Users

Use the CREATE USER statement to create additional database users.

 

CREATE USER david PASSWORD blue RESOURCE

The database administrator MONA has created the database user DAVID with the password BLUE. The database user class RESOURCE has been defined for this database user.

 

CREATE USER boris PASSWORD pink STANDARD

The database administrator MONA has created the database user BORIS with the password PINK. The database user class STANDARD has been defined for this database user.

 

See also:

SQL Reference Manual, CREATE USER Statement (create_user_statement)

 

Changing the Database User Class

Use the ALTER USER statement to change the database user class.

 

ALTER USER boris RESOURCE

The database user class RESOURCE is defined for the database user BORIS.

See also:

SQL Reference Manual, ALTER USER Statement (alter_user_statement)

 

Changing the Password

Use the ALTER PASSWORD statement to change the password of a database user.

●     Log on as the database system administrator (default values: DBADMIN/SECRET).
ALTER PASSWORD boris red
The password of the database user BORIS is changed to RED.

You can also use the ALTER PASSWORD statement as follows:

●     Log on as the database user BORIS (BORIS/RED).
ALTER PASSWORD red TO pink
The password of the database user BORIS is changed to PINK.

See also:

SQL Reference Manual, ALTER PASSWORD Statement (alter_password_statement)

 

Granting Privileges

Open a database session for the database administrator MONA and use the GRANT statement to grant privileges.

 

GRANT SELECT, UPDATE, DELETE, INSERT ON hotel.customer TO david

The database administrator MONA administers the customer table in the HOTEL schema. MONA decides that the RESOURCE user DAVID is to be entitled to maintain customer data but not to make any changes to the structure of the table.

 

GRANT SELECT ON hotel.customer TO PUBLIC

All of the database users in the system are to be granted read access to the data in the customer table. You can use the keyword PUBLIC to grant privileges to all database users.

 

Check the privileges granted, on the basis of the information in the TABLEPRIVILEGES system table, for example. Log on to the database instance DEMODB as user DAVID.

SELECT * FROM DOMAIN.TABLEPRIVILEGES
  WHERE schemaname = 'HOTEL' AND tablename = 'CUSTOMER'

Result

SCHEMANAME	OWNER	TABLENAME	GRANTOR	GRANTEE	PRIVILEGE	IS_GRANTABLE
HOTEL	MONA	CUSTOMER	MONA	DAVID	SELECT	NO
HOTEL	MONA	CUSTOMER	MONA	DAVID	UPDATE	NO
HOTEL	MONA	CUSTOMER	MONA	DAVID	INSERT	NO
HOTEL	MONA	CUSTOMER	MONA	DAVID	DELETE	NO
HOTEL	MONA	CUSTOMER	MONA	PUBLIC	SELECT	NO

 

See also:

SQL Reference Manual, GRANT Statement (grant_statement)

Evaluating System Tables, TABLEPRIVILEGES

 

Revoking Privileges

Open a database session for the database administrator MONA and use the REVOKE statement to revoke privileges.

 

REVOKE DELETE ON customer FROM david

The RESOURCE user DAVID is not to be permitted to delete rows from the customer table.

 

See also:

SQL Reference Manual, REVOKE Statement (revoke_statement)

 

Deleting Database Users

To delete database users, use the DROP USER statement.

 

DROP USER boris

If no cascade option or the CASCADE option CASCADE is specified, all the synonyms and tables of the user to be deleted, as well as all indexes, privileges, view tables, and so on based on these objects, are deleted together with the database user.

 

See also:

SQL Reference Manual, DROP USER Statement (drop_user_statement)

More examples for Authorization