Entering content frame

This graphic is explained in the accompanying text Database Users and Their Privileges Locate the document in its SAP Library structure

Database administrators can create further database users and assign privileges.

Creating Database Users

Open a database session for database administrator MONA and use the CREATE USER statement to create further database users.

 

CREATE USER david PASSWORD blue RESOURCE

Database administrator MONA has created the database user DAVID with the password BLUE. The RESOURCE user class has been assigned to this database user.

 

CREATE USER boris PASSWORD pink STANDARD

Database administrator MONA has created the database user BORIS with the password PINK. The STANDARD user class has been assigned to this database user.

 

See also:

CREATE USER Statement (create_user_statement)

 

Changing the User Class

Open a database session for database administrator MONA and use the ALTER USER statement to change the user class.

 

ALTER USER boris RESOURCE

The user class RESOURCE is assigned to database user BORIS.

See also:

ALTER USER Statement (alter_user_statement)

 

Changing the Password

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

·           Log on as the database system administrator (DBA/DBA).
ALTER PASSWORD boris red
The password for database user BORIS is changed to RED.

or

·        Log on as database user BORIS.
ALTER PASSWORD pink TO red
The password for database user BORIS is changed to RED.

See also:

ALTER PASSWORD Statement (alter_password_statement)

 

Assigning Privileges

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

 

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

Database user MONA manages the customer table. He or she decides to authorize RESOURCE user DAVID to maintain customer data but not to change the structure of the table.

 

GRANT SELECT ON customer TO PUBLIC

All of the database users are allowed read the data in the customer table. You can grant this privilege to all database users by using the PUBLIC keyword.

 

Check the privileges using the information in the TABLEPRIVILEGES system table, for example.

SELECT * FROM tableprivileges
  WHERE owner = 'MONA' AND tablename = 'CUSTOMER'

Result

OWNER

TABLE NAME

GRANTOR

GRANTEE

PRIVILEGE

IS_GRANTABLE

MONA

CUSTOMER

MONA

MONA

SELECT

YES

MONA

CUSTOMER

MONA

MONA

UPDATE

YES

MONA

CUSTOMER

MONA

MONA

DELETE

YES

MONA

CUSTOMER

MONA

MONA

ALTER

YES

MONA

CUSTOMER

MONA

MONA

INDEX

YES

MONA

CUSTOMER

MONA

MONA

REFERENCES

YES

MONA

CUSTOMER

MONA

PUBLIC

SELECT

NO

MONA

CUSTOMER

MONA

DAVID

SELECT

NO

MONA

CUSTOMER

MONA

DAVID

UPDATE

NO

MONA

CUSTOMER

MONA

DAVID

INSERT

NO

MONA

CUSTOMER

MONA

DAVID

DELETE

NO

 

See also:

GRANT Statement (grant_statement)

 

Revoking Privileges

You use the REVOKE statement to revoke privileges.

 

REVOKE DELETE ON customer FROM david

RESOURCE user DAVID is no longer authorized to delete rows in the customer table.

 

See also:

REVOKE Statement (revoke_statement)

 

Deleting Database Users

Use the DROP USER statement to delete database users.

 

DROP USER boris

If you do not specify the CASCADE option or if you specify the CASCADE value for the CASCADE option, all synonyms and tables belonging to the user to be deleted as well as all the indexes, privileges, view tables, and so on, that are based on these are also deleted with the database user.

 

See also:

DROP USER Statement (drop_user_statement)

 

Leaving content frame