Database Users and Their Privileges

Database administrators can create additional database users and grant privileges.

Prerequisites

You can use the demo data for the SQL tutorial. Start the Database Studio as database administrator MONA with the password RED and log on to demo database DEMODB: Logging On to a Database.

Activities

You can use Database Studio to enter and execute SQL statements. More information: Working with SQL Statements: Overview

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 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`

Revoking Privileges

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

REVOKE DELETE ON hotel.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 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 Information

More examples for Authorization