游泳123456789 2017-08-16 09:32 采纳率: 0%
浏览 1484

postgre数据库,MD5加密盐值.sql

/*
2017.8.7(yi)~2017.8.15(er)

--注释
COMMENT ON COLUMN public.kq_users.user_display_name IS '显示名称';

--权限
GRANT ALL ON TABLE public.kq_users TO xfacedata;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE public.kq_users TO r_xfacedata_qry;
GRANT SELECT ON TABLE public.kq_users TO xfacektl;

--建表
drop table users;

CREATE TABLE users (
user_id character varying(32) NOT NULL,
user_name character varying(200) NOT NULL,
user_display_name character varying(200) NOT NULL,
password character varying(200) NOT NULL,
salt_value character varying(50) NOT NULL,
PRIMARY KEY (user_id)
)

-- 更新字段值
update kq_users set user_display_name = '120' where user_id = '120';

*/

--CRUD 表中字段
alter table kq_users add COLUMN user_display_name varchar(200);

-- false
-- alter table kq_users add COLUMN userDisplayName varying(200);

alter table kq_users rename column userDisplayName to user_display_name;

alter table kq_users add COLUMN salt_value varchar(100);

alter table kq_users rename column id to user_id;

alter table kq_users rename column user_id to id;

alter table kq_users add COLUMN id varchar(32);

-- 删除字段
alter table Test drop COLUMN BazaarType;

alter table kq_users drop COLUMN id;
alter table kq_users drop COLUMN salt_value;
alter table kq_users drop COLUMN user_display_name;

/*
MD5 加密,盐值;
--salt_value 1440345394530213
select md5('admin'||'1440345394530213');
"151ea22fe214a4b37783a3c2fcf393e7"

select md5('sys'||'1440345394530213');
"9ba01cc258bc33d0f50177553d905f73"

insert into kq_users(user_id, user_name, user_display_name, user_pwd, salt_value) values('11','admin1','管理员1','151ea22fe214a4b37783a3c2fcf393e7','1440345394530213');
insert into kq_users(user_id, user_name, user_display_name, user_pwd, salt_value) values('22','sys1','系统管理员1','9ba01cc258bc33d0f50177553d905f73','1440345394530213');

delete from kq_users where user_id='11';
delete from kq_users where user_id='22';

--ok
select t.user_id userId, t.user_name userName,t.user_pwd userPwd, t.user_display_name userDisplayName,t.salt_value salt from kq_users as t where
t.user_pwd=md5(('admin'||'1440345394530213')) and
t.user_name='admin1';
--ok
select t.user_id userId, t.user_name userName,t.user_pwd userPwd, t.user_display_name userDisplayName,t.salt_value salt from kq_users as t where
t.user_pwd=md5(('sys'||'1440345394530213')) and
t.user_name='sys1';

*/

--加注释
COMMENT ON COLUMN public.kq_users.salt_value IS 'md5加密盐值';

limit 1 offset 0;
select * from kq_users where user

select * from kq_users;

  • 写回答

1条回答 默认 最新

  • devmiao 2017-08-16 15:55
    关注
    评论

报告相同问题?

悬赏问题

  • ¥100 关于使用MATLAB中copularnd函数的问题
  • ¥20 在虚拟机的pycharm上
  • ¥15 jupyterthemes 设置完毕后没有效果
  • ¥15 matlab图像高斯低通滤波
  • ¥15 针对曲面部件的制孔路径规划,大家有什么思路吗
  • ¥15 钢筋实图交点识别,机器视觉代码
  • ¥15 如何在Linux系统中,但是在window系统上idea里面可以正常运行?(相关搜索:jar包)
  • ¥50 400g qsfp 光模块iphy方案
  • ¥15 两块ADC0804用proteus仿真时,出现异常
  • ¥15 关于风控系统,如何去选择