This chapter describes PointBase security and privileges. Schemas are an integral part of security in PointBase. When creating a PointBase user, they do not have any access privileges to schemas or other data objects within the database. The PointBase RDBMS only permits the schema owner to grant privileges to the schema and data objects within the schema. The schema owner can grant privileges to the following data objects in the schema: Show
Table 1 describes the privileges that the schema owner can grant users for tables and columns: Table 1 : User Privileges for Tables and Columns
Granting and Revoking PrivilegesWhen a PointBase database is first created the only user is the default user PUBLIC with a password of PUBLIC. The PUBLIC user owns the default PUBLIC schema. For security reasons, PointBase does not recommend using this schema to store sensitive data. Like any PointBase user, PUBLIC must be granted the appropriate privileges to access data objects in schema owned by other users. The PUBLIC user can be used initially to create new users and new schema. The PUBLIC user will own any new schema that it creates unless otherwise specified during schema creation. New users are then able to create their own new schema and users, and grant appropriate privileges on schema that they own. All new users must be granted privileges to use the PUBLIC schema if this is required. To grant the ability for a user to pass a privilege on to other users once granted, you must specify the optional WITH GRANT OPTION qualifier when granting the privilege. GRANT Statement SyntaxGRANT privilege-list ON object TO user-list [ WITH GRANT OPTION ] Use the GRANT statement to grant privileges on a data object. The following describes the GRANT statement syntax. Privilege-list Syntaxprivilege [ , privilege [ , privilege ]...] | ALL PRIVILEGES Privilege SyntaxSELECT [ ( column-name [ , column-name ]...)] | DELETE | INSERT [ ( column-name [ , column-name ]...)] | UPDATE [ ( column-name [ , column-name ]...)] |
REFERENCES [ ( column-name [ , column-name ]...)] | TRIGGER [ ( column-name [ , column-name ]...)] | EXECUTE Usage Notes
Object Syntax[ TABLE ] table-name |SPECIFIC routine_type
specific_routine-name |routine_type routine_name (parameter_types_list) [ TRIGGER ] trigger-name Usage Notes
User-list Syntaxuser [ , user ]... [WITH GRANT OPTION] | PUBLIC Usage Notes
Examples
GRANT SELECT ON customer_tbl TO marketing_mgr;
GRANT DELETE,INSERT,UPDATE ON discount_code_tbl TO financial_mgr WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON sales_rep_data_tbl TO hr_mgr REVOKE Statement SyntaxREVOKE [ GRANT OPTION FOR ] privilege_list ON object FROM user_name [ RESTRICT | CASCADE ] The REVOKE statement takes privileges away from users. The arguments are similar to the GRANT statement. The major difference is the additional RESTRICT or CASCADE keyword and the GRANT OPTION FOR clause. The following describes the optional clauses GRANT OPTION FOR and RESTRICT or CASCADE. NOTE: If none of the privileges that you are trying to revoke actually exist, an error is raised. RESTRICT | CASCADEIf you use RESTRICT keyword, the privilege will be revoked only from the specified user. If the specified user granted had the WITH GRANT OPTION and granted the same privilege to other users, they will retain the privilege. If you use CASCADE, it will revoke the privilege and any dependent privileges as a result of your grant. A dependent privilege is one that could exist, if you granted the privilege that you're trying to revoke, which is what you are trying to achieve as a result of your REVOKE statement. If the optional RESTRICT or CASCADE keywords are not used, PointBase uses RESTRICT by default. GRANT OPTION FORIf he optional GRANT OPTION FOR clause is used, the WITH GRANT OPTION right is revoked. The actual privilege itself is not revoked. the GRANT OPTION is revoked. CASCADE and RESTRICT may be used in the same way as a normal REVOKE statement. What is user access privileges to a database?To GRANT ALL privileges to a user , allowing that user full control over a specific database , use the following syntax: mysql> GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';
What is used to control privileges in database?MySQL uses the db table to determine which database a user can access and from which host. A privilege granted at the database level in the db table applies to the database and all objects belong to that database e.g., tables, triggers, views, stored procedures, etc.
How do you add privileges to a database?If we want to grant all the privileges of all the databases to the newly created user then we will execute the following command. GRANT ALL PRIVILEGES ON *. * TO 'maadi'@'localhost'; To check the privileges granted to the user we can check by the command.
Which SQL statement grants a privilege to all the database users?You can use the SQL GRANT statement to grant SQL SELECT, UPDATE, INSERT, DELETE, and other privileges on tables or views. The WITH GRANT OPTION clause indicates that JONES can grant to other users any of the SQL privileges you granted for the ORDER_BACKLOG table.
|