TA的每日心情 | 开心 2016-11-23 21:13 |
---|
签到天数: 3 天 连续签到: 1 天 [LV.2]偶尔看看I
注册会员
- 积分
- 66
|
网络收集的资料,不齐全,供参考,如有大家有知道,请补充。
数据库管理问题汇总:
1.如何登录数据库,使用用户名。
使用SQL tools工具登录, 需要事先安装ODBC驱动。
2.更改密码是否会影响系统使用。
不影响,QAD系统使用并不会调用该账号。
3.如何新增用户
4.如何授予ODBC权限。
5.如何授予只读权限
6.如何定义密码策略。
1.1新建数据库用户。
CREATE USER 'mfg', 'qadeng'
1.2 查看数据库用户名,查询系统数据库用户清单
SELECT * FROM pub."_user"
select "_userid", "_password", "_user-name" from pub."_user"
1.3查看数据库用户授权的角色。
SELECT * FROM sysprogress.SYSDBAUTH
1.4 删除用户。不影响后台用户的原先赋予的角色(保留不变)。
DROP USER 'mfg';
1.5 授予用户某个角色
grant DBA to 'mfg'
GRANT SELECT ON pub."pod_det" TO 'dangdang'
1.6 将用户的角色给予删除。
REVOKE DBA FROM 'mfg'
1.7 查看数据库每个表的权限
SELECT * FROM pub."_File"
1.8 查看数据表各个表达授权情况
SELECT "_File-Name", "_Tbl-Type", "_Tbl-Status", "_Can-Create", "_Can-Read" FROM pub."_File"
SELECT "_File-Name", "_Tbl-Type", "_Tbl-Status", "_Can-Create", "_Can-Read", "_Owner" FROM pub."_File" WHERE "_Tbl-Type" <> 't'
SELECT "_File-Name", "_Tbl-Type", "_Tbl-Status", "_Can-Create", "_Can-Read" FROM pub."_File" WHERE "_Tbl-Type" = 's'
select "_File-name", "_Can-Read", "_can-WRITE" from pub."_file" WHERE "_file-name" = 'pod_det'
1.9查询数据表具体明细
SELECT "pod_nbr", "pod_due_date", "pod_line" FROM PUB."pod_det";
2.修改用户密码有2种方法,
2.1 修改自己的密码使用下列命令,同时输入新旧2个密码。
Any user can change their own password. For example, user "Jasper" can change his existing password
from 'spaniel' to 'NewPassword' by executing the following USER SQL statement:
ALTER USER 'Jasper', 'spaniel', 'NewPassword'
2.2管理员修改他人的密码,可以采用先删除用户,再新建用户实现。
A DBA can change another user password ONLY by deleting and re-creating the user's record in the _User table.
For example, the DBA may change the password of user 'Jasper' from 'spaniel' to 'NewPassword' by executing the following two SQL statements:
DROP USER 'Jasper'
CREATE USER 'Jasper', 'NewPassword‘
3.如何使用grant 命令给用户授权,有2种方式,一个授予角色权限,另一个是按每个数据表授权。
The GRANT function can set various privileges to a specified users or group of users within a database.
There are two forms of the GRANT statement:
Grant database-wide privileges, such as system administration (DBA), general creation (RESOURCE), audit administration (AUDIT_ADMIN), audit archive (AUDIT_ARCHIVE), or audit insert (AUDIT_INSERT).
Grant various privileges on specific tables and views. Privilege definitions are stored in the system tables SYSDBAUTH, SYSTABAUTH, and SYSCOLAUTH for the database, tables, and columns, respectively.
Note: It is necessary to separate commands to grant DBA or RESOURCE privileges with any of the AUDIT privileges.
Using the same command to grant a user with DBA or RESOURCE privileges and any of the AUDIT privileges results in an error.
General syntax:
GRANT { RESOURCE, DBA, AUDIT_ADMIN, AUDIT_ARCHIVE, AUDIT_INSERT } TO username [ , username ] , ... [ WITH GRANT OPTION ];
Example:
grant RESOURCE, DBA to myuser;
General syntax to grant specific privileges on a table within the database:
GRANT { privilege [, privilege ] , ... | ALL [ PRIVILEGES] } ON table_name TO { username [, username ] , ... | PUBLIC } [ WITH GRANT OPTION ] ;
-------------------------------------------------------------------------------------
|
|