Database administrators can create additional database users and grant privileges.
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.
You can use Database Studio to enter and execute SQL statements. More information: Working with SQL Statements: Overview
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)
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)
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)
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 |
See also:
SQL Reference Manual, GRANT Statement (grant_statement)
Evaluating System Tables, TABLEPRIVILEGES
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)
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 examples for Authorization